CODEWITHSUNDEEP
sql-serverazure-sql-databasemsdtc
Kavi
Kavi

Reputation: 191

Azure SQL does not support elastic transactions

Recently we are migrating our SQL Server database to Azure SQL database but it does not support transactions even we build our web application to target .net framework 4.6.1 as per the guidance given in Microsoft documentation here. We are using MSDTC transactions in the same database not shared database, is the reason? Am I missing any configuration settings here?

Actually, I don't get any error but the transaction doesn't work and no rollback happens, allows partial data save.

When I check the transaction at Azure SQL DB, it shows no transaction happening at the time. The same code works well with my local database, I am able to see the current transactions running and transactions rolled-back when it is not committed.

Following are the details.

I have set target framework to 4.6.2 at VisualStudio And updated the same in my web.config. Sample code:

using (var scope = new TransactionScope())
                {
                    using (var aoContext = new AOCrowdFundEntities())
                    {

                        //do somthing

                        aoContext.SaveChanges();

                        throw new Exception();
                        scope.Complete();
                    }

                }

When I have a breakpoint before throwing the error and run the following query to see the current transaction, it shows an empty list.

Even when I throw the error without committing the transaction, the data are saved in Azure DB. :-(

Upvotes: 1

Views: 652

Answers (2)

Kavi
Kavi

Reputation: 191

I missed to remove a setting (Enlist=false) from DB connection string which was introduced to support MSDTC transaction. See below to know more details,

what is Enlist=false means in connection string for sql server?

Upvotes: 0

hokkaidi
hokkaidi

Reputation: 910

Azure SQL Database does not currently support distributed transactions as explained in this documentation page. To use the Microsoft Distributed Transactions Coordinator (MSDTC), deploy your database on a SQL Server instance running on a virtual machine.

Upvotes: 1

Related Questions