Creating an Active/Active SQL Cluster using Hyper-V: Part3 the Active/Active Configuration

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 this part I will show you how to configure these two SQL instances into an Active/Active configuration.

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.

clip_image002

Setting Active-Active Configuration

Since we need to configure an Active/Active configuration for this cluster and we do not want any instance to depend on other components from the other instance we will have to add another DTC clustered service to the windows cluster. This is to allow the separation of the DTC service between both instances. I will also show you how to configure the SQL service to depend on its relating DTC service instance so that it moves it along with the SQL instance.

Prepare the second DTC cluster

1- Go to the iSCSI target and create or add another shared disk to be used by the second SQL cluster
clip_image003

2- Go to one of the nodes and then open the iSCSI initiator and then click again on auto configure of the volumes and devices.
clip_image004

3- Now open the disk management utility and create the active partition on this disk and format it using NTFS.

4- Open the windows cluster management and add this disk to the cluster.
clip_image005

5- Right click on the service and applications and click to create a new one
clip_image006

6- Choose DTC
clip_image008

7- Give it a name and a unique IP
clip_image010

8- Select the available storage
clip_image012

9- The second DTC clustered instance is created
clip_image014

Move each SQL instance and its associated DTC service to its preferred node

1- Move one SQL instance and one DTC to the server UK-LIT-DB1

2- The other SQL instance and the other DTC make sure they are moved to the other server UK-LIT-DB2

clip_image015

clip_image017

clip_image019

Add each DTC service as a resource to the associated SQL service

1- Right click on the first SQL instance and click add resource
clip_image020

2- Select the available DTC (with GUID) service
clip_image022

3- Click next and finish
clip_image024

4- Bring the new resource online
clip_image025

5- Create a dependency between the SQL server service and the newly added DTC resource
clip_image026
clip_image027

6- Create a dependency between the newly created DTC service and the SQL server cluster name and disk to make sure it is moved with it.
clip_image028
clip_image029

7- Right click on the second SQL instance and click add resource
clip_image020[1]

8- Select the available DTC (with GUID) service
clip_image031

9- Click next and finish
clip_image033

10- Bring the new resource online
clip_image034

11- Create a dependency between the SQL server service and the newly added DTC resource
clip_image026[1]
clip_image035

12- Create a dependency between the newly created DTC service and the SQL server cluster name and disk to make sure it is moved with it.
clip_image028[1]
clip_image036

The dependency report for one of the SQL server clusters should look something like the below diagram.

clip_image002[4]

 

Setup the Preferred owners for both the SQL instances and associated DTC services

Now you will need to make sure that the preferred owner is one of the nodes for each couple of the SQL instances and the DTC instances.

- Services that has UK-LIT-DB-01 as the preferred owner

clip_image039

clip_image040

- Services that has UK-LIT-DB-02 as the preferred owner

clip_image041

clip_image042

This makes the two nodes working together as an Active/Active SQL cluster with the appropriate services running on both. So if you open the first node you will find a SQL server clustered instance running and a clustered DTC running. On the second node you will find the other clustered SQL server instance running and the associated clustered DTC.

Happy clustering clip_image043