Have you ever had the need to mirror an MS-SQL database? Perhaps for redundancy or maybe even a failover solution.

Recently a customer who came to us needed a solution for a disaster recovery site for their web application. Their system runs on Microsoft ASP.NET with a MS-SQL backend. We suggested the possibility of MS-SQL mirroring, this solution struck us as being very good for this application because it required very small changes to the application itself and required the minimum amount of hardware and additional software.

A quick overview of MS SQL mirroring is as follows:

- You require 3 servers, a primary database server, a secondary database server and a “witness� server.

- The 3 servers all require MS SQL. Preferably Enterprise Edition for the primary and secondary, and standard for the witness.

- The failover works on a “Quorum� system whereby 2 of the 3 servers must be able to communicate to form a Quorum for the system to continue to function. For example, if the primary server fails, the secondary and witness form a Quorum and promote the secondary to a primary.

- The secondary server is inactive as an SQL server unless promoted to primary (this is NOT a load balancing solution).

- In our situation, we required the failover to work not only for hardware failures, but link failures also, so we have to locate the witness server in a different location to the primary and failover site.

- Another option is to go without the witness server, but this requires a manual failover in the event of a problem.

The Microsoft article with more information about MS SQL mirroring can be found at http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

This is one of the automatic failover options we offer at GoHosting, although we have worked on many different solutions with Dedicated Servers, Virtual Servers and Colocation Servers.

It all depends on how important the availability of your website / application is. If you require absolute no downtime, then this kind of mirroring solution that would be suitable for your needs.