Konfiguruj oba partnery w grupach dostępności

Począwszy od SQL Server 2019 (15.x) CU 13, baza danych należąca do grupy dostępności SQL Server Always On może uczestniczyć jako partner w peer-to-peer topologii replikacji transakcyjnej. Ten artykuł opisuje, jak skonfigurować ten scenariusz z dwoma partnerami – każdy w osobnej grupie dostępności.

Skrypty w tym przykładzie wykorzystują procedury przechowywane T-SQL.

Role i nazwiska

Ta sekcja opisuje role i nazwy różnych elementów uczestniczących w topologii replikacji w tym artykule.

Równorzędny1

  • Node1: Główna replika dla pierwszej grupy dostępności
  • Node2: Replika wtórna dla pierwszej grupy dostępności
  • MyAG: Nazwa grupy dostępności dla pierwszej grupy dostępności
  • MyDBName: Baza danych Peer1 . Baza danych do publikacji
  • Dist1: zdalny dystrybutor
  • P2P_MyDBName: Nazwa publikacji
  • MyAGListenerName: detektor grupy dostępności

Peer2

  • Node3: Główna replika dla drugiej grupy dostępności
  • Node4: Replika wtórna dla drugiej grupy dostępności
  • MyAG2: Nazwa grupy dostępności dla drugiej nazwy grupy dostępności
  • MyDBName: Baza danych do publikacji
  • Dist2: dystrybutor zdalny
  • P2P_MyDBName: Nazwa publikacji
  • MyAG2ListenerName: detektor grupy dostępności

Wymagania wstępne

  • Cztery instancje SQL Server na oddzielnych fizycznych lub wirtualnych serwerach do hostowania grup dostępności. Każda z dwóch grup dostępności zawiera równorzędną bazę danych.

  • Dwie instancje SQL Server do hostowania baz danych dystrybutorów.

  • Wszystkie instancje serwera wymagają wspieranej edycji – edycji Enterprise lub Developer.

  • Wszystkie instancje serwera wymagają obsługiwanej wersji – SQL Server 2019 (15.x) CU13 lub nowszej.

  • Wystarczająca łączność sieciowa i przepustowość między wszystkimi instancjami.

  • Zainstaluj replikację SQL Server na wszystkich instancjach SQL Server.

    Aby sprawdzić, czy replikacja jest zainstalowana na jakiejkolwiek instancji, uruchom następujące zapytanie:

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

    Note

    Aby uniknąć pojedynczego punktu awarii bazy danych dystrybucji, należy używać zdalnego dystrybutora dla każdego peera.

    Do środowiska demonstracyjnego lub testowego możesz skonfigurować bazy danych dystrybucji na jednej instancji.

Konfiguruj dystrybutora i zdalnego wydawcę (Peer1)

Ta sekcja opisuje, jak skonfigurować pierwszego peera (Peer1) w grupie dostępności.

  1. Uruchom sp_adddistributor, aby skonfigurować dystrybucję w Dist1. Używa @password = się do określenia hasła, którego używa zdalny wydawca do połączenia się z dystrybutorem. Używaj tego hasła przy każdym zdalnym wydawcy podczas konfiguracji zdalnego dystrybutora.

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

  2. Utwórz bazę danych dystrybucji w dystrybutorze.

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

  3. Konfiguruj Node1 i Node2 jako zdalnego wydawcę.

    Określa, @security_mode jak agenty replikacyjne łączą się z aktualnym elementem pierwotnym.

    • 1 = uwierzytelnianie systemu Windows.
    • 0= uwierzytelnianie SQL Server. Wymaga @login i @password. Dane logowania i hasła muszą być ważne dla każdej repliki drugorzędnej.

    Note

    Jeśli jakiekolwiek zmodyfikowane agenty replikacji działają na komputerze innym niż dystrybutor, użycie uwierzytelniania systemu Windows do połączenia z serwerem głównym wymaga uwierzytelniania Kerberos na potrzeby komunikacji między komputerami hostującymi repliki. Użycie logowania SQL Server do połączenia z obecnym głównym nie wymaga uwierzytelniania 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

Konfiguruj wydawcę u oryginalnego wydawcy (Node1)

  1. Konfiguruj oryginalnego wydawcę zdalnej dystrybucji (Node1). Określ dla @password tę samą wartość, która została użyta podczas uruchamiania sp_adddistributor u dystrybutora w celu skonfigurowania dystrybucji.

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

  2. Włącz bazę danych na potrzeby replikacji.

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

Konfiguruj wtórnego hosta repliki jako wydawcę replikacji (Node2)

Na każdym drugorzędnym hostzie repliki (Node2) dla pierwszej grupy dostępności konfiguruj dystrybucję. Określ dla @password tę samą wartość, która została użyta podczas uruchamiania sp_adddistributor u dystrybutora w celu skonfigurowania dystrybucji.

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

Dodaj bazę danych do grupy dostępności i utwórz odbiornik (Peer1)

  1. Na docelowej replice głównej utwórz grupę dostępności z bazą danych jako bazę członkowską.

  2. Stwórz nasłuchiwacz DNS dla grupy dostępności. Agent replikacyjny łączy się z aktualną repliką pierwotną, korzystając ze słuchacza. Poniższy przykład tworzy słuchacza o nazwie MyAGListenerName.

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

    Note

    W powyższym scenariuszu informacje w nawiasach kwadratowych ([ ... ]) są opcjonalne. Użyj tego, aby określić wartość inną niż domyślna dla portu TCP. Nie uwzględniaj nawiasów.

Przekieruj pierwotnego wydawcę na nazwę odbiornika AG (Peer1)

Na dystrybutorze Peer1 przekieruj oryginalnego wydawcę do nazwy słuchacza AG.

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

Note

W powyższym skrypcie element ,<port> jest opcjonalny. Jest wymagana tylko wtedy, gdy używasz portów innych niż domyślne. Nie dołączaj nawiasów <>kątowych.

Utwórz publikację peer-to-peer (Peer1) u oryginalnego wydawcy — Node1

Poniższy skrypt tworzy publikację dla 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

Spraw, by publikacje peer-to-peer były kompatybilne z grupą dostępności (Peer1)

Na oryginalnym wydawcy (Node1) uruchom następujący skrypt, aby uczynić publikację kompatybilną z grupą dostępności:

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

W powyższym skrypcie element ,<port> jest opcjonalny. Jest wymagana tylko wtedy, gdy używasz niestandardowych portów.

Po ukończeniu powyższych kroków grupa dostępności jest gotowa do uczestnictwa w topologii peer-to-peer. W kolejnych krokach skonfigurujesz oddzielną grupę dostępności jako drugiego partnera (Peer2) w topologii replikacji peer-to-peer.

Skonfiguruj dystrybutora i zdalnego wydawcę (Peer2)

Ta sekcja opisuje, jak skonfigurować drugiego peera (Peer2) w innej grupie dostępności.

  1. Uruchom sp_adddistributor, aby skonfigurować dystrybucję na Dist2. Używa @password = się do określenia hasła, którego używa zdalny wydawca do połączenia się z dystrybutorem. Używaj tego hasła przy każdym zdalnym wydawcy podczas konfiguracji zdalnego dystrybutora.

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

  2. Utwórz bazę danych dystrybucji w dystrybutorze.

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

  3. Skonfiguruj Node3 i zdalnego wydawcę Node4 .

    Określa, @security_mode jak agenty replikacyjne łączą się z aktualnym elementem pierwotnym.

    • 1 = uwierzytelnianie systemu Windows.
    • 0= uwierzytelnianie SQL Server. Wymaga @login i @password. Dane logowania i hasła muszą być ważne dla każdej repliki drugorzędnej.

    Note

    Jeśli jakiekolwiek zmodyfikowane agenty replikacji działają na komputerze innym niż dystrybutor, użycie uwierzytelniania systemu Windows do połączenia z serwerem głównym wymaga uwierzytelniania Kerberos na potrzeby komunikacji między komputerami hostującymi repliki. Użycie logowania SQL Server do połączenia z obecnym głównym nie wymaga uwierzytelniania 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

Konfiguruj wydawcę (Peer2)

  1. Konfiguruj zdalną dystrybucję na (Node3). Określ dla @password tę samą wartość, która została użyta podczas uruchamiania sp_adddistributor u dystrybutora w celu skonfigurowania dystrybucji.

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

  2. Włącz bazę danych na potrzeby replikacji.

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

Konfiguruj wtórny host repliki jako wydawcę replikacji (Node4)

Na każdym hostze wtórnej repliki (Node4) dla drugiej grupy dostępności konfiguruj dystrybucję. Określ dla @password tę samą wartość, która została użyta podczas uruchamiania sp_adddistributor u dystrybutora w celu skonfigurowania dystrybucji.

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

Dodaj bazę danych do grupy dostępności i utwórz odbiornik (Peer2)

  1. W zamierzonej replice podstawowej utwórz grupę dostępności, uwzględniając bazę danych jako bazę członkowską.

  2. Stwórz nasłuchiwacz DNS dla grupy dostępności. Agent replikacyjny łączy się z aktualną repliką pierwotną, korzystając ze słuchacza. Poniższy przykład tworzy słuchacza o nazwie MyAG2ListenerName.

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

    Note

    W powyższym scenariuszu informacje w nawiasach kwadratowych ([ ... ]) są opcjonalne. Użyj tego, aby określić wartość inną niż domyślna dla portu TCP. Nie uwzględniaj nawiasów.

Przekieruj pierwotnego wydawcę do nazwy odbiornika AG (Peer2)

Na dystrybutorze dla Peer2 przekieruj oryginalnego wydawcę na nazwę odbiornika AG.

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

Note

W powyższym skrypcie element ,<port> jest opcjonalny. Jest wymagana tylko wtedy, gdy używasz niestandardowych portów. Nie dołączaj nawiasów <>kątowych.

Stwórz publikację peer-to-peer (Peer2)

Poniższy skrypt tworzy publikację dla Peer2.

Na Node3 wykonaj następujące polecenie, aby utworzyć publikację peer-to-peer.

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

Spraw, by publikacje peer-to-peer były kompatybilne z grupą dostępności (Peer2)

Na oryginalnym wydawcy (Node3) uruchom następujący skrypt, aby uczynić publikację kompatybilną z grupą dostępności:

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

W powyższym skrypcie element ,<port> jest opcjonalny. Jest wymagana tylko wtedy, gdy używasz niestandardowych portów.

Stwórz subskrypcję push z Peer1 do grupy nasłuchiwającej dostępności dla Peer2

Aby utworzyć subskrypcję push z Peer1 do słuchacza grupy dostępności Peer2, uruchom następujące polecenie na Node1.

Wykonaj następujący skrypt na Node1. Zakłada to, że Node1 uruchamia replikę podstawową.

Ważna

Poniższy skrypt określa nazwę grupy dostępności dla abonenta.

@subscriber = N'MyAGListenerName,<port>'

Note

W powyższym skrypcie element ,<port> jest opcjonalny. Jest wymagana tylko wtedy, gdy używasz niestandardowych portów. Nie dołączaj nawiasów <>kątowych.

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

Utworzenie subskrypcji push z Peer2 do grupy nasłuchującej dostępności (Peer1)

Aby utworzyć subskrypcję push z Peer2 do nasłuchiwacza grupy dostępności (Peer1), uruchom następujące polecenie na Node3.

Ważna

Poniższy skrypt określa nazwę grupy dostępności dla abonenta.

@subscriber = N'MyAGListenerName,<port>'

Note

W powyższym skrypcie element ,<port> jest opcjonalny. Jest wymagana tylko wtedy, gdy używasz niestandardowych portów. Nie dołączaj nawiasów <>kątowych.

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

Konfiguruj serwery powiązane

Na każdym hoście repliki pomocniczej upewnij się, że subskrybenci push publikacji bazy danych są widoczni jako serwery połączone.

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