June 5, 2014 at 8:20 am
we wrap all our dataflow between databases in transactions using MSDTC this has caused problems with our always on availability groups, to the extent that we have had to switch it off.
after consultation with Microsft they are saying they will not support running MSDTC and always on together. this leave us with having to look back to good old replication but as MS seem to be pushing Always On dont really want to go back to replication.
Has anyone had similar issues with MSDTC and Always On?
June 5, 2014 at 2:38 pm
Hi,
This has been the case ever since SQL Server 2005 introduced database mirroring. You will definitely have problems after a failover, either with not being able to connect to coordinator or/and with transaction integrity. It's just not meant to be.
Br,
Mika
June 5, 2014 at 3:10 pm
thanks Mike
what are the common options people use. we dont really want to re-architect things..
June 5, 2014 at 11:45 pm
Is there a reason you've moved away from Replication? Just because AlwaysOn technologies like Availability Groups are very popular these days it doesn't mean that Replication would be any less valid option.
If you really want to use AlwaysOn AGs, I'd have a look at the following MSDN article about features that work with Availability Groups.
AlwaysOn Availability Groups: Interoperability (SQL Server)
This is the list of supported features that you have.
June 6, 2014 at 2:14 am
Well not sure if we had Replication here before. But since ive been here and been on 2012 we have always been Using always on (or tyring too) i think because of its ease of setup and better deployment and performance.
ive always had issues in the past with replication BUT my main concern would be going back to replication when it dose seem MS are going down the Awlays on group.
Thanks
Philip
August 23, 2014 at 10:25 pm
Hi. Assuming all nodes are in the same domain, can this be addressed by clustering MSDTC resource alone using NAS storage? It is AlwaysOn AG, but just for MSDTC i'd rely on external storage where i can allocate 3GB diskspace for MSDTC resource.
September 8, 2014 at 5:09 am
Are you able to say more about what you wanted to do with MSDTC and AGs. Can you say what the exact problem was, and how you had configured MSDTC?
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
September 8, 2014 at 5:55 am
Fundamentally, I don't think that AlwaysOn in itself prevents MSDTC from working. The problem is when an application attempts to invoke MSDTC from a connection using an AlwaysOn Availability Group Listener Name. As MSDTC is effectively a server-level mechanism and the listener sits above this, the connection has no visibility of the underlying server that you're running against and MSDTC can't work.
There's no problem using MSDTC as long as you reference the server instance name in your connection string and manually failover by changing connection strings to point to the secondary (you could presumably build this failover automatically into your code).
September 8, 2014 at 9:39 am
Cross-Database Transactions Not Supported For Database Mirroring or AlwaysOn Availability Groups (SQL Server)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply