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.
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.
On Database properties window, click on Mirroring option and then Click on Configure Security to start database mirroring setup as shown below.
After clicking on configure security, it will display the below screen click next.
We are not using Witness Server in this case so check the No option and proceed with next.
Need restore the full database backup on mirrored server to sync both databases.
In this window select the principal server, give port number details and click on next to proceed.
Here select the mirror server and give the port number to connect after that click on next.
Provide the service account details if services are running on different domain or services account and click on next.
Click on Finish to complete the setup after that it will show dialogue box to start the database mirroring click on Yes.
Done after starting of database mirroring Start Mirroring option will get disable.
Before starting of setup we should restore the principal database on mirror server to establish the connection to avoid this error.
Transaction log backup should be restored it on the mirror database to avoid this error.
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.
To see the changes has been updated on mirror database or not do the fail over.
Click on Yes
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 😉