High Availability - Database Mirroring

Overview

Mirroring is a means to provide a secondary copy of your data. The secondary copy is automatically updated when data on the primary server is changed.

Different types of mirroring

Synchronous (high safety / high availability - database SAFETY option set to FULL) – data is always kept exactly the same on the primary and secondary server. Available in Standard & Enterprise edition. Automatic failover available with a witness server

Asynchronous (high performance - database SAFETY option set to OFF) – data is eventually up to date on secondary server

Mirroring can be configured with or without a witness server. Available in Enterprise Edition
Mirroring is per database, and only works for databases using Full recovery model

Pros

Almost instant, automatic failover when using a witness server in synchronous (high safety / high availability) mode

Cons

Mirroring is deprecated in future version of SQL Server. Microsoft advise not to use this for any new SQL Server implementations, and look to remove this function from systems that currently use it (source: https://msdn.microsoft.com/en-us/library/ms190202.aspx)Can cause some performance overhead in Synchronous configurationCan only mirror to one secondary serverMaster database cannot be mirrored - logins have to be added manually to secondary server, then mapped to the database users
Cannot access mirrored data directly (i.e. for reporting) - although you can set up a snapshot of the mirrored database and access this for reporting
Cannot mirror a database containing FILESTREAM filegroups
You can't detach a database that is being mirrored
Can only have ONE mirror copy of the database

Notes

Removing mirroring leaves the secondary copy of the database in a RECOVERING (unusable) state. The database must be recovered to be usable.

Mirroring resources

Brent Ozar blog: http://www.brentozar.com/archive/2013/07/database-mirroring-faq/
Setting up mirroring: https://technet.microsoft.com/en-us/library/ms190941(v=sql.110).aspx
MSDN on Mirroring: https://msdn.microsoft.com/en-us/library/ms189852.aspx