question

sakuraime avatar image
0 Votes"
sakuraime asked SeeyaXi-msft commented

Create distributed AG

Here is the sample tsql to create distributed AG
121554-image.png




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.

sql-server-general
image.png (63.7 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·
SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered SeeyaXi-msft edited

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://docs.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.

· 13
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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....


??

0 Votes 0 ·

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

0 Votes 0 ·

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

0 Votes 0 ·
Show more comments
SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered SeeyaXi-msft commented

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.

· 8
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

so should I create another listeners(on the other network interface) for DAG replication between 2 AG ?

0 Votes 0 ·

Hi @sakuraime,

No. BTW, if there are any errors, there will be corresponding error messages.
See this link: https://docs.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

0 Votes 0 ·

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






0 Votes 0 ·
Show more comments