Distributed transactions accross Azure SQL and on premises sql server causes errors

Question

Is it possible to have a transaction between an on premises SQL Server instance and an Azure SQL database.

I have the following tests cases.

public class TransactionsTest
{
    [Fact]
    public void Test1()
    {
        var premisesDatabaseContext = new OnPremisesDatabaseContext();
        var azureSQLDatabaseContext = new AzureSQLDatabaseContext();

        using (TransactionScope scope = new TransactionScope())
        {
            premisesDatabaseContext.Database.Connection.Open();
            azureSQLDatabaseContext.Database.Connection.Open();

            scope.Complete();
        }
    }

    [Fact]
    public void Test2()
    {
        var premisesDatabaseContext = new OnPremisesDatabaseContext();
        var azureSQLDatabaseContext = new AzureSQLDatabaseContext();

        using (TransactionScope scope = new TransactionScope())
        {
            azureSQLDatabaseContext.Database.Connection.Open();
            premisesDatabaseContext.Database.Connection.Open();

            scope.Complete();
        }
    }
}

It seems pretty straightforward. But both tests cases fails with different errors when I open the second connection.

Here is the errors details.

// TEST 1
System.Reflection.TargetInvocationException: 
Exception has been thrown by the target of an invocation. 
--->
System.NullReferenceException: Object reference not set to an instance of an object.
       at System.Transactions.Transaction.GetPromotedToken()

// TEST 2
System.Transactions.TransactionPromotionException:
There is a promotable enlistment for the transaction which has a PromoterType value that is not recognized by System.Transactions. 
1c742caf-6680-40ea-9c26-6b6846079764

I want to know if it is possible to accomplish this. If it's not possible, what are the alternatives?

Jemy

Answer 1

You can't

Azure sql databases uses Elastic Transactions. While on premises servers uses Microsoft Distributed Transaction Coordinator (MSDTC).

MSDTC is not supported on azure, and according to the elastic transactions documentation,

Only transactions across databases in SQL DB are supported. Other X/Open XA resource providers and databases outside of SQL DB cannot participate in elastic database transactions. That means that elastic database transactions cannot stretch across on premises SQL Server and Azure SQL Database. For distributed transactions on premises, continue to use MSDTC.

• On premises SQL Server uses MSDTC, which is not available on Azure SQL Database.

• Azure SQL Database uses elastic transactions, which won't work with on premises SQL server.

The easiest solution would be to move all database to either Azure or on premises.

Jemy