Configureer beide peers in beschikbaarheidsgroepen

Vanaf SQL Server 2019 (15.x) kan CU 13 een database die behoort tot een SQL Server Always On beschikbaarheidsgroep als peer deelnemen aan een peer-to-peer transactionele replicatietopologie. Dit artikel beschrijft hoe je dit scenario configureert met twee peers - elk in zijn eigen beschikbaarheidsgroep.

De scripts in dit voorbeeld gebruiken T-SQL stored procedures.

Rollen & namen

Deze sectie beschrijft de rollen en namen van de verschillende elementen die deelnemen aan de replicatietopologie voor dit artikel.

Peer1

  • Node1: Primaire replica voor de eerste beschikbaarheidsgroep
  • Node2: Secundaire replica voor de eerste beschikbaarheidsgroep
  • MyAG: Naam van de beschikbaarheidsgroep voor de eerste beschikbaarheidsgroep
  • MyDBName: Peer1-database . Database die wordt gepubliceerd
  • Dist1: Distributeur op afstand
  • P2P_MyDBName: Publicatienaam
  • MyAGListenerName: Beschikbaarheidsgroep-luisteraar

Peer2

  • Node3: Primaire replica voor de tweede beschikbaarheidsgroep
  • Node4: Secundaire replica voor de tweede beschikbaarheidsgroep
  • MyAG2: Naam van de beschikbaarheidsgroep voor de tweede beschikbaarheidsgroepnaam
  • MyDBName: Database die wordt gepubliceerd
  • Dist2: Externe distributeur
  • P2P_MyDBName: Publicatienaam
  • MyAG2ListenerName: listener van de beschikbaarheidsgroep

Prerequisites

  • Vier instanties van SQL Server op aparte fysieke of virtuele servers om de beschikbaarheidsgroepen te hosten. Twee beschikbaarheidsgroepen bevatten elk een peerdatabase.

  • Twee instanties van SQL Server om de distributeurdatabases te hosten.

  • Alle serverinstanties vereisen een ondersteunde editie - Enterprise edition of Developer edition.

  • Alle serverinstanties vereisen een ondersteunde versie - SQL Server 2019 (15.x) CU13 of later.

  • Voldoende netwerkconnectiviteit en bandbreedte tussen alle instanties.

  • Installeer SQL Server-replicatie op alle instanties van SQL Server.

    Om te zien of replicatie op een instantie is geïnstalleerd, voer je de volgende query uit:

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

    Note

    Om een single point of failure voor de distributiedatabase te voorkomen, gebruik je voor elke peer een externe distributeur.

    Voor een demonstratie- of testomgeving kun je de distributiedatabases op één enkele instantie configureren.

Configureer de distributeur en externe uitgever (Peer1)

Deze sectie beschrijft hoe je de eerste peer (Peer1) in een beschikbaarheidsgroep kunt instellen.

  1. Voer sp_adddistributor uit om distributie op Dist1 te configureren. Gebruik @password = om een wachtwoord op te geven dat de externe uitgever gebruikt om verbinding te maken met de distributeur. Gebruik dit wachtwoord bij elke externe uitgever wanneer je de externe distributeur configureert.

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

  2. Maak de distributiedatabase bij de distributeur.

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

  3. Configureer Node1 en Node2 als externe publisher.

    Bepaalt @security_mode hoe de replicatieagenten verbinding maken met de huidige primaire agent.

    • 1 = Windows-verificatie.
    • 0= SQL Server-authenticatie. Vereist @login en @password. De opgegeven login en het wachtwoord moeten geldig zijn bij elke secundaire replica.

    Note

    Als er aangepaste replicatieagenten op een andere computer dan de distributeur draaien, vereist het gebruik van Windows authentication voor de verbinding met de primaire Kerberos-authenticatie voor de communicatie tussen de replica-hostcomputers. Het gebruik van een SQL Server-login voor de verbinding met de huidige primaire vereist geen Kerberos-authenticatie.

    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

Configureer de uitgever bij de oorspronkelijke uitgever (Node1)

  1. Configureer de oorspronkelijke uitgever (Node1) voor externe distributie. Geef voor @password dezelfde waarde op als de waarde die is gebruikt toen sp_adddistributor bij de distributeur werd uitgevoerd om de distributie in te stellen.

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

  2. Schakel de database in voor replicatie.

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

Configureer de secundaire replicahost als replicatie-uitgever (Node2)

Bij elke secundaire replicahost (Node2) voor de eerste beschikbaarheidsgroep, configureer de distributie. Geef voor @password dezelfde waarde op als de waarde die is gebruikt toen sp_adddistributor bij de distributeur werd uitgevoerd om de distributie in te stellen.

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

Maak de database onderdeel van de beschikbaarheidsgroep en maak de luisteraar aan (Peer1)

  1. Op de beoogde primaire replica maak je de beschikbaarheidsgroep aan met de database als liddatabase.

  2. Maak een DNS-luisteraar aan voor de beschikbaarheidsgroep. De replicatieagent maakt verbinding met de huidige primaire replica via de luisteraar. Het volgende voorbeeld creëert een luisteraar genaamd MyAGListenerName.

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

    Note

    In het bovenstaande script is informatie tussen vierkante haken ([ ... ]) optioneel. Gebruik het om een niet-standaard waarde voor de TCP-poort te specificeren. Voeg de haakjes niet mee.

Leid de oorspronkelijke uitgever door naar de naam van AG-luisteraar (Peer1)

Op de distributeur voor Peer1 verwijs je de oorspronkelijke uitgever door naar de naam van de AG-luisteraar.

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

Note

In het bovenstaande ,<port> script is het optioneel. Het is alleen vereist als je niet-standaard poorten gebruikt. Voeg de punthaken <> niet toe.

Maak peer-to-peer publicatie aan (Peer1) op de oorspronkelijke uitgever - Node1

Het volgende script maakt de publicatie voor Peer1 aan.

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

Maak peer-to-peer publicaties compatibel met de beschikbaarheidsgroep (Peer1)

Op de oorspronkelijke uitgever (Node1) voer je het volgende script uit om de publicatie compatibel te maken met de beschikbaarheidsgroep:

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

In het bovenstaande ,<port> script is het optioneel. Het is alleen vereist als je niet-standaard poorten gebruikt.

Nadat je bovenstaande stappen hebt voltooid, is de beschikbaarheidsgroep klaar om deel te nemen aan peer-to-peer topologie. De volgende stappen configureren een aparte beschikbaarheidsgroep als de tweede peer (Peer2) in de peer-to-peer replicatietopologie.

Configureer de distributeur en externe uitgever (Peer2)

Deze sectie beschrijft hoe je de tweede peer (Peer2) in een andere beschikbaarheidsgroep kunt instellen.

  1. Voer sp_adddistributor uit om distributie te configureren op Dist2. Gebruik @password = om een wachtwoord op te geven dat de externe uitgever gebruikt om verbinding te maken met de distributeur. Gebruik dit wachtwoord bij elke externe uitgever wanneer je de externe distributeur configureert.

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

  2. Maak de distributiedatabase bij de distributeur.

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

  3. Configureer Node3 en Node4 als externe publisher.

    Bepaalt @security_mode hoe de replicatieagenten verbinding maken met de huidige primaire agent.

    • 1 = Windows-verificatie.
    • 0= SQL Server-authenticatie. Vereist @login en @password. De opgegeven login en het wachtwoord moeten geldig zijn bij elke secundaire replica.

    Note

    Als er aangepaste replicatieagenten op een andere computer dan de distributeur draaien, vereist het gebruik van Windows authentication voor de verbinding met de primaire Kerberos-authenticatie voor de communicatie tussen de replica-hostcomputers. Het gebruik van een SQL Server-login voor de verbinding met de huidige primaire vereist geen Kerberos-authenticatie.

    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

Configureer de uitgever (Peer2)

  1. Configureer externe distributie op (Node3). Geef voor @password dezelfde waarde op als de waarde die is gebruikt toen sp_adddistributor bij de distributeur werd uitgevoerd om de distributie in te stellen.

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

  2. Schakel de database in voor replicatie.

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

Configureer de secundaire replicahost als replicatie-uitgever (Node4)

Bij elke secundaire replicahost (Node4) voor de tweede beschikbaarheidsgroep, configureer de distributie. Geef voor @password dezelfde waarde op als de waarde die is gebruikt toen sp_adddistributor bij de distributeur werd uitgevoerd om de distributie in te stellen.

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

Maak de database onderdeel van de beschikbaarheidsgroep en maak de luisteraar aan (Peer2)

  1. Op de beoogde primaire replica maak je de beschikbaarheidsgroep aan met de database als liddatabase.

  2. Maak een DNS-luisteraar aan voor de beschikbaarheidsgroep. De replicatieagent maakt verbinding met de huidige primaire replica via de luisteraar. Het volgende voorbeeld creëert een luisteraar genaamd MyAG2ListenerName.

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

    Note

    In het bovenstaande script is informatie tussen vierkante haken ([ ... ]) optioneel. Gebruik het om een niet-standaard waarde voor de TCP-poort te specificeren. Voeg de haakjes niet mee.

Leid de oorspronkelijke uitgever door naar de naam van de AG-luisteraar (Peer2)

Leid op de distributeur voor Peer2 de oorspronkelijke publisher om naar de naam van de AG-listener.

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

Note

In het bovenstaande ,<port> script is het optioneel. Het is alleen vereist als je niet-standaard poorten gebruikt. Neem de punthaken <> dan niet op.

Maak peer-to-peer publicatie aan (Peer2)

Het volgende script creëert de publicatie voor Peer2.

Voer op Node3 het volgende commando uit om de peer-to-peer publicatie te maken.

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

Maak peer-to-peer-publicaties compatibel met de beschikbaarheidsgroep (Peer2)

Op de oorspronkelijke uitgever (Node3) voer je het volgende script uit om de publicatie compatibel te maken met de beschikbaarheidsgroep:

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

In het bovenstaande ,<port> script is het optioneel. Het is alleen vereist als je niet-standaard poorten gebruikt.

Maak een push-abonnement aan van Peer1 naar de beschikbaarheidsgroep-luisteraar voor Peer2

Om een push-abonnement van Peer1 aan te maken naar de beschikbaarheidsgroep luisteraar Peer2, voer je het volgende commando uit op Node1.

Voer het volgende script uit op Node1. Dit gaat ervan uit dat Node1 de primaire replica draait.

Important

Het onderstaande script specificeert de naam van de availability group listener voor de abonnee.

@subscriber = N'MyAGListenerName,<port>'

Note

In het bovenstaande ,<port> script is het optioneel. Het is alleen vereist als je niet-standaard poorten gebruikt. Voeg de punthaken <> niet toe.

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

Maak een push-abonnement aan van Peer2 naar de availability group listener (Peer1)

Om een push-abonnement van Peer2 naar de availability group listener (Peer1) aan te maken, voer je het volgende commando uit op Node3.

Important

Het onderstaande script specificeert de naam van de availability group listener voor de abonnee.

@subscriber = N'MyAGListenerName,<port>'

Note

In het bovenstaande ,<port> script is het optioneel. Het is alleen vereist als je niet-standaard poorten gebruikt. Voeg de punthaken <> niet toe.

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

Configureer gekoppelde servers

Zorg ervoor dat de push-abonnees van de databasepublicaties op elke secundaire replicahost verschijnen als gekoppelde servers.

EXEC sys.sp_addlinkedserver   
    @server = 'MySubscriber';
  • Last updated on