SQL Server Performance Forum – Threads Archive

MTS serever option

Hi All, what does exactly the "enforce distributed transactions (MTS)" option in SQL Serever Connections options means. Does SQL Server need more than the MSDTC service running on it, in order to support distributed transactions ? Thnaks. Aviel Iluz
Database Administrator
Pacific Micromarketing
Melbourne Australia
www.pacmicro.com.au

Transactions can also span multiple data resources. Distributed transactions give you the latitude to incorporate several distinct operations occurring on different systems into a single pass or fail action. Distributed transaction processing (TP) systems are designed to facilitate transactions that span heterogeneous, transaction-aware resources in a distributed environment. Supported by a distributed TP system, your application can combine into a transactional unit such diverse activities as retrieving a message from a Message Queuing queue, storing the message in a Microsoft SQL Server database. Because they span multiple data resources, it is important that distributed transactions enforce the ACID properties to maintain data consistency across all resources. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

SQL Server itself just need MSDTC. But any other server participating in the distributed transaction also need to use a MSDTC service. Then these two services communicate.

yes, but this still does not explain what the server option "enforce distributed transactions (MTS)" exactly means.
What different does it make to the distributed transaction process if MSDTC is already running on the server and on the clients. Aviel Iluz
Database Administrator
Pacific Micromarketing
Melbourne Australia
www.pacmicro.com.au

That setting is for remote procedure calls to another server (when defined with sp_addserver and not sp_addlinkedserver) from within a local transaction. It decides if the remote procedure call should be included in the current transaction (and thus start a distributed transaction) or if the remote procudure call should not be involved in the local transaction. See BOL for more info under SET REMOTE_PROC_TRANSACTIONS.

Thank you very much, Argyle. This information is exactly what I was looking for. Aviel Iluz
Database Administrator
Pacific Micromarketing
Melbourne Australia
www.pacmicro.com.au

]]>