SQL SERVER – Steps to Deploy Distributed Availability Group – Windows Clusters in Different Domains

In the current era where companies’ mergers and take over is a common phenomenon, it is possible to run into a situation where there are two domains which are connected but not trusted. I was engaged in consulting for a client who had a similar merger in the past. The reporting application was in a different domain and they wanted data to be synchronized in SQL Server database on parent domain. Essentially, they had two windows clusters and they want to have one AG across them. The only solution which came to my mind was Distributed Availability Group in SQL Server. To make things little more complicated, they did not want to use certificates to configure this setup. As per them, certificates will add an overhead of maintenance. Here is the oversimplified diagram of their deployment.

SQL SERVER - Steps to Deploy Distributed Availability Group - Windows Clusters in Different Domains dag-wg-01-800x283

SOLUTION/WORKAROUND

After contemplating and testing for a while, we decided to make use of NTLM Pass-Through Authentication between the domains and were also successful in doing the same. Below were high-level steps followed to achieve the same.

  • Create a local windows user in each of the nodes (N1, N2, N3, N4) across domains using the same name and password. Note: Only the NodeName will change here. Username and password must the same.

For example N1\DAGUser, N2\DAGUser, N3\DAGUser and so on.

  • Add this account to Local Administrators group on all the nodes across domains
  • Change SQL Server startup account to this newly created account on all the nodes across domains (via SQL Server Configuration Manager).
  • Create the Primary Availability Group (HA_AG) with a corresponding listener name (HA _LIST)
  • Grant the SQL Server service account CONNECT permissions to the endpoint. Run on ALL nodes — GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [<nodename>\UserName]
  • Each node should have “connect” permission from all other three nodes.
  • Create the secondary Availability Group (DR_AG) with a corresponding listener name (DR_LIST)
  • Join the secondary replicas to the secondary Availability Group
  • Add entries to the HOST File with “IPAddr and Listener FQDN” info of the other domain. i.e. Domain1 nodes will contain [1.1.xxx DR_LIST.MyDomain2.com] Domain2 nodes will contain [192.168.1.xxx HA_LIST.MyDomain.com]
  • Create Distributed Availability Group (HA_DR_AG) on the primary Availability Group (HA_AG)
  • Join the Secondary Availability Group (DR_AG) to the Distributed Availability Group

There were some hiccups in deployment but overall, above steps worked and we were able to fulfill their requirement. If you have such consultancy requirements for Always On, feel free to contact me.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – Event ID 1069 – Unable to Failover Clustered Instance to Another Node. Resource is not Loaded
Next Post
SQL SERVER – Unable to Start SQL After Patching – Database SSISDB is Enabled for Database Mirroring

Related Posts

3 Comments. Leave new

  • Pinal, –
    Nice write up. If we have distributed AG, is it possible to take the replicated note at secondary site to be set to read/write for testing / DR drill and add it back to the AG ? Can this be done ? Is it safe ? and the modified data will not be written back to the primary ? thanks

    Reply
  • Srikar Reddy
    April 1, 2021 8:33 pm

    Cant we use certificates and new endpoints for DAG instead of chnaging the service accounts to local?

    Reply
  • Could you help in sharing steps for adding new database to an existing Distributed AAG, plz.

    Reply

Leave a Reply

Menu