Configure Database Mirroring in SQL Server 2008

Steps to configure Database Mirroring in SQL Server 2008

SQL Server Database mirroring is one more method to provide High Availability solution for database with added as a new feature in SQL Server 2005 and 2008. Two SQL server instances takes part in this, one will act as principal server another will act as mirrored server.

How SQL Sever database mirroring works ?

Active transaction log of database travels from Source database which is present on principal server to mirror database on target mirrored server

Advantage of database mirroring

In this method database should be in full recovery model. Its alternative of log shipping present in SQL Server 2000. In log shipping if anything happens to data or transaction log or its got corrupted we cannot do the 100% recovery whereas in database mirroring 100% recovery is possible in High Safety mode.

SQL Server Database Mirroring can be configured in two different modes Synchronous and Asynchronous.
Synchronous mode: As name suggest, principal server sends the active log to the mirrored server and waits till the active log get committed on mirrored server also sends acknowledgement of the same to principal server . After that principal server commit the transaction.
In Synchronous mode is divided into two modes High Safety with automatic failover (which include witness server).
High safety without automatic failover (here manual failover is requires if any disaster strike)

Asynchronous mode : In asynchronous mode, principal server sends the active log to the mirrored server and does not wait for acknowledgement from mirrored server to commit. It’s also called as High Performance mode.

How to configure database mirroring in SQL Server 2008

Here is the check list before configuring SQL Server database mirroring
* It’s available in SQL Server Enterprise, Standard and Developer edition.
* Change the database recovery model to full.
* Create the Mirror database with same name on mirrored server (If not exists).
* Take a full database backup and transaction log backup of principal database and restored it on the mirror database with “No Recovery option”(which keeps database in restoring mode).

We are using two SQL Server 2008 instance

1. Principal Server : CHEIWTAP01
2. Mirrored Server : CHEIWTAP02

Here we are going to configure the High Performance mode of Database mirroring.
Connect to SQL Server 2008 management studio
Start -> Programs -> Microsoft SQL Server 2008->SQL Server 2008 Management Studio.
Connect to Principal Server create database named it DB1.
Right click on DB1 and then click on properties.

Database Mirroring in sql server 2008

On Database properties window, click on Mirroring option and then Click on Configure Security to start database mirroring setup as shown below.

 

configure database mirroring in sql server 2008

 

After clicking on configure security, it will display the below screen click next.

configure database mirroring security wizard

 

We are not using Witness Server in this case so check the No option and proceed with next.

Witness Server for database mirroring in sql server 2008

 

Need restore the full database backup on mirrored server to sync both databases.

Error while configuring Database mirroring

 

In this window select the principal server, give port number details and click on next to proceed.

Configure listener port for database mirroring in sql server 2008

 

Here select the mirror server and give the port number to connect after that click on next.

Mirror Server Instance configuration in sql server 2008 database mirroring

 

Provide the service account details if services are running on different domain or services account and click on next.

configure service account for database mirror instance

 

Click on Finish to complete the setup after that it will show dialogue box to start the database mirroring click on Yes.

completed database mirroring

 

Done after starting of database mirroring Start Mirroring option will get disable.

database mirror monitoring in sql server 2008

 

Before starting of setup we should restore the principal database on mirror server to establish the connection to avoid this error.

Error while starting database mirroring

 

Transaction log backup should be restored it on the mirror database to avoid this error.

starting database mirroring starting database mirroring

 

After restoring the transaction log backup on mirror server again click on Start Mirroring option this time it works and completed the setup of database mirroring successfully.

Done let’s check the working of database mirroring concept 😉

Now we have added one table in principal database to see whether its added on mirror database or not for that we have to do manual failover which will make mirror database operational database and principal database in restoring mode.

database mirroring monitor screen

 

To see the changes has been updated on mirror database or not do the fail over.

failover database mirror in sql server 2008

 

Click on Yes

information of failover Database mirror

Changes has updated on both server and its working fine.

We will see the synchronous Mode (with witness server) example in the next document. Till that you can try yourself 😉

 

 

Leave a Reply

  

  

  


3 + 9 =