SQL Server 2008 : High-availability options

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
10/10/2013 9:00:08 PM

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.

1. Failover clustering

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. 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.

2. Transaction log shipping

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.

Figure 1. Transaction log shipping automates the process of backing up, copying, and restoring transaction logs from a source database to a destination database on another server.

As illustrated in figure 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.

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 2. Database mirroring also overcomes the shared storage limitation of clustering, therefore enabling mirroring partners to be located large distances from one another.

Figure 2. Figure A typical database mirroring topology in which the mirror database is receiving and applying transactions from the principal server over a high-speed network link

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.

4. Comparing high-availability options

Table 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 1. A comparison of SQL Server high-availability solutions
AttributeClusteringLog shippingDatabase mirroring
Multiple database failoverYesNoNo
Logins, config, and job failoverYesNoNo
Automatic failover supportYesNoYes
Automatic client redirectionYesNoYes
Provides a reporting solutionNoYesYes
Central point of failureDiskNoNo
Multiple standby destinationsNoYesNo
Geographical distance supportYesYesYes
Data latencyNil15mins+Nil

  •  SQL Server 2008 : Policy-based management - Advanced policy-based management
  •  SQL Server 2008 : Policy-based management - Enterprise policy management
  •  SQL Server 2012 : Interpreting Query Execution Plans - Viewing Query Execution Plans
  •  SQL Server 2012 : SQL Server Management and Development Tools - Using the Query Editor
  •  SQL Server 2012 : SQL Server Management and Development Tools - Object Explorer (part 2)
  •  SQL Server 2012 : SQL Server Management and Development Tools - Object Explorer (part 1)
  •  SQL Server 2012 : SQL Server Management and Development Tools - Registered Servers
  •  SQL Server 2012 : SQL Server Management and Development Tools - Organizing the Interface
  •  SQL Server 2012 : SQL Server Private Cloud - Upgrading SQL Server
  •  SQL Server 2012 : SQL Server Private Cloud - Discovering SQL Server Sprawl
    Top 10
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
    - Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
    - Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    programming4us programming4us