Setup Certificate mirroring in SQL Server 2008 R2

Setup Certificate mirroring in SQL Server 2008 R2

Mirroring is my one of the favorite high availability feature till SQL Server 2008 R2 edition. I used to setup mirroring for every important production database I have in our production system if version is SQL Server 2008 R2 or less.

Database mirroring is a solution for increasing the availability of a SQL Server database. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model.

Mirroring can be easily established between servers when they are in same network domain with fully trusted environment but what, if server are in different network or located at different places? Can’t we setup mirroring between those? Answer is yes, still we can setup mirroring if both servers are able to connect each other via any media like VPN or anything else.

Please note that mirroring is deprecated feature from SQL Server 2012 and will be removed from next version so it is good practice to use Always On Availability Group feature in SQL Server 2012.

I will have two servers with SQL Server installed.

Server 1 instance name is SQLX64 which is SQL Server 2008 R2 Enterprise edition on Windows Server 2008 Enterprise OS. This is primary (Principal) server.

Server 2 instance name is MARS which is SQL Server 2008 R2 Enterprise edition on Windows Server 2008 Enterprise OS. This is mirror server.

Both servers are located in same datacenter within same domain name Edison.

I want our database “SMXP” to be mirrored.

Before you start actually implementing steps given here, please confirm that your principal database is on full recovery mode.

Take full and latest transaction backup of your database and restore it to secondary/slave/mirror server with NORECOVERY option.

Execute following code in primary (Principal) server. In my case, it is SQLX64.

--execute following script in SQLX64 which is principal server

USE master
 GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPa$$word';
 GO

--Create certificate in principal server and keep it safe for future use
 CREATE CERTIFICATE SQLX64_mirroring_cert
 WITH SUBJECT = 'SQLX64 certificate',
 START_DATE = '2012-12-01 00:00:00',
 EXPIRY_DATE = '2025-12-31 00:00:00'
 GO

-- backup certificate which we have created in previous step
 -- and manually copy it to MARS which is our mirror server (Secondary server)
 BACKUP CERTIFICATE SQLX64_mirroring_cert TO FILE = 'D:\Database\SQLX64_mirroring.cer';
 GO
 --creating endpoing for mirroring in principal server
 CREATE ENDPOINT Mirroring_Endpoint
 STATE = STARTED
 AS TCP (
 LISTENER_PORT = 5022,
 LISTENER_IP = ALL
 )
 FOR DATABASE_MIRRORING (
 AUTHENTICATION = CERTIFICATE SQLX64_mirroring_cert,
 ENCRYPTION = REQUIRED ALGORITHM AES,
 ROLE = ALL
 )
 GO

-- Create a login for MARS which will be used to connect MARS to SQLX64 server

CREATE LOGIN MARS_mirroring_login WITH PASSWORD = 'StrongPa$$word';
 GO
 -- Create a user for new login we have created above
CREATE USER MARS_mirroring_user FOR LOGIN MARS_mirroring_login;
GO

 

Execute following code in secondary (mirror/slave) server. In my case, it is MARS.

--execute following script in MARS which is mirror server

USE master
 GO

-- setting up master key
 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPa$$word';
 GO

-- creating certificate, keep it safe for future use
 CREATE CERTIFICATE MARS_mirroring_cert
 WITH SUBJECT = 'MARS certificate',
 START_DATE = '2012-12-01 00:00:00',
 EXPIRY_DATE = '2025-12-31 00:00:00'
 GO

-- Backup certificate and coppy it manually to SQLX64
 BACKUP CERTIFICATE MARS_mirroring_cert TO FILE = 'D:\Database\MARS_mirroring.cer';
 GO

--creating endpoint
 CREATE ENDPOINT Mirroring_Endpoint
 STATE = STARTED
 AS TCP (
 LISTENER_PORT = 5022,
 LISTENER_IP = ALL
 )
 FOR DATABASE_MIRRORING (
 AUTHENTICATION = CERTIFICATE MARS_mirroring_cert,
 ENCRYPTION = REQUIRED ALGORITHM AES,
 ROLE = ALL
 )
 GO

-- Create a login for the SQLX64 in MARS
 CREATE LOGIN SQLX64_mirroring_login WITH PASSWORD = 'StrongPa$$word'
 GO

-- Create a user for the new login we have created above.
 CREATE USER SQLX64_mirroring_user FOR LOGIN SQLX64_mirroring_login;
 GO
 

After restoring full and latest transaction backup of principal database to mirror database and performing all of the above given steps.  we are just few steps away now.

We had created certificate in principal as well as in mirror server and I have mentioned that copy certificate backup to other server. Hope you have copied Principal server’s certificate to mirror server and vice versa.

Execute following code in primary (Principal) server. In my case, it is SQLX64.


-- Associating MARS’s certificate in our primary server SQLX64

CREATE CERTIFICATE MARS_cert
 AUTHORIZATION MARS_mirroring_user
 FROM FILE = 'D:\Database\MARS_mirroring.cer'
 GO

--we have to grant permission to login for connecting with ENDPOINT
 GRANT CONNECT ON ENDPOINT::Mirroring_Endpoint TO [MARS_mirroring_login]
 GO
 

Execute following code in secondary (mirror/slave) server. In my case, it is MARS.


---- Associating SQLX64’s certificate in our mirror server MARS

CREATE CERTIFICATE SQLX64_mirroring_cert
 AUTHORIZATION SQLX64_mirroring_user
 FROM FILE = 'D:\Database\SQLX64_mirroring.cer'
 GO

--we have to grant permission to login for connecting with ENDPOINT
 GRANT CONNECT ON ENDPOINT::Mirroring_Endpoint TO [SQLX64_mirroring_login]
 GO

-- Setting up partnership between principal and mirror
 --provide with fully qualified server.domain name or IP.

ALTER DATABASE SMXP SET PARTNER ='tcp://SQLX64.Edison:5022'
 GO

 

Execute following code in primary (Principal) server. In my case, it is SQLX64.


-- Setting up partnership between principal and mirror
 --provide with fully qualified server.domain name or IP.

ALTER DATABASE SMXP SET PARTNER ='tcp://MARS.Edison:5022'
 GO

After performing all steps given above, mirroring will be ready and looks something like this:

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

3 thoughts on “Setup Certificate mirroring in SQL Server 2008 R2

  1. Pingback: Check mirroring status from mirroring monitor in SQL Server

  2. Pingback: Database maintenance in mirroring principal database in SQL Server

  3. Pingback: Milestone of 500+ article

Comments are closed.