Creating an Active/Active SQL Cluster using Hyper-V: Part4 Configuration Revisited

In part 1of this series I showed you how to configure the virtual storage required for the cluster. In part 2 of this series I showed you how to configure two SQL instances on the created windows cluster. In part 3 I showed you how to configure these two SQL instances into an Active/Active configuration. After a while and as I was playing around with the configuration I have noticed that I cannot really move the SQL server services from one node to another automatically! I had to move the DTC first then I can move the SQL server service. Then it hit me! Why am I complicating things and doing everything by hand although the Windows Failover cluster can do much of the plumping in the correct way. So in this follow-up post I will show you how you can configure things the right way (the windows failover cluster way ).

Introduction

In this series of posts I will walk you through the processes of creating an Active/Active SQL server cluster using Hyper-V and Microsoft iSCSI target software for virtualized SAN. The target is to create first a storage server hosted on a normal Windows 2008 R2 server. Then connect to this server using two other machines as iSCSI initiators. Then I will create the windows cluster along with the DTC clustered service. A clustered SQL server instance will then be created. Finally another clustered SQL server instance will be created and Active/Active configuration of both instances will be applied.

Solution Architecture

The solution is fairly simple as per the below configuration.

Revisiting the Cluster configuration and Dependencies

The old dependency was not set correctly since the SQL server service and the associated DTC service are not the same clustered service but are managed differently and I established the link between them using an external service dependency. This rendered the SQL service not movable since the cluster really does not know that it is required to move the DTC service to be able to move the SQL service but instead it treats it as an external service that must exist for the SQL service to start.

The old dependency diagram was like this:

The problem is actually in the dependency on an external clustered service rather than a cluster service resource.

Then I remembered that the Failover Cluster can add the DTC as a generic service resource to the same clustered service, or even better it actually knows of DTC and has a special type of resource as per the below image:

So to configure everything correctly I performed the following:

         1- Brought all services offline including all two SQL server services and even disks.

          2- I removed all DTC related configuration by deleting the two clustered services for DTC including all dependencies from the SQL server services.

          3- Then I started by configuring the first SQL instance (let’s say BCINST) I clicked on “Add Distributed Transaction Coordinator” as per the image above.

          4- I then added the DTC disk to the SQL server clustered service. So I now have two disks in this clustered service as below:

          5- Then after it is added I simply configured the services dependencies so that the new DTC is configured as below

So as you see it is dependent on the name and the disk.

          6- Also I configured the IP to depend on the SQL data disk and the DTC disk also to make sure that the disks are the first thing to be brought online and before anything else.

          7- Then I configured the SQL service to depend on three things (The name, the DTC service, and the Disk) as follows:

     8- Then I crossed my fingers and brought all services online. It worked like a charm and I was finally able to move the SQL service from one node to another with no problems. I then configured the other service exactly the same way. So as you can see below now there are no more external DTC services but only internal clustered service resources as per the below:

The final dependency reports for both services is displayed as below.

This makes the two SQL server clustered services operate as expected and makes moving one service from a node to the other successful.

Happy clustering