Post reply

Always On

  • March 8, 2019 at 11:39 am

    #2023967

    Thanks again!
    Do you see any issue setting up Availability group in SQL 2014?

  • March 10, 2019 at 6:19 pm

    #2024047

    So DTC is part of windows operating system right? Distributed transaction has nothing to do with SQL Server right?
    What version of SQL Server did you configure Always on Availability group?

    DTC is part of Windows, however, DTC support for Availability Groups only exists in SQL 2016 and higher. DTC uses a resource manager to help manage the distributed transactions, and in the case of SQL Server it uses the instance itself. The instance has a Resource Manager ID, and in the case of a standalone instance or a Failover Clustered Instance, this ID does not change and DTC is supported.

    Prior to SQL 2016, the same methodology was used with Availability Groups, however, a failover would mean moving databases (and the clustered DTC) to a new replica and hence a new SQL instance with a different Resource Manager ID. This is why DTC is not supported. In SQL 2016, if you enable DTC support, your AG is assigned a separate Resource Manager ID that does not change between failovers. This is how DTC is supported in AGs from 2016 onwards.

    More info here: https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/06/not-supported-ags-with-dtccross-database-transactions/

    Thanks John for the info! However, i have been doing lot of learning on my side as well, just wanted some clarification on DTC. There were no much information posted about the real need of Distributed transactions.
     Do you still think that DTC is needed to be configured for manual failover as well? I am under impression that Distrubuted transactions are only needed for Automatic failover in Always on Availability Group.
    I have read that Distributed transactions are also not supported for Mirroring(with Witness Server) and Always on Avaialability group prior to 2016 for Automatic failover. Please advise?

    Whether you need DTC or not is determined entirely by your applications. Do the apps connecting to this instance require DTC for distributed transactions? If not, then you don't need DTC support and can go with SQL 2014. DTC has nothing to do with AG failover or commit modes, it is an application architecture used to coordinate a single transaction across multiple systems to provide distributed commit\rollback.

    Under the hood, AGs user Service Broker, Mirroring and Windows Failover Clustering technologies to manage the group availability, none of which rely on DTC to function.

    Some more info on DTC: https://blogs.msdn.microsoft.com/florinlazar/2004/03/04/what-is-msdtc-and-why-do-i-need-to-care-about-it/

Viewing 2 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply