SQL Server Redundancy

When I saw SQL Server redundancy on a recent internal e-mail I wondered if it was about a replacement for SQL Server , DBA job losses, or the rise of the NoSQL movement.  It turned out to be nothing more than a general ask about the pros and cons of clustering and mirroring. If there are internal Microsoft staff who don’t know when to use what, then I imagine there must still be confusion in the real world about these two approaches. 

First some introductions:

SQL Server clustering is built  on Windows Server clustering, which use a number of servers (nodes) and shared storage to automatically recover a service from a server failure.  The service doesn’t have to be SQL Server it could be IIS, or Hyper-V for example.  It’s also important to understand that the recovery form failure (failover) is not instantaneous and can take several minutes. 

As far a s SQL Server is concerned there are good things about clustering:

• Clustering operates at the instance level so all of the databases in the instance that are clustered are protected in the same way including the system databases.  This means protecting an application that uses more than one database is straightforward and also all the metadata, logins etc, associated with the application that are in the system database are also protected.
• The whole cluster looks like a  SQL Server instance which doesn’t change no matter which server is is the active one.  So any application connection can be used and this doesn’t need to be changed when a server failover occurs

And some things be aware of:

• There is only one copy of any database in a cluster it’s just that this located on shared storage.  This means there is no protection against logical error occurs like you or a user accidentally deleting  a row , table etc. so clustering is not a replacement for backups.
• Recovery and switchover to a new server in the cluster is not instantaneous and can take several minutes.  During this time an application might time out.  You could therefore lose a transaction that wasn’t committed.

SQL Server Mirroring  is a process by which a copy of a database is kept up to date on another (mirror) server, and this mirror server can take over as the lead (principal) server at anytime should the first server fail.

The good things about mirroring are:

• It’s really simple to setup
• Failover to the mirror in the event the principal fails is very quick.
• Mirroring comes in several flavours like synchronous and asynchronous depending on whether you want to wait for the transaction to be applied to the mirror beofr it commits on the principal.

and the things to be aware of are:

• Mirroring only protects one database at a time, so if you create logins and other system objects you’ll need to have a separate process (e.g. a script in sql agent) to keep these in synch on the mirror server.
• Although there is a second copy of the database on the mirror server, if you have mirroring setup in synchronous mode any logical errors on the principal will be copied to the mirror so mirroring is not a replacement for a good backup strategy.
• You can only mirror a database to one other server.
• Each of the servers in a principal mirror pair has it’s own server name ip address etc. so for the application to know which one to connect to you do need to use the ado.net native connection (SNAC) as when the application starts it doesn’t ‘know’ which server is the principal.

In summary here are the key comparisons..

Hopefully these strengths and weaknesses will guide you in when to use what but I also want to add in 2 other factors:

• You might also consider using both technologies together e.g. you could have a cluster on site with mirroring to remote location.
• Virtualisation technologies like Hyper-V allows virtual machines to run in a cluster, but there is only the one copy of the virtual machine shared between the cluster nodes.

I will defer to the SQL Server Customer Advisory Team (SQLCAT) for a detailed discussion on this and their whitepapers on getting the most out of mirroring and clustering as this is just a gentle introduction to the world of SQL redundancy, or high availability as it more commonly referred to.

Finally just to be clear there is no substitute for database backups whatever else you decide to use.