Konfigurera båda peers i tillgänglighetsgrupper

Från och med SQL Server 2019 (15.x) CU 13 kan en databas som ingår i en SQL Server Always On-tillgänglighetsgrupp delta som en likvärdig nod i en topologi för peer-to-peer-transaktionsreplikering. Den här artikeln beskriver hur man konfigurerar detta scenario med två peers – var och en i sin egen tillgänglighetsgrupp.

Skripten i detta exempel använder T-SQL-lagrade procedurer.

Roller och namn

Detta avsnitt beskriver rollerna och namnen på de olika elementen som deltar i replikationstopologin för denna artikel.

Peer1

  • Nod1: Primär replika för första tillgänglighetsgruppen
  • Nod2: Sekundär replika för första tillgänglighetsgruppen
  • MyAG: Namn på den första tillgänglighetsgruppen
  • MyDBName: Peer1-databas. Databas som ska publiceras
  • Dist1: Fjärrfördelare
  • P2P_MyDBName: Publikationsnamn
  • MyAGListenerName: Tillgänglighetsgrupplyssnare

Peer2

  • Nod3: Primär replika för andra tillgänglighetsgruppen
  • Nod4: Sekundär replika för andra tillgänglighetsgruppen
  • MyAG2: Tillgänglighetsgruppnamn för andra tillgänglighetsgruppnamn
  • MyDBName: Databas som ska publiceras
  • Dist2: Fjärrdistributör
  • P2P_MyDBName: Publikationsnamn
  • MyAG2ListenerName: Tillgänglighetsgrupplyssnare

Förutsättningar

  • Fyra instanser av SQL Server på separata fysiska eller virtuella servrar för att hosta tillgänglighetsgrupperna. Två tillgänglighetsgrupper innehåller vardera en peer-databas.

  • Två instanser av SQL Server för att hosta distributörsdatabaserna.

  • Alla serverinstanser kräver en stödd version – Enterprise edition eller Developer edition.

  • Alla serverinstanser kräver en stödd version – SQL Server 2019 (15.x) CU13 eller senare.

  • Tillräcklig nätverksanslutning och bandbredd mellan alla instanser.

  • Installera SQL Server-replikering på alla instanser av SQL Server.

    För att se om replikering är installerad på någon instans, kör följande fråga:

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

    Note

    För att undvika en enda felpunkt för distributionsdatabasen, använd en fjärrdistributör för varje peer.

    För demonstrations- eller testmiljö kan du konfigurera distributionsdatabaserna på en enskild instans.

Konfigurera distributören och fjärrpublicisten (Peer1)

Detta avsnitt beskriver hur man sätter upp den första peern (Peer1) i en tillgänglighetsgrupp.

  1. Kör sp_adddistributor för att konfigurera distribution på Dist1. Använd @password = för att ange ett lösenord som den fjärrutgivaren använder för att ansluta till distributören. Använd detta lösenord hos varje fjärrutgivare när du konfigurerar fjärrdistributören.

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

  2. Skapa distributionsdatabasen hos distributören.

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

  3. Konfigurera Node1 och Node2 fjärrutgivare.

    @security_mode avgör hur replikeringsagenterna ansluter till den aktuella primärservern.

    • 1 = Windows-autentisering.
    • 0= SQL Server-autentisering. Kräver @login och @password. Inloggningen och lösenordet som anges måste vara giltiga vid varje sekundär replik.

    Note

    Om några modifierade replikeringsagenter körs på en annan dator än distributören kräver användning av Windows authentication för anslutningen till primären Kerberos-autentisering för kommunikationen mellan replikavärddatorerna. Användning av en SQL Server-inloggning för anslutning till den nuvarande primära datorn kräver inte Kerberos-autentisering.

    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

Konfigurera utgivaren på den ursprungliga utgivaren (Node1)

  1. Konfigurera fjärrdistribution för ursprunglig utgivare (Node1). Ange samma värde för @password som användes när du körde sp_adddistributor på distributören för att konfigurera distributionen.

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

  2. Aktivera databasen för replikering.

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

Konfigurera den sekundära replikavärden som replikeringsutgivare (Nod2)

På varje värd för sekundär replik (Node2) för den första tillgänglighetsgruppen konfigurerar du distribution. Ange samma värde för @password som det värde du använder när du körde sp_adddistributor hos distributören för att sätta upp distribution.

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

Gör databasen till en del av tillgänglighetsgruppen och skapa lyssnaren (Peer1)

  1. På den avsedda primärrepliken skapar du tillgänglighetsgruppen med databasen som medlemsdatabas.

  2. Skapa en DNS-lyssnare för tillgänglighetsgruppen. Replikationsagenten kopplas till den aktuella primära repliken genom att använda lyssnaren. Följande exempel skapar en lyssnare som heter MyAGListenerName.

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

    Note

    I det föregående skriptet är information inom hakparenteser ([ ... ]) valfri. Använd den för att ange ett icke-standardvärde för TCP-porten. Inkludera inte bracketen.

Omdirigera den ursprungliga utgivaren till AG:s lyssnarnamn (Peer1)

På distributionsservern för Peer1 omdirigerar du den ursprungliga utgivaren till AG-lyssnarens namn.

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

Note

I det föregående skriptet ,<port> är valfritt. Det krävs bara om du använder icke-standardportar. Inkludera inte vinkelfästena <>.

Skapa peer-to-peer-publikation (Peer1) på den ursprungliga utgivaren - Nod1

Följande skript skapar publikationen för 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

Gör peer-to-peer-publikationer kompatibla med tillgänglighetsgruppen (Peer1)

På originalutgivaren (Node1), kör följande skript för att göra publikationen kompatibel med tillgänglighetsgruppen:

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

I det föregående skriptet ,<port> är valfritt. Det krävs bara om du använder icke-standardportar.

Efter att du har slutfört föregående steg är tillgänglighetsgruppen redo att delta i peer-to-peer-topologi. Nästa steg konfigurerar en separat tillgänglighetsgrupp som den andra peeren (Peer2) i peer-to-peer-replikationstopologin.

Konfigurera distributören och fjärrpublicisten (Peer2)

Detta avsnitt beskriver hur man ställer in den andra peeren (Peer2) i en annan tillgänglighetsgrupp.

  1. Kör sp_adddistributor för att konfigurera distribution på Dist2. Använd @password = för att ange ett lösenord som den fjärrutgivaren använder för att ansluta till distributören. Använd detta lösenord hos varje fjärrutgivare när du konfigurerar fjärrdistributören.

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

  2. Skapa distributionsdatabasen hos distributören.

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

  3. Konfigurera Node3 och Node4 fjärrutgivare.

    @security_mode avgör hur replikeringsagenterna ansluter till den aktuella primärservern.

    • 1 = Windows-autentisering.
    • 0= SQL Server-autentisering. Kräver @login och @password. Inloggningen och lösenordet som anges måste vara giltiga vid varje sekundär replik.

    Note

    Om några modifierade replikeringsagenter körs på en annan dator än distributören kräver användning av Windows authentication för anslutningen till primären Kerberos-autentisering för kommunikationen mellan replikavärddatorerna. Användning av en SQL Server-inloggning för anslutning till den nuvarande primära datorn kräver inte Kerberos-autentisering.

    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

Konfigurera utgivaren (Peer2)

  1. Konfigurera fjärrdistribution på (Nod3). Ange samma värde för @password som det som användes när du körde sp_adddistributor till distributören för att sätta upp distributionen.

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

  2. Aktivera databasen för replikering.

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

Konfigurera värden för den sekundära repliken som publicerare för replikering (Node4)

På varje sekundär replikeringsvärd (Node4) för den andra tillgänglighetsgruppen konfigurerar du distribution. Ange samma värde för @password som det värde du använde när du körde sp_adddistributor hos distributören för att sätta upp distributionen.

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

Gör databasen till en del av tillgänglighetsgruppen och skapa lyssnaren (Peer2)

  1. På den avsedda primärrepliken skapar du tillgänglighetsgruppen med databasen som medlemsdatabas.

  2. Skapa en DNS-lyssnare för tillgänglighetsgruppen. Replikationsagenten kopplas till den aktuella primära repliken genom att använda lyssnaren. Följande exempel skapar en lyssnare som heter MyAG2ListenerName.

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

    Note

    I det föregående skriptet är information inom hakparenteser ([ ... ]) valfri. Använd den för att ange ett icke-standardvärde för TCP-porten. Inkludera inte bracketen.

Omdirigera den ursprungliga utgivaren till AG-lyssnarnamnet (Peer2)

På distributören för Peer2, omdirigera den ursprungliga utgivaren till AG-lyssnarnamnet.

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

Note

I det föregående skriptet ,<port> är valfritt. Det krävs bara om du använder icke-standardportar. Inkludera inte vinkelfästena <>.

Skapa peer-to-peer-publikation (Peer2)

Följande skript skapar publikationen för Peer2.

På Nod3, kör följande kommando för att skapa peer-to-peer-publikationen.

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

Gör peer-to-peer-publikationer kompatibla med tillgänglighetsgruppen (Peer2)

På den ursprungliga utgivaren (Nod3), kör följande skript för att göra publiceringen kompatibel med tillgänglighetsgruppen:

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

I det föregående skriptet ,<port> är valfritt. Det krävs bara om du använder icke-standardportar.

Skapa en push-prenumeration från Peer1 till tillgänglighetsgruppens lyssnare för Peer2

För att skapa en push-prenumeration från Peer1 till tillgänglighetsgruppens lyssnare Peer2, kör följande kommando på Nod1.

Exekvera följande skript på Node1. Detta skript antar att Nod1 kör den primära repliken.

Important

Följande skript specificerar namnet på tillgänglighetsgruppens lyssnare för abonnenten.

@subscriber = N'MyAGListenerName,<port>'

Note

I det föregående skriptet ,<port> är valfritt. Det krävs bara om du använder icke-standardportar. Inkludera inte vinkelfästena <>.

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

Skapa en push-prenumeration från Peer2 till tillgänglighetsgruppens lyssnare (Peer1)

För att skapa en push-prenumeration från Peer2 till tillgänglighetsgruppens lyssnare (Peer1), kör följande kommando på Nod3.

Important

Följande skript specificerar namnet på tillgänglighetsgruppens lyssnare för abonnenten.

@subscriber = N'MyAGListenerName,<port>'

Note

I det föregående skriptet ,<port> är valfritt. Det krävs bara om du använder icke-standardportar. Inkludera inte vinkelfästena <>.

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

Konfigurera länkade servrar

Vid varje sekundär replikavärd, se till att push-prenumeranterna till databaspublikationerna visas som länkade servrar.

EXEC sys.sp_addlinkedserver   
    @server = 'MySubscriber';
  • Uppdaterades senast