Configurez les deux homologues dans les groupes de disponibilité

À partir de SQL Server 2019 (15.x) CU 13, une base de données appartenant à un groupe de disponibilité SQL Server Always On peut participer en tant que pair à une topologie de réplication transactionnelle pair-à-pair. Cet article décrit 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 & noms

Cette section décrit les rôles et noms des différents éléments participant à la topologie de réplication pour cet article.

Peer1

  • Node1 : Réplique principale du premier groupe de disponibilité
  • Node2 : Réplique secondaire pour le premier groupe de disponibilité
  • MyAG : Nom du groupe de disponibilité pour le premier groupe de disponibilité
  • MyDBName : base de données Peer1 . Base de données à publier
  • Dist1 : Distributeur à distance
  • P2P_MyDBName : Nom de la publication
  • MyAGListenerName : Auditeur du groupe de disponibilité

Peer2

  • Node3 : Réplique primaire pour le second groupe de disponibilité
  • Node4 : Réplique secondaire pour le second groupe de disponibilité
  • MyAG2 : nom du groupe de disponibilité pour le deuxième groupe de disponibilité
  • MyDBName : Base de données à publier
  • Dist2 : Distributeur à distance
  • P2P_MyDBName : Nom de la publication
  • MyAG2ListenerName: Écouteur de groupe de disponibilité

Conditions préalables

  • Quatre instances de SQL Server sur des serveurs physiques ou virtuels séparés pour héberger les groupes de disponibilité. Deux groupes de disponibilité contiennent chacun une base de données homologue.

  • Deux utilisations de SQL Server pour héberger les bases de données des distributeurs.

  • Toutes les instances serveur nécessitent une édition prise en charge - édition Enterprise ou édition Développeur.

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

  • Une connectivité réseau et une bande passante suffisantes entre toutes les instances.

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

    Pour vérifier si la réplication est installée sur une instance, lancez la requête suivante :

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

    Note

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

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

Configurez le distributeur et l’éditeur distant (Peer1)

Cette section décrit comment configurer le premier homologue (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 l’éditeur distant utilise pour se connecter au distributeur. Utilisez ce mot de passe chez chaque éditeur distant lorsque vous configurez le distributeur 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 le serveur de publication distant de Node1 et Node2.

    Le @security_mode détermine la manière dont les agents de réplication se connectent au nœud principal actuel.

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

    Note

    Si des agents de réplication modifiés s’exécutent sur un ordinateur autre que le distributeur, l’utilisation de Authentification Windows pour la connexion au principal nécessite une authentification Kerberos pour la communication entre les ordinateurs hôtes répliques. L'utilisation d'une connexion SQL Server pour la connexion au principal actuel ne nécessite pas d'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

Configurez l’éditeur au niveau de l’éditeur d’origine (Node1)

  1. Configurez l’éditeur d’origine de la distribution à distance (Node1). Spécifiez la même valeur pour @password que celle utilisée lors de l’exécution de sp_adddistributor sur le distributeur pour configurer 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';

Configurez l’hôte réplique secondaire en tant que publieur de réplication (Node2)

Sur chaque hôte de réplica secondaire (Node2) du premier groupe de disponibilité, configurez la distribution. Spécifiez pour @password la même valeur que celle utilisée lors de l’exécution de sp_adddistributor sur le distributeur pour configurer la distribution.

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

Faites de la base de données une partie du groupe de disponibilité et créez l’auditeur (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 à la réplique primaire courante en utilisant l’écouteur. L’exemple suivant crée un auditeur nommé MyAGListenerName.

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

    Note

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

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>';

Note

Dans le script ci-dessus ,<port> , c’est optionnel. C’est seulement obligatoire si vous utilisez des ports non par défaut. N’incluez pas les crochets angulaires <>.

Créer une publication pair-à-pair (Peer1) sur l’éditeur 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 la publication peer-to-peer compatible avec le groupe de disponibilité (Peer1)

Sur l’éditeur 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

Note

Dans le script ci-dessus ,<port> , c’est optionnel. C’est seulement obligatoire si vous utilisez des ports non 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 consistent à configurer un groupe de disponibilité séparé comme second pair (Peer2) dans la topologie de réplication pair-à-pair.

Configurez le distributeur et l’éditeur distant (Peer2)

Cette section décrit comment mettre en place le second homologue (Peer2) dans un groupe de disponibilité différent.

  1. Exécuter sp_adddistributor pour configurer la distribution sur Dist2. Utilisez @password = pour spécifier un mot de passe que l’éditeur distant utilise pour se connecter au distributeur. Utilisez ce mot de passe chez chaque éditeur distant lorsque vous configurez le distributeur 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 le publicateur distant Node4 .

    Le @security_mode détermine la manière dont les agents de réplication se connectent au nœud principal actuel.

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

    Note

    Si des agents de réplication modifiés s’exécutent sur un ordinateur autre que le distributeur, l’utilisation de Authentification Windows pour la connexion au principal nécessite une authentification Kerberos pour la communication entre les ordinateurs hôtes répliques. L'utilisation d'une connexion SQL Server pour la connexion au principal actuel ne nécessite pas d'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

Configurez l’éditeur (Peer2)

  1. Configurez la distribution à distance sur (Node3). Spécifiez pour @password la même valeur que celle utilisée lors de l’exécution de sp_adddistributor sur le distributeur pour configurer 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';

Configurez l’hôte réplique secondaire comme publicateur de réplication (Node4)

À chaque hôte réplique secondaire (Node4) pour le second groupe de disponibilité, configurez la distribution. Spécifiez la même valeur pour @password que celle utilisée lors de l’exécution de sp_adddistributor sur le distributeur pour configurer la distribution.

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

Faites de la base de données une partie du groupe de disponibilité et créez l’auditeur (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 à la réplique primaire courante en utilisant l’écouteur. L’exemple suivant crée un auditeur nommé MyAG2ListenerName.

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

    Note

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

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>';

Note

Dans le script ci-dessus ,<port> , c’est optionnel. C’est seulement obligatoire si vous utilisez des ports non par défaut. N’incluez pas les crochets angulaires <>.

Créer une publication pair-à-pair (Peer2)

Le script suivant crée la publication pour Peer2.

Sur Node3 , exécutez la commande suivante pour créer la publication 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

-- 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 la publication peer-to-peer compatible avec le groupe de disponibilité (Peer2)

Sur l’éditeur 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

Note

Dans le script ci-dessus ,<port> , c’est optionnel. C’est seulement obligatoire si vous utilisez des ports non 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 de type push de Peer1 vers l’écouteur du groupe de disponibilité Peer2, exécutez la commande suivante sur Node1.

Exécutez le script suivant sur Node1. Cela suppose que Node1 exécute la réplique principale.

Important

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

@subscriber = N'MyAGListenerName,<port>'

Note

Dans le script ci-dessus ,<port> , c’est optionnel. C’est seulement obligatoire si vous utilisez des ports non par défaut. N’incluez pas les 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 push 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>'

Note

Dans le script ci-dessus ,<port> , c’est optionnel. C’est seulement obligatoire si vous utilisez des ports non par défaut. N’incluez pas les crochets d’angle <>.

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';
  • Last updated on