Home Page Icon
Home Page
Table of Contents for
I. The High Availability Primer
Close
I. The High Availability Primer
by Frank McBath, Kimberly Tripp, Cathan Cook, Allan Hirt
Microsoft® SQL Server™ 2000 High Availability
Microsoft® SQL Server™ 2000 High Availability
A Note Regarding Supplemental Files
Foreword
Preface
Acknowledgments
Allan Hirt
Cathan Cook
Frank McBath
Kimberly L. Tripp
I. The High Availability Primer
1. Preparing for High Availability
High Availability—What It Is and How to Get It
Prevention
Disaster Recovery
Agreeing on a Solution
The Project Team
Guiding Principles for High Availability
Making Trade-Offs
Identifying Risks
Next Steps
Availability Calculations and Nines
Calculating Availability
What Is a Nine?
What Level of Nines Is Needed?
Negotiating Availability
Types of Unavailability
Where Does Availability Start?
Assessing Your Environment for Availability
The Cost of Availability
Barriers to Availability
Summary
2. The Basics of Achieving High Availability
Data Center Best Practices
Location
Security
Cabling, Power, Communications Systems, and Networks
Third-Party Hosting
Support Agreements
The "Under the Desk" Syndrome
Staffing
Creating a Database Team
Service Level Agreements
Manage Change or Be Managed by It
Change Management for Databases: The Basics
Development, Testing, and Staging Environments
Maintaining Separate Environments
Change Request Form
Managing Change and Availability in Development
High Availability Is Not Just an IT Problem
Version Control
Testing Applications for High Availability
Managing Change in Production
Preparing for Change
The Change Plan
Contingency Plans
Implementation Team
Determining an Implementation Window
Planning the Implementation, Twice
Testing the Implementation
Release Readiness Review
Completing a Change Request Form
Implementing Change
Step 1: Notify Users of the Systems
Step 2: Back Up, Back Up, Back Up
Step 3: The Rollout
Step 4: When It Is All Over
System and Process Standardization
Documentation
Summary
3. Making a High Availability Technology Choice
Windows Clustering
Server Clusters
Cluster Components
Cluster Concepts
Network Load Balancing Clusters
Geographically Dispersed Clusters
SQL Server 2000
Failover Clustering
SQL Server 2000 Failover Clustering versus Previous SQL Server Clustering Implementations
How SQL Server Failover Clustering Works
Clients and SQL Server 2000 Failover Clustering
Failover Clustering and Wasted Resources
Log Shipping
Log Shipping Transactions
Clients and Log Shipping
Replication
Replication Issues
Clients and Replication
Backup and Restore
Decisions, Decisions ...
The Decision Process
Step 1: Analyzing Requirements
Step 2: Comparing Technologies
Step 3: Selecting Technology
A Comparison of the SQL Server Technologies
Choosing Between Log Shipping and Transactional Replication
Choosing Between Failover Clustering and Log Shipping or Replication
Using the SQL Server Availability Technology Comparison Table
What Should You Use?
Summary
II. Technology Building Blocks
4. Disk Configuration for High Availability
Quick Disk Terminology Check
Capacity Planning
Raw Disk Space Needed
Application Database Usage
Application Schema
Indexes
Deletes, Inserts, Selects, and Updates
Understanding Physical Disk Performance
Using SQL Server to Assist with Disk Capacity Planning
Types of Disk Subsystems
Direct-Attached Storage
Network-Attached Storage
Performance Considerations
Using Network-Attached Storage as a Data Store with SQL Server
Storage Area Networks
What Disk Technology to Use
Server Clusters, Failover Clustering, and Disks
Pre-Windows Disk Configuration
Number of Spindles Needed
Step 1—Amount of Data Being Returned
Step 2—Individual Drive Throughput
Step 3—Calculating Number of Spindles
Understanding Disk Drives
Understanding Your Hardware
Understanding How SQL Server Interacts with Disks
Types of SQL Server Systems
Understanding Disk Cache
A RAID Primer
Striping
Mirroring
Striped Mirrors
Mirrored Stripes
Striping with Parity
Hardware RAID versus Software RAID
Remote Mirroring
Storage Composition
Types of Disks and File Systems in Windows
Formatting the Disks
File Placement and Protection
System Databases and Full-Text Indexes
User Databases
Databases, the Quorum, and Failover Clustering
Files and Filegroups
Database File Size
Shrinking Databases and Files
Configuration Example
The Scenario
Sample Drive Configurations
Sample 1
Sample 2
Sample 3
Sample 4
Sample 5
Summary
5. Designing Highly Available Microsoft Windows Servers
General Windows Configuration for SQL Servers
Choosing a Version of Windows
32-Bit Versus 64-Bit
Versions of SQL Server and Windows Server 2003
Disk Requirements for Windows
Security
Terminal Server
Windows Server 2003 Enhancements
Server Roles
Shutdown
High Availability Options for Windows
Windows Reliability Features
Windows 2000 Server
Windows Server 2003
Server Clusters
Planning a Server Cluster
Types of Server Clusters
Disk Subsystem
Cluster Service Account
Server Clusters, the Hardware Compatibility List, and the Windows Catalog
Certified Cluster Applications
Ports, Firewalls, Remote Procedure Calls, and Server Clusters
Geographically Dispersed Clusters
Antivirus Programs, Server Clusters, and SQL Server
Server Clusters, Domains, and Networking
Network Configuration
Network Card Configuration
Public Network Configuration
Private Network (Heartbeat) Configuration
Implementing a Server Cluster
Preconfiguration Tasks
Configuring Network Cards
Network Cards Used on the Public Network
Network Cards Used on the Private Network
Changing Network Priority
Verifying Your Network Connectivity
Creating the Shared Disks
Installing the Server Cluster
Postconfiguration Tasks
Configuring Network Priorities
Enabling Kerberos
Changing the Size of the Cluster Log
Configuring MS DTC
Creating MS DTC on Windows 2000 Server
Creating MS DTC on Windows Server 2003
Verifying Your Server Cluster Installation
Verifying Connectivity and Name Resolution
Failover Validation
Server Cluster Administration
Changing Domains
Changing a Node’s IP Address or Name
Changing Service Accounts and Passwords
Windows 2000 Server
Windows Server 2003
Disk Management
Adding a New Disk
Using DISKPART to Expand Disk Capacity
Forcing Quorum for an MNS Cluster
Network Load Balancing
General Network Load Balancing Best Practices
Implementing Network Load Balancing for SQL Server–Based Architectures
Configuring the Network Cards
Configuring Network Load Balancing for Use with SQL Server
Starting Network Load Balancing on the Primary
Verifying a Network Load Balancing Cluster
Switch Method
Read-Only SQL Servers
Adding a Network Load Balancing Cluster to DNS
Configuring Logging for Network Load Balancing Manager
Uninstalling Network Load Balancing
Summary
III. Microsoft SQL Server Technology
6. Microsoft SQL Server 2000 Failover Clustering
Planning for Failover Clustering
Versions of Windows Supported
Number of SQL Server 2000 Instances per Server Cluster
Name of the SQL Server Virtual Server
Number of Nodes
Disks
IP Addresses, Ports, and Network Card Usage
Applications and Failover Clustering
Third-Party Applications, File Shares, Dependencies, and SQL Server 2000 Failover Clustering
Hardware-Assisted Backups and SQL Server 2000 Failover Clustering
Service Accounts and SQL Server 2000 Failover Clustering
Memory
Coexistence with Stand-Alone Instances and Other Versions of SQL Server
Analysis Services and Failover Clustering
SQL Mail and Failover Clustering
Exchange and SQL Server on the Same Cluster
Cluster Group Configuration for Failover Clustering
Implementing SQL Server 2000 Failover Clustering
Prerequisites
Installation Order
Windows 2000 Advanced Server and Windows 2000 Datacenter Server
Windows Server 2003 Enterprise Edition and Windows Server 2003 Datacenter Edition
Installing a SQL Server Virtual Server
Postinstallation Tasks
Configuring Antivirus Software
Adding Drives for SQL Server Use
Verifying the Drive Configuration
Disabling Unnecessary Services
Assigning a Static Port Number
Enabling Advanced Security
IPSec
Kerberos
SSL Certificates
Configuring SQL Server Resources
Setting Preferred Owners
Failing Back to the Preferred Owner, Thresholds, and Other Parameters
Resource Group Failback
Configuring Resource Parameters
Cluster Group Thresholds
Verifying Your Failover Cluster Installation
Verifying Connectivity and Name Resolution
Verifying Connectivity and Name Resolution from a Server Node
Verifying Connectivity and Name Resolution from a Client
Validating Failover
Verifying the SQL Server Service Account and Node Participation
Verifying the Application with Failover
Administering SQL Server Virtual Servers
Ensuring a Virtual Server Will Not Fail Due to Other Service Failures
Adding or Removing a Cluster Node from the Virtual Server Definition and Adding, Changing, or Updating a TCP/IP Address
Renaming a SQL Server 2000 Virtual Server
Uninstalling a SQL Server Virtual Server
Manually Removing Failover Clustering
Manually Removing Clustered Instances of SQL Server
Changing SQL Server Service Accounts
SQL Server Service Account
SQL Server Agent Service Account
Changing Domains
Troubleshooting SQL Server 2000 Failover Clusters
Barriers for Failover Clustering
Support for Failover Clustering
The Troubleshooting Process
Where to Look for Problems
Microsoft Product Support Reporting Tool
Common Troubleshooting Issues
Installation Problems
Connectivity
Full-Text Search Setup
Disaster Recovery for Failover Clustering
Scenario 1: Quorum Disk Failure
Scenario 2: Cluster Database Corruption on a Node
Scenario 3: Quorum Corruption
Scenario 4: Checkpoint Files Lost or Corrupt
Scenario 5: Cluster Node Failure
Single Node Failure
Multiple Node Failure
All Nodes Failed
Scenario 6: A Cluster Disk Is Corrupt or Nonfunctional
If You Do Not Have Backups
Problems on One or More Cluster Nodes
Bad Quorum Disk, Intact Cluster Nodes
Corrupt Cluster Database on One Node
Corrupt Quorum
Corrupt or Missing Checkpoint Files
Corrupt or Bad Cluster Disk
Summary
7. Log Shipping
Uses of Log Shipping
Basic Considerations for All Forms of Log Shipping
Ask the Right Questions
Business Questions
Technical Questions
How Current Do You Need To Be?
Secondary Server Capacity and Configuration
Log Shipping More Than One Database to One Secondary
Disk Space, Retention, and Archiving
Full-Text Searching and Log Shipping
Recovery Models and Log Shipping
Network Bandwidth
Logins and Other Objects
Stored Procedures, Extended Stored Procedures, Functions, and More
Logins
DTS Packages
Clients, Applications, and Log Shipping
Coding Your Application for Log Shipping
Role Change
Security
SQL Server Startup Account
Securing the Transfer of Logins
Securing the Backup Share
Log Shipping Across Domains
Authentication for Log Shipping
Log Shipping and Database Backups
Service Packs and Log Shipping
Files, Filegroups, and Transaction Logs
Custom Log Shipping Versus Microsoft’s Implementation
Configuring and Administering the Built-In Functionality Using SQL Server 2000 Enterprise Edition
Log Shipping Components
Stored Procedures
Jobs
Log Shipping Monitor
Configuring Log Shipping
Step 1: Restoring the Initial Backup on the Secondary
Step 2: Running the Database Maintenance Plan Wizard
Step 3: Post-Wizard Configuration Tasks
Task 1: Configure Transfer Logins Task DTS Package
Task 2: Set Up a Job to bcp out SYSLOGINS
Task 3: Configure the Role Change SQL Server Agent Jobs for Manual Execution
Step 4: Verifying and Testing the Log Shipping Pair
Task 4: Modify sp_resolve_logins
Troubleshooting Log Shipping
Administering Log Shipping
Removing Log Shipping
Deleting a Secondary
Monitoring Log Shipping
Changing Log Shipping Parameters After Configuration
Database Maintenance Plan
Log Shipping Monitor
Moving the Log Shipping Monitor
Step 1: Configure the New Log Shipping Monitor
Step 2: Update the log_shipping_monitor Table
Step 3: Verify the New Log Shipping Monitor
Step 4: Delete Old Monitor History and Entries
Adding Additional Secondaries
Role Changes
Types of Role Changes
Performing a Role Change
Switching Back to the Original Primary
Creating a Custom Coded Log Shipping Solution
Log Shipping From SQL Server 7.0 to SQL Server 2000
Configuring Log Shipping from SQL Server 7.0 to SQL Server 2000
Summary
8. Replication
Using Replication to Make a Database Available
Choosing a Replication Model for Availability
Merge Replication
Snapshot Replication
Transactional Replication
Switch Methods and Logins
Replication and Database Schemas
Primary Keys
Uniqueidentifier Columns and Merge Replication
Schema Changes, Data Definition Language, and Replication
Text and Image Fields
Integer Columns
Timestamp Columns
Server Collations and Case Sensitivity
Highly Available Replication Architecture
Replication Agents
Snapshot Agent
Distribution Agent
Log Reader Agent
Merge Agent
Queue Reader Agent
Scenario 1: Separate Publisher and Distributor
Scenario 2: Using a Republisher
SQL Server Service Packs and Replication
Planning Disk Capacity for Replication
Disaster Recovery with a Replicated Environment
Backing Up Replication Databases
Publisher
Distributor
Using the Sync with Backup Option with Transactional Replication Publisher and Distributor
Subscriber
System Databases
Disaster Recovery Restore Scenarios
Scenario 1
Scenarios 2 and 4
Scenario 3
Scenario 5
Scenario 6
Scenario 7
Log Shipping and Replication
Transactional Replication and Log Shipping
Synchronous Mode
Semisynchronous Mode
Merge Replication and Log Shipping
Semisynchronous Mode
Alternate Synchronization Partners
Performing a Role Change Involving Replication
Summary
9. Database Environment Basics for Recovery
Fundamentals
Technology Last
Understanding Your Backup and Restore Barriers
Hardware Failure
User Error
Minimizing Human Error
Symptoms and Recovery
Backup
Understanding Database Structures
Understanding the Write-Ahead Log
Optimizing the Performance of the Transaction Log
Understanding Continuity of the Transaction Log
What If the Transaction Log Fills?
Breaking the Continuity of the Transaction Log
Properly Managing the Transaction Log
Initial Database Settings and Recovery Models
Understanding Recovery Model Settings by Default
Understanding Log Behavior on Initial Database Creation
Recovery Models
Understanding the Purpose of Recovery Models
The "Tail" of the Log and Recovery Models
Choosing the Right Recovery Model
Full Recovery Model
Bulk-Logged Recovery Model
Simple Recovery Model
Choosing the Right Recovery Model: An Example Test Case
Changing Between Recovery Models
Backup Types
Full Database Backups
How Do Full Database Backups Work?
Transaction Log Backups
How Do Transaction Log Backups Work?
The Effects of Recovery Models on the Transaction Log
Differential Database Backups
How Differential Database Backups Work
Full File/Filegroup Backups
Differential File/Filegroup Backups
Summary
10. Implementing Backup and Restore
Creating an Effective Backup Strategy
Backup Retention
Devising a Backup Strategy to Create an Optimal Recovery Strategy
The Full Database–Based Backup Strategy
Case Study: A Differential Rotation Schedule and Cost Analysis
The File-Based Backup Strategy
What Both Strategies Do Well
The Pros and Cons of the Full Database–Based Backup Strategy
The Pros and Cons of the File-Based Backup Strategy
Implementing Your Backup Strategy
Options for Performing a Backup
Creating a Backup Device
Special Backup Device: NUL
Parallel Striped Backup
Multifile Backups
Useful Backup Options
Defining a media set
Defining an individual backup
Manipulating the media or backup device
Working with tape devices
Third-Party Backup and Restore Tools and SQL Server
Storage Assisted Backups
Split-Mirror Basics, Pros, and Cons
Windows Server 2003 Volume Shadow Copy Service and SQL Server 2000
Executing the Full Database–Based Backup Strategy Using Transact-SQL
Executing a Full Database Backup
Executing a Transaction Log Backup
Executing a Differential Database Backup
Executing the File-Based Backup Strategy Using Transact-SQL
Simplifying and Automating Backups
Creating a Production SQL Server Agent Backup Job
Checking the Completion of a Backup
Verifying Backups
Implementing an Effective Backup Strategy: In Summary
Database Recovery
Phases of Recovery
File Creation
Media Copy
Redo and Undo
Recovery Completion States
RECOVERY
NORECOVERY
STANDBY
Useful RESTORE Options
Disaster Recovery with Backup and Restore
Recovering from Hardware Failure: In Place and Up to the Minute
Recovering In Place and Up to the Minute with Full Database–Based Backup Strategy
Recovering In Place and Up to the Minute with the File-Based Backup Strategy
Additional Recovery Examples
Point-In-Time Recovery
Recovery Plan
Moving Data on a RESTORE
Collected Wisdom and Good Ideas for Backup and Restore
Backing Up the Operating System
Using Backup
Creating a Backup
Restoring a Backup
Backing Up and Restoring Clustered Environments
Backing Up a Standard Server Cluster
Third-Party Backup Software and SQL Server 2000 Failover Clustering
Summary
IV. Putting the Pieces of the Puzzle Together
11. Real-World High Availability Solutions
The Scenario
Conditions and Constraints
The Planning Process
Step 1: Breaking Down the Requirements
Step 2: Considering Technologies
Step 3: Designing the Architecture
Designing the Hardware
People and Processes
Step 4: Choosing Hardware and Costs
Server Cluster
Reporting Server and Log Shipped Secondary
Rethinking the Strategy
Exercise Summary
Case Study: Microsoft.com
Background Information
Planning and Development
How Microsoft.com Achieves High Availability in Production
Microsoft.com’s Barriers to Availability
Summary
12. Disaster Recovery Techniques for Microsoft SQL Server
Planning for Disaster Recovery
Run Book
Items to Place in a Run Book
SQL Server Administrative Information
Analysis Services Administrative Information
Application System Information
Database Components
Server Configuration
SQL Server Configuration
Network Information and Configuration
Storage Information and Configuration
Other Necessary Information
SLAs, Risk, and Disaster Recovery
Planning Step 1: Assessing Risk and Defining Dependencies
Example Scenario
Known Facts About Servers
BizTalk
Commerce Server
Content Management Server
CustomDB1, CustomDB2, and CustomDB3
Other Servers
Risks and Unknowns
Planning Step 2: Putting the Plan Together
When All Else Fails, Go to Plan B
Testing Disaster Recovery Plans
Executing Disaster Recovery Plans
Example Disaster Recovery Execution
Roles and Responsibilities
Recovery Timeline
Disaster Recovery Techniques
Step 1: Assessing Damage
Step 2: Preparing for Reconstruction
Step 3: Reconstructing a System
Restoring the Tempdb Database
Step 1: Rebuilding and Restoring Master
Rebuilding Master for 32-Bit SQL Servers
Rebuilding Master for 64-Bit SQL Server
Restoring the Master Database
Step 2: Restoring the Msdb Database
Restoring the Model Database
Restoring User Databases
Restoring the Full-Text Indexes
Synchronizing Logins
Summary
13. Highly Available Upgrades
General Upgrade, Consolidation, and Migration Tips
Upgrading, Consolidating, and Migrating to SQL Server 2000
Phase 1: Envisioning
Profile Target Systems
Gathering System Performance Metrics
Other SQL Server Information to Gather
Phase 2: Technical Considerations for Planning
Single Instance Versus Multiple Instances
Application Compatibility with Instances
Disk Subsystem
Memory and Processor
Networking
Security and Logins
High Availability
Replication
Migrating Objects
Administration
Chargeback
System Databases
Collations and Sort Orders
Other Technical Considerations
Phase 3: Consolidation Planning—The Server Design and the Test Process
Designing the New Production Servers
Migration of Applications, Users, and Data
Test the Process
Determining Risk
Phase 4: Developing
Phase 5: Deploying and Stabilizing
Windows Version Upgrades
Should You Upgrade Your Version of Windows?
Performing a Windows Version Upgrade on a Server
Upgrading Stand-Alone Servers
Upgrading Clustered Servers
SQL Server Version Upgrades or Migrations
Tools for Upgrading from SQL Server 6.5
Tools for Upgrading from SQL Server 7.0
Upgrading Between Different Versions of SQL Server 2000
Upgrading from Previous Versions of SQL Server Clustering
Attaching and Detaching Databases Versus Backup and Restore
Service Packs and Hotfixes
Emergency Hotfixes and Testing Requirements
Applying a Windows Service Pack
Windows Service Packs and Server Clusters
Applying a SQL Server 2000 Service Pack
Failover Clustering and SQL Server 2000 Service Packs
Log Shipping and SQL Server 2000 Service Packs
Replication and SQL Server 2000 Service Packs
Applying a SQL Server Service Pack to a Nonwritable Database or Filegroup
Nonwritable Database
Nonwritable Filegroup
Hotfixes
Summary
V. Administering Highly Available Microsoft SQL Servers
14. Administrative Tasks for High Availability
Security
Securing Your SQL Server Installations
Securing Your SQL Server–Based Applications
Maintenance
Calculating the Cost of Maintenance
Intrusive Maintenance
Defragmenting Indexes
Logical vs. Physical Fragmentation
Example: Defragmenting a VLDB That Uses Log Shipping
Database Corruption
Changing Database Options
Memory Management for SQL Server 2000
Understanding the Memory Manager
Breaking the 2-GB Barrier Under 32-Bit
/3GB boot.ini Switch
The /USERVA boot.ini Switch
Physical Address Extensions (PAE)
Combining /PAE and /3GB
Address Windowing Extensions (AWE)
Paging File Sizing and Location
Option 1: Through the Graphical User Interface (GUI)
Option 2: Through the Registry
SQL Server Memory Recommendations
Memory and Multiple Instances on the Same Server or Server Cluster
Managing SQL Server Resources with Other Tools
Transferring Logins, Users, and Other Objects to the Standby
Transferring Logins, Users, and Other Objects Between Instances
Transferring Logins and Users
Orphaned Users
Transferring Objects
Generating SQL Scripts
Using a DTS Package to Transfer Objects
DTS Packages
Summary
15. Monitoring for High Availability
Monitoring Basics
Setting Ground Rules
How Available Is Available?
Implementing a Monitoring Solution
Hardware Layer Monitoring
Monitoring Windows and SQL Server Events
Tools for Monitoring and Alerting
Sqldiag.exe
System Monitor
Event Viewer
Log Files
SQL Server Profiler
WMI
Microsoft Operations Manager
SQL Server Alerts
SQL Server Notification Services
Monitoring System Uptime
Using System Monitor
Automated Collection of Data
Using Sysperfinfo
Profiler/Trace Core
Monitoring Extended Blocking and Deadlocks
Monitoring Database and Transaction Log Space
Monitoring Index Fragmentation
Monitoring Read/Write Statistics per File
Monitoring Your Monitor and Other Critical Services
Capacity Planning and Monitoring
Summary
Glossary
Allan Hirt
Allan Hirt
Cathan Cook
Frank McBath
Kimberly L. Tripp
System Requirements
Index
About the Authors
Copyright
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Prev
Previous Chapter
Acknowledgments
Next
Next Chapter
1. Preparing for High Availability
Part I. The High Availability Primer
Chapter 1
Chapter 2
Chapter 3
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset