Mengonfigurasi kedua rekan dalam grup ketersediaan

  • Artikel

Dimulai dengan SQL Server 2019 (15.x) CU 13 database milik grup ketersediaan AlwaysOn SQL Server dapat berpartisipasi sebagai peer dalam topologi replikasi transaksional peer-to-peer. Artikel ini menjelaskan cara mengonfigurasi skenario ini dengan dua rekan - masing-masing dalam grup ketersediaannya sendiri.

Skrip dalam contoh ini menggunakan prosedur tersimpan T-SQL.

Peran & nama

Bagian ini menjelaskan peran dan nama berbagai elemen yang berpartisipasi dalam topologi replikasi untuk artikel ini.

Peer1

  • Node1: Replika utama untuk grup ketersediaan pertama
  • Node2: Replika sekunder untuk grup ketersediaan pertama
  • MyAG: Nama grup ketersediaan untuk grup ketersediaan pertama
  • MyDBName: Database peer1 . Database yang akan diterbitkan
  • Dist1: Distributor jarak jauh
  • P2P_MyDBName: Nama publikasi
  • MyAGListenerName: Pendengar grup ketersediaan

Peer2

  • Node3: Replika utama untuk grup ketersediaan kedua
  • Node4: Replika sekunder untuk grup ketersediaan kedua
  • MyAG2: Nama grup ketersediaan untuk nama grup ketersediaan kedua
  • MyDBName: Database yang akan diterbitkan
  • Dist2: Distributor jarak jauh
  • P2P_MyDBName: Nama publikasi
  • MyAG2ListenerName: Listener grup ketersediaan

Prasyarat

  • Empat instans SQL Server di server fisik atau virtual terpisah untuk menghosting grup ketersediaan. Dua grup ketersediaan masing-masing berisi database serekan.

  • Dua instans SQL Server untuk menghosting database distributor.

  • Semua instans server memerlukan edisi yang didukung - Edisi perusahaan atau edisi Pengembang.

  • Semua instans server memerlukan versi yang didukung - SQL Server 2019 (15.x) CU13 atau yang lebih baru.

  • Konektivitas jaringan dan bandwidth yang memadai antara semua instans.

  • Instal replikasi SQL Server pada semua instans SQL Server.

    Untuk melihat apakah replikasi diinstal pada instans apa pun, jalankan kueri berikut:

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

    Catatan

    Untuk menghindari satu titik kegagalan untuk database distribusi, gunakan distributor jarak jauh untuk setiap rekan.

    Untuk demonstrasi atau lingkungan pengujian, Anda dapat mengonfigurasi database distribusi pada satu instans.

Mengonfigurasi distributor dan penerbit jarak jauh (Peer1)

Bagian ini menjelaskan cara menyiapkan peer pertama (Peer1) dalam grup ketersediaan.

  1. Jalankan sp_adddistributor untuk mengonfigurasi distribusi pada Dist1. Gunakan @password = untuk menentukan kata sandi yang digunakan penerbit jarak jauh untuk menyambungkan ke distributor. Gunakan kata sandi ini di setiap penerbit jarak jauh saat Anda mengonfigurasi distributor jarak jauh.

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

  2. Buat database distribusi di distributor.

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

  3. Konfigurasikan penerbit jarak jauh Node1 dan Node2 .

    @security_mode menentukan bagaimana agen replikasi terhubung ke primer saat ini.

    • 1 = Autentikasi Windows.
    • 0 = Autentikasi SQL Server. @login Membutuhkan dan @password. Login dan kata sandi yang ditentukan harus valid di setiap replika sekunder.

    Catatan

    Jika ada agen replikasi yang dimodifikasi yang berjalan di komputer selain distributor, penggunaan autentikasi Windows untuk koneksi ke primer mengharuskan autentikasi Kerberos untuk komunikasi antara komputer host replika. Penggunaan login SQL Server untuk koneksi ke primer saat ini tidak memerlukan autentikasi 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

Mengonfigurasi penerbit di penerbit asli (Node1)

  1. Mengonfigurasi penerbit asli distribusi jarak jauh (Node1). Tentukan nilai yang sama untuk @password seperti yang digunakan saat sp_adddistributor dijalankan di distributor untuk menyiapkan distribusi.

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

  2. Aktifkan database untuk replikasi.

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

Mengonfigurasi host replika sekunder sebagai penerbit replikasi (Node2)

Di setiap host replika sekunder (Node2) untuk grup ketersediaan pertama, konfigurasikan distribusi. Tentukan nilai yang sama untuk @password seperti yang digunakan saat sp_adddistributor dijalankan di distributor untuk menyiapkan distribusi.

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

Jadikan bagian database dari grup ketersediaan dan buat listener (Peer1)

  1. Pada replika utama yang dimaksudkan, buat grup ketersediaan dengan database sebagai database anggota.

  2. Buat pendengar DNS untuk grup ketersediaan. Agen replikasi terhubung ke replika utama saat ini dengan menggunakan pendengar. Contoh berikut membuat pendengar bernama MyAGListenerName.

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

    Catatan

    Dalam skrip di atas, informasi dalam tanda kurung siku ([ ... ]) bersifat opsional. Gunakan untuk menentukan nilai non default untuk port TCP. Jangan sertakan tanda kurung.

Mengalihkan penerbit asli ke nama pendengar AG (Peer1)

Pada distributor untuk Peer1, alihkan penerbit asli ke nama pendengar AG.

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

Catatan

Dalam skrip di atas ,<port> bersifat opsional. Ini hanya diperlukan jika Anda menggunakan port non-default. Jangan sertakan tanda kurung <>sudut kemudian .

Membuat publikasi peer-to-peer (Peer1) di penerbit asli - Node1

Skrip berikut membuat publikasi untuk 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

Membuat publikasi peer to peer kompatibel dengan grup ketersediaan (Peer1)

Pada penerbit asli (Node1), jalankan skrip berikut untuk membuat publikasi kompatibel dengan grup ketersediaan:

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

Catatan

Dalam skrip di atas ,<port> bersifat opsional. Ini hanya diperlukan jika Anda menggunakan port non-default.

Setelah Anda menyelesaikan langkah-langkah di atas, grup ketersediaan disiapkan untuk berpartisipasi dalam topologi peer-to-peer. Langkah berikutnya mengonfigurasi grup ketersediaan terpisah sebagai peer kedua (Peer2) dalam topologi replikasi peer-to-peer.

Mengonfigurasi distributor dan penerbit jarak jauh (Peer2)

Bagian ini menjelaskan cara menyiapkan peer kedua (Peer2) dalam grup ketersediaan yang berbeda.

  1. Jalankan sp_adddistributor untuk mengonfigurasi distribusi pada Dist2. Gunakan @password = untuk menentukan kata sandi yang digunakan penerbit jarak jauh untuk menyambungkan ke distributor. Gunakan kata sandi ini di setiap penerbit jarak jauh saat Anda mengonfigurasi distributor jarak jauh.

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

  2. Buat database distribusi di distributor.

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

  3. Konfigurasikan penerbit jarak jauh Node3 dan Node4 .

    @security_mode menentukan bagaimana agen replikasi terhubung ke primer saat ini.

    • 1 = Autentikasi Windows.
    • 0 = Autentikasi SQL Server. @login Membutuhkan dan @password. Login dan kata sandi yang ditentukan harus valid di setiap replika sekunder.

    Catatan

    Jika ada agen replikasi yang dimodifikasi yang berjalan di komputer selain distributor, penggunaan autentikasi Windows untuk koneksi ke primer mengharuskan autentikasi Kerberos untuk komunikasi antara komputer host replika. Penggunaan login SQL Server untuk koneksi ke primer saat ini tidak memerlukan autentikasi 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

Mengonfigurasi penerbit (Peer2)

  1. Mengonfigurasi distribusi jarak jauh pada (Node3). Tentukan nilai yang sama untuk @password seperti yang digunakan saat sp_adddistributor dijalankan di distributor untuk menyiapkan distribusi.

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

  2. Aktifkan database untuk replikasi.

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

Mengonfigurasi host replika sekunder sebagai penerbit replikasi (Node4)

Pada setiap host replika sekunder (Node4) untuk grup ketersediaan kedua, konfigurasikan distribusi. Tentukan nilai yang sama untuk @password seperti yang digunakan saat sp_adddistributor dijalankan di distributor untuk menyiapkan distribusi.

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

Jadikan bagian database dari grup ketersediaan dan buat listener (Peer2)

  1. Pada replika utama yang dimaksudkan, buat grup ketersediaan dengan database sebagai database anggota.

  2. Buat pendengar DNS untuk grup ketersediaan. Agen replikasi terhubung ke replika utama saat ini dengan menggunakan pendengar. Contoh berikut membuat pendengar bernama MyAG2ListenerName.

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

    Catatan

    Dalam skrip di atas, informasi dalam tanda kurung siku ([ ... ]) bersifat opsional. Gunakan untuk menentukan nilai non default untuk port TCP. Jangan sertakan tanda kurung.

Mengalihkan penerbit asli ke nama pendengar AG (Peer2)

Pada distributor untuk Peer2, alihkan penerbit asli ke nama pendengar AG.

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

Catatan

Dalam skrip di atas ,<port> bersifat opsional. Ini hanya diperlukan jika Anda menggunakan port non-default. Jangan sertakan tanda kurung <>sudut kemudian .

Membuat publikasi peer-to-peer (Peer2)

Skrip berikut membuat publikasi untuk Peer2.

Pada Node3 jalankan perintah berikut untuk membuat publikasi 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

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

Membuat publikasi peer to peer kompatibel dengan grup ketersediaan (Peer2)

Pada penerbit asli (Node3), jalankan skrip berikut untuk membuat publikasi kompatibel dengan grup ketersediaan:

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

Catatan

Dalam skrip di atas ,<port> bersifat opsional. Ini hanya diperlukan jika Anda menggunakan port non-default.

Membuat langganan push dari Peer1 ke listener grup ketersediaan untuk Peer2

Untuk membuat langganan push dari Peer1 ke peer2 pendengar grup ketersediaan, jalankan perintah berikut pada Node1.

Jalankan skrip berikut pada Node1. Ini mengasumsikan Node1 menjalankan replika utama.

Penting

Skrip di bawah ini menentukan nama pendengar grup ketersediaan untuk pelanggan.

@subscriber = N'MyAGListenerName,<port>'

Catatan

Dalam skrip di atas ,<port> bersifat opsional. Ini hanya diperlukan jika Anda menggunakan port non-default. Jangan sertakan tanda kurung <>sudut kemudian .

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

Membuat langganan push dari Peer2 ke listener grup ketersediaan (Peer1)

Untuk membuat langganan push dari Peer2 ke listener grup ketersediaan (Peer1), jalankan perintah berikut pada Node3.

Penting

Skrip di bawah ini menentukan nama pendengar grup ketersediaan untuk pelanggan.

@subscriber = N'MyAGListenerName,<port>'

Catatan

Dalam skrip di atas ,<port> bersifat opsional. Ini hanya diperlukan jika Anda menggunakan port non-default. Jangan sertakan tanda kurung <>sudut kemudian .

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

Mengonfigurasi server tertaut

Di setiap host replika sekunder, pastikan pelanggan push publikasi database muncul sebagai server tertaut.

EXEC sys.sp_addlinkedserver   
    @server = 'MySubscriber';

Langkah berikutnya