MSSQL2005 HA. Cluster, mirror or both?
I'm looking into setting up a High Availibility (HA) MSSQL2005 cluster/mirror.
I know clustering is designed for large deployments, and mirroring can only support two mirrors, I am looking for a solution that will provide HA with the minimum fuss, for a situation where the second server will be providing redundancy only.
(Server) Clustering is more common and as such is supposed to be the least expensive in operational overhead. It enables running the same application on two or more servers providing a HA solution if one of the servers fails. Cluster software
(Cluster Service) natively handles the failover process.
Database mirroring replays transaction log records on a standby server (log shipping) and as a result can failover more rapidly in most cases than a traditional SQL cluster with no loss of committed data. A database mirroring partnership can failover automatically through the implementation of a witness server (high availability/synchronous).A witness is not a single point of failure. If one partner server thinks that there has been a problem with the other partner, the partner servers contact the witness and ask the witness what it sees. There are no specific restrictions on the network for mirroring, but the network connection between the servers is critical. The network should generally be dedicated, and be of high quality and high bandwidth. As a rough guideline, the network bandwidth should be three times the maximum log generation rate. There is a requirement to duplicate storage across the principal and mirror servers, although this requirement can be offset by a DAS or SAN
You can use clustering and mirroring at the same time:
Yes, you can mirror a database from one virtual server to another virtual server in a different cluster. You can even mirror from one virtual server to another virtual server in the same cluster. However, Microsoft does not recommend mirroring to another virtual server in the same cluster. When you use both mirroring and clustering, understand that mirroring and clustering work independently of each other. Mirroring knows nothing about clusters, and clusters know nothing about mirroring. If a node fails, mirroring will probably fail over before the clusters do. Therefore, when the other node in the cluster comes online, this node will be in the mirror role.
I have decided on Mirroring for this operation.
further reading:
http://blogs.technet.com/wbaer/archive/2007/08/15/cluster-or-mirror.aspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirfaq.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
