3

I am setting up a SQL Server Availability Group with three synchronous replicas within a cluster (one primary and two secondary) and an additional asynchronous replica from another cluster in another data center. I need to expose the primary replica using a public IP for the asynchronous replica in the remote cluster.

My goal is to have the primary replica exposed with a public IP specifically for the remote asynchronous replica. In contrast, the other synchronous replicas within the cluster communicate with the primary replica using the primary replica's DNS name in the cluster.

When creating an Availability Group, I need to specify the endpoint URL for each replica. Is it possible to explicitly use different endpoint URLs of the primary replica for different replicas in an SQL Server Availability Group? I want to ensure that only the remote asynchronous replica communicates with the primary using the public IP. In contrast, the other replicas within the cluster continue to use the primary replica's DNS name in the cluster.

Example: Created availability group of three nodes, mssql-primary-0, and mssql-primary-1 are two pods in a Kubernetes cluster communicate using headless service (pod's DNS name). The mssql-remote-0 pod is from another cluster exposed using the load balancer service's external IP.

CREATE AVAILABILITY GROUP [AG1]
      WITH (CLUSTER_TYPE = NONE)
      FOR REPLICA ON
      N'mssql-primary-0'
            WITH (
            ENDPOINT_URL = N'tcp://mssql-primary-0.headless-service:5022',
            AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
               SEEDING_MODE = AUTOMATIC,
               FAILOVER_MODE = MANUAL,
            SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
               ),
      N'mssql-primary-1'
            WITH (
            ENDPOINT_URL = N'tcp://mssql-primary-1.headless-service:5022',
            AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
               SEEDING_MODE = AUTOMATIC,
               FAILOVER_MODE = MANUAL,
            SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
               ),
      N'mssql-remote-0'
            WITH (
            ENDPOINT_URL = N'tcp://172.232.87.37:5022',
            AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
               SEEDING_MODE = AUTOMATIC,
               FAILOVER_MODE = MANUAL,
            SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
               );
GO

When I want to join from the remote node (mssql-remote-0), I use this command.

ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = NONE);

mssql-remote-0 node automatically gets the primary node's endpoint URL,

ENDPOINT_URL = N'tcp://mssql-primary-0.headless-service:5022'

Is there any option I can provide a different endpoint URL of the primary replia (mssql-primary-0), only for the mssql-remote-0 node?

Any insights or recommendations on achieving this setup would be highly appreciated.

2

1 Answer 1

5

Is it possible to explicitly use different endpoint URLs for the primary replica for different replicas in an SQL Server Availability Group?

No, it is not. There can only be one database mirroring endpoint (which is what AGs use) per instance.

I want to ensure that only the remote asynchronous replica communicates with the primary using the public IP, while the other replicas within the cluster continue to use the primary replica's DNS name in the cluster.

Set the endpoint URL as the DNS name and create a host record on the async replica for that name to whatever IP you are using.

2
  • Thanks for the nice answer. In my etc/hosts file of mssql-remote-0 node, If I map IP: 172.232.87.37 to hostname "mssql-primary-0.headless-service" Is it able to communicate with the primary? I have edited the post a bit. Can you see it again? Commented Dec 20, 2023 at 6:14
  • 1
    Yes, that should work, assuming your networking is correct. Commented Dec 20, 2023 at 12:04

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.