How can I connect to a clustered DTC?

Question

(not sure if this is the right forum - just couldn't find anything appropriate)

I am running a .NET C# application that uses MS SQL 2005 databases on different servers inside a single transaction. Until now, this worked fine without me having to specify anything about MSDTC.

But now our company has moved one server to a cluster and has (as they say due to MS best practises) installed the cluster's MSTDC service in a separate cluster group. For that reason my distributed transaction fails, because the cluster's SQL server has a different "network name" (Server name, for my connection string) than the MSDTC. This is due to the fact that MSDTC can fail over to the second cluster node without havin gSQL server following.

I do accept this setting, but now I am lost in programming: how do I specify a connection string so that SQL server knows where it's MSDTC resides?

Or does operations have to configure this inside the SQL Server instance?

Any help would be much appreciated...

Guenter

Answer 1

Hi Guenter DK,

The cluster environment has a virtual IP address and host name to the network that clients and applications use. So try to get those virtual IP address and host name and use the same in your coding this will help you to solve your problem.

Since you are going to use the virtual IP address in your coding, eventhough the cluster resource does the failover when application trying to connect you can connect to the backend sql server database without any issues.

For more information please click here to know more details. I hope you might have got the point if any more clarifications please keep us posted.

 

 

---

Thanks & Regards, Pramilarani.R

Answer 2

Hi Pramilarani,

my problem is that the MSDTC on this cluster has a different server name than the SQL Server. So I would have to have a connection string like "Server=cluster_sql;DTC=cluster_dtc". Is there something like that in OLEDB/.Net connection strings? I haven't found something yet.

I would guess that the SQl Server has some configuration setting to point to the DTC being on the same server, but not being on the same server at the same time (a bit confusing...). Can you point me to this configuration setting so I can tell my operating team to fix it?

thankx

Guenter

Answer 3

Hi,

Cluster may be Active/Passive or Active/Active.
In Active/Passive, only one virtual server is hosted and the standby node is not actively used.
In Active/Active, there are 2 virtual servers and normally each node hosts one. Each node is standby for the other node.

The connection string is written in the usual way in cluster server too, but we have to understand which ipaddress we are going to use.
In case of clustering you have 3 (or more) ip addresses,
[Incase of 2 node Active/Passive we will be having the 3 IP's(1st for cluster Node1, 2nd for Cluster Node2 & 3rd for Sql Server instance)-3rd IP is the Virtual Ip
Incase of 2 node Active/Active we will be having the 4 IP's(1st for cluster Node1, 2nd for Cluster Node2, 3rd for Sql Server instance which is on node1 & 4th for Sql Server instance which is on node2 )3rd and 4th IP is Virtual IP]

So incase of your application's connection string you have to use the SQL Server instance's IPadress(Virtual IP) as a DataSource
If you are using the machine name as a datasource use it like this Servername\InstanceName
If you are using the ip adress as a datasource use it like this Data Source=ipaddress,port number[eg-190.190.200.100,1433(where 1433 is the port number of the respective sql server instance)]
Since you have the naming problem please try using the ipadress with port number option in the data source to connect.

 

For more info regarding the connection string please refer here.

Note:- Port Number-It helps you find/connect to a particular sql instance if you have more than 1 SQL Instance on a Server.

---

Thanks & Regards, Pramilarani.R

Answer 4

Hi,

working with the cluster is not the problem.

For clarification, this is our setup:

The cluster is an active/passive cluster of two nodes (clu_1 and clu_2). SQL Server can be reached via the name clu_sql.

So my connection string goes like

"server=clu_sql;databse=my_db;..."

The change is that MSDTC originally resided in the SQL cluster group. Now it is in it's own cluster group so (as operating tells me) it can fail over wihtout afffecting the SQL Server. The cluster group of MSDTC is clu_dtc. So I guess I'd need something like "server=clu_sql;database=my_db;msdtc=clu_dtc;...", but I can't find that.

Or maybe SQL Server can locate the MSDTC for me, so the reference to clu_dtc should be made in the SQL Server configuration? But where and how?

Yours

Guenter

Answer 5

Hi Guenter,

I would like to clarify few things.

1. Yours is Active/Passive Cluster
2. Is it the SQL Resources and the MSDTC are on same cluster node(Active Node)?
3. Is it your intension is to connect to SQL Server through application to do data manipualtion?

Failure to cluster MS DTC will not block SQL Server 2005 setup, but SQL Server 2005 application functionality may be affected if MS DTC is not properly configured. Creating the MS DTC resource in its own resource group and assigning it to a separate cluster group keeps the SQL resource highly available. I hope this is the reason why they have configured MSDTC resource in its own resource group.

Eventhough you are using the correct datasource but still the application is not working, I hope there might be a problem in configuring the MSDTC. Please request the concerned team to check whether the MSDTC is configured well or not.

The following link may guide you
http://support.microsoft.com/kb/301600/--Install/Configure MSDTC
http://support.microsoft.com/kb/294209/en-us?spid=2855&sid=290--rebuild a broken MSDTC

 

 

---

Thanks & Regards, Pramilarani.R

Answer 6

Hi Pramilarani,

your clarification is correct, this is an active/passive cluster. Back when MSDTC was in the same cluster group as SQL Server, everything worked fine, including distributed transactions.

The only change is that MSDTC now belongs to a different cluster group on the same server.

Beginning with this change, the (unmodified) SSIS packages and .net applications using this server fail with DTC-related errors.

Now operating tells us the same as KB 301600, especially that this is a MUST and we cannot rolls that behavior back without losing MS support.

The problem is that none of the KB articles deals with the fact that (as I suppose) SQL Server has to be reconfigured to use this DTC that could be on the same node, but need not be, as it is in it's own failover group.

We do not ask for a specific MSDTC instance, but it seems that somewhere in the deep infrastructure of SSIS/SQL/Windows Server/MSDTC/you_name_it someone chooses to ues a DTC on the same server name as the SQL server, where now there is none (because it has the server name clu_dtc, not clu_sql any more).

Now either the connection string must be able to specify the MSDTC server name (which I would not prefer, because it brings the development to decide over infrastructure layout), or the SQL instance on clu_sql should be informed that clu_dtc is the right place to look for a DTC, if necessary (which I would repfer, as it leaves infrastructure decisions to infrastructure).

which one works, and how?

regards

Günter

Answer 7

Hi Gunter,

Have you been able to resolve this issue? I am also facing the same issue where my MSDTC is uisng network name as SQL17 while SQL cluster is running under SQL05. The same problem where DTC transections are failing if I try to use SQL05. If you have resolved the issue, please let us know the resolution.

Regards

Aashaka

Answer 8

Firewall may be the problem. Look at this blog post: http://blogs.msdn.com/b/chrisforster/archive/2009/05/29/windows-2008-sql-server-cluster-with-msdtc-when-using-windows-firewall-with-advanced-security.aspx

Answer 9

Hi Gunter,

I had a similar problem and was able to resolve it.  The Failover Cluster Manager defines an "MSDTC Server".  Ours was called "srv-1-msdtc11".  The problem turned out to be DNS.  When I pinged this name on the database server, I got a response.  However, when I pinged this name on the application server I did not.  After adding an entry in the application hosts file to resolve this address, distributed transactions started to work.

Regards,

Kevin