So I’m doing some research on MSDTC and its role it does or does not play in various types of calls within SQL Server, and it got me thinking on the following questions:
Are there any 3rd party alternatives/replacements for MSDTC (if they exist) & their pros/cons
Does DTC come into play when I simply query a single table (CRUD or Select) via a linked server?
Is MSDTC required for transactional replication? I know updatable subscriptions require MSDTC, but not clear if non-updatable subscriptions need/use MSDTC.
From googling, I can’t seem to find good answers to these questions, so turning to the community here. Any help in bettering my understanding of MSDTC is greatly appreciated.
MSDTC provides transaction management services for applications. It can act as a regular transaction manager or an XA-compliant resource manager. SQL Server is the well known app that uses DTC but it isn’t really a SQL Server feature or even component. MSDTC is really intended to provide transaction management for two or more distinct resources to ensure transactional consistency. Transaction management is built into SQL Server for a single resource/database but right now it doesn’t natively handle multiple resources. See this old MSDTC blog for a more detailed description. The content is really old but the concepts are still accurate.
As for your questions,
- There are many other transaction managers out there but SQL Server is integrated with DTC by default (e.g. starting your query with
BEGIN DISTRIBUTED TRANSACTIONwill automatically enlist DTC as a transaction manager). If you use JDBC, you can also configure SQL Server to support XA by installing the XA component from the JDBC package. There’s a driver and some install script to do that (all within the package). That sets up some XPs to provide XA transaction support. There may be other TMs out there for SQL Server but I’ve not encountered them.
- Yes it can, depends on what you’re doing. DQ can enlist DTC to manage transactions (it’ll automatically promote to a DT) if needed. E.g. READ only operations will not but WRITE will.
- Transactional replication without updateable subscriptions do not require MSDTC. It is log based and regardless of push or pull, there is no concept of distributed transactions.