0

Hello SQL Server Experts,

I'm in the process of configuring a Distributed Availability Group (DAG) in SQL Server and am looking for guidance on implementing it with manual seeding (back up restore) instead of the automatic seeding.

The DAG I plan to set up involves two normal Availability Groups (AGs) named ag1 and ag2, each serving as a replica within the DAG. My aim is to replicate the following topology:

+--------------------+         +------------------------+
|  WSFC1             |         |   WSFC2                |
|  +--------------+  |         |  +------------------+  |
|  | AG1 (P)      |  |         |  |  AG2 (S)         |  |
|  | +---------+  |  |         |  |  +-------------+ |  |
|  | |node1 (P)|  |======DAG=====>|  |node3 (P)(F) | |  |
|  | +----+----+  |  |         |  |  +-----+-------+ |  |
|  |      |       |  |         |  |        |         |  |
|  | +----v----+  |  |         |  |  +-----v-------+ |  |
|  | |node2 (S)|  |  |         |  |  |node4 (S)    | |  |
|  | +---------+  |  |         |  |  +-------------+ |  |
|  +--------------+  |         |  +------------------+  |
+--------------------+         +------------------------+
In this topology, we have:
- 4 nodes: node1, node2, node3, node4
- 2 WSFC clusters: WSFC1 and WSFC2
- 2 AGs: AG1 and AG2
- 1 DAG, from AG1 to AG2
- (P) = primary replica
- (S) = secondary replica
- (F) = forwarder

The official Microsoft documentation (Configure Distributed Availability Groups) primarily covers the setup using automatic seeding. However, I want to utilize the traditional backup and restore method.

Unfortunately, I am uncertain about the correct sequence of operations regarding when and where to take backups and where to restore them within this topology.

Could anyone provide a step-by-step guide or clarify the process in plain English on how to create a DAG using manual seeding?

Below is what I have pieced together so far, but I'm stuck on the ordering and appropriate steps:

  • Enable the Always On Availability Groups feature on all instances.
  • Create an endpoint on all instances.
  • If ag1 and ag2 span across different domains, configure the endpoints to authenticate via certificate.
  • Take a full and log backup on node1, then restore them to node2, node3, and node4 using the NORECOVERY option???
  • Create ag1 on node1.
  • Create ag2 on node3???
  • On node1, create the DAG that includes ag1 and ag2.

I'd greatly appreciate any help or resources that could point me in the right direction.

Thank you for your time and assistance!

1 Answer 1

2

The step by step process is in the documentation that you linked (I know it is there because I helped write it) but it's kind of hidden and easy to miss. It's in the tab called Manual Seeding and I've linked it here.

The basic premise is the same (just in case we get MSDN'd, BOL'd, or Docs'd):

  1. Complete all normal steps to get to the point of creating the Distributed AG and joining it on the forwarder side (which is still an empty AG at this point).
  2. Restore the databases to each replica on the forwarder side and leave them in a NORECOVERY state.
  3. On the Forwarder join the database to the Distributed AG by using the ALTER DATABASE ... SET HADR functionality.
  4. Join the remaining secondary replicas on the forwarder side.
3
  • Oh my god! Yes, I missed that part.... Thanks Sean! Jan 30 at 10:41
  • 1
    The tab bar is not visually distinct and only becomes apparent as a tab upon mouse hover. Jan 30 at 10:44
  • @Justalearner Yeah, I don't like that they did that and gave feedback - it didn't get far :D Jan 30 at 10:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.