Microsoft SQL Server: Database Replication (Mirroring)

By | March 4, 2016

Microsoft SQL Server Standard Edition has a great feature present in this basic license: Database Replication in Synchronous mode.

To activate the database replication of a database from Primary Site (PR) to Disaster Recovery Site (DR) where the database to be replicated is called myDB the following simple steps must be performed:

On PR site do the steps 1-5

STEP 1
Login with database Administrator and create a master-key for the instance. Note that the key will be stored in the master database of the instance.

STEP 2
Create a SSL certificate for this instance.

STEP 3
Create a mirroring endpoint for the server instance using the certificate created above. This mirroring endpoint will be the “service” to which the other site will connect during replication from DR to PR. Note that port 4242 must be opened in the firewall of PR server.

STEP 4
Save the PR certificate

STEP 5
Copy the PR certificate to the DR site as D:\Backup\PR_cert.cer

On DR site do the steps 6-10

STEP 6
Login with database Administrator and create a master-key for the instance. Note that the key will be stored in the master database of the instance.

STEP 7
Create a SSL certificate for this instance.

STEP 8
Create a mirroring endpoint for the server instance using the certificate created above. This mirroring endpoint will be the “service” to which the other site will connect during replication from PR to DR. Note that port 4242 must be opened in the firewall of DR server.

STEP 9
Save the DR certificate

STEP 10
Copy the DR certificate to the PR site as D:\Backup\DR_cert.cer

On PR site execute steps 11-16

STEP 11
Create a login on PR so DR will to be able to connect to PR.

STEP 12
Create a user for the above created login.

STEP 13
Associate the new user with the instance certificate with the same name.

Note: The DR_cert.cer file will be accessed in the security context of the SQL Server service account. This account must have the required file-system permissions.
If you are not sure what is your service account just grant permission to all for this file temporary.

STEP 14
Grant CONNECT permission on the login for the remote mirroring endpoint.

STEP 15
Create a back-up of the database MyDB

STEP 16
Create a back-up of the database log for MyDB

On DR site execute steps 17-22

STEP 17
Create a login on DR so PR will to be able to connect to DR.

STEP 18 … UPDATED
Create a user for the above created login.

Note: As suggested by Paul Barriga the following change is done to this step:

Step 18 is better if you get the SID from live db and it’s used to create the new login. Something like this:

With this extra parameter you are able to failover to DR and don’t need to remap users to existing logins.
The sid from LIVE can be retrieved with the following query:

STEP 19
Associate the new user with the instance certificate with the same name.

STEP 20
Grant CONNECT permission on the login for the remote mirroring endpoint.

STEP 21
Restore the PR back-up of the database MyDB to DR

Note that before restore is done you must first create the MyDB database on the DR site then restore the content and log.

STEP 22
Restore the PR back-up of the database log for MyDB to DR

STEP 23
At DR, set server instance on PR as partner (principal server).

Note that the port must be the port configured at the mirroring endpoint defined on the other site. Make sure that the port is opened in the firewall for both inbound and outbound connections.

After all this the PR database should appear as MyDB (Principal,Synchronized) and the DR database as MyDB (Mirror,Synchronized).

To switch sides simply trigger Task – > Fail-over on the active database. As a consequence the roles of the databases will switch.

STEP 24
At PR, set server instance on DR as partner (mirror server).

Note that the port must be the port configured at the mirroring endpoint defined on the other site. Make sure that the port is opened in the firewall for both inbound and outbound connections.

NOTE:
It is important to set the partner fist on the DR site. When setting a partner on PR the instance from PR site node tries to connect to the partner and fails. The error message says something like: “The database is not configured to replication”. The instance on the DR site node will not try to do that. Thanks to Paul Barriga again for this observation 🙂

STEP 25 … UPDATED
After replication is done make DR as principal and remap the user owner of the MyDB database to the login. From some reason replication broke the link between the user and the login.

Note that this step is no longer necessary if we reuse the SID from the PR database.

STEP 26

Perform fail-over on DR database so PR is principal again.
See the following how to switch sites manually:
Role Switching During a Database Mirroring Session (SQL Server)

Remarks:

For a detail description of what mirroring is see: Database Mirroring (SQL Server)

For details on how to manually promote the mirror as principal in case the principal is failed see:
Manually Fail Over a Database Mirroring Session (Transact-SQL)
or
How can I bring mirror database online after principal server is down ?

To check if the mirror end point is up on either site execute the following SQL query:

Make sure that the 4242 TCP ports are opened on both sites.

Advertisements

2 thoughts on “Microsoft SQL Server: Database Replication (Mirroring)

  1. George Voina

    Thank you Paul. This is in fact a great idea to avoid the user remap. I am updating the post with this.

  2. Paúl Barriga

    Step 18 is better if you get the SID from live db and it’s used to create the new login. Something like this:
    CREATE LOGIN PR_login WITH PASSWORD = ‘PR Login to LIVE @#$%^&123’, SID = ;

    With this extra parameter you are able to failover to DR and don’t need to remap users to existing logins.

    The sid from LIVE can be retrieved with the following query:

    SELECT name, [sid] FROM sys.server_principals WHERE [type] = ‘s’;

    https://www.mssqltips.com/sqlservertip/1590/understanding-and-dealing-with-orphaned-users-in-a-sql-server-database/