SQL Server High Availability Choices: Part II

April 16th, 2009 by Jason C.

This is Part II of the article “SQL Server High Availability Choices.” Click here for Part I.

In Part 1 of this article we covered failover clustering. Here in Part II will continue the high availability discussion with database mirroring to provide the necessary information to ensure that you are choosing the right solution for your environment.

Database mirroring requires that your application is mirror aware. Making an application mirror aware could be as simple as modifying a connection string to be aware of the failover partner, or it could involve more complex changes.

The configuration for database mirroring is much less complex than with clustering. There is still the need for at least two servers but there is no requirement for an Active Directory domain, shared storage device, and multiple networks (a backend network is recommended). To provide the automatic failover functionality, you will need to have a third SQL Server instance known as the witness. This third instance of SQL Server should reside on a third server that is not taking part in database mirroring. The other server could be one you already have in your environment. The Witness server can run SQL Server 2005 Express edition, helping to keep costs low. The role of the witness server is to ensure that only one server is serving the database in the event that the partners become disconnected from each other.

Database mirroring works at the database level. This allows a failover to occur if the database becomes unavailable for any reason. Since the mirroring works at database level, each database that you want to mirror must be configured separately. If you have several mirrored databases configured, it is possible that some may failover to the other server and some will not, depending on the reason for the failover. In some cases, you may end up with one or more databases running on each server which cannot occur in a MS SQL Cluster hosting environment. Since database mirroring only replicates a database, SQL users, SQL configuration, and other data not held in the database will not be reflected on both sides of the mirror.

Both failover clustering and database mirroring provide a way to ensure that your critical data is highly available. Weighing the pros and cons of both solutions will help you to decide on the best solution for your needs.