Implementing a distributed transaction across multiple instances of databases is one of the most commonly used items which are less known by developers and DBAs.
Here we intend to outline a method for executing distributed transactions between multiple database instances (from any databases).
First we need to know more about distributed transactions .As we know a transaction is a set of related tasks that either succeed or fail and it should respect the ACID (Atomicity, Consistency, Isolation and Durability) Properties, but the distributed transaction is a transaction which effects on several resources. For a distributed transaction to commit, all participants must guarantee that any change to data will be permanent.
Before discussing how to implement Distributed Transactions, we will take a look at some examples of how using Distributed Transaction.
Example 1: You are working in an organization which has a central sales office and a large number of satellite branches. You are asked to submit daily sales bills (invoices) for each branch in the form of an aggregate sales summary at the end of each day to the head office.
One of the solutions is to create a sales bill (invoices) for each branch in the “sales invoice” table on the central office database, then by using the Link Server, connect to the database servers of each branches and writing a Query and send the result to the related table on the central office database. In order to better manage events and avoid repetitive reading of the data in each branch, after inserting the sales bill( invoice) in the central office database you would prefer to flag the Processed Records (in the Branch) which you do not re-read them later.
Normally, we need to use Transaction to do this operation In case of an error in each steps, the entire work will be canceled, but the important thing is that you encounter an error immediately after executing your commands, because by default SQL Server manages transactions locally while you are using Linked Server in your Transactions, the transactions are not locally anymore.
So, you need to use Distributed Transactions which can support this type of scenarios or you should not use Transaction at all, which will increase the possibility of an error in your system and can cause a lot of financial hardship for your organization.
Example 2: Suppose the sales software uses the SQL Server database and you have sold your software to an organization which has accounting software that uses the ORACLE database. Now the customer has asked you as soon as a record insert in the sales software (SQL database) a new record insert in the Oracle database. Also they asked you if the new records don’t record or inserted in oracle database, your sales system should not be able to record the invoice.
Again one of the most trusted solutions is to use a single transaction to enter a record in the SQL Server and ORACLE database, in this way, the record either is committed in both systems and the sales bill is recorded or if there is any problem nothing record not in the sales system, nor in the accounting system.
In this case, with two different RDBMSs that are installed on separate servers, you will not be able to use the normal Transactions and your best way is using the Distributed Transaction.
By using this method you make a single transaction on multiple independent physical servers with different RDBMS and at the same time you respect the ACID capability on your Transaction.
Here’s how to implement Distributed Transactions in SQL Server.
Step 1: Setting the MSDTC service on Windows
Perform all these 4 of the following settings on all the servers that are supposed to be used on the transaction. (Also on the server which is executing the command):
- In the Run command, type Services.msc and run it to display the list of Windows services.
- Find the Distributed Transaction Coordinator service, start it and put the Startup Type on the Automatic (Delayed Start).
![]()
- From Control Panel, go to Administrative Tools and run Component Services.
- On the Component Services page, go to: Console Root> Component Services> Computers> My Computer> Distributed Transaction Coordinator, and then right-click on the Local DTC and select Properties, now go to the Security tab and select the selected item.
Check these items Network DTC Access, Allow Remote Clients, Allow Inbound, Allow Outbound, No Authentication Required and Enable SNA LU 6.2 Transactions Then click OK button and confirm the item.
Step 2: Setting the Linked Servers Used in SQL Server
- Scroll to the Security tab of your used Linked Server and enable the Enable Promotion of Distributed Transactions.
Step 3: Setting Up Advanced Options in SQL Server Instance:
sp_configure 'show advanced options', 1 reconfigure GO sp_configure 'Ad Hoc Distributed Queries', 1 reconfigure
Step 4: Configure the Firewall
If you use a firewall on your server, apply the following settings to all servers used for Distributed Transaction.
- From the Control Panel, open the Windows Firewall.
- From the right panel, select Allow an app or feature through Windows Firewall and find and activate the Distributed Transaction Coordinator option.
![]()
Step 5: Run the Distributed Transaction
All the settings are done, now it’s time to know how to write a Distributed Transaction, but first of all, do not forget that Distributed Transactions naturally are slower than the Local Transactions because of having the synchronization between different servers.
SET XACT_ABORT ON Begin Distributed Transaction T1 ... ... ... Commit Transaction T1 --OR, Rollback Transaction T1 SET XACT_ABORT OFF
Look at Begin Transaction command in the Syntax! The difference between the Distributed Transaction and the Local Transaction in T-SQL is determined by the Distributed Key word between the Begin and the Transaction, and it is also better to use the XACT_ABORT SET command before and after each Distributed Transactions.
Special thanks to Haleh Simaei for translating this article.
Database administration is an exciting job combining knowledge, art and experience; you must live with it to love it, despite all the difficulties.
Currently I'm working as a Microsoft SQL Server DBA and BI consultant.
I make SQL Server faster and more reliable. Database administration isn’t about passing a certified exam or about pointing and clicking your way through a crisis. Database administration is about applying the right solution at the right time, avoiding risk, and making robust choices that get you home each night in time for dinner with your family.
https://waterfallmagazine.com
Hello, i believe that i noticed you visited my weblog thus i came to go back the choose?.I am attempting to find things to enhance my site!I assume its good
enough to use some of your ideas!!