Microsoft SQL Server 2008 Database Mirroring

Scenario:

We have below two server's details. Both servers are in workgroup. Need to setup database mirroring using certificate based authentication. This is going to be Active-Passive mirroring. So at a time only one server(Active) server will serve Database, another DB server(passive) will not be usable as it will be in synchronization stat.

The Active DB server will be called as “Principal” server and Passive DB server will be called as “Mirror” server.

DB Servers:
Active DB IP: 10.36.100.54
Passive DB IP: 10.36.100.53
DB name: liferayprod

Prerequisites
Keep port 5022 open between two Database servers

Database Backup and Restore Procedure
(1) First take Database backup from Principal server. Follow below steps to take database backup.

(2) Select “Backup Type” full and click on Ok button.

(3) Now we need to backup transaction logs.

(4) Now go to location where you have backup database. Copy liferayprod.bak file from that location to Mirror Database.
(5) Now perform below steps on Mirror database to restore database.
(6) Create database, give name “Liferayprod”. Now do right click on that database, click on Task-> Restore → Database.

(7) Now locate database file. Do check mark as shown in figure.

(8) Now click on “Options”. Select below two options as shown in figure. Click on “Ok” button.

(9) Once restore complete, you will see database's status as shown in below figure.

Database Mirroring Queries on Principal and Mirror server

Perform below steps on Principal server:

On Active server open Query browser and fire below queries one by one. Follow the instructions given 

	create master key encryption by password = 'abcd@12s';
	create certificate principal_cert with subject= 'principal_certificate', start_date = '06/28/2012', expiry_date = '06/28/2030';
	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

view raw Create Certificate on Master hosted with ❤ by GitHub

Before running below query create c:\certificate directory in server. If required, need to give full permission to "Everyone".

	Backup certificate principal_cert to file = 'c:\certificate\principal_cert.cer';
	GO
	create login mirror_login with PASSWORD = 'abcd@12s';
	GO
	create user mirror_user from login mirror_login;
	GO

view raw Backup Certificate hosted with ❤ by GitHub

Before running below query, copy mirror server's certificate from mirror server to principal server's c:\certificate directory. 

	Create certificate mirror_cert
	Authorization mirror_user
	From file = 'c:\certificate\mirror_cert.cer';
	GO
	Grant CONNECT ON Endpoint::mirroring to [mirror_login];
	GO

view raw Create Mirror Certificate hosted with ❤ by GitHub

Now wait for 10 mins before applying below query. As first time it takes time to synchronization.

Alter database liferayprod set partner = 'TCP://10.36.100.53:5022';

view raw Alter Database hosted with ❤ by GitHub

Perform below steps in Mirror server:

On Passive server open Query browser and fire below queries one by one. 

	create master key encryption by password = 'abcd@12s';
	create certificate mirror_cert with subject= 'mirror_certificate', start_date = '06/28/2012', expiry_date = '06/28/2030';
	Go
	Create endpoint mirroring state = started
	as tcp ( listener_port = 5022, listener_ip = all)
	for database_mirroring (authentication = certificate mirror_cert, encryption = disabled, role = all);
	Go

view raw Create Master Key hosted with ❤ by GitHub

Before running below query create c:\certificate directory in server. If required, need to give full permission to "Everyone".

	Backup certificate mirror_cert to file = 'c:\certificate\mirror_cert.cer';
	GO
	create login principal_login with PASSWORD = 'abcd@12s';
	GO
	create user principal_user from login principal_login;
	GO

view raw Backup Mirror Certificate hosted with ❤ by GitHub

Before running below query, copy  Principal server's certificate from principal server to mirror server's c:\certificate directory. 

	Create certificate principal_cert
	Authorization principal_user
	From file = 'c:\certificate\principal_cert.cer';
	GO
	Grant CONNECT ON Endpoint::mirroring to [principal_login];
	GO

view raw Create Principal Cert hosted with ❤ by GitHub

Now wait for 10 mins before applying below query. As first time it takes time to synchronization.

Alter database liferayprod set partner = 'TCP://10.36.100.54:5022';

view raw Alter DB hosted with ❤ by GitHub

Varify Mirroring Status

(1) Once you run queries for mirroring, DB mirroring should be started. On the mirror server you should see below mentioned status.

(2) At the principal server, you should be able to see below mentioned status.

(3) We can check mirroring status as shown in below figure. Right click on Database → Tasks → Launch Database mirroring Monitor.

Manual Failover

If you want to do manual Failover then use below query. Which will make Principal database as Mirror Database and Mirror Database->Principal database.

	use master
	ALTER DATABASE liferayprod SET PARTNER FAILOVER

view raw Alter Database hosted with ❤ by GitHub

You will need to do manual Fail over when both databases are not connected with each other. There could be the reason like, database server it self is down or may be blocked port. So in that case you can remove database server's mirroring function. Then database will act like standalone database server. Later on you can configure mirroring once you are able to fix connectivity issue. You can use below command to remove database from mirroring.

ALTER DATABASE liferayprod SET PARTNER OFF

view raw set partner off hosted with ❤ by GitHub

To bring the mirror online manually after dropping mirroring, issue the following command (you can’t do it while it is still mirrored).

Restore Database liferayprod With Recovery;

view raw RestoreDB hosted with ❤ by GitHub

Note: At the time of reconfiguration, you may need to delete “master key” and endpoint through below query.

→ On Mirror server follow below steps.

	drop certificate principal_cert
	drop user principal_user
	drop login principal_login
	drop endpoint mirroring
	drop certificate mirror_cert
	drop master key

view raw run on master hosted with ❤ by GitHub

→ On principal Server follow below steps.

	drop certificate mirror_cert
	drop user mirror_user
	drop login mirror_login
	drop endpoint mirroring
	drop certificate principal_cert
	drop master key

view raw On Mirror run hosted with ❤ by GitHub