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.
You need to create three virtual machines as illustrated above. One as the AD and storage server and another two as the SQL server nodes that will act as Active/Active nodes.
These are all windows 2008 R2 servers and we have created the domain and joined all servers to this domain. You need also to setup two network cards in each machine to function as normal LAN connection and another one for the cluster heartbeat. It would be advisable also to separate the storage usage to another network if you have heavy usage. The configuration given here is all static with normal local IPs assigned on all network cards.
In this section we will go through the needed steps to create the virtual storage server based SAN.
1- Download the required iSCSI target software from http://www.microsoft.com/download/en/details.aspx?id=19867.
2- Copy the software to the storage server UK-LIT-AD in this case.
3- Double click the file to start the installation.
4- After it completes it will take you to a web page
5- Scroll down and click as below
8- Click install
10- Now open server manager and you will find a new tree as below
12- Give the new target a name (Just any name)
13- In the initiator list just click advanced and enter all the domain names of the servers that will have access to this target. In our case this is UK-LIT-DB1 and UK-LIT-DB2.
14- If it displays a warning about the multiple initiators just accept it.
15- Click finish. And now you have completed the creation of your iSCSI target and what remains is to add the required virtual disks to it.
17- Place the new VHD and give it a name.
18- Choose the disk size
19- Click finish and this would create the fixed size disk.
20- You will need to create the following disks so just follow the same approach
DTC cluster 1 log disk
DTC cluster 2 log disk
SQL cluster 1 shared disk
SQL cluster 2 shared disk
Now we will configure the two SQL nodes to be able to access these disks.
1- Log on to the first node UK-LIT-SQL1
2- Open the iSCSI initiator
3- Change the initiator name to match the machine name
4- In the discovery tab add a new discovery portal using the IP of the storage server.
5- Click on the targets tab and click refresh to show the available targets
6- Click connect then OK.
7- Go to the volumes and devices tab and click auto configure
8- Do the same steps on UK-LIT-SQL2 starting at step 1 above but change the initiator name to match the machine name as below
9- Go to any node of the two and open the server manager and then the disk management.
10- Bring all disks online to this node and then prepare them with primary partitions and format those using NTFS.
In the next parts I will show you how to configure the Active/Active SQL cluster.
And where are the next parts?
Part2 here: blogs.technet.com/.../creating-an-active-active-sql-cluster-using-hyper-v-part2-the-clustered-instances.aspx
Part3 here: blogs.technet.com/.../creating-an-active-active-sql-cluster-using-hyper-v-part3-the-active-active-configuration.aspx
Do i need to create two quorum disks, one quorum disk per node?
Only one Quorum disk is needed.
Thanks ... great guide ... however, doesn't the Storage Server node, where the iSCSI target is defined and hosted become a single point of failure? What is the solution/approach to make that redundant? If the above setup is created on Azure, is there a way to make both the SCSI initiators (or the SQL Instances) directly be aware of the common Azure storage to which they would be anyway writing to? Can the Azure Blob Storage not be exposed directly and consumed by the two instances on the Failover Cluster, without using iSCSI?
Yes you are correct in this setup the storage node is a single failure point. There are multiple solutions for this. The first one is of course using a SAN with any SAN replication strategy (and this is the usual setup any enterprise would apply), the second one is to cluster the iSCSI target service itself on a new windows cluster (but I haven't tried that before), or you can host the entire VM that is hosting the target in Azure and create the required network path to Azure. Can you use Azure storage? This seems very interesting but I do not know I have to dig more in that.
for active active clustering how many partition i need to create on SAN? Please give information in detail
SAN partitions are not really a function in the Active/Active configuration but rather depend on the solution itself and the databases in the solution and this is what we call storage design. I will try to write a blog about that.
would be great to have it updated on Win 2012R2 and SQL2014 design.
This is far too thick on rudimentary shared storage (iscsi) and far too vapor thin on the blog topic. iscsi is old hat, been around forever....gotta be 945875838739598584873485969684747495969568474763983959868574739495687574738959 quadrillionbazzilion
new how too's on this topic just this month alone...Microsoft SQL Server Active/Active Cluster, really? MS's first real swing at an oracle grid/rac....NOT OLD HAT, more pipe dream...but I won't lie, it was too intriguing to pass on, so i bit, like an idiot.
To bad i ended up with a mouthful of dry tang, couldn't have tasted older if it was a mouthful of fart from an astronaut that peddled tang back in the 50's
If you felt equally gypped on meat, check this out instead:
Thank you for your efforts and clear directions. I seems to me there might be some confusion as to what "active-active" clustering's older term is for MS SQL Srv. versus load-balancing RAC with Oracle.
Just a thought. Look forward to your other blogs lines.
Oracle RAC is totally different from Active active cluster(Sql server) .... Oracle RAC behaves like network load balancing .. this information is for pre sales team some one commented for the same.If required will explain as i have worked on both.
Oracle RAC has only one instance with one database on it and that database can be accessed by all the nodes sharing the load ..... so it called as network load balancing.Active Active is totally a different concept