Setup Database Mirroring in SQL Server Management Studio

Setting up database mirroring greatly increases the availability of your database and allows you to have a complete redundancy of the data. You can choose to bring the warm standby online anytime or automatically switch to the warm standby when your main database goes down (Automatic failover).

There are different operating modes that we can use when setting up database mirroring, the following is the recommended order to implement them:

  1. Start in asynchronous, high performance mode. (Monitor network errors that may cause database mirroring to fail)
    It is not recommended to use a witness for this mode.
  2. Then synchronous, high-safety mode without automatic failover. (Check for database performance)
  3. Lastly, synchronous, high-safety mode with automatic failover.

The following are some points to note before proceeding:

  • To setup the database mirror, both SQL Servers must be running on the same version and edition of SQL Server (Except the witness server if you’re using one). You can run the following query to check:

    SELECT @@VERSION

    Which gives the following result for my servers:

    Microsoft SQL Server 2012 (SP1) – 11.0.3000.0 (X64)
    Oct 19 2012 13:38:57
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

  • The full recovery model must be used for backup.
  • Port 5022 must be opened in your firewall for all the servers involved (Principal, Mirror and Witness).
  • AlwaysOn Availability Groups cannot be used as it’s only supported in Enterprise editions of SQL Server.
  • Asynchronous mode is only supported in Enterprise editions of SQL Server.

Now, to get on to the actual database mirroring:

  1. Backup the database of your principal server. (Backup type must be Full)
    sql-server-backup-database-full-dialog
  2. Backup the transaction log of your principal server.
    sql-server-backup-database-transaction-log-dialog
  3. Restore the database to your mirror server WITH NORECOVERY. After the database is restored, it’ll be in restoring state (this is normal).sql-server-restore-database-context-menu sql-server-restore-database-general-dialog sql-server-restore-database-no-recovery-options-dialog sql-server-database-state-restoring
  4. Restore the transaction log to your mirror server WITH NORECOVERY.
    sql-server-restore-database-transaction-log-context-menu sql-server-restore-database-transaction-log-general-dialog sql-server-restore-database-transaction-log-no-recovery-options-dialog
  5. Proceed to setup database mirroring. Since my servers are in the same domain, I am using the domain account that the SQL Server instance is running as.
    sql-server-mirror-database-context-menu sql-server-mirror-database-mirroring-dialog sql-server-mirror-database-wizard-start sql-server-mirror-database-wizard-include-witness sql-server-mirror-database-wizard-configure-servers sql-server-mirror-database-wizard-configure-principal sql-server-mirror-database-wizard-configure-mirror sql-server-mirror-database-wizard-configure-witness sql-server-mirror-database-wizard-configure-service-accounts sql-server-mirror-database-wizard-finalize-configuration sql-server-mirror-database-wizard-summary
  6. Once the setup is done, you will have the option to start the mirroring by clicking on Start Mirroring. If you would like to change the operating mode, click on Do Not Start Mirroring.
    sql-server-mirror-database-mirroring-start-dialog
  7. If the setup is successful, you should see the following.
    sql-server-mirror-database-mirroring-operational-dialog sql-server-database-state-mirror-principal-synchronized sql-server-database-state-mirror-mirror-synchronized

To get information about the mirroring state for all the servers involved (Principal, Mirror and Witness), you can run the following query:

SELECT e.name, e.endpoint_id, e.principal_id, e.protocol, e.protocol_desc, ec.local_net_address,
ec.local_tcp_port, e.[type], e.type_desc, e.[state], e.state_desc, e.is_admin_endpoint
FROM sys.endpoints e
LEFT JOIN sys.dm_exec_connections ec
ON ec.endpoint_id = e.endpoint_id
GROUP BY e.name, e.endpoint_id, e.principal_id, e.protocol, e.protocol_desc, ec.local_net_address,
ec.local_tcp_port, e.[type], e.type_desc, e.[state], e.state_desc, e.is_admin_endpoint

Share:Share on FacebookTweet about this on TwitterShare on LinkedInShare on Google+

Leave a Reply

Your email address will not be published. Required fields are marked *