Share via

Setting Up Database Mirroring (SQL Server)

  • Article

This section describes the prerequisites, recommendations, and steps for setting up database mirroring. For an introduction to database mirroring, see Database Mirroring (SQL Server).

Important

We recommend that you configure database mirroring during off-peak hours because configuration can impact performance.

Preparing a Server Instance to Host a Mirror Server

For each database mirroring session:

  1. 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:

  2. 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).

  3. 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).

Overview: Establishing a Database Mirroring Session

The basic steps for establishing a mirroring session are as follows:

  1. Create the mirror database by restoring the following backups, using RESTORE WITH NORECOVERY on every restore operation:

    1. 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.

    2. If you have taken any differential backups of the database since the restored full backup, restore your most recent differential backup.

    3. 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).

    Important

    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.

  2. You can set up mirroring by using either Transact-SQL or the Database Mirroring Wizard. For more information, see one of the following:

  3. 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:

Note

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).

In This Section

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.

Related Tasks

SQL Server Management Studio

Transact-SQL

Transact-SQL/SQL Server Management Studio

See Also

Database Mirroring (SQL Server)
Database Mirroring: Interoperability and Coexistence (SQL Server)
Transport Security for Database Mirroring and AlwaysOn Availability Groups (SQL Server)
Specify a Server Network Address (Database Mirroring)