SQL DTC Confusion

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 High Availability » SQL DTC Confusion

SQL DTC Confusion

Rate Topic

Display Mode

Topic Options

Author

Message

SQLSACT

Posted Monday, February 25, 2013 2:33 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 1:04 AM
Points: 1,191, Visits: 2,117

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

opc.three

Posted Tuesday, February 26, 2013 9:03 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:55 PM
Points: 6,720, Visits: 11,759

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1424317

SQLSACT

Posted Tuesday, February 26, 2013 11:14 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 1:04 AM
Points: 1,191, Visits: 2,117

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

opc.three

Posted Wednesday, February 27, 2013 9:05 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:55 PM
Points: 6,720, Visits: 11,759

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1424588

Perry Whittle

Posted Thursday, February 28, 2013 3:44 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 3:08 AM
Points: 5,203, Visits: 11,157

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" Wink

Post #1424975

SQLSACT

Posted Thursday, February 28, 2013 6:11 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 1:04 AM
Points: 1,191, Visits: 2,117

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 »

Permissions