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 

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

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

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

Perform below steps in Mirror server:

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

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

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

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

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.

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.

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

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

→ On Mirror server follow below steps.

→ On principal Server follow below steps.