11.1. High-availability options

Broadly defined, a high-availability solution refers to any system or mechanism put in place to ensure the ongoing availability of a SQL Server instance in the event of a planned or unplanned outage. We've already covered the importance of a well-designed and tested backup and recovery strategy, so the major high-availability options we'll examine here are clustering, log shipping, and database mirroring. While replication can (and is) used by many as part of a high-availability solution, we won't consider it here on the basis of its major purpose as a data distribution technology.

11.1.1. Failover clustering

As you learned in chapter 5, failover clustering's major advantage is that it protects the entire server and all of its components from failure. From a SQL Server perspective, the benefits of this are numerous:

• All databases for a given failover clustering instance are failed over in a single action.

• SQL Agent jobs, logins, system configuration, and all other items are automatically moved.

• No client redirection logic is required; a failover clustering instance is accessed over the network using a virtual server name which automatically maps to the new server should failover occur.

The major limitation of failover clustering, particularly in Windows Server 2003 and earlier, is that other than a RAID solution, there's no protection from failure of the disks containing the database files and/or the cluster quorum resource. As we saw in chapter 5, Windows Server 2008 brings with it a number of enhanced quorum models that eliminate the problem of a single shared storage quorum resource, but that still leaves the issue of the potential failure of the disks containing the database files. Both log shipping and database mirroring address this by maintaining a hot/warm copy of the database, often in a physically separate location. Let's consider log shipping first.

11.1.2. Transaction log shipping

In the previous chapter we covered the importance of the transaction log in providing the ability to recover a database to a point in time. We also highlighted the need to perform regular restores of backups to ensure their validity, which is a frequently skipped proactive maintenance task amid the chaos of reactive work environments. Transaction log shipping takes care of both of these goals while enabling additional reporting options.

As illustrated in figure 11.1, a log shipped database sends its transaction log backups to a copy of the database on one or more secondary servers for regular restoration. As we'll see shortly, the log shipping configuration screens provided in SQL Server Management Studio enable the frequency of the transaction log backup, copy, and restore jobs to be set, along with the option to leave the database copy in a read-only state in between log restores, thus enabling the database to be used for reporting purposes.

Unlike clustering, log shipping has no shared storage and therefore no central point of failure. Each server in the log shipping pair is completely independent: it has its own storage and could theoretically be located anywhere in the world.

The major disadvantage of log shipping is that each database must be log shipped independently. For a SQL Server instance containing multiple databases, all of which require protection, the administrative effort required to set up and administer log shipping for each database is substantial when compared to a clustering solution.

Log shipping has no automatic failover process. If one server fails, manual intervention is required to bring the log ship partner online and redirect clients to the new server. Database mirroring, discussed next, addresses this issue nicely.

11.1.3. Database mirroring

In a manner similar to log shipping, servers in a database mirroring session use the transaction log to move transactions between a principal server and a mirror server. The main advantage of database mirroring is that the movement of transactions can be performed synchronously, guaranteeing that the mirror is an exact copy of the principal at any given moment. In contrast, a log shipping destination is typically at least 15 minutes behind the source (which can actually be an advantage in some situations, as we'll see shortly).

Like log shipping, database mirroring needs to be set up on a database-by-database basis, therefore limiting its appeal for instances containing many critical databases. Unlike log shipping, however, it can optionally be configured with a witness instance to initiate automatic failover to the mirror server. Further, with the correct configuration, client connections can be automatically redirected to the mirror server on failure of the principal.

A typical database mirroring session is illustrated in figure 11.2. Covered in detail later in the chapter, database mirroring also overcomes the shared storage limitation of clustering, therefore enabling mirroring partners to be located large distances from one another.

When compared with log shipping, the major disadvantages of database mirroring are the fact that only a single mirror can exist for each principal (log shipping allows multiple destinations for the one source) and the inability to read the mirror database (unless using a database snapshot), thus limiting the use of mirroring in providing a reporting solution.

Service level agreements A critical component of any SQL Server solution (but particularly a high-availability solution) is a service level agreement (SLA), which defines a number of system attributes such as the acceptable data loss, disaster-recovery time, and transaction performance targets. A common SLA entry is the availability target, usually expressed as a percentage; for example, a 99 percent availability target allows approximately 3.5 days of downtime per year. In contrast, a 99.999 percent target allows 5 minutes! Each "9" added to the availability target exponentially increases the cost of building an appropriate solution. As such, agreeing on an availability target before designing and building a solution is a critical step in both minimizing costs and meeting customer expectations.

To more easily highlight the strengths and weaknesses of each solution, let's compare them side by side.

11.1.4. Comparing high-availability options

Table 11.1 compares clustering, log shipping, and mirroring from various perspectives. Note that combinations of these solutions are frequently deployed for mission-critical databases, therefore minimizing the weaknesses of any one option. For example, using a failover cluster in combination with database mirroring enables local failover support for all databases with mission-critical databases mirrored to an offsite location.

Table 11.1. A comparison of SQL Server high-availability solutions
 

Attribute	Clustering	Log shipping	Database mirroring
Multiple database failover	Yes	No	No
Logins, config, and job failover	Yes	No	No
Automatic failover support	Yes	No	Yes[]
Automatic client redirection	Yes	No	Yes[]
Provides a reporting solution	No	Yes[]	Yes[]
Central point of failure	Disk	No	No
Multiple standby destinations	No	Yes	No
Geographical distance support	Yes[]	Yes	Yes
Data latency	Nil	15mins+[]	Nil[]
[]
[]
[]
[]
[]
[]
[]

^[] Optional

^[] If using SNAC or custom application logic

^[] Assuming standby restore mode

^[] If using database snapshots

^[] Typically with high-end custom solutions

^[] Configurable; defaults to 15 minute backup/copy/restore frequency

^[] If using synchronous (high safety) mode

Regardless of which of the high-availability solutions you choose (if any), the importance of a solid backup strategy together with adherence to other best practices covered throughout this book can't be overstated; all of them contribute to a highly available SQL Server environment in their own way.

Before we get to the main focus of this chapter, database mirroring, let's delve a little deeper into transaction log shipping.