• 2 min read

Elastic Database Transactions are now generally available for Azure SQL Database

Elastic Database Transactions for Azure SQL Database are now generally available. Elastic database transactions now support distributed transactions across Azure SQL Databases in multiple logical servers.

We’re delighted to announce the general availability of Elastic Database Transactions for Azure SQL Database. Elastic database transactions enable the familiar atomicity and isolation properties when you need to ensure that state across databases remains consistent. For example, these properties are essential when processing financial transactions across databases, or when updating inventory in one database and orders in another.

With general availability, elastic database transactions add support for distributed transactions across logical servers. Besides crossing server boundaries, elastic transactions in SQL Database can now also span Azure regions.

transactions
To implement transactions between different servers, potentially located in different Azure regions, the logical servers participating in elastic transactions need to first enter a communication relationship. This allows messages for distributed transaction coordination to flow between the logical servers. You can manage communication relationships for elastic transactions through PowerShell cmdlets that create, retrieve and delete communication relationships between pairs of servers.

Once communication relationships have been added for the logical servers participating in elastic transactions, you can use familiar programming abstractions such as the TransactionScope class in System.Transactions to control the boundaries of distributed transactions in your application. Note that elastic database transactions are only available with .NET applications using .NET 4.6.1 or later. Distributed transaction coordination through T-SQL such as BEGIN DISTRIBUTED TRANSACTION is not supported in SQL Database.

Cross-server transactions

The previous blog post announcing the public preview for elastic database transactions showed an example with two databases on the same logical server in Azure SQL Database. The following example illustrates how to run the same distributed transaction across databases that are located on different logical servers in SQL Database.

Establishing cross-server communication links

The first part is to establish a communication relationship between the two servers using the New-AzureRmSqlServerCommunicationLink cmdlet. The cmdlet and other cmdlets to manage communication relationships are available in the PowerShell Gallery. Follow the instructions how to install the latest cmdlets from the PowerShell Gallery.

The following PowerShell statement creates a communication relationship between the logical servers myserver1 and myserver2 in Azure SQL Database.

-AzureRmSqlServerCommunicationLink `
 -LinkName etlaunch `
 -PartnerServer myserver02 `
 -ServerName myserver01 `
 -ResourceGroupName myrg

Development Experience

The following example extends the code sample from the previous blog post to the cross-server scenario. The connection strings in the code sample simply refer to the logical server names that we have registered in the previous step with the new Azure PowerShell cmdlet.

SqlConnectionStringBuilder scsbDb1 = new SqlConnectionStringBuilder
{
    DataSource = "myserver01.database.windows.net",
    InitialCatalog = "db01",
    …
};

SqlConnectionStringBuilder scsbDb2 = new SqlConnectionStringBuilder
{
    DataSource = "myserver02.database.windows.net",
    InitialCatalog = "db02",
    …
};

using (var scope = new TransactionScope())
{
    using (var conn1 = new SqlConnection(scsbDb1.ConnectionString))
    {
        conn1.Open();
        SqlCommand cmd1 = conn1.CreateCommand();
        cmd1.CommandText = string.Format("insert into T1 values(1)");
        cmd1.ExecuteNonQuery();
    }

    using (var conn2 = new SqlConnection(scsbDb2.ConnectionString))
    {
        conn2.Open();
        var cmd2 = conn2.CreateCommand();
        cmd2.CommandText = string.Format("insert into T1 values(2)");
        cmd2.ExecuteNonQuery();
    }

    scope.Complete();
}

Next steps

We encourage you to download .NET 4.6.1 and start using elastic database transactions in SQL Database today. To learn more about elastic database transactions with SQL Database and .NET 4.6.1, please visit the elastic database transactions documentation and the announcement of the .NET 4.6.1 Release.