MSSQL Database Mirroring Configuration

There are several ways to perform MSSQL database replication, including log shipping, database mirroring and AlwaysOn Availability Groups. It is highly recommended to adopt AlwaysOn Availability Groups whenever it is possible, however, sometime we still need to use database mirroring due to the environment limitation.

This post is to outline the steps to configure the MSSQL database mirroring. The example topology is as below:

Database Mirroring

  • Step-1:  On Principle SQL server S1, perform the backup of database “source01” with both full and transaction log backup. Copy the backup files to the Mirror SQL server.

SQLBackup1SQLBackup2

 

 

 

 

 

 

 

 

 

 

  • Step-2:  On Mirror SQL server S2, do NOT CREATE source01 database before restoration. Right click “databases”and select “restore database”,perform the full and transaction log restoration of the database “source01” with option “RESTORE WITH NORECOVERY”. It is noted that, after the restoration, the database status should be “restoring…..”

SQLRestore1SQLRestore2

  • Step-3: On Principle SQL server S1, right click the database “source01” and select “properties” and “mirroring”. Then click “Configure Security”

SQLMirrorSetup2

  • Step-4: Follow the Configure Security wizard, choose “NO” in witness configuration. (If witness server is required, select “YES” and provides witness server details.)

SQLMirrorSetup3

  • Step-5: Provide the details of the principle server instance details

SQLMirrorSetup4

  • Step-6: Provide the details of the mirror server instance details

SQLMirrorSetup5

  • Step-7: Provide the Domain service account to configure the database mirroring.

SQLMirrorSetup6

  • Step-8:  After the security configuration is setup, select “Start Mirroring” to establish the database mirroring. The database mirroring should be setup after this step.

SQLMirrorSetup7


I have found that SQL Server Management Studio version 17 has a known issue to start the database mirroring session. The error message is similar like below:

“Database xxxx cannot be opened. It is in the middle of a restore.”

Error

In this case, below command lines can be used to setup database mirroring:

  • Command Step-1: On Mirror SQL Server (S2 in our example), execute below SQL commands:
ALTER DATABASE source01  
    SET PARTNER = 'TCP://S1.XXXXXX.COM:5022' 
GO
  • Command Step-2: On Principle SQL Server (S1 in our example), execute below SQL commands:
ALTER DATABASE source01  
    SET PARTNER = 'TCP://S2.XXXXX.COM:5022' 
GO

 

 

 

 

 

 

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s