Microsoft SQL Server High Availability

by Bertucci, Paul

• ISBN13: 9780672326257
• ISBN10: 0672326256
• Edition: 1st
• Format: Paperback
• Copyright: 2004-11-05
• Publisher: Sams Publishing

Summary

Introduction

"If your company's High Availability requirements are well understood, the potential benefits gained by applying the correct High Availability solution can be enormous! Applying the wrong solution or not understanding your company's high availability needs could cause your company to go out of business!" IT Forum, Atlanta, GA—Paul Bertucci November, 2003

Five 9s

Downtime (system unavailability) directly translates to loss of profit, productivity, and customer good will—plain and simple. If your current or planned applications are vulnerable to downtime problems—or you are unsure of the potential downtime issues—then this book is aimed at you. Is your business at or nearing a requirement to be "highly available" or "continually available" in order to protect the previously mentioned profit, productivity, and customer good will? Again, this book is aimed at you.

Helping you understand the high availability (HA) solutions available to you and choosing the high availability approach that maximizes benefit and minimizes cost is our primary goal. A roadmap to design and implement these high availability solutions will be described herein. The good news is that software and hardware vendors in general, and Microsoft specifically, have come a long way in supporting high availability needs and will move even further to achieving 99.999% availability (herein referred to as "five 9s") in the near future. For a 24x7 application that aspires to achieve five 9s, that system would only tolerate a yearly total of 5.26 minutes of downtime. Knowing how to design for this will be crucial.

We will even touch on some alternatives for continually available systems (100% availability). These capabilities, coupled with a formal methodology tailored for designing high availability solutions, will allow you to design, install, and maintain systems maximizing availability while minimizing development and platform cost.

The success or failure of your company may well be influenced, if not be driven, by your ability to understand the essential elements that comprise a high availability environment, the business requirements driving the proper high availability approach, and the cost considerations affecting the ROI (return on investment) of a high availability solution. It is likely that a company's more critical applications demand some type of high availability solution—if a global online ordering system goes down and remains down for any length of time, millions of dollars would be lost along with the public's good will toward that company. The stakes are truly high indeed!

This book will outline how you can "design in" high availability for new applications and "upgrade" current applications to improve availability. In all cases, a crucial consideration will be the business drivers influencing a proposed application's uptime requirements, factoring in the dollar cost, productivity cost, and the good-will cost of NOT having that system available to the end-users for any period of time.

Current Microsoft capabilities and options allowing you to achieve high availability systems will be highlighted. These include, among others, Microsoft Cluster Services, Microsoft SQL Server 2000 SQL Clustering, Microsoft SQL Server 2000 Data Replication, Microsoft SQL Server 2000 Log Shipping, and Microsoft Distributed Transaction Coordinator capabilities. Many references to the Microsoft .NET Framework will be made since most of the previously mentioned components are deeply embedded in .NET.

Most importantly, a set of business scenarios will be introduced that will reflect actual companies' high availability requirements. We will use these business scenarios to guide you through the design process, determine the high availability approach best suited for a particular business scenario, and specify a roadmap to implement the business scenario with a specific technical solution.

This book will feel more like a cookbook or AAA route suggestion than a typical technical manual—this is the intention. It is one thing to describe technical syntax, but it is much more important to actually explain why you choose a particular approach to meet a particular business or application requirement. This book will focus on the later. The business scenarios introduced and implemented in this book come from live customer implementations. We will not reveal the names of these customers for obvious nondisclosure reasons. However, these business scenarios should allow the reader to correlate their own business requirements to these high availability scenarios. We will also include examples using the infamous Northwind database provided with Microsoft SQL Server 2000. This will allow you to replicate some of the solutions quickly and easily in your own sandbox.

Several tools, scripts, documents, and references to help you jump-start your next high availability implementation will be made available at the Sams Publishing website.

Who Is This Book's Intended Audience?

This material is intended for an intermediate-to-advanced level user. This would include roles such as system designer/architect, system administrator, data architect, database administrator, SQL programmer, and even managerial types such as chief information officer (CIO) or chief technology officer (CTO). It has been pointed out to me on several occasions that the justifications, alternatives, and ROI considerations might well be beneficial for a chief financial officer (CFO), since many of the issues and ramifications translate into lost profit, productivity, and good will. A motivated CFO who understands the benefits, complexities, and capabilities of achieving high availability can rest easier at night knowing that they are in good hands with their well-designed high availability solution protecting the bottom line ($).

Author Biography

Paul Bertucci is a globally recognized expert in database design, system architecture, and highly available systems. He is a frequent speaker at technology conferences and has taught complex database related subjects for two decades

Table of Contents

Introduction	1	(1)
Five 9s	1	(2)
Who Is This Book's Intended Audience?	3	(1)
How This Book Is Organized	3	(1)
Conventions Used in This Book	4	(1)
Setting Your Goals High!	4	(1)
PART I: UNDERSTANDING HIGH AVAILABILITY
Essential Elements of High Availability	5	(26)
Overview of High Availability	5	(5)
Calculating ``Availability''	10	(2)
Availability Example---A 24x7x365 Application	11	(1)
Availability Continuum	12	(3)
Availability Variables	15	(3)
General Design Approach for Achieving High Availability	18	(7)
Development Methodology with High Availability ``Built In''	21	(1)
Assessing Existing Applications	22	(2)
Service Level Agreement	24	(1)
High Availability Business Scenarios (Applications)	25	(3)
Application Service Provider	25	(1)
Worldwide Sales and Marketing---Brand Promotion	26	(1)
Investment Portfolio Management	27	(1)
Call Before You Dig	27	(1)
Microsoft Technologies that Yield High Availability	28	(2)
Summary	30	(1)
PART II: CHOOSING THE RIGHT HIGH AVAILABILITY APPROACHES
Microsoft High Availability Options	31	(22)
What High Availability Options Are There?	31	(13)
Fundamental Areas to Start With	32	(2)
Fault Tolerant Disk: RAID and Mirroring	34	(2)
Redundant Array of Independent Disks (RAID)	36	(6)
Mitigate Risk by Spreading Out Server Instances	42	(2)
Building Your HA Solution with One or More of These Options	44	(8)
Microsoft Cluster Services (MSCS)	45	(1)
SQL Clustering	46	(1)
Data Replication	47	(2)
Log Shipping	49	(2)
Distributed Transactions	51	(1)
Summary	52	(1)
Choosing High Availability	53	(38)
Moving Toward High Availability	53	(2)
Step 1---Launching a Phase 0 (Zero) HA Assessment	55	(4)
Resources for a Phase 0 HA Assessment	56	(1)
The Phase 0 HA Assessment Tasks	56	(3)
Step 2---HA Primary Variables Gauge	59	(1)
Step 3---Determining the Optimal HA Solution	60	(29)
A Hybrid High Availability Selection Method	61	(25)
Cost Justification of a Selected High Availability Solution	86	(2)
Adding HA Elements to Your Development Methodology	88	(1)
Summary	89	(2)
PART III: IMPLEMENTING HIGH AVAILABILITY
Microsoft Cluster Services	91	(36)
Understanding Microsoft Cluster Services	91	(2)
Hardware/Network/OS Requirements for MSCS	93	(3)
How Clustering Actually Works	96	(6)
The Disk Controller Configuration	98	(1)
The Disk Configuration	98	(1)
Network Configuration	99	(2)
Considerations at the Operating System Level	101	(1)
Installing MSCS	102	(18)
Pre-installation	104	(2)
Installing MSCS---Step 1	106	(10)
Installing MSCS for the Next Node: Step 2	116	(2)
Extending Clustering with Network Load Balancing (NLB)	118	(2)
Windows 2003 Options for Quorum Disks and Fail-over	120	(4)
4-node and 8-node Clustering Topologies	124	(1)
Summary	125	(2)
Microsoft SQL Server Clustering	127	(40)
Microsoft SQL Clustering Core Capabilities	127	(11)
SQL Clustering Is Built on MSCS	128	(2)
Configuring MS DTC for Use with SQL Clustering	130	(1)
Laying Out a SQL Cluster Configuration	131	(7)
Installing SQL Clustering	138	(10)
Failure of a Node	148	(4)
Removing SQL Clustering	152	(3)
Client Test Program for a SQL Cluster	155	(5)
A Node Recovery	160	(1)
Application Service Provider---Scenario #1 with SQL Clustering	161	(3)
Summary	164	(3)
Microsoft SQL Server Log Shipping	167	(28)
Microsoft Log Shipping Overview	167	(4)
Data Latency and Log Shipping	169	(1)
Design and Administration Implications of Log Shipping	170	(1)
Setting Up Log Shipping	171	(20)
Before Creating the Log Shipping DB Maintenance Plan	172	(1)
Using the DB Maintenance Plan Wizard to Create Log Shipping	173	(11)
Viewing Log Shipping Properties	184	(4)
Changing the Primary Role	188	(1)
Log Shipping System Stored Procedures	189	(2)
Call Before You Dig---Scenario #4 with Log Shipping	191	(2)
Summary	193	(2)
Microsoft SQL Server Data Replication	195	(76)
Microsoft SQL Server Data Replication Overview	195	(2)
What Is Data Replication?	197	(2)
The Publisher, Distributor, and Subscriber Metaphor	199	(7)
Publications and Articles	201	(1)
Filtering Articles	201	(5)
Replication Scenarios	206	(8)
Central Publisher	207	(1)
Central Publisher with Remote Distributor	208	(2)
Publishing Subscriber	210	(1)
Central Subscriber	210	(1)
Multiple Publishers or Multiple Subscribers	211	(1)
Updating Subscribers	212	(2)
Subscriptions	214	(2)
Pull Subscriptions	214	(1)
Push Subscriptions	215	(1)
Anonymous Subscriptions (Pull Subscriptions)	215	(1)
The Distribution Database	216	(1)
Replication Agents	217	(8)
The Snapshot Agent	218	(3)
The Log Reader Agent	221	(1)
The Distribution Agent	222	(1)
The Merge Agent	223	(1)
The Miscellaneous Agents	224	(1)
Planning for SQL Server Data Replication	225	(3)
Timing, Latency, and Autonomy of Data	226	(1)
Methods of Data Distribution	227	(1)
SQL Server Replication Types	228	(4)
Snapshot Replication	228	(1)
Transactional Replication	229	(1)
Merge Replication	230	(2)
User Requirements Drive the Replication Design	232	(2)
Setting Up Replication	234	(18)
Enable a Distributor	235	(4)
Enable Publishing/Configure the Publisher	239	(2)
Creating a Publication	241	(5)
Creating Subscriptions	246	(6)
Switching Over to a Warm Standby (Subscriber)	252	(2)
Scenarios That Will Dictate Switching to the Warm Standby	252	(1)
Switching Over to a Warm Standby (Subscription)	253	(1)
Turning the Subscriber into a Publisher (if Needed)	254	(1)
Insulate the Client Using an NLB Cluster Configuration	254	(1)
Scripting Replication	254	(4)
Monitoring Replication	258	(10)
SQL Statements	258	(1)
SQL Enterprise Manager	259	(2)
The Performance Monitor	261	(1)
Backup and Recovery in a Replication Configuration	261	(3)
Alternate Synchronization Partners	264	(1)
Worldwide Sales and Marketing---Scenario #2 with Data Replication	265	(3)
Summary	268	(3)
Other Ways to Distribute Data for High Availability	271	(24)
Alternate Ways to Achieve High Availability	271	(11)
A Distributed Data Approach from the Outset	272	(3)
Setting Up Access to Remote SQL Servers	275	(4)
Querying a Linked Server	279	(1)
Transact-SQL with Linked Servers	280	(2)
Distributed Transactions	282	(1)
MS DTC Architecture	283	(8)
Two-Phase Commit Protocol	283	(8)
COM+ Applications for HA	291	(2)
Summary	293	(2)
High Availability Pieced Together	295	(20)
Achieving Five 9s	295	(1)
Foundation First	296	(2)
Assemble Your HA Assessment Team	298	(1)
Set the HA Assessment Project Schedule/Timeline	299	(1)
Doing a Phase O High Availability Assessment	300	(8)
Step 1---HA Assessment	301	(3)
Step 2---Primary Variable Gauge Specification	304	(2)
High Availability Tasks Integrated into Your Development Life Cycle	306	(2)
Selecting the HA Solution	308	(3)
Is the HA Solution Cost Effective?	311	(2)
Summary	313	(2)
High Availability Design Issues and Considerations	315	(54)
Things to Consider for High Availability	315	(18)
Hardware/OS/Network Design Considerations	316	(4)
Remote Mirroring	320	(1)
Microsoft Cluster Services Design Considerations	321	(1)
SQL Server Clustering Design Considerations	322	(3)
Stretch Clustering	325	(1)
SQL Server Data Replication Design Considerations	326	(3)
SQL Server Log Shipping Design Considerations	329	(2)
Distributed Transaction Processing Design Considerations	331	(2)
General SQL Server File/Device Placement Recommendations	333	(2)
Database Backup Strategies in Support of High Availability	335	(9)
Two Backup Approaches for High Availability	337	(2)
Parallel Striped Backup	339	(1)
Split-Mirror Backups (Server-less Backups)	340	(2)
Volume Shadow Copy Service (VSS)	342	(1)
Monitoring/Verifying Backups	342	(2)
Disaster Recovery Planning	344	(7)
The Overall Disaster Recovery Approach	344	(1)
The Focus for Disaster Recovery	345	(4)
Documenting Environmental Details Using SQLDIAG.EXE	349	(2)
Plan and Execute a Complete Disaster Recovery test	351	(1)
Software Upgrade Considerations	351	(2)
High Availability and MS Analysis Services/OLAP	353	(8)
OLAP Cubes Variations	356	(2)
Recommended MSAS Implementation for High Availability	358	(3)
Alternative Techniques in Support of High Availability	361	(5)
Data Transformation Service (DTS) Packages Used to Achieve HA	361	(1)
Have You Detached a Database Recently?	362	(1)
Third-party Alternatives to High Availability	362	(2)
IBM/DB2 High Availability Example	364	(2)
Summary	366	(3)
High Availability and Security	369	(22)
Security Breakdowns' Effect on High Availability	369	(9)
Using an Object Permissions and Roles Method	371	(2)
Object Protection Using Schema-Bound Views	373	(5)
Proper Security in Place for HA Options	378	(11)
MSCS Security Considerations	379	(1)
SQL Clustering Security Considerations	380	(1)
Log Shipping Security Considerations	381	(3)
Data Replication Security Considerations	384	(4)
General Thoughts on Database Backup/Restore, Isolating SQL Roles, and Disaster Recovery Security Considerations	388	(1)
Summary	389	(2)
Future Directions of High Availability	391	(16)
Microsoft Stepping Up to the Plate	391	(1)
What's Coming in Yukon for High Availability?	392	(6)
Enhancements in Fail-over Clustering (SQL Clustering)	393	(1)
Database Mirroring for Fail-over	394	(3)
Combining Fail-over and Scale Out Options	397	(1)
Data Access Enhancements for Higher Availability	398	(3)
High Availability from the Windows Server Family Side	401	(2)
Microsoft Virtual Server 2005	401	(2)
Virtual Server 2005 and Disaster Recovery	403	(1)
Other Industry Trends in High Availability	403	(1)
Summary	404	(3)
Index	407

Supplemental Materials

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.

Excerpts

Introduction Introduction "If your company's High Availability requirements are well understood, the potential benefits gained by applying the correct High Availability solution can be enormous! Applying the wrong solution or not understanding your company's high availability needs could cause your company to go out of business!" IT Forum, Atlanta, GA--Paul Bertucci November, 2003 Five 9s Downtime (system unavailability) directly translates to loss of profit, productivity, and customer good will--plain and simple. If your current or planned applications are vulnerable to downtime problems--or you are unsure of the potential downtime issues--then this book is aimed at you. Is your business at or nearing a requirement to be "highly available" or "continually available" in order to protect the previously mentioned profit, productivity, and customer good will? Again, this book is aimed at you. Helping you understand the high availability (HA) solutions available to you and choosing the high availability approach that maximizes benefit and minimizes cost is our primary goal. A roadmap to design and implement these high availability solutions will be described herein. The good news is that software and hardware vendors in general, and Microsoft specifically, have come a long way in supporting high availability needs and will move even further to achieving 99.999% availability (herein referred to as "five 9s") in the near future. For a 24x7 application that aspires to achieve five 9s, that system would only tolerate a yearly total of 5.26 minutes of downtime. Knowing how to design for this will be crucial. We will even touch on some alternatives for continually available systems (100% availability). These capabilities, coupled with a formal methodology tailored for designing high availability solutions, will allow you to design, install, and maintain systems maximizing availability while minimizing development and platform cost. The success or failure of your company may well be influenced, if not be driven, by your ability to understand the essential elements that comprise a high availability environment, the business requirements driving the proper high availability approach, and the cost considerations affecting the ROI (return on investment) of a high availability solution. It is likely that a company's more critical applications demand some type of high availability solution--if a global online ordering system goes down and remains down for any length of time, millions of dollars would be lost along with the public's good will toward that company. The stakes are truly high indeed! This book will outline how you can "design in" high availability for new applications and "upgrade" current applications to improve availability. In all cases, a crucial consideration will be the business drivers influencing a proposed application's uptime requirements, factoring in the dollar cost, productivity cost, and the good-will cost of NOThaving that system available to the end-users for any period of time. Current Microsoft capabilities and options allowing you to achieve high availability systems will be highlighted. These include, among others, Microsoft Cluster Services, Microsoft SQL Server 2000 SQL Clustering, Microsoft SQL Server 2000 Data Replication, Microsoft SQL Server 2000 Log Shipping, and Microsoft Distributed Transaction Coordinator capabilities. Many references to the Microsoft .NET Framework will be made since most of the previously mentioned components are deeply embedded in .NET. Most importantly, a set of business scenarios will be introduced that will reflect actual companies' high availability requirements. We will use these business scenarios to guide you through the design process, determine the high availability approach best suited for a particular business scenario, and specify a roadmap to implement the business scenario with a specific technical solution. This book will feel more like a cookbook or AAA route suggestion than a typical technical manual--this is the intention. It is one thing to describe technical syntax, but it is much more important to actually explain why you choose a particular approach to meet a particular business or application requirement. This book will focus on the later. The business scenarios introduced and implemented in this book come from live customer implementations. We will not reveal the names of these customers for obvious nondisclosure reasons. However, these business scenarios should allow the reader to correlate their own business requirements to these high availability scenarios. We will also include examples using the infamous Northwind database provided with Microsoft SQL Server 2000. This will allow you to replicate some of the solutions quickly and easily in your own sandbox. Several tools, scripts, documents, and references to help you jump-start your next high availability implementation will be made available at the Sams Publishing website. Who Is This Book's Intended Audience? This material is intended for an intermediate-to-advanced level user. This would include roles such as system designer/architect, system administrator, data architect, database administrator, SQL programmer, and even managerial types such as chief information officer (CIO) or chief technology officer (CTO). It has been pointed out to me on several occasions that the justifications, alternatives, and ROI considerations might well be beneficial for a chief financial officer (CFO), since many of the issues and ramifications translate into lost profit, productivity, and good will. A motivated CFO who understands the benefits, complexities, and capabilities of achieving high availability can rest easier at night knowing that they are in good hands with their well-designed high availability solution protecting the bottom line ($). How This Book Is Organized This book is divided into three main sections: Part I, "Understanding High Availability"--This section will establish our definition of high availability, introduce the high availability business scenarios that are typically found in the real world, and describe the various hardware and software options within the Microsoft family of products that directly address high availability. Part II, "Choosing the Right High Availability Approaches"--This section will explicitly define a formal design approach to be used as a roadmap to navigate the appropriate high availability solution for each business scenario introduced. Part III, "Implementing High Availability"--This section will describe the architecture, design, implementation steps, and techniques needed for each high availability solution. Each business scenario will be driven to their "complete" implementation. This is a "soup-to-nuts" approach that should yield ample clarity for the reader--from inception of the business requirements to the complete implementation of a high availability solution for the given business and service level requirements. Conventions Used in This Book Names of commands and stored procedures are presented in a specialmonospacedcomputer typeface. We have tried to be consistent in our use of uppercase and lowercase for keywords and object names. However, because the default installation of SQL Server doesn't make a distinction between upper- and lowercase for SQL keywords or object names and data, you might find some of the examples presented in either upper- or lowercase. "Design notes" will cover any design or architecture idea that is related to the topic that is being discussed. They are meant to supplement the discussed idea or to help guide design. An example would be to provide so

Rewards Program

Currently unavailable

×

Digital License

You are licensing a digital product for a set duration. Durations are set forth in the product description, with "Lifetime" typically meaning five (5) years of online access and permanent download to a supported device. All licenses are non-transferable.

More details can be found here.