Setup Database Mirroring in SQL Server 2012 with Certificates

Many times we faced issues with mirroring setup with authentication, So in this article we are going to configure database mirroring by creating endpoint and required certificates for authenticate and authorization.

Follow below steps to setup database mirroring in sql server with 1 Principal server and 1 Mirror server. This article does not include witness server. So if you want automatic failover setup this article would not help you. Read this article for role of witness server

Step 1. Setup Endpoint and Certificates

With the first step we are going to create endpoint on both server and configure certificates for authentication and authorization.

Create Certificate and end-points (On Principal Instance):

use master;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Secure_Password';
GO

CREATE CERTIFICATE principal_cert WITH SUBJECT = 'Principal Server Certificate', START_DATE = '2013/01/01', EXPIRY_DATE = '2020/01/01';
GO

CREATE ENDPOINT Mirroring STATE = STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE principal_cert, ENCRYPTION = disabled, ROLE = ALL);
GO

BACKUP CERTIFICATE principal_cert TO FILE = 'D:certificatesprincipal_cert.cer'
GO

Create Certificate and end-points (On Mirror Instance):

USE master
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Secure_Password';
GO

CREATE CERTIFICATE mirror_cert WITH SUBJECT = 'Mirror Server Certificate', START_DATE = '2013/01/01', EXPIRY_DATE = '2020/01/01';
GO

CREATE ENDPOINT Mirroring STATE = STARTED
    AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)
    FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE mirror_cert, ENCRYPTION = disabled, ROLE = ALL );
GO

BACKUP CERTIFICATE mirror_cert
    TO FILE = 'D:certificatesmirror_cert.cer';
GO

Create User and Associate Certificate (On Principal Instance):

At this stage we need to exchange certificates to other instance. Copy Principal instance certificate on mirror server and Mirror instance certificate to Principal server manually.

USE MASTER
GO

CREATE LOGIN mirror_login WITH PASSWORD = 'Secure_Password';
GO

CREATE USER mirror_user FOR LOGIN mirror_login;
GO

CREATE CERTIFICATE mirror_cert
    AUTHORIZATION mirror_user
    FROM FILE = 'D:certificatemirror_cert.cer';
GO

GRANT CONNECT ON ENDPOINT::Mirroring TO [mirror_login];
GO

Create User and Associate Certificate ( On Mirror Instance):

USE MASTER
GO
CREATE LOGIN principal_login WITH PASSWORD = 'Secure_Password';
go

CREATE USER principal_user FROM LOGIN principal_login;
go

CREATE CERTIFICATE principal_cert
	AUTHORIZATION Principal_user
	FROM FILE = 'D:certificateprincipal_cert.cer';
Go

GRANT CONNECT ON Endpoint::Mirroring TO [Principal_Login];
go

Step 2. Copy Database on Mirror Server

After configuring authentication do following steps given below screen shots.

Update Principal database to Full Recovery Mode:

ALTER DATABASE MirrorDBName SET RECOVERY FULL;
GO

Dump database on Principal Server:

USE MASTER
GO

BACKUP DATABASE MirrorDBName
    TO DISK = 'D:BackupsMirrorDBName_full.bak'
GO

Dump Log on Principal Server:

USE MASTER
GO
BACKUP LOG MirrorDBName
    TO DISK = 'D:BackupsMirrorDBName_log.trn'
GO

After taking backup copy the database backup and log backup to Mirror server manually. then after go to next step.

Restore Backup on Mirror Server with No Recovery option:

USE MASTER
GO

RESTORE DATABASE MirrorDBName
    FROM DISK = 'D:BackupsMirrorDBName_full.bak'
    WITH NORECOVERY
GO

Restore Log on Mirror Server with No Recovery option:

USE MASTER
GO

RESTORE LOG MirrorDBName
    FROM DISK = 'D:BackupsMirrorDBName_log.trn'
    WITH NORECOVERY
GO

Step 3. Enable Database Mirroring in SQL Server

After completing step 1 and 2, now we are ready to enable mirroring between databases using following commands.

Execute this on Principal Database Instance.

ALTER DATABASE MirrorDBName
    SET PARTNER = 'TCP://MIRROR-INSTANCE:5023'
GO

Execute this on Mirror Database Instance.

ALTER DATABASE MirrorDBName
    SET PARTNER = 'TCP://PRINCIPAL-INSTANCE:5022'
GO