About the Book
Get the definitive reference to maximizing the availability of your critical, SQL Server 2000-based business systems. Developed by Microsoft field consultants who create and support high availability solutions every day, this practical guide explains how to plan, design, implement, and support highly available SQL Server 2000-based systems for both 32-bit and 64-bit environments. You get in-depth, end-to-end guidance—from evaluating availability requirements and capacity planning to implementing failover clustering, log shipping, replication, system backup and restore, system recovery, and other technologies and processes. Real-world scenarios illustrate the book’s best practices in action. You also get a CD featuring evaluation software, planning worksheets, scripts, and other on-the-job resources.
Discover how to:
Evaluate technical, staffing, and budget requirements
Plan and configure disk subsystems for performance and availability
Configure Microsoft Windows 2000 Server or Microsoft Windows Server 2003 to optimize SQL Server 2000 availability
Plan, implement, and maintain a base server cluster and Network Load Balancing
Plan and configure failover clustering
Use built-in log shipping features or code your own
Create comprehensive backup and restore plans
Choose replication methods for high availability—and expose replication architecture
Develop prevention and disaster recovery strategies; sample runbook and scripts included on CD
Minimize downtime for server upgrades and service pack installations
Baseline performance and use availability-monitoring tools and techniques
CDs feature:
120-day evaluation version of SQL Server 2000 Enterprise Edition
Scripts for custom log shipping, backup and restoration, system monitoring, and other administrative tasks
Sample disaster-recovery runbook
Worksheets, checklists, and templates
Fully searchable eBook
For customers who purchase an ebook version of this title, instructions for downloading the CD files can be found in the ebook.
Table of Contents:
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
About the Author :
Founded in 1975, Microsoft (NASDAQ 'MSFT') is the worldwide leader in software for personal and business computing. The company offers a wide range of products and services designed to empower people through great software—any time, any place, and on any device.
Allan Hirt, Consultant, Microsoft Consulting Services (MCS), is the main writer. Contributors include Kimberly Tripp, SQL Server Trainer, Cathan Cook, Consultant, MCS, and Frank McBath, Technologist, Microsoft, who will write selected chapters.
Allan has been involved extensively in work with SQL Server and high availability over the past two years. His credentials include: • The 'SQL Server 2000 Failover Clustering" white paper posted to Microsoft.com • Authoring part of the courseware for the SQL Server High Availability Event in Redmond, WA (June 2001) • Leading SQL Server 2000 High Availability Post-Training at MGB, 2001 • Authoring the customer-ready version of the High Availability Event material (both the one-day and the three-day) • Wrote chapters (including “Log Shipping,” “Failover Clustering,” “Five Nines,” and “Federated Servers”) for the SQL Server 2000 Resource Kit • Speaking on high availability at conferences such as TechEd, MGB, and SQL PASS • Subject Matter Expert for the internal eKM Database community, 2001
