Setting Upward Database Mirroring (SQL Server)

  • Article
  • v minutes to read

Applies to: yesSQL Server (all supported versions)

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

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 divide server instances, which should be on split up host systems. Each of the server instances requires a database mirroring endpoint. If you need to create a database mirroring endpoint, ensure that information technology is accessible to the other server instances.

    The form of authentication used for database mirroring by a server instance is a belongings of its database mirroring endpoint. Two types of transport security are bachelor for database mirroring: Windows Authentication or certificate-based authentication. For more data, see Transport Security for Database Mirroring and Always On Availability Groups (SQL Server).

    The requirements for network access are specific to the grade of authentication, as follows:

    • If using Windows Authentication

      If server instances are running under different domain user accounts, each requires a login in the main database of the others. If the login does not exist, you must create it. For more data, run across Let Network Access to a Database Mirroring Endpoint Using Windows Hallmark (SQL Server).

    • If using certificates

      To enable certificate authentication for database mirroring on a given server example, 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 data, see Use Certificates for a Database Mirroring Endpoint (Transact-SQL).

  2. Make sure that logins exist on the mirror server for all the database users. For more information, come across Ready Login Accounts for Database Mirroring or Always On Availability Groups (SQL Server).

  3. On the server example that volition host the mirror database, set up the remainder of the environment that is required for the mirrored database. For more data, run across Manage Metadata When Making a Database Bachelor on Some other Server Example (SQL Server).

Overview: Establishing a Database Mirroring Session

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

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

    1. Restore a recent full database fill-in 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 accept the aforementioned proper noun 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 fill-in.

    For more than information, see Gear up a Mirror Database for Mirroring (SQL Server).

    Of import

    Complete the remaining setup steps as soon every bit you tin after taking the backup of the primary database. Before you can commencement mirroring on the partners, you should create a electric current log backup on the original database and restore it to the futurity mirror database.

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

    • Establish a Database Mirroring Session Using Windows Authentication (Transact-SQL)

    • Institute a Database Mirroring Session Using Windows Authentication (SQL Server Management Studio)

  3. By default a session is fix to full transaction safety (Rubber is set to FULL), which starts the session in synchronous, loftier-safety mode without automatic failover. You can reconfigure the session to run in high-condom mode with automatic failover or in asynchronous, loftier-performance mode, as follows:

    • High-condom style with automatic failover

      If yous want a high-safety mode session to support automatic failover, add a witness server case.

      To add a witness

      • Add a Database Mirroring Witness Using Windows Authentication (Transact-SQL)

      • Establish a Database Mirroring Session Using Windows Authentication (SQL Server Management Studio)

      Note

      The database owner can turn off the witness for a database at any fourth dimension. Turning off the witness is equivalent to having no witness, and automated failover cannot occur.

    • Loftier-performance fashion

      Alternatively, if you lot exercise not desire automatic failover and you prefer to emphasize performance over availability, plough off transaction rubber. For more information, meet Change Transaction Prophylactic in a Database Mirroring Session (Transact-SQL).

In This Department

Set 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 case, and how to find the fully-qualified domain name of a system.

Establish a Database Mirroring Session Using Windows Hallmark (SQL Server Management Studio)
Describes how to use the Configure Database Mirroring Security Magician 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.

Instance: Setting Upward 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 Ever On Availability Groups (SQL Server)
Describes creating a login for a remote server example that is using a different account than the local server instance.

SQL Server Management Studio

  • Start the Configuring Database Mirroring Security Wizard (SQL Server Direction Studio)

  • Establish a Database Mirroring Session Using Windows Authentication (SQL Server Management Studio)

Transact-SQL

  • Permit Network Access to a Database Mirroring Endpoint Using Windows Authentication (SQL Server)

  • Allow a Database Mirroring Endpoint to Use Certificates for Outbound Connections (Transact-SQL)

  • Allow a Database Mirroring Endpoint to Employ Certificates for Entering Connections (Transact-SQL)

  • Create a Database Mirroring Endpoint for Windows Authentication (Transact-SQL)

  • Establish a Database Mirroring Session Using Windows Hallmark (Transact-SQL)

  • Add a Database Mirroring Witness Using Windows Hallmark (Transact-SQL)

  • Ready a Mirror Database to Use the Trustworthy Belongings (Transact-SQL)

Transact-SQL/SQL Server Management Studio

  • Upgrading Mirrored Instances

  • Prepare a Mirror Database for Mirroring (SQL Server)

  • Troubleshoot Database Mirroring Configuration (SQL Server)

Run into As well

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