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:
And some things be aware of:
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:
and the things to be aware of are:
In summary here are the key comparisons..
in HA Mode
Transparent to Client
+ DB Recovery
Masking of Disk Failure
Hopefully these strengths and weaknesses will guide you in when to use what but I also want to add in 2 other factors:
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.