Microsoft SQL Server High Availability

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 ($).