SQL Server Administration: AlwaysOn Enhancement - DTC Support
-by Ginger Keys
*Only available with Windows Server 2016 or Windows Server 2012 R2 with update KB3090973
Distributed Transaction Coordinator (DTC) is necessary if your client application needs to perform transactions across multiple instances. DTC is part of the operating system, and ensures consistency when your database engine makes multi-server transactions. Applications can use DTC when they connect to the database engine, or can be started through TSQL by using the BEGIN DISTRIBUTED TRANSACTION command.
USE AdventureWorks2012;
GO
BEGIN DISTRIBUTED TRANSACTION;
-- your tsql statement here
DELETE FROM AdventureWorks2012.HumanResources.JobCandidate
WHERE JobCandidateID = 13;
GO
COMMIT TRANSACTION;
GO
Not only can your application perform transactions between multiple SQL Server instances, but also between other DTC compliant servers such as WebSphere or Oracle.
To implement this in AlwaysOn 2016, Availability Groups must be created with the CREATE AVAILABILITY GROUP command and the WITH DTC_SUPPORT = PER_DB clause.
___________________________________________________________________________
SQLRx has worked hard to minimize the impact of monitoring SQL Servers to the point that we are able to monitor high transaction systems with little impact (1% load) on the target system. Contact us today to help you monitor your SQL servers!
|
