Sdílet prostřednictvím

Setting Up Database Mirroring (SQL Server)

  • Článek

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.

In this Topic:

  • Preparing Server Instances to Participate in Database Mirroring

  • Overview: Establishing a Database Mirroring

  • In This Section

  • Related Tasks

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 to set up database mirroring using certificate-based security, see Example: Setting Up Database Mirroring Using Certificates (Transact-SQL).

[Top]

In This Section

SQL Server Management Studio

Transact-SQL

Transact-SQL/SQL Server Management Studio

[Top]

See Also

Concepts

Database Mirroring (SQL Server)

Transport Security for Database Mirroring and AlwaysOn Availability Groups (SQL Server)

Specify a Server Network Address (Database Mirroring)

Other Resources

Database Mirroring: Interoperability and Coexistence (SQL Server)