Pro SQL Server 2008 Mirroring (eBook)

Robert L. Davis is a database administrator and data architect consulting at Microsoft who has worked extensively in setting up and creating automated processes to manage database mirroring for many internal Microsoft applications. He additionally consults with various internal Microsoft IT groups to assist them with their database mirroring needs. Robert is currently working on the Architecture & Performance Engineering team of the CAP application program group. CAP is the internal software used by support personnel for all of Microsoft's customer facing products such as Xbox, Zune, MSN, Microsoft Live, and HotMail. He has over eight years of SQL Server administration experience, much of that in high-availability environments. Rob writes frequently about mirroring and related topics in his blog: www.sqlservercentral.com/blogs/robert_davis/default.aspx

Contents at a Glance 4
Contents 5
About the Authors 13
About the Technical Reviewer 14
Introduction 15
Who This Book Is For 15
How This Book Is Structured 15
Prerequisites 16
Downloading the Code 16
Contacting the Authors 16
High-Availability Options 17
High Availability vs. Disaster Recovery 17
Database Mirroring 18
Failover Clustering 18
Log Shipping 20
Replication 22
Comparing High-Availability Features 23
Designing a Complete High-Availability Solution 24
Summary 26
Database Mirroring Overview 27
Defining Key Terms 27
Choosing an Operating Mode 28
Understanding Sessions 33
Understanding Mirroring States 34
Switching Roles 36
Transparent Client Redirection 36
Enhanced Features 36
Features by Edition 38
Summary 38
Planning and Best Practices 39
Step One: Know Your Environment 39
Step Two: Know Your Applications 41
Step Three: Know Your Plan 43
Sample Plan 43
Best Practices 44
Summary 46
Database Mirroring Setup 47
Before You Begin 47
Following the Wizard 49
Setting Up Mirroring Manually 59
Special Case: Mirroring with Certificates 63
Test, Monitor, and Wrap Up 65
Troubleshooting Common Setup Issues 66
Summary 68
Automating Setup 69
Prepare the Database for Mirroring 69
Back Up the Principal Database 71
Restore Full Backup on the Mirror Database with No Recovery 76
Back Up the Log of the Principal Database 80
Restore the Log on the Mirror Database with No Recovery 80
Create Endpoints on Instances 81
Create Login for the Service Accounts and Grant Connect 83
Set the Mirroring and Witness Partners 85
Change the Operating Mode If Not Using a Witness 88
The Master Procedure 90
Alternative Setup Using PowerShell 98
Summary 110
Maintenance Simplified 111
Routine Maintenance Considerations 111
Transaction Log Backups 111
Log File Size 126
Routine Patching and SQL Server Upgrades 131
Fail Back to the Original Principal 133
Setting Database Owner and TRUSTWORTHY Property 135
Controlled Failover of Mirrored Databases 137
Automatic Failover of Mirrored Databases 145
Summary 157
Monitoring and Alerting 158
Monitoring Components 158
Understanding Warning Thresholds 160
Using the Database Mirroring Monitor 161
Using System Stored Procedures 167
Understanding Performance Counters 172
Configuring Database Mail 174
Creating Alerts 177
Useful Views 183
Using the State Change Trace Event 184
Summary 185
Upgrading to SQL Server 2008 186
Using the Upgrade Advisor 186
Upgrade Strategies 195
Performing an In-Place Upgrade 196
Minimizing the Downtime of a Side-by-Side Upgrade 199
Post-Upgrade Procedures 200
Summary 202
Reporting Considerations 203
Using Database Snapshots 203
Using Log Shipping 209
Using Replication 213
Summary 221
Planning for Failure 223
Detecting Errors 223
Failure Scenarios 225
Failover Considerations 234
Planning and Research 237
Microsoft Support Options 239
Summary 240
Index 241