Microsoft SQL Server High Availability
Published on 5. November 2004
Book
Paperback/Softback
456 pages
978-0-672-32625-7 (ISBN)
Description
Offers example based coverage for various high availability solutions with Microsoft SQL Server.
More details
Language
English
Place of publication
Indianapolis
United States
Publishing group
Pearson Education (US)
Target group
Professional and scholarly
ISBN-13
978-0-672-32625-7 (9780672326257)
Copyright in bibliographic data and cover images is held by Nielsen Book Services Limited or by the publishers or by their respective licensors: all rights reserved.
Schweitzer Classification
Person
Microsoft SQL Server High AvailabilityAbout the Author
Paul Bertucci is the founder of Database Architechs (http://www.dbarchitechs.com) a database consulting firm with offices in the United States and Paris, France. He has more than 24 years of experience doing database design, data architecture, data replication, performance and tuning, distributed data systems, data integration, high availability assessments, and systems integration for numerous Fortune 500 companies including Intel, 3COM, Coca-Cola, Apple, Toshiba, Lockheed, Wells Fargo, Safeway, Texaco, Charles Schwab, Cisco Systems, Sybase, and Honda, to name a few. He has authored numerous articles, standards, and high profile courses such as Sybase's "Performance and Tuning" and "Physical Database Design" courses. Other Sams books that he has authored include the highly popular Microsoft SQL Server 2000 Unleashed and ADO.NET in 24 Hours. He has deployed numerous systems with MS SQL Server, Sybase, DB2, and Oracle database engines, and has designed/architected several commercially available tools in the database, data modeling, performance and tuning, data integration, and multi-dimensional planning spaces. Paul also serves part time as CTO for a strategic planning software company and part time as chief technical advisor for a data integration server software company. Paul received his formal education in computer science and electrical engineering from UC Berkeley (Go Bears!). He lives in northern California with his wife, Vilay, and five children, Donny, Juliana, Paul Jr., Marissa, and Nina. Paul can be reached at pbertucci@dbarchitechs.com or by phone at 925-674-0000.
(c) Copyright Pearson Education. All rights reserved.
Paul Bertucci is the founder of Database Architechs (http://www.dbarchitechs.com) a database consulting firm with offices in the United States and Paris, France. He has more than 24 years of experience doing database design, data architecture, data replication, performance and tuning, distributed data systems, data integration, high availability assessments, and systems integration for numerous Fortune 500 companies including Intel, 3COM, Coca-Cola, Apple, Toshiba, Lockheed, Wells Fargo, Safeway, Texaco, Charles Schwab, Cisco Systems, Sybase, and Honda, to name a few. He has authored numerous articles, standards, and high profile courses such as Sybase's "Performance and Tuning" and "Physical Database Design" courses. Other Sams books that he has authored include the highly popular Microsoft SQL Server 2000 Unleashed and ADO.NET in 24 Hours. He has deployed numerous systems with MS SQL Server, Sybase, DB2, and Oracle database engines, and has designed/architected several commercially available tools in the database, data modeling, performance and tuning, data integration, and multi-dimensional planning spaces. Paul also serves part time as CTO for a strategic planning software company and part time as chief technical advisor for a data integration server software company. Paul received his formal education in computer science and electrical engineering from UC Berkeley (Go Bears!). He lives in northern California with his wife, Vilay, and five children, Donny, Juliana, Paul Jr., Marissa, and Nina. Paul can be reached at pbertucci@dbarchitechs.com or by phone at 925-674-0000.
(c) Copyright Pearson Education. All rights reserved.
Content
Introduction.
Five 9s.
Who Is This Book's Intended Audience?
How This Book Is Organized.
Conventions Used in This Book.
Setting Your Goals High!
I. UNDERSTANDING HIGH AVAILABILITY.
1. Essential Elements of High Availability.
Overview of High Availability.
Calculating "Availability".
Availability Example-A 24/7/365 Application.
Availability Continuum.
Availability Variables.
General Design Approach for Achieving High Availability.
Development Methodology with High Availability "Built In".
Assessing Existing Applications.
Service Level Agreement.
High Availability Business Scenarios (Applications).
Application Service Provider.
Worldwide Sales and Marketing-Brand Promotion.
Investment Portfolio Management.
Call Before You Dig.
Microsoft Technologies that Yield High Availability.
Summary.
II. CHOOSING THE RIGHT HIGH AVAILABILITY APPROACHES.
2. Microsoft High Availability Options.
What High Availability Options Are There?
Fundamental Areas to Start With.
Fault Tolerant Disk: RAID and Mirroring.
Redundant Array of Independent Disks (RAID).
Mitigate Risk by Spreading Out Server Instances.
Building Your HA Solution with One or More of These Options.
Microsoft Cluster Services (MSCS).
SQL Clustering.
Data Replication.
Log Shipping.
Distributed Transactions.
Summary.
3. Choosing High Availability.
Moving Toward High Availability.
Step 1-Launching a Phase 0 (Zero) HA Assessment.
Resources for a Phase 0 HA Assessment.
The Phase 0 HA Assessment Tasks.
Step 2-HA Primary Variables Gauge.
Step 3-Determining the Optimal HA Solution.
A Hybrid High Availability Selection Method.
Cost Justification of a Selected High Availability Solution.
Adding HA Elements to Your Development Methodology.
Summary.
III. IMPLEMENTING HIGH AVAILABILITY.
4. Microsoft Cluster Services.
Understanding Microsoft Cluster Services.
Hardware/Network/OS Requirements for MSCS.
How Clustering Actually Works.
The Disk Controller Configuration.
The Disk Configuration.
Network Configuration.
Considerations at the Operating System Level.
Installing MSCS.
Pre-installation.
Installing MSCS-Step 1.
Installing MSCS for the Next Node: Step 2.
Extending Clustering with Network Load Balancing (NLB).
Windows 2003 Options for Quorum Disks and Fail-over.
4-node and 8-node Clustering Topologies.
Summary.
5. Microsoft SQL Server Clustering.
Microsoft SQL Clustering Core Capabilities.
SQL Clustering Is Built on MSCS.
Configuring MS DTC for Use with SQL Clustering.
Laying Out a SQL Cluster Configuration.
Installing SQL Clustering.
Failure of a Node.
Removing SQL Clustering.
Client Test Program for a SQL Cluster.
A Node Recovery.
Application Service Provider-Scenario #1 with SQL Clustering.
Summary.
6. Microsoft SQL Server Log Shipping.
Microsoft Log Shipping Overview.
Data Latency and Log Shipping.
Design and Administration Implications of Log Shipping.
Setting Up Log Shipping.
Before Creating the Log Shipping DB Maintenance Plan.
Using the DB Maintenance Plan Wizard to Create.
Log Shipping.
Viewing Log Shipping Properties.
Changing the Primary Role.
Log Shipping System Stored Procedures.
Call Before You Dig-Scenario #4 with Log Shipping.
Summary.
7. Microsoft SQL Server Data Replication.
Microsoft SQL Server Data Replication Overview.
What Is Data Replication?
The Publisher, Distributor, and Subscriber Metaphor.
Publications and Articles.
Filtering Articles.
Replication Scenarios.
Central Publisher.
Central Publisher with Remote Distributor.
Publishing Subscriber.
Central Subscriber.
Multiple Publishers or Multiple Subscribers.
Updating Subscribers.
Subscriptions.
Pull Subscriptions.
Push Subscriptions.
Anonymous Subscriptions (Pull Subscriptions).
The Distribution Database.
Replication Agents.
The Snapshot Agent.
The Log Reader Agent.
The Distribution Agent.
The Merge Agent.
The Miscellaneous Agents.
Planning for SQL Server Data Replication.
Timing, Latency, and Autonomy of Data.
Methods of Data Distribution.
SQL Server Replication Types.
Snapshot Replication.
Transactional Replication.
Merge Replication.
User Requirements Drive the Replication Design.
Setting Up Replication.
Enable a Distributor.
Enable Publishing/Configure the Publisher.
Creating a Publication.
Creating Subscriptions.
Switching Over to a Warm Standby (Subscriber).
Scenarios That Will Dictate Switching to the Warm Standby.
Switching Over to a Warm Standby (Subscription).
Turning the Subscriber into a Publisher (if Needed).
Insulate the Client Using an NLB Cluster Configuration.
Scripting Replication.
Monitoring Replication.
SQL Statements.
SQL Enterprise Manager.
The Performance Monitor.
Backup and Recovery in a Replication Configuration.
Alternate Synchronization Partners.
Worldwide Sales and Marketing-Scenario #2 with Data Replication.
Summary.
8. Other Ways to Distribute Data for High Availability.
Alternate Ways to Achieve High Availability.
A Distributed Data Approach from the Outset.
Setting Up Access to Remote SQL Servers.
Querying a Linked Server.
Transact-SQL with Linked Servers.
Distributed Transactions.
MS DTC Architecture.
Two-Phase Commit Protocol.
COM+ Applications for HA.
Summary.
9. High Availability Pieced Together.
Achieving Five 9s.
Foundation First.
Assemble Your HA Assessment Team.
Set the HA Assessment Project Schedule/Timeline.
Doing a Phase 0 High Availability Assessment.
Step 1-HA Assessment.
Step 2-Primary Variable Gauge Specification.
High Availability Tasks Integrated into Your Development Life Cycle.
Selecting the HA Solution.
Is the HA Solution Cost Effective?
Summary.
10. High Availability Design Issues and Considerations.
Things to Consider for High Availability.
Hardware/OS/Network Design Considerations.
Remote Mirroring.
Microsoft Cluster Services Design Considerations.
SQL Server Clustering Design Considerations.
Stretch Clustering.
SQL Server Data Replication Design Considerations.
SQL Server Log Shipping Design Considerations.
Distributed Transaction Processing Design Considerations.
General SQL Server File/Device Placement Recommendations.
Database Backup Strategies in Support of High Availability.
Two Backup Approaches for High Availability.
Parallel Striped Backup.
Split-Mirror Backups (Server-less Backups).
Volume Shadow Copy Service (VSS).
Monitoring/Verifying Backups.
Disaster Recovery Planning.
The Overall Disaster Recovery Approach.
The Focus for Disaster Recovery.
Documenting Environmental Details Using SQLDIAG.EXE.
Plan and Execute a Complete Disaster Recovery test.
Software Upgrade Considerations.
High Availability and MS Analysis Services/OLAP.
OLAP Cubes Variations.
Recommended MSAS Implementation for High Availability.
Alternative Techniques in Support of High Availability.
Data Transformation Service (DTS) Packages Used to Achieve HA.
Have You Detached a Database Recently?
Third-party Alternatives to High Availability.
IBM/DB2 High Availability Example.
Summary.
11. High Availability and Security.
Security Breakdowns' Effect on High Availability.
Using an Object Permissions and Roles Method.
Object Protection Using Schema-Bound Views.
Proper Security in Place for HA Options.
MSCS Security Considerations.
SQL Clustering Security Considerations.
Log Shipping Security Considerations.
Data Replication Security Considerations.
General Thoughts on Database Backup/Restore.
Isolating SQL Roles, and Disaster Recovery Security Considerations.
Summary.
12. Future Directions of High Availability.
Microsoft Stepping Up to the Plate.
What's Coming in Yukon for High Availability?
Enhancements in Fail-over Clustering (SQL Clustering).
Database Mirroring for Fail-over.
Combining Fail-over and Scale Out Options.
Data Access Enhancements for Higher Availability.
High Availability from the Windows Server Family Side.
Microsoft Virtual Server 2005.
Virtual Server 2005 and Disaster Recovery.
Other Industry Trends in High Availability.
Summary.
Index.
Five 9s.
Who Is This Book's Intended Audience?
How This Book Is Organized.
Conventions Used in This Book.
Setting Your Goals High!
I. UNDERSTANDING HIGH AVAILABILITY.
1. Essential Elements of High Availability.
Overview of High Availability.
Calculating "Availability".
Availability Example-A 24/7/365 Application.
Availability Continuum.
Availability Variables.
General Design Approach for Achieving High Availability.
Development Methodology with High Availability "Built In".
Assessing Existing Applications.
Service Level Agreement.
High Availability Business Scenarios (Applications).
Application Service Provider.
Worldwide Sales and Marketing-Brand Promotion.
Investment Portfolio Management.
Call Before You Dig.
Microsoft Technologies that Yield High Availability.
Summary.
II. CHOOSING THE RIGHT HIGH AVAILABILITY APPROACHES.
2. Microsoft High Availability Options.
What High Availability Options Are There?
Fundamental Areas to Start With.
Fault Tolerant Disk: RAID and Mirroring.
Redundant Array of Independent Disks (RAID).
Mitigate Risk by Spreading Out Server Instances.
Building Your HA Solution with One or More of These Options.
Microsoft Cluster Services (MSCS).
SQL Clustering.
Data Replication.
Log Shipping.
Distributed Transactions.
Summary.
3. Choosing High Availability.
Moving Toward High Availability.
Step 1-Launching a Phase 0 (Zero) HA Assessment.
Resources for a Phase 0 HA Assessment.
The Phase 0 HA Assessment Tasks.
Step 2-HA Primary Variables Gauge.
Step 3-Determining the Optimal HA Solution.
A Hybrid High Availability Selection Method.
Cost Justification of a Selected High Availability Solution.
Adding HA Elements to Your Development Methodology.
Summary.
III. IMPLEMENTING HIGH AVAILABILITY.
4. Microsoft Cluster Services.
Understanding Microsoft Cluster Services.
Hardware/Network/OS Requirements for MSCS.
How Clustering Actually Works.
The Disk Controller Configuration.
The Disk Configuration.
Network Configuration.
Considerations at the Operating System Level.
Installing MSCS.
Pre-installation.
Installing MSCS-Step 1.
Installing MSCS for the Next Node: Step 2.
Extending Clustering with Network Load Balancing (NLB).
Windows 2003 Options for Quorum Disks and Fail-over.
4-node and 8-node Clustering Topologies.
Summary.
5. Microsoft SQL Server Clustering.
Microsoft SQL Clustering Core Capabilities.
SQL Clustering Is Built on MSCS.
Configuring MS DTC for Use with SQL Clustering.
Laying Out a SQL Cluster Configuration.
Installing SQL Clustering.
Failure of a Node.
Removing SQL Clustering.
Client Test Program for a SQL Cluster.
A Node Recovery.
Application Service Provider-Scenario #1 with SQL Clustering.
Summary.
6. Microsoft SQL Server Log Shipping.
Microsoft Log Shipping Overview.
Data Latency and Log Shipping.
Design and Administration Implications of Log Shipping.
Setting Up Log Shipping.
Before Creating the Log Shipping DB Maintenance Plan.
Using the DB Maintenance Plan Wizard to Create.
Log Shipping.
Viewing Log Shipping Properties.
Changing the Primary Role.
Log Shipping System Stored Procedures.
Call Before You Dig-Scenario #4 with Log Shipping.
Summary.
7. Microsoft SQL Server Data Replication.
Microsoft SQL Server Data Replication Overview.
What Is Data Replication?
The Publisher, Distributor, and Subscriber Metaphor.
Publications and Articles.
Filtering Articles.
Replication Scenarios.
Central Publisher.
Central Publisher with Remote Distributor.
Publishing Subscriber.
Central Subscriber.
Multiple Publishers or Multiple Subscribers.
Updating Subscribers.
Subscriptions.
Pull Subscriptions.
Push Subscriptions.
Anonymous Subscriptions (Pull Subscriptions).
The Distribution Database.
Replication Agents.
The Snapshot Agent.
The Log Reader Agent.
The Distribution Agent.
The Merge Agent.
The Miscellaneous Agents.
Planning for SQL Server Data Replication.
Timing, Latency, and Autonomy of Data.
Methods of Data Distribution.
SQL Server Replication Types.
Snapshot Replication.
Transactional Replication.
Merge Replication.
User Requirements Drive the Replication Design.
Setting Up Replication.
Enable a Distributor.
Enable Publishing/Configure the Publisher.
Creating a Publication.
Creating Subscriptions.
Switching Over to a Warm Standby (Subscriber).
Scenarios That Will Dictate Switching to the Warm Standby.
Switching Over to a Warm Standby (Subscription).
Turning the Subscriber into a Publisher (if Needed).
Insulate the Client Using an NLB Cluster Configuration.
Scripting Replication.
Monitoring Replication.
SQL Statements.
SQL Enterprise Manager.
The Performance Monitor.
Backup and Recovery in a Replication Configuration.
Alternate Synchronization Partners.
Worldwide Sales and Marketing-Scenario #2 with Data Replication.
Summary.
8. Other Ways to Distribute Data for High Availability.
Alternate Ways to Achieve High Availability.
A Distributed Data Approach from the Outset.
Setting Up Access to Remote SQL Servers.
Querying a Linked Server.
Transact-SQL with Linked Servers.
Distributed Transactions.
MS DTC Architecture.
Two-Phase Commit Protocol.
COM+ Applications for HA.
Summary.
9. High Availability Pieced Together.
Achieving Five 9s.
Foundation First.
Assemble Your HA Assessment Team.
Set the HA Assessment Project Schedule/Timeline.
Doing a Phase 0 High Availability Assessment.
Step 1-HA Assessment.
Step 2-Primary Variable Gauge Specification.
High Availability Tasks Integrated into Your Development Life Cycle.
Selecting the HA Solution.
Is the HA Solution Cost Effective?
Summary.
10. High Availability Design Issues and Considerations.
Things to Consider for High Availability.
Hardware/OS/Network Design Considerations.
Remote Mirroring.
Microsoft Cluster Services Design Considerations.
SQL Server Clustering Design Considerations.
Stretch Clustering.
SQL Server Data Replication Design Considerations.
SQL Server Log Shipping Design Considerations.
Distributed Transaction Processing Design Considerations.
General SQL Server File/Device Placement Recommendations.
Database Backup Strategies in Support of High Availability.
Two Backup Approaches for High Availability.
Parallel Striped Backup.
Split-Mirror Backups (Server-less Backups).
Volume Shadow Copy Service (VSS).
Monitoring/Verifying Backups.
Disaster Recovery Planning.
The Overall Disaster Recovery Approach.
The Focus for Disaster Recovery.
Documenting Environmental Details Using SQLDIAG.EXE.
Plan and Execute a Complete Disaster Recovery test.
Software Upgrade Considerations.
High Availability and MS Analysis Services/OLAP.
OLAP Cubes Variations.
Recommended MSAS Implementation for High Availability.
Alternative Techniques in Support of High Availability.
Data Transformation Service (DTS) Packages Used to Achieve HA.
Have You Detached a Database Recently?
Third-party Alternatives to High Availability.
IBM/DB2 High Availability Example.
Summary.
11. High Availability and Security.
Security Breakdowns' Effect on High Availability.
Using an Object Permissions and Roles Method.
Object Protection Using Schema-Bound Views.
Proper Security in Place for HA Options.
MSCS Security Considerations.
SQL Clustering Security Considerations.
Log Shipping Security Considerations.
Data Replication Security Considerations.
General Thoughts on Database Backup/Restore.
Isolating SQL Roles, and Disaster Recovery Security Considerations.
Summary.
12. Future Directions of High Availability.
Microsoft Stepping Up to the Plate.
What's Coming in Yukon for High Availability?
Enhancements in Fail-over Clustering (SQL Clustering).
Database Mirroring for Fail-over.
Combining Fail-over and Scale Out Options.
Data Access Enhancements for Higher Availability.
High Availability from the Windows Server Family Side.
Microsoft Virtual Server 2005.
Virtual Server 2005 and Disaster Recovery.
Other Industry Trends in High Availability.
Summary.
Index.