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