Differences in Escalation to Distributed Transaction Coordinator starting in SQL Server 2008

There’s a difference in when connections are escalated to Distributed Transaction Coordinator (DTC) starting in SQL Server 2008.

Microsoft’s Distributed Transaction Coordinator (DTC) is a service that coordinates transactions that span multiple connections to databases.  When multiple connections are made within a Transaction Scope, SQL Server will escalate to DTC.  Past versions of SQL have different conditions about when this escalation happens.  To demonstrate this, I have a SQL Server 2005 instance and a SQL Server 2008 instance running on my local machine against which I’m going to execute the same code.

The difference depends on whether you’re opening multiple connections at once (nested connections) or opening up a new connection and closing it every time you hit the database.  The former (nested connections) will escalate to DTC in SQL Server 2005 and 2008.  The latter (one connection at a time) will escalate to DTC in 2005, but WILL NOT in 2008.

Let’s demonstrate.

First, we’ll need to turn off DTC.  That way we can easily tell when we’ve escalated to DTC as our code will raise an error saying “MSDTC on server ‘SERVERNAME’ is unavailable.” 

To turn it off:

1. On the Start menu, click Control Panel. In Control Panel, click Administrative Tools. In Administrative Tools, click Services.
2. In the details pane, click “Distributed Transaction Coordinator“ in the list of services.
3. On the Action menu, click Stop. 

I’ve made this quick Windows Form to execute 3 different snippets of code against a SQL Server of my choosing.  We’ll see which snippets succeed and which give the afore-mentioned error to indicate that they are escalating to DTC.

 

The first button. “1 Connection”

Code:

        private void btnOneConnection_Click(object sender, EventArgs e)
        {
            string connectionString = @"Data Source=" + tbServer.Text
                + @";Initial Catalog=master;Integrated Security=True; timeout=0";

            using (TransactionScope transactionScope = new TransactionScope())
            {
                SqlConnection connectionOne = new SqlConnection(connectionString);

                try
                {
                    //One connection
                    connectionOne.Open();
                    connectionOne.Close();

                    MessageBox.Show("Success");
                }
                catch (Exception ex)
                {
                    MessageBox.Show("ERROR: " + ex.Message);
                }
                finally
                {
                    connectionOne.Dispose();
                }
            }

        }

Within one Transaction Scope, it opens one SQL connection.  The result:
Against SQL Server 2005 Instance:            Success
Against SQL Server 2008 Instance:            Success

 

The second button.  “2 Connections, 1 at a time”

Code:

        private void btnTwoConnectionsOneAtTime_Click(object sender, EventArgs e)
        {
            string connectionString = @"Data Source=" + tbServer.Text
                + @";Initial Catalog=master;Integrated Security=True; timeout=0";

            using (TransactionScope transactionScope = new TransactionScope())
            {
                SqlConnection connectionOne = new SqlConnection(connectionString);

                try
                {
                    //2 connections, 1 at a time
                    connectionOne.Open();
                    connectionOne.Close();
                    connectionOne.Open(); // escalates to DTC on 05, not 08
                    connectionOne.Close();

                    MessageBox.Show("Success");
                }
                catch (Exception ex)
                {
                    MessageBox.Show("ERROR: " + ex.Message);
                }
                finally
                {
                    connectionOne.Dispose();
                }
            }
        }

Within one Transaction Scope, it will open a connection, close it, and open it again.  Here is the difference.  The result:
Against SQL Server 2005 Instance:            MSDTC on server ‘SERVERNAME’ is unavailable.
Against SQL Server 2008 Instance:            Success

Starting in 2008, a connection closed and reopened will no longer be escalated to the Distributed Transaction Coordinator.  Which is good, because it doesn’t need to be.

 

The third button. “2 Connections, nested”

Code:

        private void btnTwoConnectionsNested_Click(object sender, EventArgs e)
        {
            string connectionString = @"Data Source=" + tbServer.Text
                + @";Initial Catalog=master;Integrated Security=True; timeout=0";

            using (TransactionScope transactionScope = new TransactionScope())
            {
                SqlConnection connectionOne = new SqlConnection(connectionString);
                SqlConnection connectionTwo = new SqlConnection(connectionString);

                try
                {
                    //2 connections, nested
                    connectionOne.Open();
                    connectionTwo.Open(); // escalates to DTC on 05 and 08
                    connectionTwo.Close();
                    connectionOne.Close();

                    MessageBox.Show("Success");
                }
                catch (Exception ex)
                {
                    MessageBox.Show("ERROR: " + ex.Message);
                }
                finally
                {
                    connectionOne.Dispose();
                    connectionTwo.Dispose();
                }
            }
        }

Within one Transaction Scope, it will open two connections, nested.  The result:
Against SQL Server 2005 Instance:            MSDTC on server ‘SERVERNAME’ is unavailable.
Against SQL Server 2008 Instance:            MSDTC on server ‘SERVERNAME’ is unavailable.

SQL Server does, and should, escalate to DTC for a nested connection in both versions.

 

In Summary:

Nested connections will escalate to DTC in SQL Server 2005 and 2008.  Opening one connection at a time will escalate to DTC in 2005, but WILL NOT in 2008.