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.
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.
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.
1- Go to the iSCSI target and create or add another shared disk to be used by the second SQL cluster
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.
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.
5- Right click on the service and applications and click to create a new one
6- Choose DTC
7- Give it a name and a unique IP
8- Select the available storage
9- The second DTC clustered instance is created
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
1- Right click on the first SQL instance and click add resource
2- Select the available DTC (with GUID) service
3- Click next and finish
4- Bring the new resource online
5- Create a dependency between the SQL server service and the newly added DTC resource
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.
7- Right click on the second SQL instance and click add resource
8- Select the available DTC (with GUID) service
9- Click next and finish
10- Bring the new resource online
11- Create a dependency between the SQL server service and the newly added DTC resource
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.
The dependency report for one of the SQL server clusters should look something like the below diagram.
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
- Services that has UK-LIT-DB-02 as the preferred owner
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.
Nice article. Well explained
Do you need to configure DTC or can you set this up without DTC?
This is still active Passive when looking from Instance perspective.Its not the same as Oracle RAC
Do you need DTC? well it depends. Are you going to use distributed transactions? then yes. Usually because we never know if DTC will be needed later or not it is always best practice to configure DTC.
Shiva yes I know it is not like Oracle RAC.
Very Helpfull doc
This is possible in production enviroment, with a lot of transactions?
How work the balanced??. I think that NO?
What is the web config connection string for both instance?
How I will connect to both instance using sql management studio?
My concern is different but couldnt find a thread related to the same. So sharing the details here.
This with regards to Biztalk 2010 application ( Two nodes in a cluster and one Database instance)
We have two BizTalk nodes in a cluster pointing to one Database instance where the BizTalkMsgBox database lies. Now to explain further scenario. We have application that has the BTS HTTP Receive Location (two way) where the client would be hitting the request to this receive location and the particular orchestration would pick up the message through Direct Binding.( message hit to receive location would reside in MsgBox for subscription to pick it up based on messagetype). During this course , we track the messages and orchestration flow through Logs (.txt files) that we have written in a particular location. The Logs generated is through an external class library called in the orchestration just to write the sequence of events taking place. This library is added to global assembly cache in both nodes. So far the request that is entered and processed before it hits the destination is written to the Logs in one specific node. Now the crazy part starts. The destination is SOAP service ,so once the orchestration sends out the processed request to this send port( two way) where the SOAP service URL is provided, we would expect the response to hit back to same BizTalk node from where send port has sent a request, but funny thing is the response from SOAP service hits back to the other BizTalk node in the cluster and external class library in that node generates the log starting with response received. Now another thing is the HTTP receive location which had send the request would be waiting for the response ,but it has gone to the other node and neither way the HTTP receive location at other node could send back to the client. (What I understand, one website hosted in IIS in one node and pushes the request would expect response on the same node rather than going to other website hosted on IIS on the other node). I am unable to figure out this behavior
I presume the scenario is understood and si I hope I get a response.Please push this concern in another thread where ever applicable, if it doesnt fit in current context, since I pretty much concerned for a response.
I lab follow this guide but when i turn of node 1 ( i have 2 node run sql cluster), SQL server clustered instance and a clustered DTC are off. it automatic running on the node 2. How can i setup as resuilt "This makes the two nodes working together as an Active/Active SQL cluster with the appropriate services running on both" and if one of node off, two instance still running on the last node?