Partager via

Configurer un pair dans le cadre d’un groupe 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 décrit comment configurer ce scénario.

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 1 dans le groupe de disponibilité MyAG.
  • Node2 : réplica 2 dans le groupe de disponibilité MyAG.
  • MyAG : nom du groupe de disponibilité que vous créez sur Node1 et Node2.
  • MyAGListenerName : nom de l’écouteur du groupe de disponibilité.
  • Dist1 : nom de l’instance de serveur de distribution distant.
  • MyDBName : nom de la base de données.
  • P2P_MyDBName : nom de la publication.

Peer2

  • Node3 : serveur autonome hébergeant une instance par défaut de SQL Server.
  • Dist2 : nom de l’instance de serveur de distribution distant.
  • MyDBName : nom de la base de données.
  • P2P_MyDBName : nom de la publication.

Prérequis

  • Deux instances de SQL Server sur des serveurs physiques ou virtuels distincts pour héberger le groupe de disponibilité. Le groupe de disponibilité contient une base de données de pair.

  • Une instance de SQL Server pour héberger une autre 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)

  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)

Pour chaque hôte de réplica secondaire, 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é MyAGListername.

    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. Dans ce cas, n’ajoutez pas de crochets angulaires <>.

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 une instance autonome de SQL Server (Peer2) pour participer.

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

  1. Configurez la distribution sur le serveur de distribution.

    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. Configurer Node3 comme serveur de publication distant sur le serveur de distribution Dist2

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

Configurer le serveur de publication (Peer2)

  1. Sur Node3, configurez la distribution à distance.

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

  2. Sur Node3, activez la base de données pour la réplication.

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

Créer la publication de pair à pair (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

Créer un abonnement par émission de données de Peer1 vers Peer2

Cette étape crée un abonnement par émission de données du groupe de disponibilité vers l’instance autonome de SQL Server.

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

EXEC [MyDBName].dbo.sp_addsubscription 
   @publication = N'P2P_MyDBName'
 , @subscriber = N'Node3'
 , @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'Node3'
 , @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é

Pour créer un abonnement par émission de données de Peer2 vers l’écouteur du groupe de disponibilité, 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>'

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 des abonnements par émission de données pour les publications apparaissent comme serveurs liés.

EXEC sys.sp_addlinkedserver   
    @server = 'MySubscriber';

Étapes suivantes

Configurer la base de données de publication de pair à pair dans des groupes de disponibilité