Saturday, August 7, 2010

Mirroring SQL Server 2005 SP3 Database

Mirroring SQL Server 2005 SP3 database is big headache. Actually database mirroring is very easy in SQL Server 2005 if you avoid some common mistakes.  Here we are going to use SQL Server Management Studio to accomplish mirroring.
Prerequisite:

  1. Three instances of SQL Server 2005 as a Principal, Mirror and Witness instance.
  2. Make sure that each instance has same service account. Ex. In domain environment create a account domain\SQLService and assign it as an service account for each Server instance and SQL Server Agent. and assign connect and sysadmin roles for each instance to the domain\SQLService account. 
  3. Take full database and transaction log backup of database.
  4. Restore that backup(Database and Transaction Log) on Mirror server instance with no recovery option.
Now all set to go.

  1. Start SQL Server Management Studio 2005.   
  2. Connect to the Principal instance using domain\SQLService account.
  3. Right click on database and goto Task->Mirroring.
  4. On the mirroring dialog select Configure Security.
  5. On the security dialog select Witness Instance check box and click Next.
  6. Choose Principal server instance and click connect. Enter port no, default will work. and Enter Endpoint name.
  7. Check encrypt data option for secure data transmission between Principal and Mirror server instance. Click next.
  8. Now choose mirror server instance and click connect using same domain account which we have created for Mirroring. Enter port no and Endpoint name. Click Next.
  9. Now finally choose witness server instance and click connect as we did for mirror instance. Enter port no and Endpoint name. Click next.
  10. On the next screen it will ask for account information as we are using same account for each server instance no need to enter details just click Finish.
  11. All done it will configure each instance and ask you to start mirroring click on Start Mirroring.
Thats it, we have successfully configured database for mirroring you can check status of your mirroring using  Mirroring Monitor dialog to open dialog Right Click on database choose Task->Launch Mirroring Monitor.