Troubleshooting Microsoft Distributed Transaction Coordinator (MSDTC / DTC)

Microsoft Distributed Transaction Coordinator (abbreviated to MSDTC, or DTC) is usually very quick and simple to set up. However, it isn't the most intuitive thing to troubleshoot if you run into issues. This page outlines various tools that can be used to assist in troubleshooting MSDTC, and how to use them.

What is MSDTC?

MSDTC is the software that allows "distributed transactions" to run across multiple servers. A distributed transaction is something that updates data on two or more servers. MSDTC ensures that updates on all servers succeeed, or if there is a problem with ANY of the updates, then all of the updates are rolled back.

For example, if you wanted to move data from Server A to Server B, you could do this in a distributed transaction. The data would be written to Server B, and deleted from Server A simeltaneously. If there was a problem with the write to Server B, or the deletion from Server A, the transaction would fail, rolling back both the write and the deletion, leaving both servers exactly as they were. In this example, this ensures there is no loss of data (if only the deletion of data from Server A succeeded), and no duplication of data (if only the write to Server B succeeded).

DTC uses Port 135, and the DCOM port range, which is 1024 - 65535. As this is such a large range of ports to open, the DCOM port range can be limited in the registry if required

How to Enable and Configure MSDTC

1. Navigate to Control Panel > Administrative Tools > Component Services
2. Within Component Services, expand Component Services > Computers > My Computer > Distributed Transaction Coordinator. Here you'll see Local DTC, or, if you're on a cluster, you'll also see Clustered DTC
3. Right click the Local DTC (or Clustered DTC if required), and click Properties
4. Navigate to the Security tab
5. Select the following options as a default:
   
   Network DTC Access
   Allow Inbound
   Allow Outbound
   No Authentication Required (set by default)
   Enable SNA LU 6.2 Transactions (set by default)
   Account NT Authority\NetworkService (set by default)
   
   Note, Enable XA Transactions may also be required for SQL Server

It really is that simple enabling DTC, and typically, this is all that needs to be done to allow two servers to run distributed transactions between them.

There is more information about each DTC setting here: https://technet.microsoft.com/en-us/library/cc753620(v=ws.10).aspx

Test DTC is Working Between Two SQL Servers

It is relatively simple to test whether or not DTC is working between two SQL servers.

1. On Server A, set up a linked server to Server B. On Server B, create a test table. Ensure the linked server account on Server A has permission to write to the test table on Server B
2. On Server A, run the following SQL, replacing the table name etc with a test table:

set xact_abort on

begin distributed transaction

insert into ServerB.DBName.dbo.testTable (ID) values (1)

commit transaction

If it has committed successfully, SQL Server Management Studio will return (1 row(s) affected). If it fails, there may be DTC connectivity issues. The following tools may prove helpful troubleshooting.

DTCPing

DTCPing is a tool that allows testing DTC connectivity between two servers.

How to Use DTCPing

DTCPing must be installed on both servers you wish to test connectivity issues between. By default, the DTCPing installation installs to the C:\Windows\Temp folder (or subfolder buried in the temp directory somewhere). It's usually helpful to move the files to C:\DTCPing for ease of use.

1. Once DTCPing has been installed on both servers, open the application on both servers
2. On SOURCE server, enter DESTINATION server NETBIOS name into the Remote Server Name field
3. On DESTINATION server, enter SOURCE server NETBIOS name into the Remote Server Name field
4. Click PING on SOURCE server
5. Click PING on DESTINATION server

If a successful test has happened, the output will read similar to the following:

++++++++++++++++++++++++++++++++++++++++++++++
DTCping 1.9 Report for SOURCE
++++++++++++++++++++++++++++++++++++++++++++++
RPC server is ready
++++++++++++Validating Remote Computer Name++++++++++++
11-21, 04:31:01.455–>Start DTC connection test
Name Resolution:
DESTINATION–>65.52.22.254–>DESTINATION.contoso.com11-21, 04:31:01.470–>Start RPC test (SOURCE–>DESTINATION)
RPC test is successful
Partner’s CID:084B708C-F0C5-4E65-95F2-8E2DEF73FFF3
++++++++++++RPC test completed+++++++++++++++
++++++++++++Start DTC Binding Test +++++++++++++
Trying Bind to DESTINATION
11-21, 04:31:01.830–>SOURCE Initiating DTC Binding Test….
Test Guid:B5544E05-D64B-40AC-B283-71947914DED3
Received reverse bind call from DESTINATION
Network Name: SOURCE
Source Port: 1116
Hosting Machine:SOURCE
Binding success: SOURCE–>DESTINATION
++++++++++++DTC Binding Test END+++++++++++++

Should any errors be reported, the following link describes common errors and their resolutions: https://blogs.msdn.microsoft.com/puneetgupta/2008/11/12/troubleshooting-msdtc-issues-with-the-dtcping-tool/

DTCPing outputs logs to the folder the DTCPing.exe has been installed to, and also includes various .txt files explaining the DTCPing log format.

DTCTester

Next up is DTCTester, which only tests DTC transactions one way, from a source server to a destination server. It is command line only.

How to Use DTCTester

1. Install DTCTester on the server you want to test distributed transactions from. Install to C:\DTCTester or somewhere helpful!
2. Create an ODBC connection on the source server, to the destination server
3. Navigate to Administrative Tools > Data Sources (ODBC)
4. On the User DSN tab, click Add to add a new ODBC connection
5. Select SQL Server
6. Enter details for the destination SQL Server you wish to test DTC for
7. Run cmd.exe as an administrator
8. Navigate to the folder where you installed dtctester.exe (type cd c:\DTCTester)
9. Next, run dtctester <ODBC Name> <Username> <Password> where
   <ODBC Name> is the name of the ODBC connection you set up in step 1
   <Username> is the name of a sysadmin user
   <Password> is the corresponding sysadmin password

DTCTester will then attempt to create, and write to, a temporary table on the destination server using a distributed transaction. A successful output looks as follows:

Command Line: dtctester test sa
Executed: dtctester
DSN: test
User Name: sa
Password is assumed to be NULL.
Connecting to the database
tablename= #dtc7488
Creating Temp Table for Testing: #dtc7488
Warning: No Columns in Result Set From Executing: 'create table #dtc7488 (ival int)'
Initializing DTC
Beginning DTC Transaction
Enlisting Connection in Transaction
Executing SQL Statement in DTC Transaction
Inserting into Temp...insert into #dtc7488 values (1)
Warning: No Columns in Result Set From Executing: 'insert into #dtc7488 values (1) '
Verifying Insert into Temp...select * from #dtc7488 (should be 1): 1
Press enter to commit transaction.

Committing DTC Transaction
Releasing DTC Interface Pointers
Successfully Released pTransaction Pointer.
Disconnecting from Database and Cleaning up Handles

More information on DTCTester here, including typical errors: https://support.microsoft.com/en-gb/kb/293799

Tracing DTC Output

It is possible to stop and start logging of DTC output to help understand what is actually happening when enlisting distributed transactions.

Setting up DTC Tracing

DTC tracing can be configured from the Tracing tab of the DTC Properties (see How to Read DTC Trace Files below)

1. Navigate to Control Panel > Administrative Tools > Component Services
2. Within Component Services, expand Component Services > Computers > My Computer > Distributed Transaction Coordinator. Here you'll see Local DTC, or, if you're on a cluster, you'll also see Clustered DTC
3. Right click the Local DTC (or Clustered DTC if required), and click Properties
4. Navigate to the Logging tab to see the Location value where DTC trace data will be saved to (by default, it is C:\Windows\system32\Msdtc\Trace)
5. Navigate to the Tracing tab. Ensure Trace Output and Trace Transactions is selected. Also ensure types of transactions you wish to trace are selected
6. Click Stop Session, then New Session to begin a new DTC trace. You can now test your DTC connectivity. Once finished, click Stop Session to end tracing of DTC
7. Navigate to the folder selected in step 4 to find the trace files (see How to Read DTC Trace Files below)

How to Read DTC Trace Files

Frustratingly, DTC trace files are written in binary, and are indeciperable to the average human eyeball. Microsoft do not make it easy to read these files. The software required to open the files is not installed with Windows. Again, this is command line only.

1. Copy tracefmt.exe to the directory that DTC trace files are output to (by default it is C:\Windows\system32\Msdtc\Trace). Note, you cannot paste this path into explorer to get to this folder! But you can navigate to it manually
2. Run cmd.exe and navigate to the trace output folder (by default it is C:\Windows\system32\Msdtc\Trace) by running the command cd C:\Windows\system32\Msdtc\Trace
3. Next, run the command msdtcvtr.bat -tracelog <tracefile name> which will output the trace file contents to trace.csv in the same folder

There is more info on reading trace files, and where to get tracefmt.exe here: http://stackoverflow.com/questions/1329583/where-do-i-get-tracefmt-exe-and-how-do-i-read-my-msdtc-traces

Creating Unique 10 Character Strings

A nice way to make a unique 10 character string is to generate a GUID, strip out the hypehens, and take only the left / right 10 characters as follows:

select right(replace(CONVERT(varchar(255), NEWID()), '-',''),10)

 This produces no duplicates over 1 million rows, when tested as follows:

create table #tmpID(ID varchar(10))

declare @i int

set @i = 1

while @i <1000000

begin

       insert into #tmpID

       select right(replace(CONVERT(varchar(255), NEWID()), '-',''),10)

       set @i = @i+1

end

select ID, count(*) [No of IDs]

from #tmpID

group by ID

having count(*) >1

Profiler - Querying Trace Files From SSMS

If you have trace data that has been saved to a file, it is possible to query this data via SQL Server Management Studio, and run SQL commands against the file. Example SQL:

SELECT *

FROM ::fn_trace_gettable('c:\TraceFile.trc', default)

This makes it much easier to work with the trace file comapred to opening the file within SQL Server Profiler. It means it is also possible to sort, filter, apply aggregations (sums, counts, etc) against the data. Of course, you could always save the trace data to a table from within SQL Server Profiler, and then perform these sorts of SQL queries against that table, but using fn_trace_gettable, you don't need to.

SQL Server Start Up Switches

Some useful SQL Server start up switches:

Switch	Action
-d	Default startup option – specifies the fully qualified path for the master database file. If this is not provided, the current registry value will be used
-e	Default startup option – specifies the fully qualified path for the master database error log file. If this is not provided, the current registry value will be used
-l	Default startup option – specifies the fully qualified path for the master database log file If this is not provided, the current registry value will be used
-m	Start SQL Server in single user mode. Allows any member of the Local Administrator group to connect as sysadmin onto the SQL instance. CHECKPOINT process is not started
-m”client app name”	Specify which application can take the single user connection (e.g. –m”SQL Server Management Studio – Query”). Use when an unknown application keeps grabbing the single user connection when SQL Server starts
-f	Starts SQL Server in minimal configuration mode (this is also single user mode as well). Useful if a configuration setting is stopping SQL Server from starting
-n	Doesn’t use Windows Application Log to record events. Use –e in conjunction with this to ensure events are logged to SQL error log
-s	Start a named instance. Without this, a default instance is started
-Ttrace#	Start SQL with a specified trace flag (note the uppercase T)
-x	Disables several monitoring features
-c	Shortens the time taken to start SQL Server from command line

The following T-SQL code will tell you which start up switches have currently been applied to SQL Server:

SELECT

    DSR.registry_key,

    DSR.value_name,

    DSR.value_data

FROM sys.dm_server_registry AS DSR

WHERE

    DSR.registry_key LIKE N'%MSSQLServer\Parameters';

...and querying the sys.dm_server_registry dynamic view in it's entirety will give you details about the registry entries for SQL Server.

Resources:
Info on trace flags: http://www.sqlservercentral.com/articles/trace+flags/70131/

What's new in SQL Server 2014

Some information on the new features / enhancements  introduced in SQL Server 2014:

In memory OLTP engine:
Allows tables and stored procedures to move to memory to drastically reduce IO and improve performance many times over. Microsoft provide the Analysis, Migrate and Report (AMR) tool that identifies objects that would benefit from being moved into memory.

AlwaysOn Availability Groups:
SQL Server now supports up to 8 secondary replicas (as opposed to 4 in SQL 2012), and secondary replicas are available for read only workloads even when the primary replica is unavailable. Now also supports using Azure for asynchronous replicas for high availability / disaster recovery scenarios.

http://sqlmag.com/sql-server-2014/sql-server-2014-important-new-features

Permissions - Server & Database Roles

When assigning permissions, it is important to always grant the least privileged role - minimum permission to perform the function that is required.

Server Roles:
More info from MSDN

Fixed server-level role	Description
sysadmin	Members of this role can perform any action on the SQL Server instance. Initial membership of this role happens at installation
serveradmin	Members can perform instance wide administration tasks, including shutting down the instance
securityadmin	Members can manage instance level permissions. Permissions for this role are equivalent to sysadmin - as members of this role can elevate their own permissions to sysadmin
processadmin	Members can view and terminate processes on the database engine
setupadmin	Members can add / remove linked servers to the database instance, and execute some system stored procedures
bulkadmin	Members can run the BULK INSERT statement
diskadmin	Members can administer database file configuration
dbcreator	Ability to create databases (database will be owned by the creator)
public	Every login is automatically a member of the public role; you cannot change membership for public. You can change the permissions for public however. If a user has no permissions granted, they will revert to the permissions assigned to public

Database Roles:

More info from MSDN

Database-level role name	Description
db_owner	Has permissions to do anything within the database
db_securityadmin	Members can administer security of roles (note: can elevate their own permissions to db_owner)
db_accessadmin	Can administer who has access to database for Windows Logins, Windows Groups and SQL Server Logins
db_backupoperator	Members have ability to backup the database. Cannot restore the database - this is reserved for dbcreator / sysadmin fixed server roles
db_ddladmin	Can perform any Data Definition Language (DDL) operations (those that create / modify structure of database), e.g. CREATE, ALTER, DROP, TRUNCATE
db_datawriter	Allows users to add / delete / update data on all user tables
db_datareader	Allows users to read data from all user tables
db_denydatawriter	Members cannot add / delete / update any data in any user tables
db_denydatareader	Members cannot read data from any user tables

:

High Availability - AlwaysOn Availability Groups

Overview:
AlwaysOn Availability Groups can be thought of as a hybrid of the best of clustering, and the best of mirroring. It must be configured on a Windows Server Failover Cluster, but rather than installing SQL Server as a clustered installation with shared storage for the databases, a separate installation of SQL Server is installed on each node of the Windows Cluster, using the local disks of the cluster node (i.e. the OS is clustered, SQL Server isn't). Like clustering, the Availability Groups have a shared IP / name that everyone can point to. Unlike clustering, databases are mirrored across all servers in the cluster. There can be one primary, and up to four replicas. Updates can be asynchronous or synchronous. Requires Full recovery mode for databases.

Secondaries / Read Only Replicas:
Secondaries can be used as read only databases for backups / reporting / DBCC checks etc, although these functions lend themselves more to secondary replicas in an asynchronous configuration, as synchronous secondaries are more useful for failover.

Connecting to Secondaries:
You can connect directly to a secondary replica, or indirectly by using the "ApplicationIntent" (either ReadWrite or ReadOnly) within the connection string for SQL Server connections, with Read Only routing configured on the listener service for the main shared instance name.

Failover Types:
From MSDN:

• Synchronous-commit replicas support two settings—automatic or manual. The "automatic" setting supports both automatic failover and manual failover. To prevent data loss, automatic failover and planned failover require that the failover target be a synchronous-commit secondary replica with a healthy synchronization state (this indicates that every secondary database on the failover target is synchronized with its corresponding primary database). Whenever a secondary replica does not meet both of these conditions, it supports only forced failover. Note that forced failover is also supported a replicas whose role is in the RESOLVING state.
• Asynchronous-commit replicas support only the manual failover mode. Moreover, because they are never synchronized, they support only forced failover.

Pros:
Good for high availability, and reporting via usable secondary copies of the data.

Cons:
You cannot add system databases to an Availability Group. To that end, logins, SQL Jobs etc are not available on secondary replicas automatically - these must be manually set up / kept in sync.
Expensive, as you must license each node individually.
Doubles your data (or more) - as you have multiple copies of the databases many times.
Distributed transactions are not guaranteed in the event of failover.
All servers in the availability group must be in the same domain

Resources:
Great post here around HA options in SQL Server: http://dba.stackexchange.com/questions/53815/clustering-vs-transactional-replication-vs-availability-groups

Moving a SQL Server to a Different Domain

Things to consider:

http://dba.stackexchange.com/questions/37583/moving-sql-server-to-different-domain

SP_WHO2 into a table to query

The following SQL will return the results of a SP_WHO2 query into a temporarty #SP_WHO2 table to allow querying of the table:

CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),

      Login  VARCHAR(255),HostName  VARCHAR(255),

      BlkBy  VARCHAR(255),DBName  VARCHAR(255),

      Command VARCHAR(255),CPUTime INT,

      DiskIO INT,LastBatch VARCHAR(255),

      ProgramName VARCHAR(255),SPID2 INT,

      REQUESTID INT)

INSERT INTO #sp_who2 EXEC sp_who2

SELECT      *

FROM        #sp_who2

-- Add any filtering of the results here :

WHERE       DBName <> 'master'

-- Add any sorting of the results here :

ORDER BY    DBName ASC

DROP TABLE #sp_who2

Estimate time remaining for backup / restores

Use the following SQL to estimate time remaining for active backup / restore jobs within SQL Server:

select    session_id,
    start_time,
    status,
    command,
    percent_complete,
    estimated_completion_time,
    estimated_completion_time / 60 / 1000 as estimate_completion_minutes,    dateadd(n,(estimated_completion_time / 60 / 1000),getdate()) as estimated_completion_timefrom    sys.dm_exec_requests
where    command = 'BACKUP DATABASE'
    or command = 'RESTORE DATABASE'