| Foreword | xvii |
| Preface | xix |
| Acknowledgments | xxi |
| PART I THE HIGH AVAILABILITY PRIMER | |
| 1 Preparing for High Availability | 3 |
| High Availability—What It Is and How to Get It | 4 |
| Prevention | 4 |
| Disaster Recovery | 5 |
| Agreeing on a Solution | 6 |
| The Project Team | 6 |
| Guiding Principles for High Availability | 7 |
| Making Trade-Offs | 8 |
| Identifying Risks | 9 |
| Next Steps | 10 |
| Availability Calculations and Nines | 10 |
| Calculating Availability | 10 |
| What Is a Nine? | 11 |
| What Level of Nines Is Needed? | 12 |
| Negotiating Availability | 13 |
| Types of Unavailability | 14 |
| Where Does Availability Start? | 14 |
| Assessing Your Environment for Availability | 15 |
| The Cost of Availability | 16 |
| Barriers to Availability | 18 |
| Summary | 18 |
| 2 The Basics of Achieving High Availability | 19 |
| Data Center Best Practices | 19 |
| Location | 21 |
| Security | 24 |
| Cabling, Power, Communications Systems, and Networks | 25 |
| Third-Party Hosting | 27 |
| Support Agreements | 28 |
| The "Under the Desk" Syndrome | 29 |
| Staffing | 30 |
| Creating a Database Team | 30 |
| Service Level Agreements | 32 |
| Manage Change or Be Managed by It | 35 |
| Change Management for Databases: The Basics | 35 |
| Development, Testing, and Staging Environments | 36 |
| Managing Change and Availability in Development | 38 |
| Managing Change in Production | 41 |
| Preparing for Change | 41 |
| Implementing Change | 46 |
| System and Process Standardization | 48 |
| Documentation | 49 |
| 3 Making a High Availability Technology Choice | 51 |
| Windows Clustering | 52 |
| Server Clusters | 52 |
| Network Load Balancing Clusters | 58 |
| Geographically Dispersed Clusters | 60 |
| SQL Server 2000 | 61 |
| Failover Clustering | 61 |
| Log Shipping | 67 |
| Replication | 69 |
| Backup and Restore | 71 |
| Decisions, Decisions . | 72 |
| The Decision Process | 72 |
| A Comparison of the SQL Server Technologies | 74 |
| What Should You Use? | 81 |
| PART II TECHNOLOGY BUILDING BLOCKS | |
| 4 Disk Configuration for High Availability | 85 |
| Quick Disk Terminology Check | 85 |
| Capacity Planning | 86 |
| Raw Disk Space Needed | 87 |
| Application Database Usage | 88 |
| Understanding Physical Disk Performance | 93 |
| Using SQL Server to Assist with Disk Capacity Planning | 96 |
| Types of Disk Subsystems | 97 |
| Direct-Attached Storage | 97 |
| Network-Attached Storage | 98 |
| Storage Area Networks | 101 |
| What Disk Technology to Use | 102 |
| Server Clusters, Failover Clustering, and Disks | 103 |
| Pre-Windows Disk Configuration | 107 |
| Number of Spindles Needed | 108 |
| Understanding Disk Drives | 109 |
| Understanding Your Hardware | 110 |
| Understanding How SQL Server Interacts with Disks | 112 |
| Understanding Disk Cache | 113 |
| A RAID Primer | 114 |
| Remote Mirroring | 119 |
| Storage Composition | 120 |
| Types of Disks and File Systems in Windows | 121 |
| Formatting the Disks | 122 |
| File Placement and Protection | 123 |
| System Databases and Full-Text Indexes | 124 |
| User Databases | 125 |
| Databases, the Quorum, and Failover Clustering | 125 |
| Files and Filegroups | 126 |
| Database File Size | 127 |
| Shrinking Databases and Files | 129 |
| Configuration Example | 130 |
| The Scenario | 131 |
| Sample Drive Configurations | 133 |
| 5 Designing Highly Available Microsoft Windows Servers | 139 |
| General Windows Configuration for SQL Servers | 139 |
| Choosing a Version of Windows | 139 |
| Versions of SQL Server and Windows Server 2003 | 143 |
| Disk Requirements for Windows | 144 |
| Security | 146 |
| Windows Server 2003 Enhancements | 147 |
| High Availability Options for Windows | 154 |
| Windows Reliability Features | 154 |
| Server Clusters | 156 |
| Planning a Server Cluster | 157 |
| Certified Cluster Applications | 165 |
| Ports, Firewalls, Remote Procedure Calls, and Server Clusters | 165 |
| Geographically Dispersed Clusters | 166 |
| Antivirus Programs, Server Clusters, and SQL Server | 166 |
| Server Clusters, Domains, and Networking | 167 |
| Implementing a Server Cluster | 170 |
| Server Cluster Administration | 190 |
| Changing Domains | 190 |
| Changing a Node's IP Address or Name | 190 |
| Changing Service Accounts and Passwords | 191 |
| Disk Management | 193 |
| Forcing Quorum for an MNS Cluster | 198 |
| Network Load Balancing | 199 |
| General Network Load Balancing Best Practices | 200 |
| Implementing Network Load Balancing for SQL Server-Based Architectures | 201 |
| Adding a Network Load Balancing Cluster to DNS | 206 |
| Configuring Logging for Network Load Balancing Manager | 206 |
| Uninstalling Network Load Balancing | 207 |
| PART III MICROSOFT SQL SERVER TECHNOLOGY | |
| 6 Microsoft SQL Server 2000 Failover Clustering | 211 |
| Planning for Failover Clustering | 211 |
| Versions of Windows Supported | 212 |
| Number of SQL Server 2000 Instances per Server Cluster | 212 |
| Name of the SQL Server Virtual Server | 213 |
| Number of Nodes | 214 |
| Disks | 216 |
| IP Addresses, Ports, and Network Card Usage | 218 |
| Applications and Failover Clustering | 219 |
| Third-Party Applications, File Shares, Dependencies, and SQL Server 2000 Failover Clustering | 220 |
| Hardware-Assisted Backups and SQL Server 2000 Failover Clustering | 221 |
| Service Accounts and SQL Server 2000 Failover Clustering | 222 |
| Memory | 223 |
| Coexistence with Stand-Alone Instances and Other Versions of SQL Server | 224 |
| Analysis Services and Failover Clustering | 224 |
| SQL Mail and Failover Clustering | 225 |
| Exchange and SQL Server on the Same Cluster | 225 |
| Cluster Group Configuration for Failover Clustering | 226 |
| Implementing SQL Server 2000 Failover Clustering | 226 |
| Prerequisites | 227 |
| Installation Order | 227 |
| Installing a SQL Server Virtual Server | 229 |
| Postinstallation Tasks | 229 |
| Verifying Your Failover Cluster Installation | 241 |
| Verifying Connectivity and Name Resolution | 242 |
| Verifying the SQL Server Service Account and Node Participation | 243 |
| Verifying the Application with Failover | 244 |
| Administering SQL Server Virtual Servers | 244 |
| Ensuring a Virtual Server Will Not Fail Due to Other Service Failures | 245 |
| Adding or Removing a Cluster Node from the Virtual Server Definition and Adding, Changing, or Updating a TCP/IP Address | 245 |
| Renaming a SQL Server 2000 Virtual Server | 249 |
| Uninstalling a SQL Server Virtual Server | 249 |
| Manually Removing Failover Clustering | 251 |
| Manually Removing Clustered Instances of SQL Server | 254 |
| Changing SQL Server Service Accounts | 256 |
| Changing Domains | 259 |
| Troubleshooting SQL Server 2000 Failover Clusters | 260 |
| Barriers for Failover Clustering | 260 |
| The Troubleshooting Process | 262 |
| Disaster Recovery for Failover Clustering | 265 |
| Scenario 1: Quorum Disk Failure | 266 |
| Scenario 2: Cluster Database Corruption on a Node | 267 |
| Scenario 3: Quorum Corruption | 267 |
| Scenario 4: Checkpoint Files Lost or Corrupt | 268 |
| Scenario 5: Cluster Node Failure | 268 |
| Scenario 6: A Cluster Disk Is Corrupt or Nonfunctional | 271 |
| If You Do Not Have Backups | 271 |
| 7 Log Shipping | 273 |
| Uses of Log Shipping | 273 |
| Basic Considerations for All Forms of Log Shipping | 275 |
| Ask the Right Questions | 276 |
| How Current Do You Need To Be? | 279 |
| Secondary Server Capacity and Configuration | 279 |
| Disk Space, Retention, and Archiving | 280 |
| Full-Text Searching and Log Shipping | 281 |
| Recovery Models and Log Shipping | 283 |
| Network Bandwidth | 285 |
| Logins and Other Objects | 286 |
| Clients, Applications, and Log Shipping | 288 |
| Security | 291 |
| Log Shipping and Database Backups | 293 |
| Service Packs and Log Shipping | 295 |
| Files, Filegroups, and Transaction Logs | 295 |
| Custom Log Shipping Versus Microsoft's Implementation | 296 |
| Configuring and Administering the Built-In Functionality Using SQL Server 2000 Enterprise Edition | 297 |
| Log Shipping Components | 298 |
| Configuring Log Shipping | 302 |
| Administering Log Shipping | 319 |
| Role Changes | 335 |
| Creating a Custom Coded Log Shipping Solution | 339 |
| Log Shipping From SQL Server 7.0 to SQL Server 2000 | 340 |
| Configuring Log Shipping from SQL Server 7.0 to SQL Server 2000 | 341 |
| 8 Replication | 345 |
| Using Replication to Make a Database Available | 345 |
| Choosing a Replication Model for Availability | 346 |
| Switch Methods and Logins | 348 |
| Replication and Database Schemas | 348 |
| Highly Available Replication Architecture | 352 |
| Replication Agents | 353 |
| Scenario 1: Separate Publisher and Distributor | 356 |
| Scenario 2: Using a Republisher | 358 |
| SQL Server Service Packs and Replication | 360 |
| Planning Disk Capacity for Replication | 360 |
| Disaster Recovery with a Replicated Environment | 363 |
| Backing Up Replication Databases | 364 |
| Disaster Recovery Restore Scenarios | 370 |
| Log Shipping and Replication | 372 |
| Transactional Replication and Log Shipping | 373 |
| Merge Replication and Log Shipping | 374 |
| Performing a Role Change Involving Replication | 375 |
| 9 Database Environment Basics for Recovery | 379 |
| Fundamentals | 379 |
| Technology Last | 380 |
| Understanding Your Backup and Restore Barriers | 381 |
| Minimizing Human Error | 382 |
| Symptoms and Recovery | 384 |
| Backup | 384 |
| Understanding Database Structures | 385 |
| Initial Database Settings and Recovery Models | 399 |
| Recovery Models | 404 |
| Backup Types | 416 |
| 10 Implementing Backup and Restore | 429 |
| Creating an Effective Backup Strategy | 429 |
| Backup Retention | 433 |
| Devising a Backup Strategy to Create an Optimal Recovery Strategy | 434 |
| Implementing Your Backup Strategy | 444 |
| Options for Performing a Backup | 444 |
| Creating a Backup Device | 445 |
| Executing the Full Database-Based Backup Strategy Using Transact-SQL | 460 |
| Executing the File-Based Backup Strategy Using Transact-SQL | 462 |
| Simplifying and Automating Backups | 466 |
| Implementing an Effective Backup Strategy: In Summary | 478 |
| Database Recovery | 479 |
| Phases of Recovery | 481 |
| Useful RESTORE Options | 485 |
| Disaster Recovery with Backup and Restore | 486 |
| Collected Wisdom and Good Ideas for Backup and Restore | 499 |
| Backing Up the Operating System | 501 |
| Using Backup | 503 |
| Backing Up and Restoring Clustered Environments | 507 |
| Backing Up a Standard Server Cluster | 507 |
| Third-Party Backup Software and SQL Server 2000 Failover Clustering | 509 |
| PART IV PUTTING THE PIECES OF THE PUZZLE TOGETHER | |
| 11 Real-World High Availability Solutions | 513 |
| The Scenario | 513 |
| Conditions and Constraints | 514 |
| The Planning Process | 515 |
| Step 1: Breaking Down the Requirements | 515 |
| Step 2: Considering Technologies | 517 |
| Step 3: Designing the Architecture | 518 |
| Step 4: Choosing Hardware and Costs | 519 |
| Exercise Summary | 525 |
| Case Study: Microsoft.com | 527 |
| Background Information | 527 |
| Planning and Development | 528 |
| How Microsoft.com Achieves High Availability in Production | 529 |
| Microsoft.com's Barriers to Availability | 530 |
| 12 Disaster Recovery Techniques for Microsoft SQL Server | 533 |
| Planning for Disaster Recovery | 534 |
| Run Book | 534 |
| SLAs, Risk, and Disaster Recovery | 541 |
| Planning Step 1: Assessing Risk and Defining Dependencies | 542 |
| Known Facts About Servers | 548 |
| Risks and Unknowns | 552 |
| Planning Step 2: Putting the Plan Together | 553 |
| When All Else Fails, Go to Plan B | 556 |
| Testing Disaster Recovery Plans | 556 |
| Executing Disaster Recovery Plans | 557 |
| Example Disaster Recovery Execution | 558 |
| Disaster Recovery Techniques | 560 |
| Step 1: Assessing Damage | 562 |
| Step 2: Preparing for Reconstruction | 563 |
| Step 3: Reconstructing a System | 565 |
| 13 Highly Available Upgrades | 579 |
| General Upgrade, Consolidation, and Migration Tips | 579 |
| Upgrading, Consolidating, and Migrating to SQL Server 2000 | 584 |
| Phase 1: Envisioning | 586 |
| Phase 2: Technical Considerations for Planning | 593 |
| Phase 3: Consolidation Planning—The Server Design and the Test Process | 606 |
| Phase 4: Developing | 611 |
| Phase 5: Deploying and Stabilizing | 612 |
| Windows Version Upgrades | 612 |
| Should You Upgrade Your Version of Windows? | 612 |
| Performing a Windows Version Upgrade on a Server | 614 |
| SQL Server Version Upgrades or Migrations | 617 |
| Tools for Upgrading from SQL Server 6.5 | 619 |
| Tools for Upgrading from SQL Server 7.0 | 619 |
| Upgrading Between Different Versions of SQL Server 2000 | 620 |
| Upgrading from Previous Versions of SQL Server Clustering | 621 |
| Attaching and Detaching Databases Versus Backup and Restore | 622 |
| Service Packs and Hotfixes | 624 |
| Emergency Hotfixes and Testing Requirements | 626 |
| Applying a Windows Service Pack | 627 |
| Applying a SQL Server 2000 Service Pack | 628 |
| Hotfixes | 635 |
| PART V ADMINISTERING HIGHLY AVAILABLE MICROSOFT SQL SERVERS | |
| 14 Administrative Tasks for High Availability | 639 |
| Security | 640 |
| Securing Your SQL Server Installations | 640 |
| Securing Your SQL Server-Based Applications | 646 |
| Maintenance | 649 |
| Calculating the Cost of Maintenance | 650 |
| Intrusive Maintenance | 652 |
| Defragmenting Indexes | 653 |
| Logical vs. Physical Fragmentation | 654 |
| Example: Defragmenting a VLDB That Uses Log Shipping | 655 |
| Database Corruption | 656 |
| Changing Database Options | 657 |
| Memory Management for SQL Server 2000 | 657 |
| Understanding the Memory Manager | 658 |
| Breaking the 2-GB Barrier Under 32-Bit | 663 |
| Paging File Sizing and Location | 670 |
| SQL Server Memory Recommendations | 674 |
| Managing SQL Server Resources with Other Tools | 681 |
| Transferring Logins, Users, and Other Objects to the Standby | 681 |
| Transferring Logins, Users, and Other Objects Between Instances | 682 |
| Transferring Objects | 683 |
| DTS Packages | 689 |
| 15 Monitoring for High Availability | 691 |
| Monitoring Basics | 692 |
| Setting Ground Rules | 696 |
| How Available Is Available? | 699 |
| Implementing a Monitoring Solution | 702 |
| Hardware Layer Monitoring | 702 |
| Monitoring Windows and SQL Server Events | 703 |
| Monitoring Your Monitor and Other Critical Services | 723 |
| Capacity Planning and Monitoring | 724 |
| GLOSSARY | 727 |
| INDEX | 733 |
