Solved

How expensive is it to enable MSDTC or is there a way to measure the cost?

Posted on 2012-03-28
9
999 Views
Last Modified: 2012-04-13
We want to enable MSDTC for certain reasons, but we are a little bit worried about the performance.  I'm just wondering if there is a way we can measure the cost?  Thanks.
0
Comment
Question by:thomaszhwang
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 400 total points
ID: 37778945
I think that depends directly on your distributed transactions and here's how to monitor it:

"Monitor MS DTC Performance"
http://technet.microsoft.com/en-us/library/cc732972(v=ws.10).aspx
0
 

Author Comment

by:thomaszhwang
ID: 37778963
Actually we only need to coordinate with one SQL Server.  We are trying to enable it for SSIS.
0
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 400 total points
ID: 37778967
And please see below more details and very good analysis from MSDTC point of view as it is almost imposible to give you an estimate about the cost - everybody knows it is costly but by hou much...best is to use article I posted above and measure it in a stress test environment close to your real:

http://stackoverflow.com/questions/1564454/confusion-about-transactions-and-msdtc
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 400 total points
ID: 37778977
Do you know what in the SSIS requires it? Like what type of SSIS task you have that is failing if MSDTC is not enabled?
0
 

Author Comment

by:thomaszhwang
ID: 37779044
If I want to use the TransactionOption property for components that have this option, I will need MSDTC enabled.

Specially, I want one of my data flow tasks to either commit all or commit nothing.
0
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 400 total points
ID: 37779123
In that case as "The built-in transaction support in SSIS makes use of the Distributed Transaction Coordinator (MSDTC) service which must be running. " I your only option may be to either use explicit transactions in that step (which I would NOT recommend) OR to try and use TRY/CATCH for that matter before enabling MSDTC.


http://msdn.microsoft.com/en-us/library/ms175976.aspx
0
 

Author Comment

by:thomaszhwang
ID: 37779167
SSIS doesn't work like stored procedure.  You cannot simply apply a TRY/CATCH to it.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 total points
ID: 37783461
You really can't worry too much about the overhead ... because if your task requires MSDTC, you simply must have it running, or risk a whole lot of other issues.
0
 

Author Closing Comment

by:thomaszhwang
ID: 37845656
Thanks.
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question