Setting Up Database Mirroring (SQL Server)
Applies To: SQL Server 2016
This section describes the prerequisites, recommendations, and steps for setting up database mirroring. For an introduction to database mirroring, see Database Mirroring (SQL Server).
We recommend that you configure database mirroring during off-peak hours because configuration can impact performance.
In this Topic:
For each database mirroring session:
The principal server, mirror server, and witness, if any, must be hosted by separate server instances, which should be on separate host systems. Each of the server instances requires a database mirroring endpoint. If you need to create a database mirroring endpoint, ensure that it is accessible to the other server instances.
The form of authentication used for database mirroring by a server instance is a property of its database mirroring endpoint. Two types of transport security are available for database mirroring: Windows Authentication or certificate-based authentication. For more information, see Transport Security for Database Mirroring and AlwaysOn Availability Groups (SQL Server).
The requirements for network access are specific to the form of authentication, as follows:
If using Windows Authentication
If server instances are running under different domain user accounts, each requires a login in the master database of the others. If the login does not exist, you must create it. For more information, see Allow Network Access to a Database Mirroring Endpoint Using Windows Authentication (SQL Server).
If using certificates
To enable certificate authentication for database mirroring on a given server instance, the system administrator must configure each server instance to use certificates on both outbound and inbound connections. Outbound connections must be configured first. For more information, see Use Certificates for a Database Mirroring Endpoint (Transact-SQL).
Make sure that logins exist on the mirror server for all the database users. For more information, see Set Up Login Accounts for Database Mirroring or AlwaysOn Availability Groups (SQL Server).
On the server instance that will host the mirror database, set up the rest of the environment that is required for the mirrored database. For more information, see Manage Metadata When Making a Database Available on Another Server Instance (SQL Server).
The basic steps for establishing a mirroring session are as follows:
Create the mirror database by restoring the following backups, using RESTORE WITH NORECOVERY on every restore operation:
Restore a recent full database backup of the principal database, after making sure that the principal database was already using the full recovery model when the backup was taken. The mirror database must have the same name as the principal database.
If you have taken any differential backups of the database since the restored full backup, restore your most recent differential backup.
Restore all the log backups done since the full or differential database backup.
For more information, see Prepare a Mirror Database for Mirroring (SQL Server).
Complete the remaining setup steps as soon as you can after taking the backup of the principal database. Before you can start mirroring on the partners, you should create a current log backup on the original database and restore it to the future mirror database.
You can set up mirroring by using either Transact-SQL or the Database Mirroring Wizard. For more information, see one of the following:
By default a session is set to full transaction safety (SAFETY is set to FULL), which starts the session in synchronous, high-safety mode without automatic failover. You can reconfigure the session to run in high-safety mode with automatic failover or in asynchronous, high-performance mode, as follows:
High-safety mode with automatic failover
If you want a high-safety mode session to support automatic failover, add a witness server instance.
To add a witness
The database owner can turn off the witness for a database at any time. Turning off the witness is equivalent to having no witness, and automatic failover cannot occur.
Alternatively, if you do not want automatic failover and you prefer to emphasize performance over availability, turn off transaction safety. For more information, see Change Transaction Safety in a Database Mirroring Session (Transact-SQL).
In high-performance mode, WITNESS needs to be set to OFF. For more information, see Quorum: How a Witness Affects Database Availability (Database Mirroring).
For an example of using Transact-SQL to set up database mirroring using Microsoft Windows Authentication, see Example: Setting Up Database Mirroring Using Windows Authentication (Transact-SQL).
For an example of using Transact-SQL to set up database mirroring using certificate-based security, see Example: Setting Up Database Mirroring Using Certificates (Transact-SQL).
- Prepare a Mirror Database for Mirroring (SQL Server)
Summarizes the steps for creating a mirror database or preparing a mirror database before resuming a suspended session. Also provides links to how-to topics.
- Specify a Server Network Address (Database Mirroring)
Describes the syntax of a server network address, how the address identifies the database mirroring endpoint of the server instance, and how to find the fully-qualified domain name of a system.
- Establish a Database Mirroring Session Using Windows Authentication (SQL Server Management Studio)
Describes how to use the Configure Database Mirroring Security Wizard to start database mirroring on a database.
- Establish a Database Mirroring Session Using Windows Authentication (Transact-SQL)
Describes the Transact-SQL steps for setting up database mirroring.
- Example: Setting Up Database Mirroring Using Windows Authentication (Transact-SQL)
Contains an example of all the stages required to create a database mirroring session with a witness, using Windows Authentication.
- Example: Setting Up Database Mirroring Using Certificates (Transact-SQL)
Contains an example of all the stages required to create a database mirroring session with a witness, using certificate-based authentication.
- Set Up Login Accounts for Database Mirroring or AlwaysOn Availability Groups (SQL Server)
Describes creating a login for a remote server instance that is using a different account than the local server instance.
SQL Server Management Studio
Transact-SQL/SQL Server Management Studio