Database Mirroring Setup - SQL Server

Prerequisites¶

1. You must have three database servers: a principal, a mirror, and a witness. (Actually, the witness is optional.)

2. You must have the SA password for each of the database servers.

3. Each database instance must be the same version.

4. Each database must be use a FULL recovery model. You can check this by running the following SQL on the principal server.

select name, recovery_model_desc 
from master.sys.databases 
where name not in (
     'master'
    ,'model'
    ,'msdb'
    ,'ReportServer'
    ,'ReportServerTempDB'
    ,'tempdb'
    ) 
order by name

5. You must have Desktop access (e.g., be able to RDP into) each of the database servers.

6. The following procedure requires that each server has the folder C:\SQLMIRROR.

Procedure Notes¶

1. Server Names should be the public DNS name: for example, ec2-100-100-100-100.compute-1.amazonaws.com

2. Server Normalized Names should be the Server Name with all punctuation changed to underscores: for example, ec2_100_100_100_100_compute_1_amazonaws_com

Procedure¶

1. Create database master key, certificate, and endpoint on each server¶

Principal Server

use master
GO
/* If there is not already a DATABASE MASTER KEY in the master database, create one. */
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeyPassword';
GO
/* Backup the Master Key */
BACKUP MASTER KEY TO FILE = 'C:\sqlmirror\PrincipalServerNormalizedName.DMK' 
ENCRYPTION BY PASSWORD = 'MasterKeyBackupPassword' 
GO
/* Create Certificate */
CREATE CERTIFICATE PrincipalServerNormalizedName_cert AUTHORIZATION dbo 
WITH SUBJECT='Certificate for instance on PrincipalServerName'
     START_DATE='03/09/2013',  /* make sure this is a day prior to the current date */
     EXPIRY_DATE='12/31/2020'; /* make sure this is set out 10-20 years */
GO
/* Create EndPoint */
CREATE ENDPOINT Endpoint_Mirroring
	STATE = STARTED
		AS TCP(LISTENER_PORT=8123, LISTENER_IP=ALL)
		FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE 
		PrincipalServerNormalizedName_CERT, 
		ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL)
GO
/* Backup Certificate */
BACKUP CERTIFICATE PrincipalServerNormalizedName_CERT 
TO FILE = 'C:\sqlmirror\PrincipalServerNormalizedName.cer'
GO

Mirror Server

use master
GO
/* If there is not already a DATABASE MASTER KEY in the master database, create one. */
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeyPassword';
GO
/* Backup the Master Key */
BACKUP MASTER KEY TO FILE = 'C:\sqlmirror\PrincipalServerNormalizedName.DMK' 
ENCRYPTION BY PASSWORD = 'MasterKeyBackupPassword' 
GO
/* Create Certificate */
CREATE CERTIFICATE MirrorServerNormalizedName_CERT AUTHORIZATION dbo 
WITH SUBJECT='Certificate for instance on MirrorServerName'
     START_DATE='03/09/2013',  /* make sure this is a day prior to the current date */
     EXPIRY_DATE='12/31/2020'; /* make sure this is set out 10-20 years */
GO
/* Create EndPoint */
CREATE ENDPOINT Endpoint_Mirroring
	STATE = STARTED
		AS TCP(LISTENER_PORT=8123, LISTENER_IP=ALL)
		FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE 
		MirrorServerNormalizedName_CERT, 
		ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL)
GO
/* Backup Certificate */
BACKUP CERTIFICATE MirrorServerNormalizedName_CERT 
TO FILE = 'C:\sqlmirror\MirrorServerNormalizedName.cer'
GO

Witness Server
Note that this is the same as the other two scripts except for (1) server name, and (2) the CREATE ENDPOINT statement uses "ROLE = WITNESS" instead of "ROLE = ALL". (The latter is not supported on SQL Server Express, which can be used for the witness server.)

use master
GO
/* If there is not already a DATABASE MASTER KEY in the master database, create one. */
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeyPassword';
GO
/* Backup the Master Key */
BACKUP MASTER KEY TO FILE = 'C:\sqlmirror\PrincipalServerNormalizedName.DMK' 
ENCRYPTION BY PASSWORD = 'MasterKeyBackupPassword' 
GO
/* Create Certificate */
CREATE CERTIFICATE WitnessServerNormalizedName_CERT AUTHORIZATION dbo 
WITH SUBJECT='Certificate for instance on WitnessServerName'
     START_DATE='03/09/2013',  /* make sure this is a day prior to the current date */
     EXPIRY_DATE='12/31/2020'; /* make sure this is set out 10-20 years */
GO
/* Create EndPoint */
CREATE ENDPOINT Endpoint_Mirroring
	STATE = STARTED
		AS TCP(LISTENER_PORT=8122, LISTENER_IP=ALL)
		FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE 
		WitnessServerNormalizedName_CERT, 
		ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = WITNESS)
GO
/* Backup Certificate */
BACKUP CERTIFICATE WitnessServerNormalizedName_CERT 
TO FILE = 'C:\sqlmirror\WitnessServerNormalizedName.cer'
GO

2. Copy certificate files to all servers¶

The previous step created a *.CER file in the C:\SQLMIRROR folder on each server. Copy each such file to the other two servers so that each server has the same set of all three files in its C:\SQLMIRROR folder.

3. Grant permission to connect to endpoints¶

Principal Server

/* MIRROR */
use master
GO
CREATE LOGIN MirrorServerNormalizedName_LOGIN WITH PASSWORD='Password1!';
GO
CREATE USER MirrorServerNormalizedName_USER 
FOR LOGIN MirrorServerNormalizedName_LOGIN;
GO
CREATE CERTIFICATE MirrorServerNormalizedName_CERT 
FROM FILE = 'C:\sqlmirror\MirrorServerNormalizedName.cer';
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring 
TO [MirrorServerNormalizedName_LOGIN];
GO

/* WITNESS */
use master
GO
CREATE LOGIN WitnessServerNormalizedName_LOGIN WITH PASSWORD='Password1!';
GO
CREATE USER WitnessServerNormalizedName_USER 
FOR LOGIN WitnessServerNormalizedName_LOGIN;
GO
CREATE CERTIFICATE WitnessServerNormalizedName_CERT 
FROM FILE = 'C:\sqlmirror\WitnessServerNormalizedName.cer';
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring 
TO [WitnessServerNormalizedName_LOGIN];
GO

Mirror Server

/* PRINCIPAL */
use master
GO
CREATE LOGIN PrincipalServerNormalizedName_LOGIN WITH PASSWORD='Password1!';
GO
CREATE USER PrincipalServerNormalizedName_USER 
FOR LOGIN PrincipalServerNormalizedName_LOGIN;
GO
CREATE CERTIFICATE PrincipalServerNormalizedName_CERT 
FROM FILE = 'C:\sqlmirror\PrincipalServerNormalizedName.cer';
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring 
TO [PrincipalServerNormalizedName_LOGIN];
GO

/* WITNESS */
use master
GO
CREATE LOGIN WitnessServerNormalizedName_LOGIN WITH PASSWORD='Password1!';
GO
CREATE USER WitnessServerNormalizedName_USER 
FOR LOGIN WitnessServerNormalizedName_LOGIN;
GO
CREATE CERTIFICATE WitnessServerNormalizedName_CERT 
FROM FILE = 'C:\sqlmirror\WitnessServerNormalizedName.cer';
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring 
TO [WitnessServerNormalizedName_LOGIN];
GO

Witness Server

/* PRINCIPAL */
use master
GO
CREATE LOGIN PrincipalServerNormalizedName_LOGIN WITH PASSWORD='Password1!';
GO
CREATE USER PrincipalServerNormalizedName_USER 
FOR LOGIN PrincipalServerNormalizedName_LOGIN;
GO
CREATE CERTIFICATE PrincipalServerNormalizedName_CERT 
FROM FILE = 'C:\sqlmirror\PrincipalServerNormalizedName.cer';
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring 
TO [PrincipalServerNormalizedName_LOGIN];
GO

/* MIRROR */
use master
GO
CREATE LOGIN MirrorServerNormalizedName_LOGIN WITH PASSWORD='Password1!';
GO
CREATE USER MirrorServerNormalizedName_USER 
FOR LOGIN MirrorServerNormalizedName_LOGIN;
GO
CREATE CERTIFICATE MirrorServerNormalizedName_CERT 
FROM FILE = 'C:\sqlmirror\MirrorServerNormalizedName.cer';
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring 
TO [MirrorServerNormalizedName_LOGIN];
GO

4. Backup all databases¶

For each database of interest, execute the following statement on the Principal server.

BACKUP DATABASE [DatabaseName] TO DISK = 'C:\SQLMIRROR\DatabaseName-CurrentDate:YYYY-MM-DD.bak

5. Backup all database logs¶

For each database of interest, execute the following statement on the Principal server.

BACKUP LOG [DatabaseName] TO  DISK = N'C:\SQLMIRROR\DatabaseName-CurrentDate:YYYY-MM-DD_LOG.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'DatabaseName-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

6. Copy backup files from principal to mirror server¶

The previous two steps created *.BAK files on in the C:\SQLMIRROR folder on the principal server. Copy them to the mirror server.

7. Restore database to mirror server¶

For each database of interest, execute the following statements on the Mirror server. (Note that the first line is unnecessary if the databases don't already exist on the mirror server.)

DROP DATABASE [DatabaseName]
GO
RESTORE DATABASE [DatabaseName] FROM DISK = 'C:\SQLMIRROR\DatabaseName-CurrentDate:YYYY-MM-DD.bak
WITH NORECOVERY, NOUNLOAD, STATS = 10
GO

8. Restore database logs to mirror server¶

For each database of interest, execute the following statements on the Mirror server.

RESTORE LOG [DatabaseName] FROM DISK = N'C:\SQLMIRROR\DatabaseName-CurrentDate:YYYY-MM-DD_LOG.bak' 
WITH FILE = 1, NORECOVERY, NOUNLOAD,  STATS = 10

9. Initiate mirroring on mirror server¶

For each database of interest, execute the following statement on the Mirror server. Note that if the PARTNER is already set for the database, you will need to execute a ALTER DATABASE DatabaseName SET PARTNER OFF statement before executing the following statement.

ALTER DATABASE [DatabaseName] SET PARTNER = 'TCP://PrincipalServerName:8123'

10. Complete mirroring on principal server¶

For each database of interest, execute the following statement on the Principal server, noting the following.

If the PARTNER is already set for the database, you will need to execute a ALTER DATABASE DatabaseName SET PARTNER OFF statement before executing the following statements.
If the WITNESS is already set for the database, you will need to execute a ALTER DATABASE DatabaseName SET WITNESS OFF statement before executing the following statements.

ALTER DATABASE [DatabaseName] SET PARTNER = 'TCP://MirrorServerName:8123'
GO
ALTER DATABASE [DatabaseName] SET WITNESS = 'TCP://WitnessServerName:8122'
GO