patternsqlMinor
Can I use different endpoint URLs for the primary replica in a SQL Server Availability Group?
Viewed 0 times
endpointcantheprimarygroupsqlreplicadifferentforavailability
Problem
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,
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,
Solution
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.
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.
Context
StackExchange Database Administrators Q#334147, answer score: 5
Revisions (0)
No revisions yet.