What is the usage of the WITNESS instance in SQL Server 2005 Database Mirroring?

Question

We are considering running our SQL Server 2005 database server on a Dell 2950 x64. We also want to implement database mirroring, and are considering using an HP DL360 as a witness server. In addition, the mirrored databases will be running in a VM running on ESX.

Is it a feasible configuration to use different hardware for the witness server? In other words, does the witness server hardware have to match the other hardware involved in database mirroring?

Also, is it recommended to implement database mirroing without a witness instance?

Answer

The witness server can run on any hardware that will run SQL Server 2005, including SQL Server 2005 Express Edition.

For optimum high availability using database mirroring, a witness instance is very important, although not mandantory, assuming you want to give up some of the benefits of database mirroring.

In a typical scenario of database mirroring, in which automatic failover is desired, database mirroring must be configured with the high-safety mode, which requires a witness instance running on a third server (not running on the production or mirror instances). This witness instance enables the mirror server to recognize whether to initiate an automatic failover or not. Unlike the two partners, the witness does not serve the databases being mirrored. The only purpose of the witness instance is to monitor the production and mirrored instances, determining whether or not to initiate a failover. If a witness instance is not used, then automatic failover is not available, and failover must be implemented manually.

The location of the witness (indeed, the location and configuration of all the servers) depends on your environment. Ideally, all three servers should be in well-maintained locations with high-quality services and personnel. This should also ensure to keep the network connection 'available' always to avoid any sort of potential problems during automatic failover.

The performance of database mirroring is very closely associated with the type of application, transaction safety level, and network performance. Understanding application behavior in terms of log generation rate, number of concurrent connections, and size of transactions is important in achieving the best performance. The network plays a very important role in a database mirroring environment. When used with a high-bandwidth and low-latency network, database mirroring can provide a reliable high-availability solution for planned and unplanned downtimes.