Pro SQL Server 2008 Administration

Contents at a Glance......Page 6
Contents......Page 9
New Feature Overview......Page 29
Filtered Indexes and Statistics......Page 30
Manageability Enhancements......Page 31
Backup Compression......Page 32
Policy-Based Management......Page 33
Database Mirroring......Page 34
Hot-Add CPU......Page 35
Variables......Page 36
Transact-SQL Row Constructors......Page 37
Table-Valued Parameters......Page 38
MERGE Statement......Page 40
GROUPING SETS Operator......Page 43
Summary......Page 46
Choosing a SQL Server Edition......Page 47
Enterprise Edition......Page 48
Standard Edition......Page 49
Developer Edition......Page 50
Web Edition......Page 51
Express Edition......Page 52
Determining Hardware Requirements......Page 53
Determining CPU Needs......Page 55
Disk Subsystems......Page 57
Database Sizing......Page 58
Determining RAID Levels......Page 60
Determining Memory Requirements......Page 62
Choosing the Server......Page 63
SQL Server Consolidation......Page 64
Consolidation with Virtual Servers......Page 66
Summary......Page 67
What Exactly Is High Availability Anyway?......Page 69
Key Terms......Page 70
Failover Clustering Overview......Page 71
Implementation......Page 73
Key Terms......Page 74
Database Mirroring Overview......Page 75
High-Safety Mode......Page 76
High-Performance Mode......Page 77
Pros and Cons of Database Mirroring......Page 78
Key Terms......Page 79
Log Shipping Overview......Page 80
Making Data Available Through Replication......Page 82
Replication Overview......Page 83
Snapshot Replication......Page 84
Transactional Replication......Page 85
Merge Replication......Page 88
Other High-Availability Techniques......Page 89
High Availability Feature Comparison......Page 90
Summary......Page 91
Installing Microsoft SQL Server 2008......Page 95
Preparation and Prerequisites......Page 96
SQL Server Installation Center......Page 97
Planning......Page 98
Installation......Page 99
Maintenance......Page 100
Tools......Page 101
Resources......Page 102
Options......Page 103
Installing Your First Instance......Page 104
Checking System Configuration......Page 105
Choosing Your Features......Page 107
Configuring the Instance......Page 108
Configuring the Database Engine......Page 110
Allowing for Error and Usage Reporting......Page 112
Validating and Installing......Page 113
Getting Your System Ready......Page 116
Configuring the Instance......Page 119
Command-Line Installation......Page 121
Learning the Parameters......Page 122
Running a Command-Line Install......Page 124
Configuration File Installation......Page 130
Summary......Page 136
Microsoft SQL Server 2008 Upgrade Advisor......Page 137
Upgrade Assistant......Page 144
DTS Package Migration Wizard......Page 145
Upgrade Strategies......Page 146
In-Place Upgrade......Page 147
Side-by-Side Upgrade......Page 154
Post-Upgrade Procedures......Page 164
Checking Object Integrity......Page 165
Updating Statistics......Page 166
Summary......Page 167
SQL Server Configuration Manager......Page 169
SQL Server Services......Page 170
SQL Server Network Configuration......Page 174
SQL Native Client Configuration......Page 180
Viewing Configuration Settings......Page 183
Specifying Maximum and Minimum Server Memory......Page 187
Specifying the Backup Compression Default......Page 189
Enabling Login Failure Auditing......Page 191
Enabling Use of the CLR (Common Language Runtime)......Page 192
Enabling a Query Governor Cost Limit......Page 193
Miscellaneous Configuration Options......Page 194
Database Tasks......Page 196
Maintenance and Monitoring......Page 198
Summary......Page 199
Policy-Based Management......Page 203
Manually Creating a Policy......Page 204
Exporting Current State As Policy......Page 211
Importing a Predefined Policy......Page 213
Central Management Servers......Page 215
Creating a Central Management Server......Page 216
Running Multi-Server Queries......Page 219
Configuring Multi-Server Query Options......Page 220
Evaluating Policies......Page 222
SQL Dependency Reporting......Page 225
Summary......Page 233
Security Language......Page 235
Principals......Page 236
Creating Logins for SQL Server......Page 237
Creating SQL Server Logins for Windows Principals......Page 238
Associating Logins with Certificates and Asymmetric Keys......Page 239
Linking Credentials to Logins......Page 240
SQL Server–Level Roles......Page 241
Database Security......Page 242
Creating Database Users......Page 243
Database Roles......Page 244
Managing Server Securables......Page 246
Managing Database Securables......Page 247
Understanding Schema Securables......Page 248
Types of Permissions......Page 251
Managing Permissions......Page 252
Creating a User......Page 253
Creating Roles......Page 260
Creating Schemas......Page 265
Encrypting Data Using Certificate Encryption......Page 268
Encrypting Data Using Asymmetric Keys......Page 270
Encrypting Data Using Symmetric Keys......Page 272
Extensible Key Management......Page 273
Transparent Data Encryption......Page 274
Summary......Page 275
Database 101......Page 277
Working with System Databases......Page 278
Working with Database Snapshots......Page 279
Creating a Database Snapshot......Page 280
Viewing and Querying a Snapshot......Page 281
Reverting a Database to a Database Snapshot......Page 282
Default Constraints......Page 283
Primary Key Constraints......Page 285
Unique Constraints......Page 287
Foreign Key Constraints......Page 288
Check Constraints......Page 290
Sparse Columns......Page 292
Compression......Page 293
Partitions......Page 295
Temporary Tables......Page 297
Working with Views......Page 298
Partitioned Views......Page 300
Updateable Views......Page 301
Indexed Views......Page 302
Working with Synonyms......Page 303
Working with Stored Procedures......Page 306
Scalar-Valued Functions......Page 312
Table-Valued Functions......Page 313
DML Triggers......Page 316
DDL Triggers......Page 317
Logon Triggers......Page 318
Working with the Common Language Runtime......Page 319
Summary......Page 321
Clustered Indexes......Page 323
Structure of Indexes and the Heap......Page 324
Indexes Created by Constraints......Page 327
Other Ways to Categorize Indexes......Page 328
Other Index Concepts and Terminology......Page 329
Creating Clustered and Nonclustered Indexes......Page 330
Creating Unique and Primary Key Indexes......Page 335
Creating Filtered Indexes......Page 336
Creating XML Indexes......Page 340
Creating an Example Table......Page 342
Creating a Clustered Index via the GUI......Page 343
Creating a Nonclustered Index via the GUI......Page 348
Post-Index Creation......Page 350
Performance of Insert, Update, and Delete Statements......Page 351
Useful Dynamic Management Views......Page 352
Setting Up the Scenario......Page 355
Table Scans......Page 356
Clustered Index Seeks......Page 357
Covering Your Queries......Page 359
Indexing JOIN Criteria......Page 363
Filtered Indexes......Page 365
Summary......Page 367
Recovery Models......Page 369
Backup Architecture......Page 370
Full Backups......Page 372
Differential Backups......Page 373
Transaction Log Backups......Page 375
Partial Backups......Page 376
File Backups......Page 377
Copy-Only Backups......Page 378
Backup Compression......Page 379
Logical Backup Devices......Page 381
The GUI Backup Interface......Page 384
Backups from T-SQL......Page 390
Using Encryption......Page 392
Backing Up the System Databases......Page 393
Backup History......Page 395
Summary......Page 396
Restore Vocabulary......Page 397
The Importance of Recovery Models......Page 398
Practicing Your Recovery Strategy......Page 399
Types of Restores......Page 400
Restoring Transaction Logs......Page 402
Restoring Differential Backups......Page 403
Restoring Files and Filegroups......Page 404
Piecemeal Restores......Page 406
Restoring Snapshots......Page 407
RESTORE VERIFYONLY......Page 408
Utilizing Filegroups for Faster Restores......Page 409
Recovering from Scratch......Page 410
Recovery Examples......Page 411
Example 1: The Piecemeal Restore......Page 412
Backing Up the Tail End of the Transaction Log......Page 415
Example 2: Restoring a Compressed and Encrypted Database......Page 425
Example 3: Restoring System Databases......Page 435
Summary......Page 437
Database Mail Configuration Wizard......Page 439
Configuring Database Mail Using T-SQL......Page 448
Sending Database Mail......Page 450
Database Mail Cleanup Procedures......Page 451
Operators......Page 452
Enabling SQL Server Agent Notifications......Page 453
Alerts......Page 455
Jobs......Page 458
Proxies......Page 465
Maintenance Plans......Page 467
Maintenance Plan Wizard......Page 468
Maintenance Plan Design Tab......Page 469
Maintenance Plan Tasks......Page 470
Exporting and Importing Maintenance Plans......Page 471
Summary......Page 473
Baseline Your Server......Page 477
Familiarizing Yourself with the Performance Monitor......Page 478
Monitoring Your CPU......Page 480
Windows Counters That Indicate CPU Utilization......Page 483
SQL Server Counters That Impact CPU......Page 484
DMVs for Monitoring CPU......Page 485
Memory Usage in SQL Server......Page 488
Counters to Watch......Page 489
Memory with Windows Counters......Page 491
Memory with SQL Server Counters......Page 492
Memory with DMVs and DBCC Commands......Page 493
Monitoring Disk IO......Page 495
Disk IO with SQL Server Counters......Page 497
Leveraging the SQL Server Profiler......Page 498
Using Server-Side Tracing......Page 501
Automating Your Monitoring......Page 503
Summary......Page 504
Choosing Your Audit Type......Page 505
Creating SQL Server Audits with T-SQL......Page 506
Server-Level Action Groups......Page 507
Testing Your Server Audit Specification......Page 509
Creating Database Audit Specifications......Page 510
Database-Level Audit Action Groups......Page 511
Database-Level Audit Actions......Page 512
Testing Your Database Audit Specification......Page 513
Creating SQL Server Audits Using the GUI......Page 515
Creating a Database Audit Specification Using the GUI......Page 516
Reviewing Audit Files Using SQL Server Management Studio......Page 518
Failure to Start......Page 519
Summary......Page 520
Correlating Profiler and the Performance Monitor......Page 521
Finding Similar Queries......Page 523
Running the Standard Performance Reports......Page 525
Optimizing for Specific Parameter Values......Page 527
Forcing Index Seeks......Page 530
Forcing a Specific Execution Plan......Page 532
Creating a Plan Guide......Page 533
Validating a Plan Guide......Page 535
Managing Resources with the Resource Governor......Page 537
Resource Pools......Page 538
Workload Groups......Page 540
Classifier Function......Page 542
Performance Counters......Page 545
Trace Events......Page 546
Views......Page 547
Summary......Page 549
Face Reality......Page 553
Build Your Reputation......Page 554
Go Above and Beyond......Page 555
Talk to Your Target Audience......Page 556
Be a Good Listener......Page 557
Deal with Pressure Situations......Page 558
Be an Effective Leader......Page 560
Make Others Around You Better......Page 561
Summary......Page 562
Ongoing Experimentation and Learning......Page 563
Free Training Events......Page 564
Free Support......Page 565
Advisory Services......Page 567
Apress Titles for Specialized Topics......Page 568
Summary......Page 569
Index......Page 571