Enabling distributed transaction support for domain-joined Amazon RDS for SQL Server instances

Amazon Relational Database Service (RDS) for SQL Server now supports distributed transactions using Microsoft Distributed Transaction Coordinator (MSDTC). With MSDTC, you can run distributed transactions involving RDS for SQL Server DB instances. This post goes over the most common ways to run distributed transactions when using Amazon RDS for SQL Server using AWS Directory Service for Microsoft Active Directory (AWS Managed Microsoft AD).

Solution overview

There are three key terms used throughout this post:

• Distributed transaction – A transaction that updates data on two or more networked computer systems. Distributed transactions extend the benefits of transactions to applications that must update distributed data.
• Transaction manager – The party that is responsible for managing and distributing the outcome of atomic transactions. A transaction manager is either a root transaction manager or a subordinate transaction manager for a specified transaction.
• Resource manager – The participant that is responsible for coordinating the state of a resource with the outcome of atomic transactions. For a specified transaction, a resource manager enlists with exactly one transaction manager to vote on that transaction outcome and to obtain the final outcome.

This solution includes the following steps:

1. Configure the parameter group.
2. Configure the option group.
3. Configure the Amazon RDS for SQL Server DB instance.
4. Configure distributed transactions.

Prerequisites

Before you get started, create your AWS Managed Microsoft AD directory. For instructions on joining instances if you have multiple accounts or VPCs, see Joining your Amazon RDS DB instances across accounts to a single shared domain.

Configuring the parameter group

You can only assign the MSDTC option to an Amazon RDS for SQL Server DB instance when the DB instance has configured the in-doubt transaction resolution to presume commit or presume abort. You can achieve this by changing the in-doubt xact resolution server parameter to 1 (presume commit) or 2 (presume abort). For more information about these values, see in-doubt xact resolution Server Configuration Option on the Microsoft documentation website.

You can create and configure your parameter group either on the AWS Management Console or the AWS Command Line Interface (AWS CLI).

AWS Management Console

To create a new parameter group on the console, complete the following steps:

1. On the Amazon RDS console, choose Parameter Groups.
2. Choose Create parameter group.
3. Create a new parameter group for the correct engine type and version, for example:
   • a) For Parameter group family, choose sqlserver-ee-14.0.
   • b) For Group name, enter param-group-for-msdtc.
   • c) For Description, enter Parameter group for MSDTC.
4. Choose Create.
   
5. Choose the newly created parameter group in the Parameter groups
6. For Parameter Group Actions, choose Edit.
7. Search for the in-doubt xact resolution parameter.
8. For Values, enter the desired parameter value.
9. Choose Save changes.
   

AWS CLI

To configure the parameter group via AWS CLI, complete the following steps:

1. Create a new parameter group with the following code:

   aws rds create-db-parameter-group
       --db-parameter-group-name param-group-for-msdtc
       --db-parameter-group-family sqlserver-ee-14.0
       --description "Parameter group for MSDTC"

2. Configure the parameter value with the following code:

   aws rds modify-db-parameter-group
       --db-parameter-group-name param-group-for-msdtc
       --parameters ParameterName='in-doubt xact resolution',ParameterValue=1,ApplyMethod=IMMEDIATE

Configuring the MSDTC option group

You now need to configure the MSDTC option and associate the option with a new or existing option group. For this use case, you create a new option group. You can use either the console or AWS CLI.

AWS Management Console

To configure the option group on the console, complete the following steps:

1. On the Amazon RDS console, choose Option Groups.
2. Choose Create group.
3. Create a new option group for the desired engine type and major version, for example:
   • a) For Name, enter option-group-for-msdtc.
   • b) For Description, enter Option group for MSDTC.
   • c) For Engine, choose sqlserver-ee.
   • d) For Major engine version, choose 14.00.
4. Choose Create.
   
5. Choose the MSDTC option, then choose Add option.
6. In Option settings, configure the main parameters for the MSDTC option:
   • a) For Port, enter the port for the MSDTC service or enter the default value (5000).
   • b) For Security groups, choose the VPC security groups for which you want to enable the MSDTC option.
   • c) For Authentication type, choose the authentication type for the MSDTC service. Because post is configuring the option for a domain-joined environment, choose MUTUAL.
   • d) For Transaction log size, enter your preferred size or enter the default value (4).
     
7. In the Additional configuration section, select Enable inbound connections and Enable outbound connections.
   
8. Under Scheduling, choose Immediately to apply changes immediately.
9. Choose Add option.

AWS CLI

To configure the option group via AWS CLI, complete the following steps:

1. Create a new option group with the following code:

   aws rds create-option-group
       --option-group-name option-group-for-msdtc
       --engine-name sqlserver-ee --major-engine-version 14.00
       --option-group-description "Option group for MSDTC"

2. Add the MSDTC option and configure its settings (you must change the security group identifier) with the following code:

   aws rds add-option-to-option-group 
       --options "OptionName=MSDTC,OptionSettings=[{Name=AUTHENTICATION,Value=MUTUAL},{Name=TRANSACTION_LOG_SIZE,Value=4}],Port=5000,VpcSecurityGroupMemberships=sg-abcd1234" \
       --option-group-name option-group-for-msdtc --apply-immediately

Configuring the DB instance

You can assign the newly created option group and parameter group to an existing RDS instance, or create a new RDS instance and specify both the option and parameter groups.

Creating a new instance

To create a new instance with support for distributed transactions, complete the following steps:

1. On the Amazon RDS console, choose Databases.
2. Choose Microsoft SQL Server and 00 as the major version.
   The MSDTC service is supported in all Amazon RDS for SQL Server editions and versions starting from version 11.00.5058 (SQL Server 2012 SP2).
3. In Settings, provide the following information:
   • a) Enter a valid instance name; for example, sqlserver-msdtc1.
   • b) For Credential settings, enter the master user name and password you want to use for connecting to your instance.
4. For DB Instance Size, choose the desired size of the instance; for example, m4.xlarge.
5. Leave the default values for Storage.
6. For Availability & durability, choose No for Multi-AZ deployment.
   MSDTC is supported for Single-AZ deployments and Always On Multi-AZ deployments; it isn’t supported for Mirroring Multi-AZ.
7. In the Connectivity section, provide the following:
   • a) For Virtual Private Cloud (VPC), choose the VPC with the security group you associated with the MSDTC option.
   • b) For Additional connectivity configuration, make sure that the expected subnet group and VPC security group match the security group chosen for the MSDTC option.
8. In the Microsoft SQL Server Windows Authentication section, choose Browse Directory and choose the AWS Managed Microsoft AD directory you want to use.
   
9. In Additional configuration, select the parameter group and the option group that you created.
   
10. Leave the rest of the default settings.
11. Choose Create database.

Modifying an existing instance

To add support for distributed transactions to an existing DB instance, complete the following steps:

1. On the Amazon RDS console, choose Databases.
2. Select the DB instance you want to modify, then choose Modify.
3. In Network & Security, make sure that the correct Subnet group and Security group are chosen.
4. In Database options, choose the parameter group and the option group that you created.
   
5. In Microsoft SQL Server Windows Authentication, make sure that the correct directory is chosen.
   
6. Leave the other settings unchanged.
7. Choose Continue.
8. Choose Apply Immediately.
9. Choose Modify DB Instance to immediately configure the DB instance for distributed transactions.

Configuring distributed transaction control

You can now run distributed transactions from the client application. In your application, a distributed transaction is managed in a similar way as a local transaction. At the end of the transaction, the application requests the transaction to be either committed or rolled back. The transaction manager has to manage a distributed commit differently to minimize the risk that a network failure results in some resource managers successfully committing while others roll back the transaction. You can achieve this by managing the commit process in two phases, which is known as a two-phase commit:

• Prepare phase – When the transaction manager receives a commit request, it sends a prepare command to all the resource managers involved in the transaction. Each resource manager does everything required to make the transaction durable, and all buffers holding log images for the transaction are flushed to disk. As each resource manager completes the prepare phase, it returns success or failure of the prepare to the transaction manager.
• Commit phase – If the transaction manager receives successful prepares from all the resource managers, it sends commit commands to each resource manager. The resource managers can then complete the commit. If all the resource managers report a successful commit, the transaction manager sends a success notification to the application. If any resource manager reported a failure to prepare, the transaction manager sends a rollback command to each resource manager and indicates the failure of the commit to the application.

In Amazon RDS for SQL Server, you run distributed transactions in the same way as distributed transactions run on premises. You can either run the transaction using SQL Server as the transaction manager using linked servers, or you can promote MSDTC running on the same host as the client application to the role of transaction manager.

Linked servers

In this configuration, the distributed transaction is managed by MSDTC running within one of the RDS instances. The client application connects to the Amazon RDS for SQL Server endpoint and leaves the distributed transaction management up to the MSDTC service running on the RDS instance. You can use the BEGIN DISTRIBUTED TRANSACTION T-SQL statement to control distributed transactions on an Amazon RDS for SQL Server instance. When the distributed transaction starts, any queries sent to any number of linked servers become part of the distributed transaction.

The following diagram shows the scenario in which MSDTC running on an RDS instance acts as a transaction manager.

For more information about creating linked servers between two RDS instances, see Implement Linked Servers with Amazon RDS for Microsoft SQL Server.

TransactionScope class

For this use case, the MSDTC service running on the client host is promoted to the role of transaction manager. The MSDTC service running on the client host has to communicate with all MSDTC services running on other hosts involved in distributed transaction. As in the previous scenario, the number of hosts involved in distributed transaction isn’t limited to two.

For more information about the TransactionScope class and source code examples, see Implementing an Implicit Transaction using Transaction Scope.

The following diagram shows the configuration in which MSDTC running on the same host as the client application acts as a transaction manager.

To promote your Windows client machine to transaction manager, complete the following steps:

1. Join your client machine into the same Active Directory domain as your RDS instance so that mutual authentication can work.If your client application is powered by Amazon Elastic Compute Cloud (Amazon EC2), see Join an EC2 Instance to Your AWS Managed Microsoft AD Directory.This step is necessary for mutual authentication and fully functional domain name resolution of all hosts involved in distributed transaction.
2. Configure the MSDTC service for network access:
   • a) Open Component Services (or in Server Manager, choose Tools, and choose Component Services).
   • b) Expand Component Services, Computers, My Computer, and Distributed Transaction Coordinator; choose Local DTC (right-click); and choose Properties.
   • c) On the Security tab, select the following:
     • Network DTC Access
     • Allow Inbound
     • Allow Outbound
     • Mutual Authentication Required
   • d) Choose OK to save your changes.
   • e) If prompted to restart the service, select Yes.
3. Make sure Windows Firewall is properly configured to allow network communication for MSDTC service:
   • a) Open Windows Firewall, and choose Inbound Rules.
   • b) Make sure the following firewall rules are enabled:
     • Distributed Transactions Coordinator (RPC)
     • Distributed Transactions Coordinator (RPC-EPMAP)
     • Distributed Transactions Coordinator (TCP-In)

Summary

With MSDTC support for Amazon RDS for SQL Server, you can run distributed transactions within Amazon RDS in the same way as a non-managed instance of SQL Server. This post explained how to control distributed transactions in an Active Directory environment. For more information about distributed transactions, see Support for Microsoft Distributed Transaction Coordinator in SQL Server.

Try out distributed transactions in SQL Server today! If you have any thoughts or questions, leave them in the comments.

Some definitions in this post are from the following sources:

• Configure distributed transactions for an Always On availability group
• [MS-DTCO]: MSDTC Connection Manager: OleTx Transaction Protocol

About the Author

Vaclav Lukas is a Software Development Engineer with Amazon Web Services