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:
- 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.
- 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…..”
- Step-3: On Principle SQL server S1, right click the database “source01” and select “properties” and “mirroring”. Then click “Configure Security”
- Step-4: Follow the Configure Security wizard, choose “NO” in witness configuration. (If witness server is required, select “YES” and provides witness server details.)
- Step-5: Provide the details of the principle server instance details
- Step-6: Provide the details of the mirror server instance details
- Step-7: Provide the Domain service account to configure the database mirroring.
- 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.
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.”
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