My Data Day at Build – Azure Elastic Databases, Azure Search, IoT, Azure Stream Analytics, and more

Spent a day on nothing but data at Build.  Azure Elastic SQL Databases, Azure Search, Azure Stream Analytics, Azure Machine Learning.  Tons of new data offerings.  And all in the cloud.  You can read my blog on it here:

https://bennettadelson.wordpress.com/2015/05/01/my-data-day-at-build-azure-elastic-databases-azure-search-iot-azure-stream-analytics-and-more/

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.

“RowGUID is neither a DataColumn nor a DataRelation for table summary. (System.Data)”

We had merge replication in SQL Server 2005 set up that was working fine, except when trying to view conflicts, the Conflict Viewer would give this error: “RowGUID is neither a DataColumn nor a DataRelation for table summary. (System.Data)”.

The source of this it turns out was actually a period in the Publication name, as we had a “6.1” as part of the name. Other special characters would probably also cause trouble.

The only way to fix this was to drop the replication, resolve known conflicts manually with a data compare, recreate the replication, and reinitialize. After that, we could use the Conflict Viewer to resolve conflicts successfully.

This was a frustrating issue since the replication was allowed to be created with the period and worked completely fine, only the Conflict Viewer experienced trouble and it gave an error that did not accurately describe the issue.

The moral of the story is: don’t create a publication with a period in the name.