LLI Wiki — My Digital Knowledge Base

Always On : changer l'endpoint HADR et les ENDPOINT_URL des réplicas

Recréer l'endpoint Hadr_endpoint et mettre à jour l'ENDPOINT_URL des réplicas d'un groupe de disponibilité SQL Server.

Cet article explique comment modifier l'endpoint de mise en miroir (Hadr_endpoint) d'un groupe de disponibilité Always On, par exemple lorsque les adresses IP des réplicas changent : recréer l'endpoint sur chaque réplica, puis mettre à jour l'ENDPOINT_URL de chaque réplica dans chaque groupe de disponibilité.

L'exemple utilise deux réplicas, sqlnode01 et sqlnode02, à l'écoute sur le port 5022.

1. Vérifier l'endpoint actuel

USE master;
GO
SELECT name, state_desc, port FROM sys.tcp_endpoints WHERE name = 'Hadr_endpoint';
GO

2. Recréer l'endpoint sur chaque réplica

À exécuter en mode SQLCMD (:CONNECT change de serveur). Adaptez le LISTENER_IP de chaque réplica et le compte de service auquel CONNECT est accordé.

: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. Lister les groupes de disponibilité et les endpoints

Groupes de disponibilité dont le réplica primaire est le serveur courant :

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;

URL d'endpoint de chaque réplica, par base de données :

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;

Même requête limitée aux groupes de disponibilité dont le serveur courant est primaire :

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. Générer les instructions ALTER AVAILABILITY GROUP

Ce script parcourt les groupes de disponibilité dont le serveur courant est primaire et affiche les instructions MODIFY REPLICA pour deux réplicas. Adaptez les noms de réplicas et les adresses IP, puis relisez le résultat avant de l'exécuter.

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

Instructions obtenues, à exécuter pour chaque groupe de disponibilité sur tous les réplicas :

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
-- avec un troisième réplica
ALTER AVAILABILITY GROUP AG1
MODIFY REPLICA ON 'sqlnode03'
WITH
(
    ENDPOINT_URL = N'TCP://192.0.2.16:5022'
);
GO

5. Vérifier et corriger les permissions d'un endpoint existant

Lister qui a reçu quelles permissions sur l'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'

Changer le propriétaire de l'endpoint et accorder CONNECT au compte de service SQL Server :

BEGIN TRANSACTION
USE [master];
ALTER AUTHORIZATION ON ENDPOINT::Hadr_endpoint TO sa;
GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [CONTOSO\svc_sql];
COMMIT TRANSACTION

Remarques

Mastodon LLIMastodon SocialFosstodon