In part 1 of 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 ).
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.
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.
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.