Support for Microsoft Distributed Transaction Coordinator in RDS for SQL Server
A distributed transaction is a database transaction in which two or more network hosts are involved. RDS for SQL Server supports distributed transactions among hosts, where a single host can be one of the following:
-
RDS for SQL Server DB instance
-
On-premises SQL Server host
-
Amazon EC2 host with SQL Server installed
-
Any other EC2 host or RDS DB instance with a database engine that supports distributed transactions
In RDS, starting with SQL Server 2012 (version 11.00.5058.0.v1 and later), all editions of RDS for SQL Server support distributed transactions.
The support is provided using Microsoft Distributed Transaction Coordinator (MSDTC). For in-depth information about MSDTC, see
Distributed Transaction Coordinator
Contents
Limitations
The following limitations apply to using MSDTC on RDS for SQL Server:
-
MSDTC isn't supported on instances using SQL Server Database Mirroring. For more information, see Transactions - availability groups and database mirroring
. -
The
in-doubt xact resolution
parameter must be set to 1 or 2. For more information, see Modifying the parameter for MSDTC. -
MSDTC requires all hosts participating in distributed transactions to be resolvable using their host names. RDS automatically maintains this functionality for domain-joined instances. However, for standalone instances make sure to configure the DNS server manually.
-
Java Database Connectivity (JDBC) XA transactions are supported for SQL Server 2017 version 14.00.3223.3 and higher, and SQL Server 2019.
-
Distributed transactions that depend on client dynamic link libraries (DLLs) on RDS instances aren't supported.
-
Using custom XA dynamic link libraries isn't supported.
Enabling MSDTC
Use the following process to enable MSDTC for your DB instance:
-
Create a new option group, or choose an existing option group.
-
Add the
MSDTC
option to the option group. -
Create a new parameter group, or choose an existing parameter group.
-
Modify the parameter group to set the
in-doubt xact resolution
parameter to 1 or 2. -
Associate the option group and parameter group with the DB instance.
Creating the option group for MSDTC
Use the AWS Management Console or the AWS CLI to create an option group that corresponds to the SQL Server engine and version of your DB instance.
Note
You can also use an existing option group if it's for the correct SQL Server engine and version.
The following procedure creates an option group for SQL Server Standard Edition 2016.
To create the option group
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
. -
In the navigation pane, choose Option groups.
-
Choose Create group.
-
In the Create option group pane, do the following:
-
For Name, enter a name for the option group that is unique within your AWS account, such as
msdtc-se-2016
. The name can contain only letters, digits, and hyphens. -
For Description, enter a brief description of the option group, such as
MSDTC option group for SQL Server SE 2016
. The description is used for display purposes. -
For Engine, choose sqlserver-se.
-
For Major engine version, choose 13.00.
-
-
Choose Create.
The following example creates an option group for SQL Server Standard Edition 2016.
To create the option group
-
Use one of the following commands.
For Linux, macOS, or Unix:
aws rds create-option-group \ --option-group-name
msdtc-se-2016
\ --engine-namesqlserver-se
\ --major-engine-version13.00
\ --option-group-description "MSDTC option group for SQL Server SE 2016
"For Windows:
aws rds create-option-group ^ --option-group-name
msdtc-se-2016
^ --engine-namesqlserver-se
^ --major-engine-version13.00
^ --option-group-description "MSDTC option group for SQL Server SE 2016
"
Adding the MSDTC option to the option group
Next, use the AWS Management Console or the AWS CLI to add the MSDTC
option to the option
group.
The following option settings are required:
-
Port – The port that you use to access MSDTC. Allowed values are 1150–49151 except for 1234, 1434, 3260, 3343, 3389, and 47001. The default value is 5000.
Make sure that the port you want to use is enabled in your firewall rules. Also, make sure as needed that this port is enabled in the inbound and outbound rules for the security group associated with your DB instance. For more information, see Can't connect to Amazon RDS DB instance.
-
Security groups – The VPC security group memberships for your RDS DB instance.
-
Authentication type – The authentication mode between hosts. The following authentication types are supported:
-
Mutual – The RDS instances are mutually authenticated to each other using integrated authentication. If this option is selected, all instances associated with this option group must be domain-joined.
-
None – No authentication is performed between hosts. We don't recommend using this mode in production environments.
-
-
Transaction log size – The size of the MSDTC transaction log. Allowed values are 4–1024 MB. The default size is 4 MB.
The following option settings are optional:
-
Enable inbound connections – Whether to allow inbound MSDTC connections to instances associated with this option group.
-
Enable outbound connections – Whether to allow outbound MSDTC connections from instances associated with this option group.
-
Enable XA – Whether to allow XA transactions. For more information on the XA protocol, see XA specification
. -
Enable SNA LU – Whether to allow the SNA LU protocol to be used for distributed transactions. For more information on SNA LU protocol support, see Managing IBM CICS LU 6.2 transactions
in the Microsoft documentation.
To add the MSDTC option
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
. -
In the navigation pane, choose Option groups.
-
Choose the option group that you just created.
-
Choose Add option.
-
Under Option details, choose MSDTC for Option name.
-
Under Option settings:
-
For Port, enter the port number for accessing MSDTC. The default is 5000.
-
For Security groups, choose the VPC security group to associate with the option.
-
For Authentication type, choose Mutual or None.
-
For Transaction log size, enter a value from 4–1024. The default is 4.
-
-
Under Additional configuration, do the following:
-
For Connections, as needed choose Enable inbound connections and Enable outbound connections.
-
For Allowed protocols, as needed choose Enable XA and Enable SNA LU.
-
-
Under Scheduling, choose whether to add the option immediately or at the next maintenance window.
-
Choose Add option.
To add this option, no reboot is required.
To add the MSDTC option
-
Create a JSON file, for example
msdtc-option.json
, with the following required parameters.{ "OptionGroupName":"
msdtc-se-2016
", "OptionsToInclude": [ { "OptionName":"MSDTC", "Port":5000
, "VpcSecurityGroupMemberships":["sg-0abcdef123
"], "OptionSettings":[{"Name":"AUTHENTICATION","Value":"MUTUAL
"},{"Name":"TRANSACTION_LOG_SIZE","Value":"4
"}] }], "ApplyImmediately": true } -
Add the
MSDTC
option to the option group.For Linux, macOS, or Unix:
aws rds add-option-to-option-group \ --cli-input-json file://
msdtc-option.json
\ --apply-immediatelyFor Windows:
aws rds add-option-to-option-group ^ --cli-input-json file://
msdtc-option.json
^ --apply-immediatelyNo reboot is required.
Creating the parameter group for MSDTC
Create or modify a parameter group for the in-doubt xact resolution
parameter
that corresponds to the SQL Server edition and version of your DB
instance.
The following example creates a parameter group for SQL Server Standard Edition 2016.
To create the parameter group
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
. -
In the navigation pane, choose Parameter groups.
-
Choose Create parameter group.
-
In the Create parameter group pane, do the following:
-
For Parameter group family, choose sqlserver-se-13.0.
-
For Group name, enter an identifier for the parameter group, such as
msdtc-sqlserver-se-13
. -
For Description, enter
in-doubt xact resolution
.
-
-
Choose Create.
The following example creates a parameter group for SQL Server Standard Edition 2016.
To create the parameter group
-
Use one of the following commands.
For Linux, macOS, or Unix:
aws rds create-db-parameter-group \ --db-parameter-group-name
msdtc-sqlserver-se-13
\ --db-parameter-group-family "sqlserver-se-13.0
" \ --description "in-doubt xact resolution
"For Windows:
aws rds create-db-parameter-group ^ --db-parameter-group-name
msdtc-sqlserver-se-13
^ --db-parameter-group-family "sqlserver-se-13.0
" ^ --description "in-doubt xact resolution
"
Modifying the parameter for MSDTC
Modify the in-doubt xact resolution
parameter in the parameter group that
corresponds to the SQL Server edition and version of your DB instance.
For MSDTC, set the in-doubt xact resolution
parameter to one of the
following:
-
1
–Presume commit
. Any MSDTC in-doubt transactions are presumed to have committed. -
2
–Presume abort
. Any MSDTC in-doubt transactions are presumed to have stopped.
For more information, see in-doubt xact resolution server configuration option
The following example modifies the parameter group that you created for SQL Server Standard Edition 2016.
To modify the parameter group
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
. -
In the navigation pane, choose Parameter groups.
-
Choose the parameter group, such as msdtc-sqlserver-se-13.
-
Under Parameters, filter the parameter list for
xact
. -
Choose in-doubt xact resolution.
-
Choose Edit parameters.
-
Enter
1
or2
. -
Choose Save changes.
The following example modifies the parameter group that you created for SQL Server Standard Edition 2016.
To modify the parameter group
-
Use one of the following commands.
For Linux, macOS, or Unix:
aws rds modify-db-parameter-group \ --db-parameter-group-name
msdtc-sqlserver-se-13
\ --parameters "ParameterName='in-doubt xact resolution',ParameterValue=1
,ApplyMethod=immediate"For Windows:
aws rds modify-db-parameter-group ^ --db-parameter-group-name
msdtc-sqlserver-se-13
^ --parameters "ParameterName='in-doubt xact resolution',ParameterValue=1
,ApplyMethod=immediate"
Associating the option group and parameter group with the DB instance
You can use the AWS Management Console or the AWS CLI to associate the MSDTC option group and parameter group with the DB instance.
You can associate the MSDTC option group and parameter group with a new or existing DB instance.
-
For a new DB instance, associate them when you launch the instance. For more information, see Creating an Amazon RDS DB instance.
-
For an existing DB instance, associate them by modifying the instance. For more information, see Modifying an Amazon RDS DB instance.
Note
If you use an domain-joined existing DB instance, it must already have an Active Directory domain and AWS Identity and Access Management (IAM) role associated with it. If you create a new domain-joined instance, specify an existing Active Directory domain and IAM role. For more information, see Working with AWS Managed Active Directory with RDS for SQL Server.
You can associate the MSDTC option group and parameter group with a new or existing DB instance.
Note
If you use an existing domain-joined DB instance, it must already have an Active Directory domain and IAM role associated with it. If you create a new domain-joined instance, specify an existing Active Directory domain and IAM role. For more information, see Working with AWS Managed Active Directory with RDS for SQL Server.
To create a DB instance with the MSDTC option group and parameter group
-
Specify the same DB engine type and major version as you used when creating the option group.
For Linux, macOS, or Unix:
aws rds create-db-instance \ --db-instance-identifier
mydbinstance
\ --db-instance-classdb.m5.2xlarge
\ --enginesqlserver-se
\ --engine-version13.00.5426.0.v1
\ --allocated-storage100
\ --manage-master-user-password \ --master-usernameadmin
\ --storage-typegp2
\ --license-modelli
\ --domain-iam-role-namemy-directory-iam-role
\ --domainmy-domain-id
\ --option-group-namemsdtc-se-2016
\ --db-parameter-group-namemsdtc-sqlserver-se-13
For Windows:
aws rds create-db-instance ^ --db-instance-identifier
mydbinstance
^ --db-instance-classdb.m5.2xlarge
^ --enginesqlserver-se
^ --engine-version13.00.5426.0.v1
^ --allocated-storage100
^ --manage-master-user-password ^ --master-usernameadmin
^ --storage-typegp2
^ --license-modelli
^ --domain-iam-role-namemy-directory-iam-role
^ --domainmy-domain-id
^ --option-group-namemsdtc-se-2016
^ --db-parameter-group-namemsdtc-sqlserver-se-13
To modify a DB instance and associate the MSDTC option group and parameter group
-
Use one of the following commands.
For Linux, macOS, or Unix:
aws rds modify-db-instance \ --db-instance-identifier
mydbinstance
\ --option-group-namemsdtc-se-2016
\ --db-parameter-group-namemsdtc-sqlserver-se-13
\ --apply-immediatelyFor Windows:
aws rds modify-db-instance ^ --db-instance-identifier
mydbinstance
^ --option-group-namemsdtc-se-2016
^ --db-parameter-group-namemsdtc-sqlserver-se-13
^ --apply-immediately
Using distributed transactions
In Amazon RDS for SQL Server, you run distributed transactions in the same way as distributed transactions running on-premises:
-
Using .NET framework
System.Transactions
promotable transactions, which optimizes distributed transactions by deferring their creation until they're needed.In this case, promotion is automatic and doesn't require you to make any intervention. If there's only one resource manager within the transaction, no promotion is performed. For more information about implicit transaction scopes, see Implementing an implicit transaction using transaction scope
in the Microsoft documentation. Promotable transactions are supported with these .NET implementations:
-
Starting with ADO.NET 2.0,
System.Data.SqlClient
supports promotable transactions with SQL Server. For more information, see System.Transactions integration with SQL Serverin the Microsoft documentation. -
ODP.NET supports
System.Transactions
. A local transaction is created for the first connection opened in theTransactionsScope
scope to Oracle Database 11g release 1 (version 11.1) and later. When a second connection is opened, this transaction is automatically promoted to a distributed transaction. For more information about distributed transaction support in ODP.NET, see Microsoft Distributed Transaction Coordinator integrationin the Microsoft documentation.
-
-
Using the
BEGIN DISTRIBUTED TRANSACTION
statement. For more information, see BEGIN DISTRIBUTED TRANSACTION (Transact-SQL)in the Microsoft documentation.
Using XA transactions
Starting from RDS for SQL Server 2017 version14.00.3223.3, you can control distributed transactions using JDBC. When you set the
Enable XA
option setting to true
in the MSDTC
option, RDS automatically enables JDBC
transactions and grants the SqlJDBCXAUser
role to the guest
user. This allows executing distributed
transactions through JDBC. For more information, including a code example, see Understanding XA transactions
Using transaction tracing
RDS supports controlling MSDTC transaction traces and downloading them from the RDS DB instance for troubleshooting. You can control transaction tracing sessions by running the following RDS stored procedure.
exec msdb.dbo.rds_msdtc_transaction_tracing '
trace_action
', [@traceall='0|1
'], [@traceaborted='0|1
'], [@tracelong='0|1
'];
The following parameter is required:
-
trace_action
– The tracing action. It can beSTART
,STOP
, orSTATUS
.
The following parameters are optional:
-
@traceall
– Set to 1 to trace all distributed transactions. The default is 0. -
@traceaborted
– Set to 1 to trace canceled distributed transactions. The default is 0. -
@tracelong
– Set to 1 to trace long-running distributed transactions. The default is 0.
Example of START tracing action
To start a new transaction tracing session, run the following example statement.
exec msdb.dbo.rds_msdtc_transaction_tracing 'START', @traceall='
0
', @traceaborted='1
', @tracelong='1
';
Note
Only one transaction tracing session can be active at one time. If a new tracing session
START
command is issued while a tracing session is active,
an error is returned and the active tracing session remains
unchanged.
Example of STOP tracing action
To stop a transaction tracing session, run the following statement.
exec msdb.dbo.rds_msdtc_transaction_tracing 'STOP'
This statement stops the active transaction tracing session and saves the transaction trace data into the log directory on the RDS DB instance. The first row of the output contains the overall result, and the following lines indicate details of the operation.
The following is an example of a successful tracing session stop.
OK: Trace session has been successfully stopped. Setting log file to: D:\rdsdbdata\MSDTC\Trace\dtctrace.log Examining D:\rdsdbdata\MSDTC\Trace\msdtctr.mof for message formats, 8 found. Searching for TMF files on path: (null) Logfile D:\rdsdbdata\MSDTC\Trace\dtctrace.log: OS version 10.0.14393 (Currently running on 6.2.9200) Start Time <timestamp> End Time <timestamp> Timezone is @tzres.dll,-932 (Bias is 0mins) BufferSize 16384 B Maximum File Size 10 MB Buffers Written Not set (Logger may not have been stopped). Logger Mode Settings (11000002) ( circular paged ProcessorCount 1 Processing completed Buffers: 1, Events: 3, EventsLost: 0 :: Format Errors: 0, Unknowns: 3 Event traces dumped to d:\rdsdbdata\Log\msdtc_<
timestamp
>.log
You can use the detailed information to query the name of the generated log file. For more information about downloading log files from the RDS DB instance, see Monitoring Amazon RDS log files.
The trace session logs remain on the instance for 35 days. Any older trace session logs are automatically deleted.
Example of STATUS tracing action
To trace the status of a transaction tracing session, run the following statement.
exec msdb.dbo.rds_msdtc_transaction_tracing 'STATUS'
This statement outputs the following as separate rows of the result set.
OK SessionStatus: <
Started|Stopped
> TraceAll: <True|False
> TraceAborted: <True|False
> TraceLongLived: <True|False
>
The first line indicates the overall result of the operation: OK
or
ERROR
with details, if applicable. The subsequent lines
indicate details about the tracing session status:
-
SessionStatus
can be one of the following:-
Started
if a tracing session is running. -
Stopped
if no tracing session is running.
-
-
The tracing session flags can be
True
orFalse
depending on how they were set in theSTART
command.
Modifying the MSDTC option
After you enable the MSDTC
option, you can modify its settings. For
information about how to modify option settings, see Modifying an option setting.
Note
Some changes to MSDTC option settings require the MSDTC service to be restarted. This requirement can affect running distributed transactions.
Disabling MSDTC
To disable MSDTC, remove the MSDTC
option from its option group.
To remove the MSDTC option from its option group
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
. -
In the navigation pane, choose Option groups.
-
Choose the option group with the
MSDTC
option (msdtc-se-2016
in the previous examples). -
Choose Delete option.
-
Under Deletion options, choose MSDTC for Options to delete.
-
Under Apply immediately, choose Yes to delete the option immediately, or No to delete it at the next maintenance window.
-
Choose Delete.
To remove the MSDTC option from its option group
-
Use one of the following commands.
For Linux, macOS, or Unix:
aws rds remove-option-from-option-group \ --option-group-name
msdtc-se-2016
\ --options MSDTC \ --apply-immediatelyFor Windows:
aws rds remove-option-from-option-group ^ --option-group-name
msdtc-se-2016
^ --options MSDTC ^ --apply-immediately
Troubleshooting MSDTC for RDS for SQL Server
In some cases, you might have trouble establishing a connection between MSDTC running on a client computer and the MSDTC service running on an RDS for SQL Server DB instance. If so, make sure of the following:
-
The inbound rules for the security group associated with the DB instance are configured correctly. For more information, see Can't connect to Amazon RDS DB instance.
-
Your client computer is configured correctly.
-
The MSDTC firewall rules on your client computer are enabled.
To configure the client computer
-
Open Component Services.
Or, in Server Manager, choose Tools, and then choose Component Services.
-
Expand Component Services, expand Computers, expand My Computer, and then expand Distributed Transaction Coordinator.
-
Open the context (right-click) menu for Local DTC and choose Properties.
-
Choose the Security tab.
-
Choose all of the following:
-
Network DTC Access
-
Allow Inbound
-
Allow Outbound
-
-
Make sure that the correct authentication mode is chosen:
-
Mutual Authentication Required – The client machine is joined to the same domain as other nodes participating in distributed transaction, or there is a trust relationship configured between domains.
-
No Authentication Required – All other cases.
-
-
Choose OK to save your changes.
-
If prompted to restart the service, choose Yes.
To enable MSDTC firewall rules
-
Open Windows Firewall, then choose Advanced settings.
Or, in Server Manager, choose Tools, and then choose Windows Firewall with Advanced Security.
Note
Depending on your operating system, Windows Firewall might be called Windows Defender Firewall.
-
Choose Inbound Rules in the left pane.
-
Enable the following firewall rules, if they are not already enabled:
-
Distributed Transaction Coordinator (RPC)
-
Distributed Transaction Coordinator (RPC)-EPMAP
-
Distributed Transaction Coordinator (TCP-In)
-
-
Close Windows Firewall.