SQL 2005 Database Mirroring

SQL 2005 Database Mirroring

            At heart I am a real geek. I love to find out what is going on in the world of emerging technology. Hours and hours can slip away without notice while I am immersed in the wonders of the way software works. (Or sometimes doesn’t work) I have been working with SQL 2005 recently and I have to say “I Love It!” There is one feature that seems to keep popping up everywhere I look. Mirroring. Mirroring. Mirroring.

Did I mention the hottest thing to hit the SQL 2005 release this year was Mirroring.

I wanted to spend a couple of minutes highlighting this feature and providing relevant information on the setup and operating scenarios which might result from database mirroring.

The Summary

            Database mirroring in SQL 2005 introduces the option to share transactions between 2 servers called a principal and a mirror. The transactions can be shared in a synchronous or an asynchronous format. (The servers either wait for one another to commit transactions before moving on to additional transaction log processing, or the principal notifies the mirror of pending transactions and simply moves on without waiting for the mirror to catch up.) Each method has advantages and disadvantages. The sharing of transaction logs between server instances makes it possible for failover to occur. Failover can take on 3 forms. Automatic, manual, and forced. In order for automatic failover to occur there must be an additional server added to this mix called a witness. The witness server participates along with the principal and the mirror in forming a Quorum. (Voting Majority) Together the quorum makes decisions about failover and role changes in the event of a server failure.

The Resource

            I have read a lot of stuff on SQL 2005 and database mirroring. Here is what I would consider the definitive resource. It is a white paper from the TechNet side of the house. It is very easy reading and the quality of the descriptions and level of detail is perfect for the topic. I won’t lie to you it is a few pages in length but worth every minute of the time you will spend reading it.

https://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx