MSDTC – Microsoft Distributed Transaction.
I had requirement to configure Azure Virtual machine with SQL Server 2008 R2 and One Azure Virtual machine as an application server having few sites hosted on it. Azure virtual machine app server hosted application should be able to connect to SQL server and run SQL queries with transactions. Unfortunately transaction queries were not working in my environment set up.
The problem is, for transaction to work between SQL server and application server you need to have MSDTC to be configured on both machines. Everywhere I read through many posts that for configuring MSDTC, settings related to AD DC (domain controller) is required. However my azure virtual machines were not in any domain neither any one of them was treated as DC.
Hence I tried to configure MSDTC on azure virtual machines without domain configuration and it was successful.
Following steps will guide through the steps required to enable MSDTC on Azure virtual machines. Please note that the azure virtual machines do not have any domain configured on them neither they are not part of any domain.
I have 2 azure virtual machines with following configuration –
1. SQL Server 2008 R2
2. Windows Server 2008 R2 – call it as app server
Now I need to configure MSDTC on SQL server and app server.
At first you need to configure Azure virtual network as per the guidelines mentioned at this post.
Then provision Azure virtual machines with above configuration using the steps mentioned at this post in the virtual network you created above.
Now RDP to both Azure Virtual machines from the steps mentioned in this post.
Then on SQL server make configuration settings mentioned on this post - http://www.windowsazure.com/en-us/documentation/articles/virtual-machines-provision-sql-server/#SSMS
Then log in to App server and follow below steps.
Select Open Control Panel -> System and Security -> Allow a program through windows firewall option as shown below.
Then select enable check mark for Distributed Transaction coordinator process as shown below.
Open Run window and type “Services.msc” and make sure that Distributed Transaction Coordinator process is started and in running mode.
Selection “Component Services” as shown below from Administrative Tools.
Select properties of Local DTC as shown below –
On Local DTC properties select Security tab and make sure that you select check following options –
· Network DTC access
· Allow Inbound
· Allow Outbound
· No authentication required
· Enable SNA LU 6.2 Transactions
Rest all of the options will be unchecked as shown below –
Follow all of the above steps under heading ***MSDTC configuration*** on SQL server as well. This way configure the same setting son both servers.
Testing MSDTC –
To test MSDTC configuration I created a two sample DB on SQL server with one table in each one of it. Then created a simple windows application to make insert statement in both databases using C# - TransactionScope - http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope(v=vs.110).aspx
// Create the TransactionScope to execute the commands, guaranteeing
// that both commands can commit or roll back as a single unit of work.
using (TransactionScope scope = new TransactionScope())
// code to create connectiions to two databases and execution of insert statements using commands
catch (TransactionAbortedException ex)
//write exception details to UI
I ran the application on app server and found that my transaction was committed as shown below –
If you want test application of MSDTC just comment your email id and i will share it with you. Hence this is how you can configure MSDTC on azure virtual machines. And to configure MSDTC domain availability is not required.
Hope this helps.