SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Alternative to Availability Groups with Distributed Transaction Coordinator


Alternative to Availability Groups with Distributed Transaction Coordinator

Author
Message
lmarkum
lmarkum
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6899 Visits: 1096
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.
Joie Andrew
Joie Andrew
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25192 Visits: 2133
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"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search