This section introduces several Microsoft SQL Server 2005 high-availability solutions that improve the availability of servers or databases. A high-availability solution masks the effects of a hardware or software failure and maintains the availability of applications so that the perceived downtime for users is minimized.
SQL Server 2005 provides several options for creating high availability for a server or database. High-availability options include:
Failover clustering
Failover clustering provides high-availability support for an entire SQL Server instance. A failover cluster is a combination of one or more nodes (servers) with two or more shared disks. Applications such as SQL Server and Notification Services are each installed into a Microsoft Cluster Service (MSCS) cluster group, known as a resource group. At any given point in time, each resource group is owned by only one node in the cluster. The application service has a "virtual name" that is independent of the node names, and for that reason is referred to as a virtual server. An application can connect to the virtual server by referencing the virtual server name, without having to know which node hosts the virtual server.
A SQL Server virtual server appears on the network as if it were a single computer, but has functionality that provides failover from one node to another if the current node becomes unavailable. For example, during a non-disk hardware failure, operating system failure, or planned operating system upgrade, you can configure a SQL Server instance on one node of a failover cluster to fail over to any other node in the disk group. A failover cluster does not protect against disk failure. You can use failover clustering to reduce system downtime and provide higher application availability. Failover clustering is supported in SQL Server 2005 Enterprise Edition, Developer Edition and, with some restrictions, Standard Edition.
For more information about failover clustering, see Failover Clustering and Creating a Virtual Server, Installing a Failover Cluster.
Database mirroring
Database mirroring is primarily a software solution for increasing database availability by supporting almost instantaneous failover. Database mirroring can be used to maintain a single "hot" standby database, or mirror database, for a corresponding read-write database called the principal database. The mirror database is created by restoring a full backup of the principal database without recovery. The mirror database is inaccessible to clients. However, it is possible to use it indirectly for reporting purposes by creating a database snapshot on the mirror database. The database snapshot provides clients with read-only access to the data in the database as it existed at snapshot creation. Each database mirroring configuration contains a principal server (containing the principal database), a mirror server (containing the mirror database), and an optional witness server. The mirror server continuously brings the mirror database up to date with the principal database. Failover from the principal database to the mirror database is essentially instantaneous.Database mirroring runs with either synchronous or asynchronous operation. With asynchronous operation, the transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance. With synchronous operation a committed transaction is committed on both partners, but at the risk of increased transaction latency.In its simplest form, database mirroring involves only the principal and mirror servers. An alternative configuration intended only for synchronous operation, involves a third server instance, known as a witness.For more information about database mirroring, see Database Mirroring.
Log shippingLike database mirroring, log shipping operates at the database level. Log shipping can be used to maintain one or more "warm" standby databases, called secondary databases, for a corresponding read-write database called the primary database. Each secondary database is created by restoring a full backup of the primary database without recovery. A log shipping configuration includes a single primary server (containing the primary database), one or more secondary servers (each with a secondary database), and a monitor server. Each secondary server is updates its secondary database at regular intervals from log backups of the primary database. Before failover can occur from the primary database to one of the secondary databases, that secondary database must be brought fully up to date manually. Secondary databases have limited availability during restores, so they might not be usable for reporting purposes. Log shipping provides the flexibility of supporting multiple standby databases. If you require multiple standby databases, you use log shipping alone or as a supplement to database mirroring. When these solutions are used together, the current principal database of the database mirroring configuration is also the current primary database of the log shipping configuration. Furthermore, log shipping allows a user-specified delay between when the primary server backs up the log of the primary database and when the secondary servers must restore the log backup. Log shipping is supported in SQL Server 2005 Enterprise Edition, Standard Edition, and Workgroup Edition.
For more information about log shipping, see Log Shipping.
Replication
Replication uses a publish-subscribe model, allowing a primary server (the Publisher) to distribute data to one or more secondary servers (Subscribers). Replication allows real-time availability and scalability across these servers. It supports filtering to provide a subset of data at Subscribers, and also allows partitioned updates. Subscribers are online and available for reporting or other functions, without query recovery. SQL Server offers three types of replication: snapshot, transactional, and merge; transactional replication provides the lowest latency and is most commonly used for high availability.
For more information, see Improving Scalability and Availability.
Replication is supported in all edition of SQL Server 2005. Replication publishing is not available with SQL Server Express or SQL Server Mobile; for a complete list of replication features supported by each edition, see Features Supported by the Editions of SQL Server 2005.
Important:
A well designed and implemented backup and restore strategy is essential to any high-availability solution.
For more information, see Backing Up and Restoring Databases and Backing Up and Restoring Replicated Databases.
Selecting a High-Availability Solution:
The following list presents considerations for selecting a high-availability solution:
Failover clustering and database mirroring These solutions both provide:
Automatic detection and failover.
Manual failover.
Transparent client redirect. Additionally, failover clustering has the following constraints:
Operates at the server instance scope.
Requires certified hardware.
Has no reporting on standby.
Utilizes a single copy of the database.
Does not protect against disk failure.Additionally, database mirroring offers the following benefits:
Operates at the database scope.
Uses a single, duplicate copy of the database.
Note:
If you require additional copies, you can use log shipping on the database in addition to database mirroring.
Uses standard servers.
Provides limited reporting on the mirror server using database snapshots.
When operating synchronously, provides for zero work loss through delayed commit on the principal database.Database mirroring offers a substantive increase in availability over the level previously possible using SQL Server and offers an easy-to-manage alternative to failover clustering.
Note:
For information on using database mirroring with a failover cluster, see Database Mirroring and Failover Clustering. For information on using Notification Services with failover clustering with notification services, see Using Failover Clustering with Notification Services. For information on using Notification Services with database mirroring, see Using Log Shipping or Database Mirroring with Notification Services.
Log shippingLog shipping can be a supplement or alternative to database mirroring. While similar conceptually, asynchronous database mirroring and log shipping have key differences. Log shipping offers the following distinct capabilities:
Supports multiple secondary databases on multiple server instances for a single primary database.
Allows a user-specified delay between when the primary server backs up the log of the primary database and when the secondary servers must restore the log backup. A longer delay can be useful, for example, if data is accidentally changed on the primary database. If the accidental change is noticed quickly, a delay can allow you to retrieve still unchanged data from a secondary database before the change is reflected there.On the other hand, compared to the shortest time required by log shipping to reflect a change onto a secondary database, asynchronous database mirroring, has the potential advantage of a shorter time between when a given change is made in the primary database and when that change is reflected onto the mirror database. An advantage of database mirroring over log shipping is that it is a "no data loss" configuration that is supported natively as a simple failover strategy. Database mirroring is supported onSQL Server 2005 Enterprise Edition and, with some restrictions, also on Standard Edition.
Note:
For information on using log shipping with database mirroring, see Database Mirroring and Log Shipping. For information on using log shipping with Notification Services, see Using Log Shipping or Database Mirroring with Notification Services.
ReplicationReplication offers the following benefits:
Operates at the database scope; it allows filtering within the database to provide a subset of data at the secondary databases.
Allows more than one redundant copy of the database.
Allows real-time availability and scalability across multiple databases, supports partitioned updates.
Allows complete availability of the secondary databases for reporting or other functions, without query recovery.
