SQL Server 2005's database mirroring feature offers new functionality that allows you to configure database failover much easier than in the past. In a previous tip, Database mirroring setup in SQL Server 2005, I walked you through the steps to set up database mirroring as well as explained how it works.
When configuring database mirroring, one option is to use the High Availability mode. This option allows for synchronizing of transaction writes on both servers, as well as offers the ability of automated failover. When using the High Availability mode, you need to have three instances of SQL Server: the principal, mirror and the witness. Here is a summary of what each component does.
- Principal – this is the instance that stores the active database.
- Mirror – this is the instance that receives transactions to keep the mirrored database in sync.
- Witness – this is the instance that communicates with the principal and mirror to determine if failover should occur.
What is the role of the witness server?
The witness is a third instance of SQL Server 2005 that acts as an intermediary between the principal and the mirror in order to determine when to fail over. By having a third instance, it creates the ability to have a 2–1 vote that says one of my components is not available and therefore I am going to fail over. Because of the need to determine if the components are online or offline before an automatic failover, the witness server is only needed when you implement the High Availability mode and you want, or need, automatic failover. This instance doesn't do much more than communicate with the principal and the mirror to make sure they are still alive. No database activity is occurring on this instance, just communication between the three components.
Where should the witness be?
This really depen...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
ds on your network configuration and the reliability of your components. If you implement this over a WAN and have periodic network glitches by having the witness near the principal, then you can eliminate some unnecessary failovers. In addition, if your primary data center has some issues and you want to ensure your database stays online, it may make sense to keep this with the mirror. Therefore, if there are any issues in your primary data center, your mirror and witness can communicate and take over the job.
Physical location is not the only point of concern when placing your witness. It would also make sense to install the witness on a different physical server. It is possible to create three instances on one server and set up database mirroring. But, if you are trying to eliminate hardware failure as a possible cause of downtime, the witness should be installed on a different piece of hardware.
If you are trying to eliminate data center outages, it makes sense to have your mirror in a different physical location. Based on this assumption, you should keep the witness and the principal in the same data center and your mirror in a different location.
What version of SQL Server 2005 for the witness?
The witness server can run on any version of SQL Server 2005, including the Express edition. The principal and mirror can only run on the Standard, Enterprise and Developer editions of SQL Server 2005.
What kind of server does the witness run on?
You can install the witness on any hardware and operating system that supports the version of SQL Server 2005 you are using for the witness. Because of the nature and role of the witness, I suggest using hardware that you feel is reliable and will not cause further complications when implementing and utilizing database mirroring.
What happens if the witness fails?
Because the witness is just one of the three components, if it fails, it does not necessarily mean that a failover will occur. As long as the principal and mirror can still communicate with each other, there is no need for a failover. Therefore the failure of just the witness will not trigger an automated failover.
How does failover work?
Since three components make up the High Availability mode, two of these components need to determine that a problem has occurred and then initiate a failover. If the principal server fails and the witness and mirror can still communicate, the failover process will kick in. The mirror will become the principal and the witness will continue to perform its duties as the witness server.
Summary
Implementing the High Availability mode of database mirroring is pretty straightforward by just implementing another instance of SQL Server to act as the witness. Although from that perspective it is fairly easy, you should implement database mirroring in different phases until you get the hang of how it works and when it will kick in. Using the High Protection mode as the first implementation probably makes more sense than jumping right into the High Availability mode. Either way, though, take the time to test this new component of SQL Server 2005 before you take the leap into using it as part of your production failover strategy.
ABOUT THE AUTHOR:
Greg Robidoux is the president and founder of Edgewood Solutions LLC, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and has delivered presentations at regional SQL Server users' groups and national SQL Server events. Robidoux, who also serves as the SearchSQLServer.com Backup and Recovery expert, welcomes your questions.
Copyright 2007 TechTarget
More on SearchSQLServer.com
Tip: SQL Server bulk-logged recovery
Learning Guide: SQL Server Backup and Recovery Learning Guide
Best of: Top 10 SQL Server backup and recovery tips
