Beginning SQL Server 2008 Administration (eBook)

Rob Walters is a data platform technology specialist for Microsoft. His extensive experience with Microsoft SQL Server began over 10 years ago when he worked as a consultant for Microsoft Consulting Services in Denver, Colorado. Shortly after the dot-com bubble burst, Walters returned to Microsoft s headquarters and worked as a program manager in the SQL Server product unit. There, he owned various features within SQL Server including SQL Server Agent, various management features, and the security for the database engine. Walters has co-authored three patents for technologies used within SQL Server. Walters has also co-authored Programming Microsoft SQL Server 2005 by Microsoft Press, and Pro SQL Server 2005, Accelerated SQL Server 2008, and Beginning SQL Server 2008 Database Administration by Apress. He holds a bachelor's of science degree in electrical engineering from Michigan State University and a master's degree in business administration from Seattle University. When not at work, he is very active in his community as an on-call firefighter.

Contents at a Glance 4
Table of Contents 5
About the Authors 15
About the Technical Reviewer 17
Acknowledgments 18
Introduction 19
Who This Book Is For 20
How This Book Is Structured 20
Getting the Most from This Book 22
Errata 22
Contacting the Authors 22
Chapter 1: The Database Administration Profession 23
Why Do We Need a DBA? Access Runs Great! 23
What Exactly Does a DBA Do Anyway? 24
Database Maintenance 24
Security and Regulatory Compliance 25
Disaster Recovery 25
Design and Performance Improvement 25
Documentation 26
Salary Information 26
Words from Real-World DBAs 26
Grant Fritchey, Principal DBA 26
Roman Rehak, Principal Database Architect 28
Summary 30
Chapter 2: Planning and Installing SQL Server 2008 31
Understanding the Editions 31
Demystifying 32-Bit and 64-Bit Architectures 32
Server Editions 32
Enterprise Edition (x86, x64, and IA64) 33
Standard Edition (x86 and x64) 33
Specialized Editions 33
Developer Edition (x86, x64, and IA64) 33
Workgroup (x86 and x64) 33
Web (x86 and x64) 33
Express (x86 and x64) 34
Compact (x86 Only) 34
Evaluation (x86, x64, and IA64) 34
SQL Server Terminology 34
Planning Your Installation 34
Assessing the Environment 35
Choosing the Right Edition 37
Validating Hardware and Software Requirements 38
Installing SQL Server Evaluation Edition 38
Step 1: Downloading SQL Server Evaluation Edition 38
Step 2: Understanding the SQL Server Installation Center 39
Planning Tab 39
Installation Tab 42
Maintenance Tab 43
Tools Tab 44
Resources Tab 45
Advanced Tab 46
Options Tab 47
Step 3: Installing an Instance of SQL Server 48
Feature Selection Page 48
Instance Configuration Page 50
Disk Space Requirements Page 51
Server Configuration Page 51
Database Engine Configuration Page 52
Error and Usage Reporting Page 55
Installation Rules Page 55
Ready to Install Page 55
Upgrading SQL Server 57
A Word from the SQL Server Setup Team 60
Summary 61
Chapter 3: What’s in the Toolbox? 63
SQL Server Management Studio 63
Connecting to SQL Server 63
Issuing Queries Using SSMS 66
Object Explorer 67
Writing the Query 70
Document Windows 72
Results Pane 73
Managing Multiple Servers 73
Registered Servers 73
Queries Against Multiple Servers 75
Policy-Based Management 75
Monitoring Server Activity 75
Activity Monitor 76
Performance Data Collector 77
Reports 80
Other Tools from the Start Menu 81
Analysis Services Folder 82
Configuration Tools Folder 83
SQL Server Installation Center 83
SQL Server Error and Usage Reporting 83
Reporting Services Configuration Manager 85
SQL Server Configuration Manager 86
Documentation and Tutorials Folder 88
Microsoft SQL Server Samples Overview 88
SQL Server Books Online 89
SQL Server Tutorials 89
Integration Services Folder 89
Data Profiler Viewer 89
Execute Package Utility 89
Performance Tools Folder 90
Database Engine Tuning Advisor 90
SQL Server Profiler 90
Import and Export Data Wizard 90
Business Intelligence Development Studio (BIDS) 90
Command-Line Tools 91
SQLCMD 91
Connecting to SQL Server 91
Passing Variables 92
PowerShell Provider for SQL Server 93
Summary 94
Chapter 4: Creating Tables and Other Objects 96
Navigating the Object Explorer Tree 96
Scripting the Actions of an SSMS Dialog Box 97
Transact-SQL (T-SQL) Primer 100
Data Definition Language (DDL) 101
Data Manipulation Language (DML) 101
Data Control Language (DCL) 102
Creating Tables 103
Creating Tables from the Table Designer 103
Issuing the CREATE TABLE Statement 104
Altering Tables 106
Adding Constraints 106
NULL Constraints 106
CHECK Constraints 107
Primary Key and Unique Constraints 107
Foreign Key Constraints 108
Dropping Tables 109
Creating Indexes 111
Summary 115
Chapter 5: Transact-SQL 117
The VetClinic Sample Database Revisited 117
Data Types 118
Unicode vs. ANSI 119
Living with NULL 120
Data Manipulation Language 120
SELECT 120
INSERT 123
UPDATE 123
DELETE 124
Transactions 124
Execution 124
Transaction Isolation 125
Deadlocks 126
Stored Procedures 128
Creating Stored Procedures Using Templates 129
Modifying Stored Procedures 132
System Stored Procedures 132
Functions 133
Creating a Function 133
Invoking a Function 133
System-Defined Functions 134
Triggers 134
Summary 135
Chapter 6: SQL Server Internals 137
Databases 137
Master Database 137
Tempdb Database 137
Model Database 138
MSDB Database 138
Resource Database 139
Repairing Corrupt System Databases 139
Writing Data to Disk 139
SQL Server Services 140
Single-User Mode 142
Placing an Already-Started Database into Single-User Mode 142
Starting SQL Server in Single-User Mode 143
Summary 146
Chapter 7: Storage Management Strategies 147
Storage Systems 147
Storage System Interfaces 149
IDE ATA 149
SATA 149
SCSI 149
Serial Attached SCSI 149
Fibre Channel Direct Attached Storage 150
Fibre Channel Storage Area Network 150
iSCSI 150
InfiniBand 150
Storage System Types 150
Direct Attached Storage 150
Storage Area Network 151
Logical Unit Numbers (LUNs) 153
Network Attached Storage 153
Figure 7-3. NAS architecture 154
Disk Configuration: RAID 155
RAID 0 (Striping) 155
RAID 1 (Mirroring and Duplexing) 155
RAID 10 (Stripe of Mirrors) 156
RAID 5 (Striping with Parity) 157
Hardware and Software RAID 159
Selecting a Storage System for SQL Server 2008 159
I/O Performance 159
Redundancy Is Critical 161
Configuring Your Database 165
Data Compression 167
Disk Volume Alignment 169
Summary 169
Chapter 8: Database Backup Strategies 170
Defining the Types of Backups 170
Full Backups 171
Full Backup Using SQL Server Management Studio 171
Full Backup Using T-SQL 176
Differential Backups 177
File and Filegroup Backups 178
Backing Up the Logs 180
Initiating Fully Recovery Mode 181
Log Backup Using Management Studio 182
Log Backup Using T-SQL 183
Backing Up a Copy of the Database 183
Automating the Backup Process 183
Encrypting Backup Files for Security 195
Compressing Backups to Save Space 196
Designing a Backup Plan 199
Summary 201
Chapter 9: Database Restore Strategies 202
Restoring a Database from the Management Studio GUI 202
Specifying Restore Options from the GUI 210
Restoring a Database Using T-SQL 212
Executing a Simple Restore 212
Creating a Copy of a Database 212
Retrieving Logical and Physical File Names 214
Displaying General Information About a Backup 216
Cleaning Up 217
Restoring Differential Backups 217
Restoring Log Backups 219
Restoring File Backups 223
Testing Backups 225
Developing a Full Disaster Recovery Plan 226
Gathering Information About the Business 226
Establishing the Disaster Plan 227
Scenario 1: Large Commercial Insurance Company 227
Scenario 2: Small Online Retail Business 228
Testing the Disaster Plan 228
Summary 229
Chapter 10: Common Database Maintenance Tasks 230
Backing Up and Restoring 230
Checking the Database Integrity 231
Shrinking the Database 234
Reorganizing and Rebuilding Indexes 237
Detecting Undue Fragmentation 237
Rebuilding an Index 238
Reorganizing an Index 239
Getting Updated Statistics 239
Checking the Status of Automatic Statistics Gathering 240
Manually Updating Statistics 240
Monitoring Logs 241
Creating SQL Server Agent Jobs 244
Connecting to SQL Server 245
Creating an Agent Job 246
Defining Alerts 253
Summary 257
Chapter 11: Automation Through SQLCMD and PowerShell 258
Scripting in SQLCMD 258
Executing Commands Interactively 258
Executing Script in Batch Mode 259
Testing SQLCMD Scripts 261
Using SQLCMD to Back Up a Database 263
Generating Scripts from Management Studio 264
Scripting in PowerShell 266
Configuring PowerShell for Use With SQL Server 267
Understanding the Components 269
Starting PowerShell in Interactive Mode 270
Specifying an Execution Policy 272
Executing a PowerShell Script Interactively 272
Running PowerShell Scripts in Batch Mode 276
Running PowerShell from SQL Server Agent 277
Running PowerShell Scripts from Maintenance Plans 278
Using the PowerShell Integrated Scripting Environment 279
Summary 281
Chapter 12: Database Maintenance Plans 282
Understanding the Fundamentals 284
Creating a Maintenance Plan 286
Starting the Maintenance Plan Wizard 286
Specifying Plan Properties 287
Specifying Job Schedule Properties 288
Selecting Maintenance Tasks 289
Selecting Maintenance Task Order 290
Configuring Individual Tasks 291
Check Database Integrity 291
Shrink Database 293
Reorganize Index 294
Rebuild Index 295
Update Statistics 297
History Cleanup 298
Back Up Database (Full) 298
Maintenance Cleanup 300
Select Report Options 301
Completing the Maintenance Plan Wizard 301
Modifying an Existing Maintenance Plan 303
Summary 304
Chapter 13: Performance Tuning and Optimization 306
Measuring SQL Server Performance 306
Performance Counters 307
Performance Monitor 308
Dynamic Management Views 312
Data Collector 314
Setting Up the Data Collector 315
Viewing the Data Collector Data 319
Server Activity History 319
Disk Usage Summary 322
Query Statistics History 324
Tuning Queries 327
Understanding Execution Plans 327
Gathering Query Information with Profiler 334
Working with Trace Data 337
Using Trace to Capture Execution Plans 338
Using the Database Engine Tuning Advisor 339
Managing Resources 342
Limiting Resource Use 342
Leveraging Data Compression 344
Summary 345
Chapter 14: SQL Server Security 346
Terminology 346
Authentication 346
Authentication Mode 347
Authorization 347
Server Instance vs. the Database 347
SQL Server Instance Security 349
Creating a SQL Server Login 349
Server Roles 351
Server Permissions 352
Endpoints 354
Database Security 356
Database Users 356
Schemas 356
An Example of the “Wrong” Way 356
The “Right” Way 357
Four-Part Naming Convention 358
Default Schema 358
Reassigning Schema Ownership 359
Fixed Database Roles 359
Database Permissions 360
Flexible Database Roles 362
Security Functions 363
fn_my_permissions() Function 363
HAS_PERMS_BY_NAME Function 364
Summary 364
Chapter 15: Auditing, Encryption, and Compliance 365
Auditing in SQL Server 2008 366
Auditing Objects 367
Server Audit Object 368
Server Audit Specification Object 370
Database Audit Specification Object 374
Encryption 376
Encryption Primer 376
Password-Based Encryption 377
Certificate-Based Encryption 380
Transparent Data Encryption 381
Validating Server Configuration 382
The Need for a Policy 383
Create Policy on a Local Server 383
Evaluate the Policy 386
Using the Central Management Server 388
Summary 389
Chapter 16: SQL Server in the Enterprise 390
Systems Center Operations Manager 2007 390
Installing the SQL Server SCOM Management Pack 392
Getting Familiar with the SQL Server Management Pack 395
Finding and Resolving an Issue 398
Using System Center Data Protection Manager 402
Setting Up a Protection Group 404
Executing DPM Reports 407
Recovering a Databases 409
Summary 411
Chapter 17: Where to Go Next? 412
Visit SQL Server Books Online 412
Keep Up with Public SQL Server Websites 412
Attend a Conference 413
Find a Local Users Group 413
Create a Lab System and Practice 413
Get Certified! 416
Index 417