Beginning SQL Server 2012 Administration

429 Pages·2012·11.59 MB·English

by  Rob Walters

Beginning SQL Server
2012 Administration

■ ■ ■
Rob Walters
Grant Fritchey
Beginning SQL Server Administration
Copyright © 2012 by Rob Walters, Grant Fritchey
This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the material is
concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting,
reproduction on microfilms or in any other physical way, and transmission or information storage and retrieval,
electronic adaptation, computer software, or by similar or dissimilar methodology now known or hereafter
developed. Exempted from this legal reservation are brief excerpts in connection with reviews or scholarly analysis or
material supplied specifically for the purpose of being entered and executed on a computer system, for exclusive use
by the purchaser of the work. Duplication of this publication or parts thereof is permitted only under the provisions
of the Copyright Law of the Publisher's location, in its current version, and permission for use must always be
obtained from Springer. Permissions for use may be obtained through RightsLink at the Copyright Clearance Center.
Violations are liable to prosecution under the respective Copyright Law.
ISBN978-1-430 2-3981-9
ISBN978-1-4302-3982-6(eBook)
Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every
occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion
and to the benefit of the trademark owner, with no intention of infringement of the trademark.
The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not
identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to
proprietary rights.
While the advice and information in this book are believed to be true and accurate at the date of publication,
neither the authors nor the editors nor the publisher can accept any legal responsibility for any errors or
omissions that may be made. The publisher makes no warranty, express or implied, with respect to the
material contained herein.
President and Publisher: Paul Manning
Lead Editor: Jonathan Gennick
Developmental Editor: Jonathan Gennick, Douglas Pundick
Technical Reviewer: Matt Eddinger
Editorial Board: Steve Anglin, Ewan Buckingham, Gary Cornell, Louise Corrigan, Morgan Ertel, Jonathan
Gennick, Jonathan Hassell, Robert Hutchinson, Michelle Lowman, James Markham, Matthew Moodie,
Jeff Olson, Jeffrey Pepper, Douglas Pundick, Ben Renow-Clarke, Dominic Shakeshaft, Gwenan
Spearing, Matt Wade, Tom Welsh
Coordinating Editor: Brent Dubi
Copy Editor: Heather Lang
Production Support: Patrick Cunningham
Indexer: SPi Global
Artist: SPi Global
Cover Designer: Anna Ishchenko
Distributed to the book trade worldwide by Springer Science+Business Media New York, 233 Spring Street, 6th
Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax [phone], e-mail [email protected],
or visit www.springeronline.com.
For information on translations, please e-mail [email protected], or visit www.apress.com.
Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use. eBook
versions and licenses are also available for most titles. For more information, reference our Special Bulk Sales–
eBook Licensing web page at www.apress.com/bulk-sales.
Any source code or other supplementary materials referenced by the author in this text is available to readers
at www.apress.com. For detailed information about how to locate your book’s source code, go to
www.apress.com/source-code/.
Contents at a Glance
About the Authors ..................................................................................................... xv

About the Technical Reviewer ................................................................................. xvi
Acknowledgments .................................................................................................. xvii
Introduction ........................................................................................................... xviii
■Chapter 1: The Database Administration Profession ............................................... 1
■Chapter 2: Planning and Installing SQL Server 2012 ............................................. 13
■Chapter 3: What’s in the Toolbox? ......................................................................... 43
■Chapter 4: SQL Server Development ...................................................................... 77
■Chapter 5: Creating Tables and Other Objects ..................................................... 105
■Chapter 6: Transact-SQL ...................................................................................... 125
■Chapter 7: SQL Server Internals .......................................................................... 147
■Chapter 8: Database Backup Strategies .............................................................. 157
■Chapter 9: Database Restore Strategies .............................................................. 185
■Chapter 10: Common Database Maintenance Tasks ........................................... 213
■Chapter 11: SQL Server Security ......................................................................... 249
■Chapter 12: Auditing, Encryption, and Compliance ............................................. 271
■Chapter 13: Performance Tuning and Optimization ............................................ 299
■Chapter 14: Fault Tolerance ................................................................................ 339
■Chapter 15: Cloud Computing for the DBA ........................................................... 365
■Chapter 16: SQL Server Private Cloud ................................................................. 387
■Chapter 17: Where to Go Next? ............................................................................ 401
Index ....................................................................................................................... 407
iii
Contents
About the Authors ..................................................................................................... xv

About the Technical Reviewer ................................................................................. xvi
Acknowledgments .................................................................................................. xvii
Introduction ........................................................................................................... xviii

■Chapter 1: The Database Administration Profession ............................................... 1
Why Do We Need a DBA? Access Runs Great! ................................................................... 1
What Exactly Does a DBA Do Anyway? .............................................................................. 2
Database Maintenance ............................................................................................................................. 2
Security and Regulatory Compliance........................................................................................................ 3
Disaster Recovery ..................................................................................................................................... 3
Design and Performance Improvement .................................................................................................... 3
Documentation ......................................................................................................................................... 4
Salary Information ............................................................................................................. 4
Words from Real-World DBAs ............................................................................................ 4
Grant Fritchey, Principal DBA ................................................................................................................... 5
Roman Rehak, Principal Database Architect ............................................................................................ 7
Charlie Pyne, Senior DBA .......................................................................................................................... 8
Ed Clapper, Database Product Architect Manager ................................................................................... 9
Michael Esposito, Senior Technical Consultant and DBA ....................................................................... 11
Summary ......................................................................................................................... 12
iv
■ CONTENTS
■Chapter 2: Planning and Installing SQL Server 2012 ............................................. 13
Understanding the Editions .............................................................................................. 14
Demystifying 32-Bit and 64-Bit Architectures ........................................................................................ 14
Server Editions ....................................................................................................................................... 14
SQL Server Terminology ......................................................................................................................... 16
Planning Your Installation ................................................................................................ 17
Assessing the Environment .................................................................................................................... 17
Choosing the Right Edition ..................................................................................................................... 18
Validating Hardware and Software Requirements ................................................................................. 19
Installing SQL Server Evaluation Edition .......................................................................... 19
Step 1: Downloading SQL Server Evaluation Edition .............................................................................. 19
Step 2: Understanding the SQL Server Installation Center ..................................................................... 20
Step 3: Installing an Instance of SQL Server .......................................................................................... 28
Upgrading SQL Server...................................................................................................... 37
Summary ......................................................................................................................... 40
■Chapter 3: What’s in the Toolbox? ......................................................................... 43
Connecting to SQL Server ....................................................................................................................... 43
Issuing Queries Using SSMS .................................................................................................................. 46
Managing Multiple Servers ..................................................................................................................... 53
Monitoring Server Activity ...................................................................................................................... 55
Other Tools from the Start Menu ..................................................................................... 61
Analysis Services Folder ......................................................................................................................... 63
Configuration Tools Folder ...................................................................................................................... 63
Documentation and Community Folder .................................................................................................. 68
Integration Services Folder ..................................................................................................................... 68
Performance Tools Folder ...................................................................................................................... 69
Import and Export Data Wizard ............................................................................................................... 70
SQL Server Data Tools ............................................................................................................................ 70
v
■ CONTENTS
Command-Line Tools ....................................................................................................... 70
SQLCMD .................................................................................................................................................. 71
PowerShell Provider for SQL Server ....................................................................................................... 73
Summary ......................................................................................................................... 74
■Chapter 4: SQL Server Development ...................................................................... 77
Touring SSMS Through the Eyes of a Developer ............................................................. 77
IntelliSense ............................................................................................................................................. 77
Query Designer ....................................................................................................................................... 78
Templates ............................................................................................................................................... 81
Debugging in T-SQL ................................................................................................................................ 84
Data-Tier Applications ..................................................................................................... 85
Defining a Data-Tier Application ............................................................................................................. 85
Deploying a Data-Tier Application .......................................................................................................... 89
Registering an Existing Database as a Data-Tier Application................................................................. 90
Monitoring a Data-Tier Application Using the SQL Server Utility ............................................................ 90
SQL Server Developer Tools ............................................................................................ 95
Working with SSDT in Connected Mode ................................................................................................. 96
Working with SSDT in Offline Mode ........................................................................................................ 99
Application Versioning .......................................................................................................................... 102
SQL Server LocalDB ....................................................................................................... 102
Summary ....................................................................................................................... 103
■Chapter 5: Creating Tables and Other Objects ..................................................... 105
Navigating the Object Explorer Tree .............................................................................. 105
Scripting the Actions of an SSMS Dialog Box ................................................................ 106
Transact-SQL (T-SQL) Primer ........................................................................................ 109
Data Definition Language (DDL) ............................................................................................................ 109
Data Manipulation Language (DML) ..................................................................................................... 109
Data Control Language (DCL) ................................................................................................................ 110
vi
■ CONTENTS
Creating Tables .............................................................................................................. 110
Creating Tables from the Table Designer ............................................................................................. 111
Issuing the CREATE TABLE Statement .................................................................................................. 112
Altering Tables ............................................................................................................... 114
Adding Constraints ........................................................................................................ 114
NULL Constraints .................................................................................................................................. 114
CHECK Constraints ................................................................................................................................ 114
Primary Keys and Unique Constraints .................................................................................................. 115
Foreign Key Constraints ....................................................................................................................... 116
Dropping Tables ............................................................................................................. 117
Creating Indexes ............................................................................................................ 119
Summary ....................................................................................................................... 123
■Chapter 6: Transact-SQL ...................................................................................... 125
The VetClinic Sample Database Revisited ..................................................................... 125
Data Types ..................................................................................................................... 126
Unicode vs. ANSI .................................................................................................................................. 127
Living with NULL ................................................................................................................................... 128
Data Manipulation Language ......................................................................................... 128
SELECT ................................................................................................................................................. 128
INSERT .................................................................................................................................................. 133
UPDATE ................................................................................................................................................. 135
DELETE ................................................................................................................................................. 135
Transactions .................................................................................................................. 136
Executing Transactions ........................................................................................................................ 136
Isolating Transactions .......................................................................................................................... 136
Deadlocks ............................................................................................................................................. 137
Stored Procedures ......................................................................................................... 139
Creating Stored Procedures Using Templates ...................................................................................... 140
vii
■ CONTENTS
Modifying Stored Procedures ............................................................................................................... 142
System Stored Procedures ................................................................................................................... 142
Functions ....................................................................................................................... 143
Creating a Function .............................................................................................................................. 143
Invoking a Function .............................................................................................................................. 143
Triggers ......................................................................................................................... 144
Summary ....................................................................................................................... 145
■Chapter 7: SQL Server Internals .......................................................................... 147
Databases ...................................................................................................................... 147
Master Database .................................................................................................................................. 147
Tempdb Database ................................................................................................................................. 147
Model Database .................................................................................................................................... 148
MSDB Database .................................................................................................................................... 148
Resource Database ............................................................................................................................... 149
Repairing Corrupt System Databases ............................................................................ 149
Writing Data to Disk ....................................................................................................... 149
SQL Server Services ...................................................................................................... 150
Single-User Mode .......................................................................................................... 152
Placing an Already-Started Database into Single-User Mode .............................................................. 153
Starting SQL Server in Single-User Mode ............................................................................................. 153
Summary ....................................................................................................................... 156
■Chapter 8: Database Backup Strategies .............................................................. 157
Defining the Types of Backups ...................................................................................... 157
Full Backups ......................................................................................................................................... 158
Differential Backups ............................................................................................................................. 163
File and Filegroup Backups .................................................................................................................. 164
Backing Up the Logs ...................................................................................................... 166
Initiating Fully Recovery Mode ............................................................................................................. 167
viii
■ CONTENTS
Log Backup Using Management Studio ................................................................................................ 168
Log Backup Using T-SQL ...................................................................................................................... 169
Backing Up a Copy of the Database ..................................................................................................... 169
Automating the Backup Process .................................................................................... 169
Encrypting Backup Files for Security ............................................................................. 179
Compressing Backups to Save Space ........................................................................... 180
Designing a Backup Plan ............................................................................................... 182
Summary ....................................................................................................................... 184
■Chapter 9: Database Restore Strategies .............................................................. 185
Restoring a Database from the Management Studio GUI .............................................. 186
Specifying Restore Options from the GUI ....................................................................... 192
Restoring a Database Using T-SQL ................................................................................ 194
Executing a Simple Restore .................................................................................................................. 194
Creating a Copy of a Database ............................................................................................................. 195
Retrieving Logical and Physical File Names ......................................................................................... 196
Displaying General Information About a Backup .................................................................................. 198
Cleaning Up .......................................................................................................................................... 200
Restoring Differential Backups ...................................................................................... 200
Restoring Log Backups .................................................................................................. 201
Restoring File Backups .................................................................................................. 205
Testing Backups ............................................................................................................ 207
Developing a Full Disaster Recovery Plan ..................................................................... 207
Gathering Information About the Business ........................................................................................... 208
Establishing the Disaster Plan .............................................................................................................. 209
Testing the Disaster Plan ...................................................................................................................... 210
Summary ....................................................................................................................... 211
ix
■ CONTENTS
■Chapter 10: Common Database Maintenance Tasks ........................................... 213
Backing Up and Restoring Databases ............................................................................ 213
Moving Data ................................................................................................................... 214
Moving a Database Using the “Detach and Attach” Method ................................................................ 214
Moving a Database Using the Copy Database Wizard .......................................................................... 216
Moving Data Using the Import and Export Wizard ................................................................................ 220
Moving Data Using the Bulk Copy Program (BCP) ................................................................................ 227
Checking Database Integrity .......................................................................................... 229
Maintaining Indexes....................................................................................................... 230
Detecting Fragmentation ...................................................................................................................... 231
Defragmenting Indexes ........................................................................................................................ 233
Distribution Statistics .................................................................................................... 234
Understanding Distribution Statistics ................................................................................................... 234
Updating Statistics ............................................................................................................................... 237
Creating Maintenance Plans .......................................................................................... 237
Scheduling Maintenance Tasks ..................................................................................... 244
Creating Log Files .......................................................................................................... 245
Summary ....................................................................................................................... 247
■Chapter 11: SQL Server Security ......................................................................... 249
Terminology ................................................................................................................... 249
Authentication ...................................................................................................................................... 249
Authentication Mode ............................................................................................................................ 250
Authorization ........................................................................................................................................ 250
Server Instance vs. the Database ......................................................................................................... 250
SQL Server Instance Security ........................................................................................ 252
Creating a SQL Server Login ................................................................................................................. 252
Server Roles ......................................................................................................................................... 253
Server Permissions ............................................................................................................................... 255
x