March 20, 2016 at 1:12 am
We just implemented Always on for a couple of servers, and I am getting the following error when trying to do a distributed transaction between them.
OLE DB provider "SQLNCLI11" for linked server "<ServerName>" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "<ServerName>" was unable to begin a distributed transaction.
I've configured MSDTC on all servers involved as follows:
#Configure MSDTC Security
Set-DtcNetworkSetting -RemoteClientAccessEnabled 1 -Confirm:$false
Set-DtcNetworkSetting -RemoteAdministrationAccessEnabled 1 -Confirm:$false
Set-DtcNetworkSetting -InboundTransactionsEnabled 1 -Confirm:$false
Set-DtcNetworkSetting -OutboundTransactionsEnabled 1 -Confirm:$false
Set-DtcNetworkSetting -XATransactionsEnabled 1 -Confirm:$false
Set-DtcNetworkSetting -AuthenticationLevel NoAuth -Confirm:$false
And opened up the firewall for it as follows:
# Open firewall for MSDTC
New-NetFirewallRule -DisplayName "MSDTC - IN" -Direction Inbound -Program "%SystemRoot%\system32\msdtc.exe" -Action Allow
New-NetFirewallRule -DisplayName "MSDTC - OUT" -Direction Outbound -Program "%SystemRoot%\system32\msdtc.exe" -Action Allow
Any suggestions as to what else to look at?
The Redneck DBA
March 21, 2016 at 12:36 pm
Check the linked server itself and make sure that RPC and RPC Out are both set to TRUE.
March 21, 2016 at 12:58 pm
I've done that. And even tried "Collation Compatible = True" just for fun, with no change in behavior.
I've found that setting "Enable Promotion of Distributed Transactions" = false causes things to work, but that seems like it is at best a "not good" solution, and most likely a really bad solution.
The Redneck DBA
March 21, 2016 at 2:28 pm
Distributed transactions for AlwayOn will be supported in SQL 2016 but not SQL 2012.
https://msdn.microsoft.com/en-us/library/ms366279(v=sql.110).aspx
March 22, 2016 at 4:04 am
JeremyE (3/21/2016)
Distributed transactions for AlwayOn will be supported in SQL 2016 but not SQL 2012.https://msdn.microsoft.com/en-us/library/ms366279(v=sql.110).aspx
Really?
Thank you for that information, Jeremy.
March 22, 2016 at 6:00 am
March 24, 2016 at 4:51 am
If you do plan to use MSDTC with AGs on Windows 2012 R2, then you need to check out https://support.microsoft.com/en-us/kb/3090973.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 27, 2016 at 9:15 pm
I saw those references saying MSDTC isn't supported with AlwaysOn 2012. But apparently Microsoft still supports MSDTC itself, because I ended up opening a case with them and they helped me resolve the issue.
They were able to determine that the folks that built the servers for me (All VMs) missed a step and the servers in question all had the same CID.
They gave me the following steps to clean that up, and they worked like a charm:
1. Stop the Distributed Transaction Coordinator service in the Services Control Panel.
2. Open command prompt with Run as Administrator.
3. Type the command msdtc –uninstall and hit enter.
4. Once the command completes open registry editor.
5. Check the below paths to see if the keys exist (highlighted in bold red).
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSDTC
HKEY_CLASSES_ROOT\CID
HKEY_CLASSES_ROOT\CID.Local
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC
6. If the above keys exist, please manually delete the keys.
7. Reboot the server.
8. Open command prompt with Run as Administrator.
9. Type the command msdtc –install and hit enter. This reinstalls the MSDTC service and the 4
registry hives above which we deleted manually.
10. Type the command msdtc –resetlog and hit enter.
11. Reconfigure the msdtc settings from the properties of local dtc.
The Redneck DBA
March 28, 2016 at 4:00 am
TheRedneckDBA (3/27/2016)
I saw those references saying MSDTC isn't supported with AlwaysOn 2012. But apparently Microsoft still supports MSDTC itself, because I ended up opening a case with them and they helped me resolve the issue.They were able to determine that the folks that built the servers for me (All VMs) missed a step and the servers in question all had the same CID.
They gave me the following steps to clean that up, and they worked like a charm:
1. Stop the Distributed Transaction Coordinator service in the Services Control Panel.
2. Open command prompt with Run as Administrator.
3. Type the command msdtc –uninstall and hit enter.
4. Once the command completes open registry editor.
5. Check the below paths to see if the keys exist (highlighted in bold red).
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSDTC
HKEY_CLASSES_ROOT\CID
HKEY_CLASSES_ROOT\CID.Local
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC
6. If the above keys exist, please manually delete the keys.
7. Reboot the server.
8. Open command prompt with Run as Administrator.
9. Type the command msdtc –install and hit enter. This reinstalls the MSDTC service and the 4
registry hives above which we deleted manually.
10. Type the command msdtc –resetlog and hit enter.
11. Reconfigure the msdtc settings from the properties of local dtc.
So is that working with the linked servers then?
March 28, 2016 at 7:21 am
It's working. I'm not sure what to think about how supported it is. There are MS articles scattered about saying it is not supported, one of which is linked in this thread.
But in my MS support case, I specifically mentioned we were using AlwaysOn and they gathered all sorts of data from the servers in the process, and nowhere did anyone mention that it wasn't supported.
Not sure what to make of it. It seems like not supporting distributed transactions would be a pretty major deterrent to using AlwaysOn as a solution for a lot of people. In my case we're talking about ETL transactions for tables that are truncated and repopulated every day anyway, so a transaction that doesn't cleanly roll back isn't going to cause all that much heartburn - but in many cases I would think this would make a lot of people shy about using Always On.
The Redneck DBA
March 28, 2016 at 7:38 am
Well we do use MSDTC quite regularly so it's good to hear that there is a solution. Thank you for posting the details.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply