Notes and final opinion on MSSQL Server 2012 MSCS

First, the setup. I created two vmware VMs with the following:

• Microsoft Server 2012 R2
• 24GB RAM
• 120GB Primary HDD
• 4 vCPU
• 2x VMXNET 3 NICs

And placed them in their own resource pool

Next I configured networking on the VMs. One NIC was on the iSCSI network connected to the SAN while the other NIC connected to the server network. I then configured CHAP and got iSCSI running from the VMs to the SAN so they could access the SQL Datastore created beforehand.

Here’s a quick overview of the storage solution for this cluster:

I chose to use iSCSI directly from the VM for SQL Datastore access to allow snapshotting of the VMs from within veeam. I had previously tested out using a second HDD from within vmware but the required SCSI bus sharing would not allow proper snapshotting of the VMs.

 

The next step was to cluster the nodes using MSCS and set them up for failover, all pretty straightforward and simple:

After that came preparing the SQL Data disk in the cluster. This proved far more difficult than it should have as SQL Server 2012 does not want the disks to be a cluster shared volume before it is installed. I have to admit this tripped me up quite a bit until I discovered I need to add the disks to the cluster, but not set them to shared cluster mode. I also had to format them and then put the disk into maintenance mode for the duration of the SQL Server install process.

A fun thing I didn’t find much information on was that if your disk is set to cluster shared storage the install doesn’t fail beforehand. Oh no, it waits until it is about 1/2 done installing SQL server and then gives a “KeyNotFoundException” error with code:  0x80131577. The workaround for this error was to add the disk to the cluster but not share it. Put it in maintenance mode. Make sure it is owned by the node that will be installing SQL Server. Then mount it in disk manager as a local drive before installing SQL Server.

Once SQL Server 2012 was installed in clustered mode it was smooth sailing.

Final Verdict:

While the MSCS clustering was simple and easy, and SQL Server 2012 ran well once installed, the hiccups during the install process that required me to uninstall/reinstall the entire SQL Server instance made everything more painful than it should have been. I do like the idea of simple failover support for SQL Server 2012 and would recommend this to others that are looking for an easy to manage SQL failover solution, provided you have the time to work through the initial setup fustrations.

 

the remaining screenshots display the newly formed SQL Server 2012 MSCS failover cluster in all its glory as well as showing just how easy it is to manage the cluster.