Configurer un homologue comme faisant partie d’un groupe 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 dans une topologie de réplication transactionnelle pair-à-pair. 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éplique 1 dans le groupe de disponibilité MyAG.
Node2 : Réplica 2 dans le groupe de disponibilité MyAG.
MyAG : Le 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 : Un 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 homologue.
Une instance de SQL Server pour héberger une autre base de données homologue.
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 unique de défaillance de 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.

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

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

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;

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. Le nom de connexion et le mot de passe indiqué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 distributeur, utilisez Authentification Windows car la connexion au principal nécessite une authentification Kerberos pour la communication entre les ordinateurs hôtes répliques. Utiliser 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
```

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

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

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 en tant que serveur de publication de réplication (Node2)

Sur chaque hôte de la réplique secondaire, configurez la distribution. Spécifiez pour @password la même valeur que celle que vous utilisez lorsque vous exécutez 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>'

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

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.
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 l’écriture précédente, 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 vers le nom de l’écouteur du groupe de disponibilité (Peer1)

Sur le distributeur de Peer1, redirigez le serveur de publication d’origine vers le nom du listener du groupe de disponibilité.

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

Remarque

Dans l’écriture précédente, ,<port> est optionnel. C’est seulement obligatoire si vous utilisez des ports non par défaut. N’incluez pas les chevrons <>.

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 la publication pair-à-pair compatible 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 l’écriture précédente, ,<port> est optionnel. C’est seulement obligatoire si vous utilisez des ports non par défaut. N’incluez pas les chevrons <>.

Après avoir terminé les étapes précédentes, le groupe de disponibilité est prêt à participer à la topologie 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)

Configurez la distribution au niveau du distributeur.

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

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;

Configurez Node3 comme éditeur distant sur le distributeur 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)

Sur Node3, configurez la distribution à distance.

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

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

Créer un abonnement push 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. Cette étape suppose que Node1 exécute la réplique principale.

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 de type push de Peer2 vers le listener du groupe de disponibilité

Pour créer un abonnement de type push de Peer2 vers l’écouteur du groupe de disponibilité, exécutez la commande suivante sur Node3.

Important

Le script suivant spécifie le nom d’auditeur 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

À chaque hôte réplique secondaire, assurez-vous que les abonnés push des publications apparaissent comme serveurs liés.

EXEC sys.sp_addlinkedserver   
    @server = 'MySubscriber';

Étape suivante

Configurer la base de données de publication peer-to-peer pour qu’elle fasse partie des groupes de disponibilité

Commentaires

Cette page a-t-elle été utile ?

Last updated on 2026-09-24