Questions? Feedback? powered by Olark live chat software
Hopp over navigasjon

Elastic Database Transactions with Azure SQL Database

Posted on 3 november, 2015

Principal Program Manager Lead, Azure SQL Database

Today, we are excited to announce the preview of elastic database transactions in Azure SQL Database. Distributed transactions have consistently ranked among the most requested features for SQL Database. You can now perform operations across several databases in SQL Database with transactional properties. This enables the same guarantees that you are familiar with in SQL Server when using Microsoft Distributed Transaction Coordinator (MSDTC). The guarantees with elastic database transactions make sure that changes to different databases in SQL Database happen either in all of the databases or none. This enables the familiar atomicity and isolation properties that ensure your state across databases remains consistent. For instance, this is important when processing financial transactions across databases, or when updating inventory in one database and orders in another.

Starting with .NET 4.6.1, we surface elastic database transactions for SQL Databases through the same .NET APIs that are used today to coordinate transactions on premise using MSDTC. This means that you can start using elastic database transactions for SQL Database by simply re-building your existing .NET applications against the newest .NET libraries – no changes to your code are necessary. Moreover, there is no need to deploy MSDTC for elastic transactions in SQL Database, since elastic transactions are managed directly by SQL Database.

This preview focuses on .NET applications that rely on functionality in System.Transactions such as TransactionScope to coordinate distributed transactions. With .NET developers, this already constitutes a widely adopted programming pattern with SQL Server. A server-side experience for coordinating distributed transactions purely using T-SQL, however, is not part of this preview. The following figure illustrates the scenario for elastic database transactions in SQL Database.

Elastic Database Transactions with Azure SQL Database

See the following paragraphs for more details how to start using elastic database transactions in Azure SQL DB in your .NET applications.

Installation

With this preview, you can now build applications that make atomic changes to data stored in several different SQL Databases. The focus of the preview is on client-side development experiences with .NET and particularly C#. A server-side experience for coordinating distributed transactions or support for programming languages other than .NET is beyond the scope of this preview. We plan to support this in the future.

The capabilities of this preview are provided through updates to the .NET libraries System.Data.dll and System.Transactions.dll. The libraries transparently ensure that two-phase commit is used where necessary to ensure atomicity. These libraries are automatically installed to your system as part of the .NET 4.6.1 Release Candidate. For details on how to get the release candidate, check out the announcement on the Microsoft .NET Blog.

Once you’ve installed the release candidate, you can take your existing applications that use the distributed transaction APIs in System.Transactions and rebuild them for .NET 4.6. If your projects target .NET 4.6, they will automatically use the updated DLLs from the release candidate.

As opposed to SQL Server, elastic database transactions with SQL Database do not require the Microsoft Distributed Transaction Coordinator. Instead, elastic transactions are natively managed by and within SQL Database. This significantly simplifies the experience particularly for cloud deployments since a deployment of MSDTC is not necessary to use distributed transactions with SQL Database.

Application Development Experience

The following code sample illustrates the familiar programming experience with .NET System.Transactions. It uses the TransactionScope class to establish an ambient transaction in .NET. All database connections opened within the scope of the ambient transaction participate in the transaction and the ambient transaction is elevated to an elastic database transaction if multiple different databases participate.

In this example, the outcome of the transaction is controlled by setting the scope to complete to indicate a commit.

using (var scope = new TransactionScope())
{
	using (var conn1 = new SqlConnection(connStrDb1))
	{
		conn1.Open();
		SqlCommand cmd1 = conn1.CreateCommand();
		cmd1.CommandText = string.Format("insert into T1 values(1)");
		cmd1.ExecuteNonQuery();
	}
	
	using (var conn2 = new SqlConnection(connStrDb2))
	{
		conn2.Open();
		var cmd2 = conn2.CreateCommand();
		cmd2.CommandText = string.Format("insert into T2 values(2)");
		cmd2.ExecuteNonQuery();
	}
	
	scope.Complete();
}

Make sure that your project settings target .NET Framework version 4.6. This will ensure that the new libraries from the .NET Release Candidate are used – they are required for elastic transactions in SQL Database to work.

Monitoring Transaction Status

You can use existing Dynamic Management Views (DMVs) for transactions such as sys.dm_tran_active_transactions to monitor status and progress of your ongoing elastic database transactions.

For instance, you can run the sample code given above, place a breakpoint right on the scope.Complete() call, then query the sys.dm_tran_active_transactions DMV. All participating databases will show the participant transaction of the elastic database transaction carrying the same UOW value, as shown in the following screenshot.

BlogPostVSand SSMSScreenshot

Next Steps

We encourage you to download the .NET 4.6.1 Release Candidate 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 Candidate.