Pro SQL Server 2008 Administration (eBook)

Ken Simmons is a database administrator and developer specializing in Microsoft SQL Server and .NET. He is currently working with a global electronic payment service provider in Columbus, Georgia. He has been working in the IT industry since 2000 and holds certifications for MCP, MCAD, MCSD, MCDBA, and MCTS for SQL 2005. Ken is highly active in the online community and often participates in the SQL forums on MSDN and SQLServerCentral. He enjoys sharing tips that he discovers by writing articles for SQLServerCentral and MSSQLTips.com. He has also formed an SQL Server Users Group in Columbus, Georgia, for SQL Server professionals to come together to share knowledge and resources. When he is not working, Ken enjoys traveling with his wife, Susan, and son, Nathan, and can often be found on a cruise ship, at a Disney resort, or at the beach in his hometown of Pensacola, Florida.

Contents at a Glance 5
Contents 7
About the Authors 19
About the Technical Reviewer 20
Acknowledgments 21
Introduction 22
Who Should Read This Book 22
How the Book Is Structured 22
Downloading the Code 22
Contacting the Authors 23
Introducing Microsoft SQL Server 2008 24
New Feature Overview 25
Scalability Enhancements 26
Manageability Enhancements 27
Availability Enhancements 30
Programmability Enhancements 32
Security Enhancements 42
Summary 42
Pre-Installation Considerations 43
Choosing a SQL Server Edition 43
Determining Hardware Requirements 49
SQL Server Consolidation 60
Consolidation with Virtual Servers 62
Summary 63
Choosing a High-Availability Solution 64
What Exactly Is High Availability Anyway? 64
Failover Clustering 65
Database Mirroring 69
Copying Data with Log Shipping 74
Making Data Available Through Replication 77
Other High- Availability Techniques 84
High Availability Feature Comparison 85
Summary 86
Getting Started 87
Installing Microsoft SQL Server 2008 88
User Accounts 89
Preparation and Prerequisites 89
SQL Server Installation Center 90
Installing Your First Instance 97
Installing More Than One Instance 109
Command- Line Installation 114
Configuration File Installation 123
Summary 129
Upgrading to Microsoft SQL Server 2008 130
Upgrade Tools 130
SQL Server Integration Services 138
Upgrade Strategies 139
Post- Upgrade Procedures 157
Summary 160
Post-Installation 161
SQL Server Configuration Manager 161
Configuring the Instance 175
Preproduction Tasks 188
Summary 191
Administering Microsoft SQL Server 2008 192
Multi-Server Administration 193
Policy- Based Management 193
Central Management Servers 205
SQL Dependency Reporting 215
Summary 223
Managing Security Within the Database Engine 224
Security Language 224
Creating SQL Server Principals 226
Database Security 231
Securables 235
Permissions 240
GUI Security Administration 242
Encryption 257
Summary 264
Administering Database Objects 265
Database 101 265
Working with System Databases 266
Working with Database Snapshots 267
Working with Tables 271
Working with Views 286
Working with Synonyms 291
Working with Stored Procedures 294
Working with Functions 300
Working with Triggers 304
Working with the Common Language Runtime 307
Summary 309
Indexing for Performance 310
Index Vocabulary, Structure, and Concepts 310
Creating Indexes via T- SQL 317
Creating Indexes Using the GUI 329
Post- Index Creation 337
Putting It All Together 342
Summary 354
Managing Backups 355
Recovery Models 355
Backup Architecture 356
Backup Types 358
Backup Compression 365
Logical Backup Devices 367
The GUI Backup Interface 370
Backups from T- SQL 376
Using Encryption 378
Backing Up the System Databases 379
Backup History 381
Summary 382
Restore and Recovery Strategies 383
Restore Vocabulary 383
The Importance of Recovery Models 384
Practicing Your Recovery Strategy 385
Types of Restores 386
Online Recoveries 395
Restore Considerations 395
Recovery Examples 397
Summary 423
Automating Routine Maintenance 424
Database Mail 424
SQL Server Agent 437
Maintenance Plans 452
Summary 458
Troubleshooting and Tuning 459
Monitoring Your Server 460
Baseline Your Server 460
Familiarizing Yourself with the Performance Monitor 461
Monitoring Your CPU 463
Monitoring Your Memory 471
Monitoring Disk IO 478
Monitoring Miscellaneous Counters and DMVs 481
Capturing SQL Server PerfMon Counters Using DMVs 481
Leveraging the SQL Server Profiler 481
Using Server- Side Tracing 484
Automating Your Monitoring 486
Summary 487
Auditing SQL Server 488
Choosing Your Audit Type 488
Creating SQL Server Audits with T- SQL 489
Creating Server Audit Specifications 490
Creating Database Audit Specifications 493
Creating SQL Server Audits Using the GUI 498
Audit- Related Startup and Shutdown Problems 502
Useful SQL Server Audit Objects 503
Summary 503
Managing Query Performance 504
Correlating Profiler and the Performance Monitor 504
Finding Similar Queries 506
Running the Standard Performance Reports 508
Optimizing for Specific Parameter Values 510
Forcing Index Seeks 513
Forcing a Specific Execution Plan 515
Adding Hints Through Plan Guides 516
Managing Resources with the Resource Governor 520
Monitoring Resource Governor 528
Summary 532
Conclusion 533
Secrets to Excelling As a Professional DBA 534
Be Good at What You Do 534
Go Above and Beyond 536
Communicate Effectively 537
Remain Calm Under Pressure 539
Be an Effective Leader 541
Summary 543
What’s Next? 544
Ongoing Experimentation and Learning 544
Podcasts 545
Free Training Events 545
SQL Support Options 546
Web Sites Dedicated to SQL Server 549
Apress Titles for Specialized Topics 549
Summary 550
Index 551