Always On: changing the HADR endpoint and replica ENDPOINT_URL
Recreate the Hadr_endpoint and update the ENDPOINT_URL of the replicas of a SQL Server availability group.
This article explains how to change the database mirroring endpoint (Hadr_endpoint) of an Always On availability group, for example when the replica IP addresses change: recreate the endpoint on each replica, then update the ENDPOINT_URL of every replica in every availability group.
The example uses two replicas, sqlnode01 and sqlnode02, listening on port 5022.
1. Check the current endpoint
USE master;
GO
SELECT name, state_desc, port FROM sys.tcp_endpoints WHERE name = 'Hadr_endpoint';
GO
2. Recreate the endpoint on each replica
Run the following in SQLCMD mode (:CONNECT switches the server). Adapt the LISTENER_IP of each replica and the service account that is granted CONNECT.
:CONNECT sqlnode02
DROP ENDPOINT [Hadr_endpoint];
GO
CREATE ENDPOINT [Hadr_endpoint]
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (192.0.2.12))
FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES);
GO
-- configure endpoint security
IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
BEGIN
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
END
GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [CONTOSO\svc_sql];
GO
:CONNECT sqlnode01
DROP ENDPOINT [Hadr_endpoint];
GO
CREATE ENDPOINT [Hadr_endpoint]
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (192.0.2.13))
FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES);
GO
-- configure endpoint security
IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
BEGIN
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
END
GO
USE [master];
GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [CONTOSO\svc_sql];
GO
3. List the availability groups and endpoints
Availability groups whose primary replica is the current server:
SELECT Groups.[Name] AS AGname, *
FROM sys.dm_hadr_availability_group_states States
INNER JOIN master.sys.availability_groups Groups ON States.group_id = Groups.group_id
WHERE States.primary_replica = @@Servername;
Endpoint URL of each replica, per database:
SELECT
ar.replica_server_name,
adc.database_name,
ar.endpoint_url,
ag.name AS ag_name,
drs.is_local,
drs.is_primary_replica,
drs.synchronization_state_desc,
drs.is_commit_participant,
drs.synchronization_health_desc,
drs.recovery_lsn,
drs.truncation_lsn
FROM sys.dm_hadr_database_replica_states AS drs
INNER JOIN sys.availability_databases_cluster AS adc ON drs.group_id = adc.group_id AND drs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag ON ag.group_id = drs.group_id
INNER JOIN sys.availability_replicas AS ar
ON drs.group_id = ar.group_id AND drs.replica_id = ar.replica_id
ORDER BY
ag.name,
ar.replica_server_name,
adc.database_name;
Same query restricted to the availability groups where the current server is primary:
SELECT
adc.database_name,
ag.name AS ag_name,
ar.endpoint_url
FROM sys.dm_hadr_database_replica_states AS drs
INNER JOIN sys.availability_databases_cluster AS adc ON drs.group_id = adc.group_id AND drs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag ON ag.group_id = drs.group_id
INNER JOIN sys.dm_hadr_availability_group_states States ON States.group_id = ag.group_id
INNER JOIN sys.availability_replicas AS ar ON drs.group_id = ar.group_id AND drs.replica_id = ar.replica_id
WHERE States.primary_replica = @@Servername AND is_primary_replica = 1
ORDER BY
ag.name,
ar.replica_server_name,
adc.database_name;
4. Generate the ALTER AVAILABILITY GROUP statements
This script loops over the availability groups that have the current server as primary and prints the MODIFY REPLICA statements for two replicas. Adapt the replica names and IP addresses, then review the output before running it.
DECLARE @ag_Name varchar(300)
DECLARE cur_ag_name CURSOR FOR
SELECT
ag.name AS ag_name
FROM sys.dm_hadr_database_replica_states AS drs
INNER JOIN sys.availability_databases_cluster AS adc ON drs.group_id = adc.group_id AND drs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag ON ag.group_id = drs.group_id
INNER JOIN sys.dm_hadr_availability_group_states States ON States.group_id = ag.group_id
INNER JOIN sys.availability_replicas AS ar ON drs.group_id = ar.group_id AND drs.replica_id = ar.replica_id
WHERE States.primary_replica = @@Servername AND is_primary_replica = 1
ORDER BY
ag.name,
ar.replica_server_name,
adc.database_name
OPEN cur_ag_name
FETCH cur_ag_name INTO @ag_Name
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT '---' + @ag_Name + ' sqlnode01'
PRINT 'ALTER AVAILABILITY GROUP [' + @ag_Name + ']'
PRINT 'MODIFY REPLICA ON ''sqlnode01'''
PRINT 'WITH '
PRINT '( '
PRINT ' ENDPOINT_URL = N''TCP://192.0.2.13:5022'''
PRINT ');'
PRINT 'GO'
PRINT '---' + @ag_Name + ' sqlnode02'
PRINT 'ALTER AVAILABILITY GROUP [' + @ag_Name + ']'
PRINT 'MODIFY REPLICA ON ''sqlnode02'''
PRINT 'WITH '
PRINT '( '
PRINT ' ENDPOINT_URL = N''TCP://192.0.2.12:5022'''
PRINT ');'
PRINT 'GO'
FETCH cur_ag_name INTO @ag_Name
END
CLOSE cur_ag_name
DEALLOCATE cur_ag_name
Resulting statements, to run for every availability group on all replicas:
ALTER AVAILABILITY GROUP AG1
MODIFY REPLICA ON 'sqlnode02'
WITH
(
ENDPOINT_URL = N'TCP://192.0.2.12:5022'
);
GO
ALTER AVAILABILITY GROUP AG1
MODIFY REPLICA ON 'sqlnode01'
WITH
(
ENDPOINT_URL = N'TCP://192.0.2.13:5022'
);
GO
-- with a third replica
ALTER AVAILABILITY GROUP AG1
MODIFY REPLICA ON 'sqlnode03'
WITH
(
ENDPOINT_URL = N'TCP://192.0.2.16:5022'
);
GO
5. Check and fix permissions on an existing endpoint
List who has been granted what on the endpoint:
USE [master];
SELECT ep.name,
sp.STATE,
CONVERT(nvarchar(38), SUSER_NAME(sp.grantor_principal_id)) AS [GRANT BY],
sp.TYPE AS PERMISSION,
CONVERT(nvarchar(46), SUSER_NAME(sp.grantee_principal_id)) AS [GRANT TO]
FROM sys.server_permissions sp, sys.endpoints ep
WHERE sp.major_id = ep.endpoint_id AND [name] = 'Hadr_endpoint'
Change the endpoint owner and grant CONNECT to the SQL Server service account:
BEGIN TRANSACTION
USE [master];
ALTER AUTHORIZATION ON ENDPOINT::Hadr_endpoint TO sa;
GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [CONTOSO\svc_sql];
COMMIT TRANSACTION
Notes
- Dropping and recreating the endpoint interrupts replication between the replicas; plan it in a maintenance window and check synchronisation health afterwards.
- Host names, IP addresses, the group name and the service account are generic examples.