Partager via

Configurer les deux pairs dans des groupes de disponibilité

  • Article

À compter de SQL Server 2019 (15.x) CU 13, une base de données appartenant à un groupe de disponibilité Always On SQL Server peut participer en tant qu’homologue dans une topologie de réplication transactionnelle d’égal à égal. Cet article explique comment configurer ce scénario avec deux pairs, chacun dans son propre groupe de disponibilité.

Les scripts de cet exemple utilisent des procédures stockées T-SQL.

Rôles et noms

Cette section décrit les rôles et les noms des différents éléments qui participent à la topologie de réplication utilisée dans cet article.

Peer1

  • Node1 : réplica principal du premier groupe de disponibilité
  • Node2 : réplica secondaire du premier groupe de disponibilité
  • MyAG : nom du premier groupe de disponibilité
  • MyDBName : base de données de Peer1. Base de données à publier
  • Dist1 : serveur de distribution distant
  • P2P_MyDBName : nom de la publication
  • MyAGListenerName : écouteur du groupe de disponibilité

Peer2

  • Node3 : réplica principal du deuxième groupe de disponibilité
  • Node4 : réplica secondaire du deuxième groupe de disponibilité
  • MyAG2 : nom du deuxième groupe de disponibilité
  • MyDBName : base de données à publier
  • Dist2 : serveur de distribution distant
  • P2P_MyDBName : nom de la publication
  • MyAG2ListenerName : écouteur du groupe de disponibilité

Prérequis

  • Quatre instances de SQL Server sur des serveurs physiques ou virtuels distincts pour héberger les groupes de disponibilité. Deux groupes de disponibilité contenant chacun une base de données de pair.

  • Deux instances de SQL Server pour héberger les bases de données du serveur de distribution.

  • Toutes les instances de serveur nécessitent une édition prise en charge : édition Entreprise ou Développeur.

  • Toutes les instances de serveur nécessitent une version prise en charge : SQL Server 2019 (15.x) CU13 ou version ultérieure.

  • Connectivité réseau et bande passante suffisantes entre toutes les instances.

  • Installez la réplication SQL Server sur toutes les instances de SQL Server.

    Pour voir si la réplication est installée sur une instance, exécutez la requête suivante :

    USE master;   
GO   
DECLARE @installed int;   
EXEC @installed = sys.sp_MS_replication_installed;   
SELECT @installed;

    Remarque

    Pour éviter un point de défaillance unique pour la base de données de distribution, utilisez un serveur de distribution distant pour chaque pair.

    Dans le cas d’un environnement de démonstration ou de test, vous pouvez configurer les bases de données de distribution sur une seule instance.

Configurer le serveur de distribution et le serveur de publication distant (Peer1)

Cette section décrit comment configurer le premier pair (Peer1) dans un groupe de disponibilité.

  1. Exécutez sp_adddistributor pour configurer la distribution sur Dist1. Utilisez @password = pour spécifier un mot de passe que le serveur de publication distant utilise pour se connecter au serveur de distribution. Utilisez ce mot de passe sur chaque serveur de publication distant quand vous configurez le serveur de distribution distant.

    USE master;  
GO  
EXEC sys.sp_adddistributor  
 @distributor = 'Dist1',  
 @password = '<Strong password for distributor>';

  2. Créez la base de données de distribution sur le serveur de distribution.

    USE master;
GO  
EXEC sys.sp_adddistributiondb  
 @database = 'distribution',  
 @security_mode = 1;

  3. Configurez Node1 et Node2 comme serveurs de publication distants.

    @security_mode détermine la façon dont les agents de réplication se connectent au serveur principal actuel.

    • 1 = Authentification Windows.
    • 0 = Authentification SQL Server. Nécessite @login et @password. La connexion et le mot de passe spécifiés doivent être valides sur chaque réplica secondaire.

    Remarque

    Si des agents de réplication modifiés s’exécutent sur un ordinateur autre que le serveur de distribution, l’utilisation de l’authentification Windows pour la connexion au serveur principal nécessite de configurer l’authentification Kerberos afin d’assurer la communication des ordinateurs hôtes de réplica. L’utilisation d’un compte de connexion SQL Server pour la connexion au serveur principal actuel ne nécessite pas l’authentification Kerberos.

    USE master;  
GO  
EXEC sys.sp_adddistpublisher  
 @publisher = 'Node1',  
 @distribution_db = 'distribution',  
 @working_directory = '\\MyReplShare\WorkingDir',  
 @security_mode = 1
USE master;  
GO  
EXEC sys.sp_adddistpublisher  
 @publisher = 'Node2',  
 @distribution_db = 'distribution',  
 @working_directory = '\\MyReplShare\WorkingDir',  
 @security_mode = 1

Configurer le serveur de publication sur le serveur de publication d’origine (Node1)

  1. Configurer le serveur de publication d’origine pour la distribution à distance (Node1). Spécifiez la même valeur pour @password que celle utilisée quand sp_adddistributor a été exécuté sur le serveur de distribution pendant la configuration de la distribution.

    EXEC sys.sp_adddistributor  
@distributor = 'Dist1',  
@password = '<Password used when running sp_adddistributor on distributor server>'

  2. Activez la base de données pour la réplication.

    USE master;  
GO  
EXEC sys.sp_replicationdboption  
 @dbname = 'MyDBName',  
 @optname = 'publish',  
 @value = 'true';

Configurer les hôtes de réplica secondaire comme serveurs de publication de réplication (Node2)

Sur chaque hôte de réplica secondaire (Node2) du premier groupe de disponibilité, configurez la distribution. Spécifiez la même valeur pour @password que celle utilisée quand sp_adddistributor a été exécuté sur le serveur de distribution pendant la configuration de la distribution.

EXEC sys.sp_adddistributor  
   @distributor = 'Dist1',  
   @password = '<Password used when running sp_adddistributor on distributor server>'

Intégrer la base de données au groupe de disponibilité et créer l’écouteur (Peer1)

  1. Sur le réplica principal prévu, créez le groupe de disponibilité avec la base de données comme base de données membre.

  2. Créez un écouteur DNS pour le groupe de disponibilité. L’agent de réplication se connecte au réplica principal actuel à l’aide de l’écouteur. L’exemple suivant crée un écouteur nommé MyAGListenerName.

    ALTER AVAILABILITY GROUP 'MyAG'
ADD LISTENER 'MyAGListenerName' (WITH IP (('<ip address>', '<subnet mask>') [, PORT = <listener_port>]));

    Remarque

    Dans le script ci-dessus, les informations entre crochets ([ ... ]) sont facultatives. Utilisez-les pour spécifier une valeur non définie par défaut pour le port TCP. N’incluez pas les chevrons.

Rediriger le serveur de publication d’origine sur le nom de l’écouteur du groupe de disponibilité (Peer1)

Sur le serveur de distribution de Peer1, redirigez le serveur de publication d’origine sur le nom de l’écouteur du groupe de disponibilité.

USE distribution;   
GO   
EXEC sys.sp_redirect_publisher    
@original_publisher = 'Node1',   
@publisher_db = 'MyDBName',   
@redirected_publisher = 'MyAGListenerName,<port>';

Remarque

Dans le script ci-dessus, ,<port> est facultatif. Vous en avez besoin seulement si vous utilisez des ports autres que ceux par défaut. Dans ce cas, n’ajoutez pas de crochets angulaires <>.

Créer une publication de pair à pair (Peer1) sur le serveur de publication d’origine (Node1)

Le script suivant crée la publication pour Peer1.

EXEC master..sp_replicationdboption  @dbname=  'MyDBName'   
        ,@optname=  'publish'   
        ,@value=  'true'  
GO
DECLARE @publisher_security_mode smallint = 1
EXEC [MyDBName].dbo.sp_addlogreader_agent @publisher_security_mode = @publisher_security_mode
GO
DECLARE @allow_dts nvarchar(5) = N'false'
DECLARE @allow_pull nvarchar(5) = N'true'
DECLARE @allow_push nvarchar(5) = N'true'
DECLARE @description nvarchar(255) = N'Peer-to-Peer publication of database MyDBName from Node1'
DECLARE @enabled_for_p2p nvarchar(5) = N'true'
DECLARE @independent_agent nvarchar(5) = N'true'
DECLARE @p2p_conflictdetection nvarchar(5) = N'true'
DECLARE @p2p_originator_id int = 100
DECLARE @publication nvarchar(256) = N'P2P_MyDBName'
DECLARE @repl_freq nvarchar(10) = N'continuous'
DECLARE @restricted nvarchar(10) = N'false'
DECLARE @status nvarchar(8) = N'active'
DECLARE @sync_method nvarchar(40) = N'NATIVE'
EXEC [MyDBName].dbo.sp_addpublication @allow_dts = @allow_dts, @allow_pull = @allow_pull, @allow_push = @allow_push, @description = @description, @enabled_for_p2p = @enabled_for_p2p, @independent_agent = @independent_agent, @p2p_conflictdetection = @p2p_conflictdetection, @p2p_originator_id = @p2p_originator_id, @publication = @publication, @repl_freq = @repl_freq, @restricted = @restricted, @status = @status, @sync_method = @sync_method
GO
DECLARE @article nvarchar(256) = N'tbl0'
DECLARE @description nvarchar(255) = N'Article for dbo.tbl0'
DECLARE @destination_table nvarchar(256) = N'tbl0'
DECLARE @publication nvarchar(256) = N'P2P_MyDBName'
DECLARE @source_object nvarchar(256) = N'tbl0'
DECLARE @source_owner nvarchar(256) = N'dbo'
DECLARE @type nvarchar(256) = N'logbased'
EXEC [MyDBName].dbo.sp_addarticle @article = @article, @description = @description, @destination_table = @destination_table, @publication = @publication, @source_object = @source_object, @source_owner = @source_owner, @type = @type
GO
DECLARE @article nvarchar(256) = N'tbl1'
DECLARE @description nvarchar(255) = N'Article for dbo.tbl1'
DECLARE @destination_table nvarchar(256) = N'tbl1'
DECLARE @publication nvarchar(256) = N'P2P_MyDBName'
DECLARE @source_object nvarchar(256) = N'tbl1'
DECLARE @source_owner nvarchar(256) = N'dbo'
DECLARE @type nvarchar(256) = N'logbased'
EXEC [MyDBName].dbo.sp_addarticle @article = @article, @description = @description, @destination_table = @destination_table, @publication = @publication, @source_object = @source_object, 
@source_owner = @source_owner, @type = @type
GO

Rendre compatible la publication de pair à pair avec le groupe de disponibilité (Peer1)

Sur le serveur de publication d’origine (Node1), exécutez le script suivant pour rendre la publication compatible avec le groupe de disponibilité :

USE MyDBName
GO
DECLARE @publication sysname = N'P2P_MyDBName' 
DECLARE @property sysname = N'redirected_publisher' 
DECLARE @value sysname = N'MyAGListenerName,<port>' 
EXEC MyDBName..sp_changepublication @publication = @publication, @property = @property, @value = @value 
GO

Remarque

Dans le script ci-dessus, ,<port> est facultatif. Vous en avez besoin seulement si vous utilisez des ports autres que ceux par défaut.

Une fois que vous avez effectué les étapes ci-dessus, le groupe de disponibilité est prêt à participer à la topologie de pair à pair. Les étapes suivantes configurent un groupe de disponibilité distinct comme deuxième pair (Peer2) dans la topologie de réplication de pair à pair.

Configurer le serveur de distribution et le serveur de publication distant (Peer2)

Cette section décrit comment configurer le deuxième pair (Peer2) dans un groupe de disponibilité différent.

  1. Exécutez sp_adddistributor pour configurer la distribution sur Dist2. Utilisez @password = pour spécifier un mot de passe que le serveur de publication distant utilise pour se connecter au serveur de distribution. Utilisez ce mot de passe sur chaque serveur de publication distant quand vous configurez le serveur de distribution distant.

    USE master;  
GO  
EXEC sys.sp_adddistributor  
 @distributor = 'Dist2',  
 @password = '<Strong password for distributor>';

  2. Créez la base de données de distribution sur le serveur de distribution.

    USE master;
GO  
EXEC sys.sp_adddistributiondb  
 @database = 'distribution',  
 @security_mode = 1;

  3. Configurez Node3 et Node4 comme serveurs de publication distants.

    @security_mode détermine la façon dont les agents de réplication se connectent au serveur principal actuel.

    • 1 = Authentification Windows.
    • 0 = Authentification SQL Server. Nécessite @login et @password. La connexion et le mot de passe spécifiés doivent être valides sur chaque réplica secondaire.

    Remarque

    Si des agents de réplication modifiés s’exécutent sur un ordinateur autre que le serveur de distribution, l’utilisation de l’authentification Windows pour la connexion au serveur principal nécessite de configurer l’authentification Kerberos afin d’assurer la communication des ordinateurs hôtes de réplica. L’utilisation d’un compte de connexion SQL Server pour la connexion au serveur principal actuel ne nécessite pas l’authentification Kerberos.

    USE master;  
GO  
EXEC sys.sp_adddistpublisher  
 @publisher = 'Node3',  
 @distribution_db = 'distribution',  
 @working_directory = '\\MyReplShare\WorkingDir2',  
 @security_mode = 1
USE master;  
GO  
EXEC sys.sp_adddistpublisher  
 @publisher = 'Node4',  
 @distribution_db = 'distribution',  
 @working_directory = '\\MyReplShare\WorkingDir2',  
 @security_mode = 1

Configurer le serveur de publication (Peer2)

  1. Configurez la distribution à distance sur Node3. Spécifiez la même valeur pour @password que celle utilisée quand sp_adddistributor a été exécuté sur le serveur de distribution pendant la configuration de la distribution.

    EXEC sys.sp_adddistributor  
@distributor = 'Dist2',  
@password = '<Password used when running sp_adddistributor on distributor server>'

  2. Activez la base de données pour la réplication.

    USE master;  
GO  
EXEC sys.sp_replicationdboption  
 @dbname = 'MyDBName',  
 @optname = 'publish',  
 @value = 'true';

Configurer l’hôte de réplica secondaire comme serveur de publication de réplication (Node4)

Sur chaque hôte de réplica secondaire (Node4) du deuxième groupe de disponibilité, configurez la distribution. Spécifiez la même valeur pour @password que celle utilisée quand sp_adddistributor a été exécuté sur le serveur de distribution pendant la configuration de la distribution.

EXEC sys.sp_adddistributor  
   @distributor = 'Dist2',  
   @password = '<Password used when running sp_adddistributor on distributor server>'

Intégrer la base de données au groupe de disponibilité et créer l’écouteur (Peer2)

  1. Sur le réplica principal prévu, créez le groupe de disponibilité avec la base de données comme base de données membre.

  2. Créez un écouteur DNS pour le groupe de disponibilité. L’agent de réplication se connecte au réplica principal actuel à l’aide de l’écouteur. L’exemple suivant crée un écouteur nommé MyAG2ListenerName.

    ALTER AVAILABILITY GROUP 'MyAG2'
ADD LISTENER 'MyAG2ListenerName' (WITH IP (('<ip address>', '<subnet mask>') [, PORT = <listener_port>]));

    Remarque

    Dans le script ci-dessus, les informations entre crochets ([ ... ]) sont facultatives. Utilisez-les pour spécifier une valeur non définie par défaut pour le port TCP. N’incluez pas les chevrons.

Rediriger le serveur de publication d’origine sur le nom de l’écouteur du groupe de disponibilité (Peer2)

Sur le serveur de distribution de Peer2, redirigez le serveur de publication d’origine sur le nom de l’écouteur du groupe de disponibilité.

USE distribution;   
GO   
EXEC sys.sp_redirect_publisher    
@original_publisher = 'Node3',   
@publisher_db = 'MyDBName',   
@redirected_publisher = 'MyAG2ListenerName,<port>';

Remarque

Dans le script ci-dessus, ,<port> est facultatif. Vous en avez besoin seulement si vous utilisez des ports autres que ceux par défaut. Dans ce cas, n’ajoutez pas de crochets angulaires <>.

Créer la publication de pair à pair (Peer2)

Le script suivant crée la publication pour Peer2.

Sur Node3, exécutez la commande suivante pour créer la publication de pair à pair.

EXEC master..sp_replicationdboption  @dbname=  'MyDBName'   
        ,@optname=  'publish'   
        ,@value=  'true'  
GO
DECLARE @publisher_security_mode smallint = 1
EXEC [MyDBName].dbo.sp_addlogreader_agent @publisher_security_mode = @publisher_security_mode
GO

-- Note – Make sure that the value for @p2p_originator_id is different from Peer1.
DECLARE @allow_dts nvarchar(5) = N'false'
DECLARE @allow_pull nvarchar(5) = N'true'
DECLARE @allow_push nvarchar(5) = N'true'
DECLARE @description nvarchar(255) = N'Peer-to-Peer publication of database MyDBName from Node3'
DECLARE @enabled_for_p2p nvarchar(5) = N'true'
DECLARE @independent_agent nvarchar(5) = N'true'
DECLARE @p2p_conflictdetection nvarchar(5) = N'true'
DECLARE @p2p_originator_id int = 1
DECLARE @publication nvarchar(256) = N'P2P_MyDBName'
DECLARE @repl_freq nvarchar(10) = N'continuous'
DECLARE @restricted nvarchar(10) = N'false'
DECLARE @status nvarchar(8) = N'active'
DECLARE @sync_method nvarchar(40) = N'NATIVE'
EXEC [MyDBName].dbo.sp_addpublication @allow_dts = @allow_dts, @allow_pull = @allow_pull, @allow_push = @allow_push, @description = @description, @enabled_for_p2p = @enabled_for_p2p, @independent_agent = @independent_agent, @p2p_conflictdetection = @p2p_conflictdetection, @p2p_originator_id = @p2p_originator_id, @publication = @publication, @repl_freq = @repl_freq, @restricted = @restricted, @status = @status, @sync_method = @sync_method
GO
DECLARE @article nvarchar(256) = N'tbl0'
DECLARE @description nvarchar(255) = N'Article for dbo.tbl0'
DECLARE @destination_table nvarchar(256) = N'tbl0'
DECLARE @publication nvarchar(256) = N'P2P_MyDBName'
DECLARE @source_object nvarchar(256) = N'tbl0'
DECLARE @source_owner nvarchar(256) = N'dbo'
DECLARE @type nvarchar(256) = N'logbased'
EXEC [MyDBName].dbo.sp_addarticle @article = @article, @description = @description, @destination_table = @destination_table, @publication = @publication, @source_object = @source_object, @source_owner = @source_owner, @type = @type
GO

DECLARE @article nvarchar(256) = N'tbl1'
DECLARE @description nvarchar(255) = N'Article for dbo.tbl1'
DECLARE @destination_table nvarchar(256) = N'tbl1'
DECLARE @publication nvarchar(256) = N'P2P_MyDBName'
DECLARE @source_object nvarchar(256) = N'tbl1'
DECLARE @source_owner nvarchar(256) = N'dbo'
DECLARE @type nvarchar(256) = N'logbased'
EXEC [MyDBName].dbo.sp_addarticle @article = @article, @description = @description, @destination_table = @destination_table, @publication = @publication, @source_object = @source_object, 
@source_owner = @source_owner, @type = @type
GO

Rendre compatible la publication de pair à pair avec le groupe de disponibilité (Peer2)

Sur le serveur de publication d’origine (Node3), exécutez le script suivant pour rendre la publication compatible avec le groupe de disponibilité :

USE MyDBName
GO
DECLARE @publication sysname = N'P2P_MyDBName' 
DECLARE @property sysname = N'redirected_publisher' 
DECLARE @value sysname = N'MyAG2ListenerName,<port>' 
EXEC MyDBName..sp_changepublication @publication = @publication, @property = @property, @value = @value 
GO

Remarque

Dans le script ci-dessus, ,<port> est facultatif. Vous en avez besoin seulement si vous utilisez des ports autres que ceux par défaut.

Créer un abonnement par émission de données de Peer1 vers l’écouteur du groupe de disponibilité de Peer2

Pour créer un abonnement par émission de données de Peer1 vers l’écouteur du groupe de disponibilité de Peer2, exécutez la commande suivante sur Node1.

Exécutez le script suivant sur Node1. Cela suppose que Node1 exécute le réplica principal.

Important

Le script ci-dessous spécifie le nom de l’écouteur du groupe de disponibilité pour l’abonné.

@subscriber = N'MyAGListenerName,<port>'

Remarque

Dans le script ci-dessus, ,<port> est facultatif. Vous en avez besoin seulement si vous utilisez des ports autres que ceux par défaut. Dans ce cas, n’ajoutez pas de crochets angulaires <>.

EXEC [MyDBName].dbo.sp_addsubscription 
   @publication = N'P2P_MyDBName'
 , @subscriber = N'MyAG2Listener,<port>' 
 , @destination_db = N'MyDBName'
 , @subscription_type = N'push'
 , @sync_type = N'replication support only'
GO

EXEC [MyDBName].dbo.sp_addpushsubscription_agent 
   @publication = N'P2P_MyDBName'
 , @subscriber = N'MyAG2Listener,<port>' 
 , @subscriber_db = N'MyDBName'
 , @job_login = null 
 , @job_password = null
 , @subscriber_security_mode = 1
 , @frequency_type = 64
 , @frequency_interval = 1
 , @frequency_relative_interval = 1
 , @frequency_recurrence_factor = 0
 , @frequency_subday = 4
 , @frequency_subday_interval = 5
 , @active_start_time_of_day = 0
 , @active_end_time_of_day = 235959
 , @active_start_date = 0
 , @active_end_date = 0
 , @dts_package_location = N'Distributor'
GO

Créer un abonnement par émission de données de Peer2 vers l’écouteur du groupe de disponibilité (Peer1)

Pour créer un abonnement par émission de données de Peer2 vers l’écouteur du groupe de disponibilité (Peer1), exécutez la commande suivante sur Node3.

Important

Le script ci-dessous spécifie le nom de l’écouteur du groupe de disponibilité pour l’abonné.

@subscriber = N'MyAGListenerName,<port>'

Remarque

Dans le script ci-dessus, ,<port> est facultatif. Vous en avez besoin seulement si vous utilisez des ports autres que ceux par défaut. Dans ce cas, n’ajoutez pas de crochets angulaires <>.

EXEC [MyDBName].dbo.sp_addsubscription 
   @publication = N'P2P_MyDBName'
 , @subscriber = N'MyAGListenerName,<port>'
 , @destination_db = N'MyDBName'
 , @subscription_type = N'push'
 , @sync_type = N'replication support only'
GO

EXEC [MyDBName].dbo.sp_addpushsubscription_agent 
   @publication = N'P2P_MyDBName'
 , @subscriber = N'MyAGListenerName,<port>'
 , @subscriber_db = N'MyDBName'
 , @job_login = null
 , @job_password = null
 , @subscriber_security_mode = 1
 , @frequency_type = 64
 , @frequency_interval = 1
 , @frequency_relative_interval = 1
 , @frequency_recurrence_factor = 0
 , @frequency_subday = 4
 , @frequency_subday_interval = 5
 , @active_start_time_of_day = 0
 , @active_end_time_of_day = 235959
 , @active_start_date = 0
 , @active_end_date = 0
 , @dts_package_location = N'Distributor'
GO

Configurer les serveurs liés

Sur chaque hôte de réplica secondaire, vérifiez que les abonnés de l’abonnement par émission de données des publications de la base de données apparaissent comme des serveurs liés.

EXEC sys.sp_addlinkedserver   
    @server = 'MySubscriber';

Étapes suivantes