Microsoft SQL Server: Database Replication (Mirroring)

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.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PR DB @#$%^&';
go

STEP 2
Create a SSL certificate for this instance.

use master;
create certificate PR_cert
with subject = 'PR DB Certificate',
START_DATE = '04/03/2016', EXPIRY_DATE = '12/31/2042';
go

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.

CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=4242
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE PR_cert
, ENCRYPTION = REQUIRED ALGORITHM RC4
, ROLE = ALL
);
go

STEP 4
Save the PR certificate

BACKUP CERTIFICATE PR_cert TO FILE = 'D:\Backup\PR_cert.cer';
go

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.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'DR DB @#$%^&';
go

STEP 7
Create a SSL certificate for this instance.

use master;
create certificate DR_cert
with subject = 'DR DB Certificate',
START_DATE = '04/03/2016', EXPIRY_DATE = '12/31/2042';
go

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.

CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=4242
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE DR_cert
, ENCRYPTION = REQUIRED ALGORITHM RC4
, ROLE = ALL
);
go

STEP 9
Save the DR certificate

BACKUP CERTIFICATE DR_cert TO FILE = 'D:\Backup\DR_cert.cer';
go

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.

USE master;
CREATE LOGIN DR_login WITH PASSWORD = 'DR Login to LIVE @#$%^&123';
GO

STEP 12
Create a user for the above created login.

CREATE USER DR_user FOR LOGIN DR_login;
GO

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

CREATE CERTIFICATE DR_cert
AUTHORIZATION DR_user
FROM FILE = 'D:\Backup\DR_cert.cer'
GO

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.

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [DR_login];
GO

STEP 15
Create a back-up of the database MyDB

USE MyDB
BACKUP DATABASE MyDB
TO DISK = 'D:\Backup\MyDB_full.bak'
WITH FORMAT
GO

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

BACKUP LOG MyDB
TO DISK = 'D:\Backup\MyDB_log.trn'
WITH FORMAT
GO

On DR site execute steps 17-22

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

USE master;
CREATE LOGIN PR_login WITH PASSWORD = 'PR Login to LIVE @#$%^&123';
GO

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

CREATE USER PR_user FOR LOGIN PR_login;
GO

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:

CREATE LOGIN PR_login WITH PASSWORD = 'PR Login to LIVE @#$%^&123', SID = ;

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

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

CREATE CERTIFICATE PR_cert
AUTHORIZATION PR_user
FROM FILE = 'D:\Backup\PR_cert.cer'
GO

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

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [PR_login];
GO

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

RESTORE DATABASE MyDB
FROM DISK = 'd:\Backup\MyDB_full.bak'
WITH NORECOVERY, REPLACE
GO

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

RESTORE DATABASE MyDB
FROM DISK = 'd:\Backup\MyDB_log.trn'
WITH NORECOVERY, REPLACE
GO

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

ALTER DATABASE MyDB
SET PARTNER = 'TCP://PR_IP:4242';
GO

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

ALTER DATABASE MyDB
SET PARTNER = 'TCP://DR_IP:4242';
GO

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.

ALTER USER myUser WITH LOGIN myUser

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:

 SELECT name, protocol_desc, port, state_desc FROM sys.tcp_endpoints WHERE type_desc = 'DATABASE_MIRRORING'

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