We have five SQL Servers and want to not only upgrade, but to cluster these servers as well.

Question

We currently have five SQL Server servers managing over 100 databases. We would like upgrade to the newest version, and at the same time incorporate clustering. What are our clustering options, and what would you recommend?

Answer

It looks like you have a big project ahead of you. There are several options, so let's take a look at each, exploring their pros and cons.

One option would be to purchase two very large SQL Servers and create an Active/Active cluster where the 100 databases are split more or less evenly between the two nodes of the cluster. The pros of this would be less cost and less administrative headache. The cons are that these two servers, even though they are large, may not be large enough to handle the entire load. In addition, whenever a failover occurred, performance would really suffer because a single node would have to support all 100 databases.

A second option would be to purchase four to six very large SQL Servers boxes, creating a multi-node cluster, where you have one node as the backup for the others in the cluster. This way, you could split the 100 databases among mutiple servers instead of only two. The pros of this are the ability to distribute the load over more nodes. The cons of this include the complex set up and administration, and it is very expensive for the hardware and the software licenses.

A third option is to purchase five two-node active/passive clusters. This should give you the server resources you need to handle the 100 databases and give you the option to more easily expand should you find that the number of databases, or the load on the servers, grows. The pros of this include: the capacity to easily handle the current load; 2) The capacity to more easily grow into new capacity by adding additional two-node clusters if necessary; 3) Greater performance if a failover should occur, as the passive node will take over fully for the active node (assuming hardware is the same). Cons of this option include: 1) Potentially high cost, 2) Some greater administrative needs, as you have more servers to monitor and manage. A variation of this would be to use two or three Active/Active nodes instead, as this would save some hardware costs. But this would mean that if a failover occurs, that performance during this time would suffer.

Something else you have to keep in mind is how to handle the shared array for each of the clusters. Your best bet, in any of the options described above, is to use a fibre-attached SAN device. This will offer the greatest performance, flexibility, and cost-benefits.

As you might guess, the best solution for you will depend on many factors, some of which I have not described here. My belief is that you want to remain as flexible as possible, leaving room for growth, even if this means you may have to spend a little extra on hardware and software to accomplish this goal.