March 1, 2018 at 7:41 am
I was planning to create a two node AG on SQL Server 2012, but the Distributed Transaction Coordinator is in use on the system I want to migrate to the AG. Right now it is a stand alone instance, but the system used to be a two node failover cluster. According to this link, DTC with AG can result in data loss. If we use a two node failover cluster instance, would we not have the same risk of data loss since we would still have two separate nodes with separate DTC processes running? It seems like perhaps Log Shipping is my only option here. Looking for recommendations, other than update it to SQL Server 2017, where DTC is fully supported. For right now, updating the SQL Server instance isn't really an option.
March 2, 2018 at 3:09 pm
I'm not sure it would be the same problem if you are running it on a failover cluster instance. On a traditional cluster (i.e. AlwaysOn FCI) if you need MSDTC that becomes a clustered resource. So if the cluster fails over to another node, the MSDTC resource would fail over as well, so I think any transactions that were not yet committed would roll back. I would think the issue with MSDTC and an AlwaysOn Availability Group is that the AG is cluster-aware, but MSDTC is not. So if the AG fails over there is nothing to rollback the other parts of the transaction on other databases.
At least that is what I would think the issue would be.
Joie Andrew
"Since 1982"
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply