What is SQL Server database mirroring? March 15, 2014 by Ivan Stankovic SQL Server database mirroring is a disaster recovery and high availability technique that involves two SQL Server instances on the same or different machines. One SQL Server instance acts as a primary instance called the principal, while the other is a mirrored instance called the mirror. In special cases, there can be a third SQL Server instance that acts as a witness Implementation examples One of the common mirroring configuration is the environment with two SQL Servers (SQLServer-1 and SQLServer-2), two instances (SQLInstance-1 and SQLInstance-2), and one mirrored database named SQLDB-1 The second common configuration is the environment with one SQL Server machine, two SQL Server instances, and one mirrored database named SQLDB-1. This solution has a major flaw because if SQLServer-1 goes down, both instances will be unavailable Operating modes SQL Server database mirroring can be set to provide high availability or disaster recovery. Depending on the needs, a DBA can choose among three available modes High safety – Data is written and committed on the principal and mirror databases synchronously. Only after committing on both databases, the database application can continue with activity Might produce delay and slower operation because transactions must be committed on both databases If the principal database goes down, two options are available: Do nothing – wait for the principal to become available again. During that time, the SQL Server instance is unavailable. Mirroring will continue where it has stopped Force the SQL Server instance on the mirror database – the mirror database becomes the principal. Possible data loss due to committed transactions on the original principal database which are not yet committed on the mirror currently acting as the principal High safety with automatic failover – Three servers are necessary. Data is written and must be committed synchronously both on the principal and mirror databases. Only after committing on both databases, the application can continue running Might produce delay and slower operation because transactions must be committed on both databases If the principal database goes down, only one option is available: Let the automatic failover process complete, the mirrored database becomes the principal High performance – the asynchronous communication, data is written and committed on the principal server, and later sent and committed to the mirror server. Automatic failover isn’t possible and the witness server can’t be used The high performance mode is only available in the Enterprise edition of SQL Server If the principal database goes down, three options are available: Do nothing – wait for the principal to become available again. The SQL Server is unavailable. Mirroring will continue where it has stopped Force the SQL Server instance on the mirror database – the mirror database becomes the principal. Greater possibility for data loss, due to asynchronous communication between databases Manual update – to reduce data loss, take the tail of the log backup if the failed server allows, remove mirroring and restore the tail of the log on the previously mirrored database Advantages and disadvantages of using SQL Server database mirroring Using SQL Server database mirroring has multiple benefits: a built-in SQL Server feature, relatively easy to set up, can provide automatic failover in high safety mode, etc. Database mirroring can be combined with other disaster recovery options such as clustering, log shipping, and replication Database mirroring will be removed from SQL Server in future versions in favor of AlwaysOn Availability Groups. Also, database mirroring is per database only solution, which means that logins and jobs from the principal SQL Server must be manually recreated on the mirror. There is also possibility for delay, which can only be reduced with better hardware Setting up the database mirroring environment The database mirroring feature is available in SQL Server 2005 version and greater. Availability of the operating modes depends on the SQL Server edition. Different SQL Server versions can be combined, but it’s not recommended The database that needs to be mirrored must be in the full recovery model. System databases can’t be mirrored A full database and transaction log backups of the database which will be mirrored must be created and restored on the SQL Server instance which will act as the mirror. The restore process must be executed using the WITH NORECOVERY option The database mirroring setup needs to be initiated from the principal server using the SQL Server Management Studio wizard or T-SQL code. At the beginning of the setup process, there’s an option for choosing a witness SQL Server instance which is only required if the high safety with automatic failover mode is desired. SQL Server instances must be able to communicate which requires creation of so-called endpoints with the port and name specified. These setting are required both on the principal and mirror SQL Server instances After endpoints are created and connection checked, select the operating mode, either high safety or high performance. If the witness server is specified choosing high safety mode will result in high safety with automatic failover See more To learn more about creating database backup mirrors, please see How to create multiple SQL Server backup mirrors For SQL Server recovery, consider ApexSQL Recover, a tool that recovers deleted and truncated data, objects and data lost due to drop operations, restores deleted and reads online BLOBs as files Resources Database Mirroring (SQL Server) Setting Up Database Mirroring (SQL Server) Mirroring a SQL Server Database is not as hard as you think Prerequisites, Restrictions, and Recommendations for Database Mirroring About Latest Posts Ivan StankovicIvan is a SQL Server professional and computer geek with years of IT and SQL Server experience. He has startedwith playing computer games, continued with computer programming and system administration. His areas of expertise are SQL Server disaster recovery, auditing, and complianceView all posts by Ivan Stankovic Latest posts by Ivan Stankovic (see all) Using Extended Events to review SQL Server failed logins - August 5, 2014 SQL Server backup – models and types - May 26, 2014 SQL Server Policy Based Management – Categories and Database Subscriptions - May 21, 2014 Related posts: How to create a Database Mirroring How to configure SQL Server mirroring on a TDE encrypted database What is a SQL Server disaster recovery? Database snapshot in SQL Server What is SQL Server log shipping?