SQL Server High Availability Technologies

Extracted from: http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx#EVKAG

SQL Server 2005 now has at least four high availability technologies, and while there is some overlap, each technology has its own relative advantages and disadvantages. Those technologies are

• Database Mirroring - For this discussion, we will be considering the High Availability operating mode with safety FULL and a witness.

• Failover Clustering - The most typical configuration is a two-node Windows failover cluster with one SQL Server instance.

• Log Shipping - Assume SQL Server built-in log shipping with a separate monitoring.

• Transactional Replication - Consider a configuration with a separate distribution server and one subscriber which serves as the standby server if the publisher server fails.

Database Mirroring and Clustering
The most important contrast between database mirroring and failover clustering is the level at which each provides its redundancy. Database mirroring provides protection at the database level, whereas clustering provides protection at the server instance level. Another important difference is that in database mirroring, the principal and mirror servers are separate SQL Server instances with distinct names, whereas a SQL Server instance on a cluster gets one virtual server name and IP address that remains the same no matter what node of the cluster is hosting the instance.

If you need database protection at the server level (for example, your application requires access to many databases on the same database server simultaneously), failover clustering may be a more appropriate choice. However, if you are concerned to provide availability for one database at a time, database mirroring has a number of advantages.

Unlike clustering, database mirroring does not require proprietary hardware and does not have a potential failure point with shared storage. Database mirroring brings the standby database into service much faster than any other high availability technology, and works well with new capabilities in ADO.NET and SQL Native Access Client for client-side failover.

You cannot use database mirroring within a cluster, but you may consider using database mirroring as a method for creating a hot standby for a cluster instance database. If you do, be forewarned that because a cluster failover is longer than the timeout value on database mirroring, a High Availability mode mirroring session will react to a cluster failover as a failure of the principal server. It would then put the cluster node into a mirroring state.

Database Mirroring and Transactional Replication
Database mirroring and transactional replication are both based upon reading the transaction log of an originating server, but after that their technologies diverge considerably. (For more details about transactional replication, see the associated topics in SQL Server Books Online.) Transactional replication is often used for high availability because it can deliver user transactions from a publisher database to a subscriber in matters of seconds. Database mirroring has the advantage that it is as fast as or faster than replication, but delivers all of a database's transactions, not just those related to user tables.

Transactional replication is an appropriate technology for scaling out data to multiple subscribers for reporting. Transactional replication subscriber databases are normally considered read-only anyway, so they are ideal candidates when access to near-real-time data is required.

Database mirroring is compatible with transactional replication, and is most useful as method of keeping a hot standby of a publisher database. Other methods of protecting a replication publisher, such as log shipping, cannot keep a standby server for the publisher ahead of the publisher's own subscribers. In other words, transactional replication can deliver transactions to its subscribers much faster than a transaction log backup scheme. Because database mirroring is so fast, it is much more suitable for keeping a hot standby of a publisher database.

If the publisher should fail, however, you will have to manually re-establish the recovered standby database as the publisher, and reconnect it to the distribution server, just as you must currently do if using log shipping to maintain a publisher server standby.

Database Mirroring and Log Shipping
Database mirroring and log shipping both rely on the restore and recovery capabilities of SQL Server databases. A database mirroring mirror database is in a constantly recovering state, more or less continuously replaying transactions from the principal. A log shipping secondary replays transactions applied to it periodically from transaction log backups. Because bulk-logged data is appended to a transaction log backup, log shipping can work in the bulk-logged recovery model. Database mirroring, on the other hand, transfers log records directly from the principal to the mirror and cannot deliver bulk-logged data.

In many cases database mirroring can provide the same kind of data redundancy as log shipping with higher availability and automatic failover. However, if your application relies on multiple databases on one server, log shipping may be an equally valid approach (see "Multi-Database Considerations" in the previous section).

Additionally, there are database mirroring scenarios where log shipping may supplement availability. For example, you could have a High Availability database mirroring configuration in-house, and log ship the principal server to a remote site for disaster recovery purposes. Figure 24 illustrates how such a configuration might take place.