Database mirroring is a strategy for increasing the availability of a database although we can only mirror databases that are configured to use the full recovery model.
This feature is likely to disappear in support of AlwaysOn availability groups.
Mirroring Prerequisites are:
- Mirroring support on a peredition basis in SQL Server 2012 is as follows:
- Enterprise Supports high-performance, high-safety, and witness modes
- Business Intelligence Supports high-safety and witness modes
- Standard Supports high-safety and witness modes
- Web Supports witness mode only
- Express (all versions) Supports witness mode only
Database mirroring on SQL Server 2012 has the following restrictions:
- We can mirror only user databases.
- We cannot mirror the master, msdb, tempdb, or model databases.
- We cannot rename mirrored databases.
- We cannot configure database mirroring for a database that contains FILESTREAM filegroups.
- We cannot create FILESTREAM filegroups on the principal server.
- Cross-database and distributed transactions are not supported for mirrored databases.
- We must configure a database to use the full recovery model before you can mirror it.
The mirror instance functions in:
- High safety-Failover - Committed transactions on both partners after they are synchronised. Increase in transaction latency. Hot standby using witness server, enable automatic failover.
- High-performance mode – failover, primary instance does not wait for the mirror instance to confirm that it has received the log record.
ALTER DATABASE <myDatabase> SET RECOVERY FULL;
Prior to commencing a mirroring session, we must restore a database by using the NO RECOVERY option on the mirror instance, therefore we will require a full backup of the database that you mirror.
Click here to view an example of a T-SQL command
We must configure firewall rules to allow traffic through on the endpoint when we are configuring a mirroring session.
The SQLAuthority.com link on Database Mirroring
Automatic failover is supported only in database mirroring sessions running with a witness in high-safety mode (high-safety mode with automatic failover).
For automatic failover to improve reliability, the mirror and principal databases must reside on different computers.
In high-safety mode with automatic failover, once the database is synchronised, if the principal database becomes unavailable, an automatic failover occurs.
An automatic failover causes the mirror server to take over the role of principal server and bring its copy of the database online as the principal database.
Requiring that the database be synchronised prevents data loss during failover, because every transaction committed on the principal database is also committed on the mirror database.
The principal, mirror, and witness instance must be running the same version of SQL Server when configuring a new mirroring session.
Automatic Failover can be disabled in SQL Server Management Studio on the Mirror Page of the Mirrored Database Properties, by changing the operating mode to either:
- High Performance (asynchronous) - has a lower transaction latency and commits transactions before partners are synchronised
- High Safety without Automatic Failover (synchronous) - has a higher transaction latency and commits transactions when they are synchronised on both partners