Create distributed AG

Question

Create distributed AG

sakuraime 2,341

Here is the sample tsql to create distributed AG

is it a must to specify the URL of the LISTENER_URL ? Can I specify another IP addresses which is not as the listener ip( or interface).

If I use the listener IP address as the endpoint address, the replication traffic will go through the same network interface as the listener ip .

I would like to separate all the endpoint replication traffic to another network interface.

Seeya Xi-MSFT 16,586 Reputation points

2021-08-11T07:22:50.82+00:00

Hi @sakuraime ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

2 answers

Your answer

Seeya Xi-MSFT 16,586 Reputation points

2021-08-11T07:22:50.82+00:00

Hi @sakuraime ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Answer 1

Seeya Xi-MSFT 16,586

Hi @sakuraime ,

is it a must to specify the URL of the LISTENER_URL ?

You can also use the way of FQDN.

Can I specify another IP addresses which is not as the listener ip( or interface).

Yes, you can.
Port 5022: If you want to use annother port, you should ensure that the corresponding LISTENER_URL is the port you specify, otherwise the Availability Group cannot communicate through this port.
Port 1433: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-group-listener-overview?view=sql-server-ver15#SelectListenerPort
Note: LISTENER_URL: Notice that this parameter was used instead of ENDPOINT_URL. This parameter specifies the listener for each Availability Group along with the endpoint of the Availability Group – 5022 - not the endpoint of the listener - 1433.

If I use the listener IP address as the endpoint address, the replication traffic will go through the same network interface as the listener ip .

Normally, there will be no conflicts. Alwayson is based on clusters and Replication is not.
Please see this article for examples.

Best regards,
Seeya

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

sakuraime 2,341 Reputation points

2021-08-09T14:14:02.363+00:00

suppose I have another 2 private address

ag1-listener-private.mydemosql.com and ag2-listener-private.mydemosql.com ( which these two address is not actually the listener of the two AG , and the IPs are from the other interface)

so can it still be specify on

Create availability group [mydag] with distributed....

??
Seeya Xi-MSFT 16,586 Reputation points

2021-08-10T06:39:20.973+00:00

Hi @sakuraime ，

which these two address is not actually the listener of the two AG

If you mean like these two addresses and they are not occupied, it is theoretically feasible.

and the IPs are from the other interface)

The port must be the same as the previous steps.
In this docs, the previous steps mean step1 and step2.
Also need to be careful not to let the firewall block this port.

Here is the docs about Distributed availability groups

Best regards,
Seeya
sakuraime 2,341 Reputation points

2021-08-12T02:38:19.413+00:00

I have tried to use

LISTENER_URL = 'tcp://x.x.x.x:5022'

in creating the DAG... it works!!!!!

in this way we can separate the mirroring traffic already. thanks
sakuraime 2,341 Reputation points

2021-08-12T07:12:31.063+00:00

I found it induce another issue .
because if we hardcode the ip , after the primary AG is failedover . and secondary AG lost connection.
Seeya Xi-MSFT 16,586 Reputation points

2021-08-12T08:35:36.543+00:00

Hi @sakuraime ,

Any error message? Did you check the information of windows events?

Best regards,
Seeya
sakuraime 2,341 Reputation points

2021-08-12T08:54:56.357+00:00

LISTENER_URL = 'tcp://10.10.10.1:5022'

for example the above is the primary AG endpoint (if primary AG has two replica , replica1 is 10.10.10.1 and another replica2 is 10.10.10.2 ) .

when the primary AG , failover from replica1 to replica2 . the connection between the primary AG and secondary AG will be lost , because it's still using 10.10.10.1
for the endpoint communication.

so we still need to use url / fqdn to make it more dynamic .
Seeya Xi-MSFT 16,586 Reputation points

2021-08-13T08:28:03.99+00:00

Hi @sakuraime ,

This seems to be a problem with your AG1 listener.

LISTENER_URL = 'tcp://10.10.10.1:5022'

This is a ENDPOINT_URL of replica in AG1.

Did you follow this docs to configure it: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-distributed-availability-groups?view=sql-server-ver15&tabs=automatic
And here is a docs about Distributed Availability Group.

Best regards,
Seeya
sakuraime 2,341 Reputation points

2021-08-13T08:58:12.347+00:00

which means I need to provision another listener which is rely another network interface , in order to separate the traffic from Application to the original listener , and the endpoint traffic between two AG groups.
Seeya Xi-MSFT 16,586 Reputation points

2021-08-13T10:25:56.667+00:00

Yes. And your listener should not be reused.
sakuraime 2,341 Reputation points

2021-08-14T01:16:44.463+00:00

Hi Seeya.

So this is also a recommendation from MS to use separate LISTENER for mirroring endpoint IP , in DISTRIBUTED AG CASE?
Seeya Xi-MSFT 16,586 Reputation points

2021-08-16T02:41:39.46+00:00

Hi @sakuraime ,

You mean:
https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-distributed-availability-groups?view=sql-server-ver15&tabs=automatic#create-the-primary-availability-group-on-the-first-cluster

REPLICA ON N'server1' WITH (ENDPOINT_URL = N'TCP://server1.contoso.com:5022',

AND
https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-distributed-availability-groups?view=sql-server-ver15&tabs=automatic#create-distributed-availability-group-on-first-cluster
can they be the same ip and port?
No. They do different things and listen to different content in different ports.

LISTENER_URL = 'tcp://ag1-listener.contoso.com:5022',

According to your previous statement:
LISTENER_URL = 'tcp://10.10.10.1:5022' is similar to 'TCP://server1.contoso.com:5022'

The official documentation for configuring distributed availability groups is very clear. Please read it carefully.

Best regards,
Seeya
sakuraime 2,341 Reputation points

2021-08-16T09:37:54.4+00:00

this the official document didn't mention to use another listener on other network interface for DAG setup. please read the doc carefully .

that's why I ask what's the best practice?
Seeya Xi-MSFT 16,586 Reputation points

2021-08-16T10:47:42.077+00:00

Hi @sakuraime ,

I have no intention of offending. This document is the best practice.
And I have some update following.

Best regards,
Seeya

Answer 2

Seeya Xi-MSFT 16,586

Hi @sakuraime ,

Let's sort it out again:

Suppose there are a total of four servers and two AGs.

To begin with, the four servers are all 5022 ports (in order to avoid trouble, it is recommended to use the default 5022).
The four servers are divided into two AGs, and each AG has its own listener. The port of the AG listener may not be 5022, it may be 4567 or other free interfaces, because it is only responsible for internal listening. Therefore, the ports of the two AGs can be different.
There are two listeners in the DAG, which can be regarded as a special AG, and the port is still 5022 (the same as the previous server port, it is the default and is also for the purpose of interaction).
Note: This DAG does not have its own listener.

As for annother network you mentioned,
From this link, we can see

there are four scenarios:
Both WSFC clusters are joined to the same domain.
Each WSFC cluster is joined to a different domain.
One WSFC cluster is joined to a domain, and one WSFC cluster is not joined to a domain.
Neither WSFC cluster is joined to a domain.

That is to say, like AG, we just need all the servers of each AG to be on the same cluster. It can be in the same domain or not. The example in the official document is that all servers are in the same domain named contoso.

Finally, if you have any other questions, please feel free to let me know.

Best regards,
Seeya

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

sakuraime 2,341 Reputation points

2021-08-16T13:28:44.4+00:00

so should I create another listeners(on the other network interface) for DAG replication between 2 AG ?
Seeya Xi-MSFT 16,586 Reputation points

2021-08-17T02:43:09.433+00:00

Hi @sakuraime ,

No. BTW, if there are any errors, there will be corresponding error messages.
See this link: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-replication-for-always-on-availability-groups-sql-server?view=sql-server-ver15
You can use the subscription created with the same listener name of AG.

Best regards,
Seeya
sakuraime 2,341 Reputation points

2021-08-18T04:44:56.7+00:00

seems still can't address my question. which option should be suggested to be using :

OPTION 1
CREATE AVAILABILITY GROUP [distributedag]
WITH (DISTRIBUTED)
AVAILABILITY GROUP ON
'AG01' WITH
(
LISTENER_URL = 'tcp://aglist01_interface_1.domain.com:5022', -- Which this listener also use for Client connection .
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = MANUAL
),
'AG02' WITH
(
LISTENER_URL = 'tcp://aglist02_interface_1.domain.com:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = MANUAL
);
GO

sakuraime 2,341

or option 2

CREATE AVAILABILITY GROUP [distributedag]  
   WITH (DISTRIBUTED)   
   AVAILABILITY GROUP ON  
      'AG01' WITH    
      (   
         LISTENER_URL = '**tcp://aglist01_interface_2.domain.com:5022'**, ---- WHICH is on the 2nd network interface , which not use for client connection. 
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
         FAILOVER_MODE = MANUAL,   
         SEEDING_MODE = MANUAL   
      ),   
      'AG02' WITH    
      (   
         LISTENER_URL = 'tcp://aglist02_interface_2.domain.com:5022', ---- WHICH is on the 2nd network interface , which not use for client connection. 
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
         FAILOVER_MODE = MANUAL,   
         SEEDING_MODE = MANUAL   
      );

GO

Seeya Xi-MSFT 16,586 Reputation points

2021-08-18T08:08:59.017+00:00

Hi @sakuraime ,

Both options are fine. But the suggestion is to choose 2.
Choosing 1 may cause slow connection response (but not connection failure).

Best regards,
Seeya
sakuraime 2,341 Reputation points

2021-08-18T09:44:25.557+00:00

thanks . and are there any Microsoft documentation on setting up using th2 2nd option ?
Seeya Xi-MSFT 16,586 Reputation points

2021-08-18T09:53:11.353+00:00

Not yet, but this is just a difference in ip address.
I tested that compared to an ip address that does not need to be a listener to connect to SQL Server, when the ip as a listener connects to SQL Server, the response will be slower.
Seeya Xi-MSFT 16,586 Reputation points

2021-08-23T06:54:18.16+00:00

Hi @sakuraime ,

Did the reply could help you? If the response helped, do "Accept Answer". If not, please feel free to let us know.

Best regards,
Seeya

Share via

Create distributed AG

2 answers

Your answer