Previous Post:
In my previous post we have learned how to setup database mirroring "Step by Step configuration of Database mirroring using SQL Server 2008".
After successful configuration of data mirroring we have looked into current running states of Principal and Mirror server. We have configured our database mirroring setup using (Principal, Mirror and Witness) database instances. To verify that how mirroring works lets start with some database DML operation on principal server database.
Step 1: I have a table TeaserProspect on my System database in Principal.I am going to drop it.
Step 2: As per mirroring solution this change must reflect on mirror server automatically. Now the question is how can we verify that these changes successfully synchronized to mirror server. To verify that lets stop Principal server database engine to fail-over principal database. There are other way around to test fail-over and we will discuss those in later blogs.
Step 3: Now drill down Mirror server and look at the status on System database now. Previously it shows "mirror, synchronized / restoring" but now it has been changed to " Principal, Disconnected." Remember principal server has been stopped.
It means our Mirror server successfully changed to Principal server on fail-over without any downtime. Now lets figure out that our changes has also been transferred.
Step 4: Try to find "teaserProspect" table in system database. I have tried to find out using information schema DMV and nothing returns. Lets perform these steps again to complete Principal-Mirror-Principal cycle.
To do so I have created "teaserProspect" table again but this time on newly active System database on Mirror Server.
Step 5: lets start our Principal server again and you will see it will show you as active mirror server. It means our Mirror server is Principal now and Principal server acting as a mirror.
Step 6: To verify our data changes i am going to restart our mirror server again.
Step 7: Principal and Mirror servers will reverse again.
Step 8: Lets check "teaserProspect" table in system database on Principal that we have created on Mirror server when it was Active.
:) Our table is there. It means we are able to get high available database mirroring solution on our servers.
Hope you guys will get help to configure database mirroring on your servers. Please post your comments and queries if there are any.
Previous Steps:
Future Works:
In our future posts we will discuss some common error and problems I have faced during Administration mirroring solution. These are related to some common errors, user rights, database engine user and manual failover etc.
No comments:
Post a Comment