Befriending Dragons

Transform Tech with Anti-bullying Cultures

Do I need DTC for my SQL Server?

5 Comments

I get a lot of questions about the “best” way to configure the Distributed Transaction Coordinator (DTC) for SQL Server on a Windows 2008 cluster. There is no one best way to do it, and the first question you ask should be “do you even use DTC and if so how and how often?”. If you don’t use DTC or use it rarely, then perhaps you don’t need to spend a lot of time coming up with the absolute “best” method for each instance of SQL Server or your application. If your application calls DTC directly or if you use a feature in SQL Server that calls DTC, then you need DTC to be available. Some examples of how SQL Server uses DTC: linked servers, OPENROWSET, OPENQUERY, OPENDATASOURCE, remote procedure calls, BEGIN DISTRIBUTED TRANSACTION, updatable subscriptions for transactional replication (immediate and queued updating are now deprecated). You can choose to enlist in a DTC transaction from CLR, SSIS, and DTS. SQL Server does not support using DTC with some features such as database mirroring. Be very careful using DTC with functionality such as TRY…CATCH. DTC is a Windows feature and as such can be used by many applications. In addition to finding out if your SQL Server implementation takes advantage of DTC, check if any of your applications enlist DTC, such as with XA transactions. For information on your options for setting up DTC on Windows 2008 clusters, see:

How to configure DTC for SQL Server in a Windows 2008 cluster

http://blogs.msdn.com/cindygross/archive/2009/02/22/how-to-configure-dtc-for-sql-server-in-a-windows-2008-cluster.aspx

5 thoughts on “Do I need DTC for my SQL Server?

  1. Cindy,

    Iimmediate and queued updating are now deprecated. So what should you use instead?

    TIA,

    DD

  2. It depends on your needs. Your options might include merge replication, peer to peer replication, bi-directional transactional replication, service broker, SSIS, CDC, change tracking, or something else.

  3. Thanks a lot, there are lot of article on internet which says that you need DTC if you are using SQL Clustering. You explained well on what conditions you need the DTC when you don;t need it.,
    Appreciate  your article.

  4. Also the following article substantiated your assertion
    blogs.msdn.com/…/msdtc-recommendations-on-sql-failover-cluster.aspx

Leave a Reply to CindyGross Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s