Document Sample

“High Availability” in any application is a spectrum of choices, which depends on your
business requirements and ultimately your budget. Related to the concepts of „Recovery
Time Objective‟ and „Recovery Point Objective‟, you have to decide your tolerance for
downtime and data-loss.

Microsoft SQL Server 2005 offers four solutions to mitigate the risk of hardware failure.
In order of data-availability and minimized data-loss, your choices are log shipping,
replication, and mirroring or clustering.

Log shipping

In log shipping, log backups from a primary database server are „shipped‟ to a secondary
database server at an alterable, pre-determined interval. As the logs arrive at the
secondary server, they are „applied‟ to the database. Before a failover can occur, all
unrestored logs must first be manually applied to the secondary server.

The advantage of log shipping is that the same logs can be applied to multiple standby
servers for multiple purposes, and is an integral component of any disaster recovery plan.
The disadvantage is that failover cannot be automated, that downtime can be lengthy, and
that there is a distinct possibility of data-loss depending on the shipping interval.

Log shipping is supported in all versions of SQL Server 2005, excepting „Express‟ and
„Compact‟ editions.


Replication is an automated distribution of data to one or more receiving nodes.
Microsoft refers to this relationship as a publish-subscribe model—a Publisher (the
primary database) pushes its information to a secondary database server called a

Subscriber nodes are available for reporting, data warehousing, and backup purposes, and
should be considered for any application where the strain of these tasks should not be
performed by the primary online node.

For online applications that are very lookup-intensive (e.g., searches), connections can be
sent to a farm of Subscriber nodes. In that same scenario, database writes (e.g.,
ecommerce purchases) can be sent to the Publisher for processing. This application
architecture ensures proper scalability in the database layer, and is a very effective load-
balancing mechanism.
SQL Server 2005 supports three types of replication: snapshot, transactional, and merge.
Snapshot replication is a good choice when data changes infrequently or the dataset is
small. Merge replication is appropriate when multiple environments work independently
and on separate sets of data, which later need to be re-integrated (merged). Transactional
replication is the best choice when data changes frequently and needs to be pushed to
subscribers in near-real-time.

Replication is supported by all editions of SQL Server 2005, but only „publishing‟ is not
available in the „Express‟ or „Compact‟ editions.


Database mirroring is new in SQL Server 2005. Mirroring is a form of database
clustering to keep an exact real-time copy of the database on a second server. This setup
requires three servers: a Principal, a Mirror, and a Witness. The Witness brokers the
relationship between the Principal and the Mirror, and initiates a seamless automated
failover when necessary.

The advantage of mirroring is that it is a cost-effective solution when only one or very
few databases need to be maintained, and the entire dataset fits onto the servers‟ disk
drives. The drawback is that the Mirror cannot be accessed for reporting or backups (only
snapshots). Additionally, Mirroring is not a server but a database clustering technology.
This means that the Mirror server must have all the proper logins, SQL Agent jobs, SQL
Server Integration Services, and other supporting components and configurations. Lastly,
Mirroring is not appropriate for inter-dependent databases, as only one database might
failover, breaking the dependencies.

Failover Clustering

Failover clustering is as much a function of the underlying operating system as it is SQL
Server. In failover clustering for SQL Server 2005, the advantages and caveats flip-flop
when compared to database mirroring. Failover clustering has higher hardware and
resource requirements—such as shared external storage (a SAN)—but can accommodate
a much higher volume of data and databases.

Windows Server 2003, with Microsoft Cluster Service (MSCS) supports Active/Passive
and Active/Active cluster topologies. The “Standard” edition of SQL Server 2005
supports only two nodes in the cluster, whereas “Enterprise” supports up to eight active
nodes. An Active/Passive cluster only supports one instance of a database. Should the
Active node fail, the Passive node will take over the workload. Up until then, the Passive
node has been sitting around idly with nothing to do, except for receiving transaction
updates from the Active server. However, in Active/Passive clustering the financial
benefit is that the Passive node does not require a separate database license, yet can be
used for backup and reporting purposes of the idle database.
Active/Active clustering is often misunderstood to be similar to Oracle RAC‟s load-
balancing cluster—as with Active/Passive, the clustering is nothing-shared. However,
Active/Active clustering supports multiple database instances, meaning that individual
nodes in the cluster can be online and actively collecting and manipulating data, and also
failover to another node in the cluster. In an Active/Active cluster all nodes need to be

Shared By: