Pro SQL Server Disaster Recovery

Pro SQL Server Disaster Recovery......Page 1
Contents at a Glance......Page 7
Contents......Page 9
About the Author......Page 19
About the Technical Reviewer......Page 21
Introduction......Page 23
How This Book Is Structured......Page 24
Contacting the Author......Page 26
Defining Disaster Recovery......Page 27
Disaster Recovery, High Availability, and Business Continuity......Page 29
The “We Were Supposed to Do That?” Project......Page 30
Disaster Categories......Page 31
Hardware......Page 32
Process......Page 33
User......Page 34
Predictability, Probability, and Impact......Page 35
Mitigation Technologies......Page 36
Caveats and Recommendations......Page 37
Summary......Page 38
Making Database Backups......Page 39
A Brief Review of SQL Server Storage......Page 40
SQL Server Recovery Modes......Page 42
Simple Recovery......Page 43
Bulk-Logged Recovery......Page 44
Changing Recovery Modes......Page 46
Naming Conventions......Page 47
Mapped Network Drive Letter......Page 48
Tape......Page 49
Comparison of Backup Locations......Page 50
Creating a Backup Device......Page 51
Managing Backup Devices......Page 52
Media Sets and Backup Sets......Page 53
Log Backup......Page 54
Backup File Sizes......Page 55
Error Checking......Page 57
Striped Backup......Page 58
Mirrored Backup......Page 61
Copy-Only Backup......Page 62
Full-Text Backup......Page 63
Backup Locations......Page 64
Caveats and Recommendations......Page 65
Summary......Page 67
Restoring a Database......Page 69
Restore vs. Recovery......Page 70
Availability During Recovery......Page 72
RESTORE HEADERONLY......Page 73
RESTORE FILELISTONLY......Page 76
RESTORE VERIFYONLY......Page 77
Information Contained in MSDB......Page 78
Restoring in Simple Recovery Mode......Page 79
Restoring in Full Recovery Mode......Page 81
Restoring/Moving to a New Location......Page 82
Restoring with Fast File Initialization......Page 84
Restoring Differential Backups in Full/Bulk-Logged Mode......Page 85
Time......Page 86
Log Marks......Page 87
Mirroring Backups......Page 88
Verifying Backups......Page 89
Restoring Data Pages......Page 90
Master......Page 91
MSDB......Page 92
Model......Page 93
Distribution......Page 94
Resource......Page 95
ReportServerTempDB......Page 96
Databases in SUSPECT Status......Page 97
Caveats and Recommendations......Page 98
Summary......Page 99
Backing Up and Restoring Files and Filegroups......Page 101
Creating Filegroups......Page 102
The Default Filegroup......Page 106
Creating an Object in a Filegroup......Page 107
Moving Objects to Different Filegroups......Page 109
Filegroup Strategies......Page 110
Separating Read-Only and Read/Write Tables......Page 111
Reducing Backup/Restore Time......Page 112
Backing Up Database Files......Page 113
Creating File-Specific Differential Backups......Page 114
Restoring Differential Backups......Page 115
Backing Up a Filegroup......Page 116
Restoring a Filegroup......Page 117
Performing Partial Backups and Restores......Page 118
Performing Piecemeal Restores......Page 120
Backing Up and Restoring Full-Text Indexes......Page 121
Files/Filegroups and Disaster Scenarios......Page 122
Summary......Page 123
Creating a Backup/ Recovery Plan......Page 125
Components of a Backup/Recovery Plan......Page 126
Key Business Constraints for BRPs......Page 128
Time to Back Up......Page 129
Potential Data Loss......Page 130
Cost......Page 131
Consulting Cost......Page 132
Maintenance Cost......Page 133
Key Technical Constraints for BRPs......Page 134
Hardware Capabilities......Page 135
Portability......Page 136
Training......Page 137
Ongoing Testing and Design Review......Page 138
SQL Agent......Page 139
Jobs and Job Steps......Page 140
Job Step Tokens......Page 142
Agent Proxies......Page 143
Alerts......Page 148
A General Template......Page 150
Potential Approach......Page 151
Inherent Risks......Page 153
Requirements......Page 154
Potential Approach......Page 155
Inherent Risks......Page 156
Potential Approach......Page 157
Inherent Risks......Page 158
Potential Approach......Page 159
Inherent Risks......Page 160
Inherent Risks......Page 161
Potential Approach......Page 162
Initial and Periodic Testing......Page 163
Enhancing Basic Scenarios......Page 164
Summary......Page 165
Maintaining a Warm Standby Server via Log Shipping......Page 167
Log Shipping vs. Replication......Page 168
Log Shipping Is Stateless......Page 169
Low Resource Overhead Is Incurred......Page 171
Network Latency......Page 172
Log-Shipping Architecture......Page 174
Basic Architecture......Page 175
Multiple Standby Servers......Page 176
Configuring Log Shipping......Page 177
Manual Log Shipping......Page 178
Log Shipping in SQL Server 2000......Page 181
Log Shipping in SQL Server 2005......Page 182
Dealing with Failover to a Secondary Server......Page 190
Step 3: Recover the Standby Database......Page 191
Step 5: Redirect Clients to the New Primary Server......Page 192
Dealing with Failback to the Primary Server......Page 195
Monitoring Your Log-Shipping Environment......Page 196
Log Shipping and Disaster Categories......Page 197
Caveats and Recommendations......Page 198
Summary......Page 199
Clustering Basics......Page 201
Clustering Architecture......Page 202
Cluster Resources......Page 203
Quorum......Page 204
Mount Points......Page 205
SQL Server Clustering......Page 207
Custom Utilities/Applications......Page 208
Active/Passive......Page 209
Active/Active......Page 211
Active/Active/Active/ . . .......Page 212
Multiple Instances......Page 213
Physical Failover......Page 214
Logical Failover......Page 215
Server Resource Planning......Page 216
Failback in a Cluster......Page 217
Clustering and Disaster Categories......Page 218
Caveats and Recommendations......Page 219
Summary......Page 220
Mirroring Architecture......Page 221
The Basics......Page 222
Principal......Page 223
Mirror......Page 224
Witness......Page 226
Ports......Page 227
Communication......Page 229
Client Connections with the SQL Native Access Client......Page 230
Mirroring Mode: High Performance......Page 232
Mirroring Mode: High Protection......Page 233
Failback......Page 234
Mirroring Mode: High Availability......Page 235
Failback......Page 236
Configuring Mirroring......Page 237
Guidelines for Selecting a Database Mirroring Mode......Page 249
Disaster Categories......Page 251
Caveats and Recommendations......Page 252
Summary......Page 253
Understanding the Architecture......Page 255
Creating Database Snapshots......Page 257
Restoring Database Snapshots......Page 259
Managing Database Snapshots......Page 260
Applying a Naming Convention......Page 262
Linking a Snapshot to Its Database......Page 263
Dealing with Process Errors......Page 264
Point-in-Time Reporting......Page 265
Database Snapshots and Disaster Scenarios......Page 266
Caveats and Recommendations......Page 267
Summary......Page 268
Hardware Considerations......Page 269
Online Disk Storage......Page 270
Block Size vs. Stripe Size......Page 271
Locally Attached Storage......Page 272
ATA vs. SCSI......Page 273
RAID 0......Page 274
RAID 1......Page 275
RAID 5......Page 276
RAID 6......Page 277
RAID 0+1 and RAID 1+0......Page 278
RAID 5+1......Page 279
NAS......Page 280
SAN......Page 281
Tape Storage......Page 283
Low-Cost SAN or NAS......Page 284
Virtualization......Page 285
Network Issues......Page 286
Latency vs. Bandwidth......Page 287
Name Resolution......Page 288
Authentication......Page 289
Power Surges/Lapses......Page 290
Internal System Heat......Page 291
Hardware and Disaster Categories......Page 292
Caveats and Recommendations......Page 293
Summary......Page 294
Putting It All Together......Page 295
Guiding Principles......Page 296
Identifying Risk......Page 297
Developing a Response Plan......Page 299
Complementary Technologies......Page 300
Basic Documentation for Disaster Recovery......Page 302
Testing......Page 303
The Scenario......Page 304
What Went Wrong......Page 305
How SQL Server 2005 Would Have Helped......Page 306
The Scenario......Page 307
What Went Wrong......Page 308
How SQL Server 2005 Would Have Helped......Page 309
The Result......Page 310
How SQL Server 2005 Would Have Helped......Page 311
The Result......Page 312
“Where is WHERE?” (Process Disaster)......Page 313
What Went Wrong......Page 314
The Scenario......Page 315
How SQL Server 2005 Would Have Helped......Page 316
Summary......Page 317
Realistic Disaster Recovery Planning......Page 319
Understanding Personality Archetypes......Page 320
How Do You Deal with These Problems?......Page 321
What Problems Does This Create?......Page 322
What Is Valued?......Page 323
The Information Hoarder......Page 324
How Do You Deal with These Problems?......Page 325
What Problems Does This Create?......Page 326
What Is Valued?......Page 327
The Pacifist......Page 328
How Do You Deal with These Problems?......Page 329
Overcoming Roadblocks......Page 330
Roadblock: Lack of Awareness......Page 331
Description of the Roadblock......Page 332
Techniques to Remove This Roadblock......Page 335
Description of the Roadblock......Page 337
Description of the Roadblock......Page 339
Techniques to Remove This Roadblock......Page 340
Techniques to Remove This Roadblock......Page 341
Techniques to Remove This Roadblock......Page 342
Techniques to Remove This Roadblock......Page 343
Caveats and Recommendations......Page 344
Summary......Page 345
Backup/Restore Improvements......Page 347
Native Backup Compression......Page 348
FILESTREAM Data......Page 350
Automatic Page Repair......Page 352
Change Tracking......Page 353
Index......Page 355