Transactions should (never) be distributed

For the last few blog posts, we’ve been talking about transactions. If you’ve missed any, be sure to click on the links below:

• Using Save Points with Transactions to Get Better
• How about we TRY and CATCH some Transactions?
• Setting up a Friendly TRANSACTION

In the version of transactions that we going to discuss today, we’re going to discuss doing transactions on multiple servers!

A Distributed transaction is defined by HazelSet to be “a set of operations on data that is performed across two or more data repositories”. In even simpler terms, it’s a command run against data on more than one server.

Now, in SQL Server, one way to connect to another server is using a linked server. Another is Polybase, but I’ll leave that up to Kevin Feasel to discuss way more in depth.

With a distributed transaction, you need to have the linked server setup and then you can do the following scenario:

We have 2 SQL Servers that are connected:

• DrWho – Our main server with loads of disk space (Yes, it’s totally bigger on the inside.)
• DrMcCoy – An older machine, but it knows what it can do and what it cannot.

Now, we are going to set up the distributed transaction on DrWho to initially update data there, then move over to DrMcCoy and update some corresponding data there.

USE MessyDatabase;  
GO  

BEGIN DISTRIBUTED TRANSACTION;

-- First delete what is need in our local database
DELETE FROM BBC.Executives = 'Michael Grade';

-- Then we can update things on our remote database
UPDATE DrMcCoy.Lore.Animals
SET Name = 'Tribble' -- Tribbles everywhere!

COMMIT TRANSACTION;  

GO  

Now, there are several things that can cause issues here. (Yes, let’s be upfront because there is nothing worse than thinking something will work when it just won’t.)

1. If the remote machine isn’t on a fast connection to the server that is calling it, then this will take forever. And trust me, it probably isn’t on a fast enough connection at 5 PM on a Friday before a 3-day weekend.
2. Since you have things going on on different servers, you could be locking things up on another server until it completes – you just never know!

Now, setting this up as a distributed transaction does set the transaction to be managed by the Microsoft Distributed Transaction Coordinator if the server is on-Prem and it’s managed by the service itself in Azure.

There are several good points to using the distributed transaction, but be aware of the downfalls because they are glistening!

And with that, I hope I have shown you something to think about when you’re dealing with distributed transactions. Until next time my friends!