Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL DTC Confusion Expand / Collapse
Author
Message
Posted Monday, February 25, 2013 2:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 12:53 AM
Points: 1,375, Visits: 2,667
Hi All

I am trying to figure out the ins and outs of DTC and how SQL uses it.

Does SQL only ever use DTC when SQL is installed as clustered?

Thanks

Post #1423504
Posted Tuesday, February 26, 2013 9:03 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:23 AM
Points: 7,098, Visits: 12,605
SQL also will the DTC if you try using explicit transactions with Linked Servers. I also use it with SSIS transactions, not technically the database engine but another use of DTC nonetheless.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1424317
Posted Tuesday, February 26, 2013 11:14 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 12:53 AM
Points: 1,375, Visits: 2,667
opc.three (2/26/2013)
SQL also will the DTC if you try using explicit transactions with Linked Servers. I also use it with SSIS transactions, not technically the database engine but another use of DTC nonetheless.


Thanks

So even if SQL is installed as non clustered, DTC can still become involved somehow

If that's the case, why when dealing with Clustered instances of SQL Server do we have to explicitly create a DTC component in the Cluster?

Thanks
Post #1424341
Posted Wednesday, February 27, 2013 9:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:23 AM
Points: 7,098, Visits: 12,605
So even if SQL is installed as non clustered, DTC can still become involved somehow

Yep.

If that's the case, why when dealing with Clustered instances of SQL Server do we have to explicitly create a DTC component in the Cluster?

My understanding is that the Windows cluster relies on it. DTC is not a SQL Server-specific piece of software. It is a stand-alone Windows service that SQL Server sometimes leverages. SQL Server clusters sit on top of Windows clusters, and its the Windows cluster that requires the DTC to be configured.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1424588
Posted Thursday, February 28, 2013 3:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:57 AM
Points: 6,304, Visits: 13,601
SQLSACT (2/26/2013)
So even if SQL is installed as non clustered, DTC can still become involved somehow

DTC is not just used for SQL Server, the OS uses it for remote calls too.



SQLSACT (2/26/2013)
If that's the case, why when dealing with Clustered instances of SQL Server do we have to explicitly create a DTC component in the Cluster?

Thanks

In a clustered environment, a clustered DTC is used to recover inflight transactions. Take the following scenario:

Node1 and Node2 have a clustered instance INST1 which is currently active on Node1. DTC is local and not clustered.

A remote transaction is started and during the transaction the instance goes offline and fails over to Node2. The SQL instance is no longer local to the stand alone DTC and cannot connect to recover the inflight transaction.


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1424975
Posted Thursday, February 28, 2013 6:11 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 12:53 AM
Points: 1,375, Visits: 2,667
Thanks Guys - This clears up a lot

Perry, would you say that it's safe to not cluster DTC when you have a single node cluster?

Thanks
Post #1425016
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse