Menggunakan seeding otomatis untuk menginisialisasi grup ketersediaan AlwaysOn

Berlaku untuk: SQL Server

SQL Server 2016 memperkenalkan penyemaian otomatis grup ketersediaan. Saat Anda membuat grup ketersediaan dengan seeding otomatis, SQL Server secara otomatis membuat replika sekunder untuk setiap database dalam grup. Anda tidak perlu lagi mencadangkan dan memulihkan replika sekunder secara manual. Untuk mengaktifkan penyemaian otomatis, buat grup ketersediaan dengan T-SQL atau gunakan versi terbaru SQL Server Management Studio.

Untuk informasi latar belakang, lihat Seeding otomatis untuk replika sekunder.

Prasyarat

Di SQL Server 2016, penyemaian otomatis mengharuskan jalur file data dan log sama pada setiap instans SQL Server yang berpartisipasi dalam grup ketersediaan. Di SQL Server 2017, Anda dapat menggunakan jalur yang berbeda, namun Microsoft merekomendasikan penggunaan jalur yang sama ketika semua replika dihosting di platform yang sama (misalnya Windows atau Linux). Grup ketersediaan lintas platform memiliki jalur yang berbeda untuk replika. Untuk detailnya, lihat Tata letak disk.

Penyemaian grup ketersediaan berkomunikasi melalui titik akhir pencerminan database. Buka aturan firewall masuk ke port titik akhir pencerminan di setiap server.

Database dalam grup ketersediaan harus dalam model pemulihan penuh. Database harus memiliki cadangan penuh dan pencadangan log transaksi saat ini. File cadangan ini tidak digunakan untuk seeding otomatis, tetapi diperlukan sebelum menyertakan database dalam grup ketersediaan.

Membuat grup ketersediaan dengan seeding otomatis

Untuk membuat grup ketersediaan dengan seeding otomatis, atur SEEDING_MODE=AUTOMATIC.

Contoh berikut membuat grup ketersediaan pada kluster failover Windows Server dua simpul. Sebelum menjalankan skrip, perbarui nilai untuk lingkungan Anda.

1. Buat titik akhir. Setiap server memerlukan titik akhir. Skrip berikut membuat titik akhir yang menggunakan port TCP 5022 untuk pendengar. Atur <endpoint_name> dan LISTENER_PORT agar sesuai dengan lingkungan Anda dan jalankan skrip di kedua server:

   CREATE ENDPOINT [<endpoint_name>] 
       STATE=STARTED
       AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
       FOR DATA_MIRRORING (
           ROLE = ALL, 
           AUTHENTICATION = WINDOWS NEGOTIATE, 
           ENCRYPTION = REQUIRED ALGORITHM AES
           )
   GO
   

2. Buat grup ketersediaan. Skrip berikut membuat grup ketersediaan. Perbarui nilai dalam tanda kurung <> sudut untuk nama grup, nama server, dan nama domain, dan jalankan pada instans utama SQL Server.

   CREATE AVAILABILITY GROUP [<availability_group_name>]
       FOR DATABASE db1
       REPLICA ON'<*primary_server*>'
       WITH (ENDPOINT_URL = N'TCP://<primary_server>.<fully_qualified_domain_name>:5022', 
           FAILOVER_MODE = AUTOMATIC, 
           AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
           BACKUP_PRIORITY = 50, 
           SECONDARY_ROLE(ALLOW_CONNECTIONS = NO), 
           SEEDING_MODE = AUTOMATIC),
       N'<secondary_server>' WITH (ENDPOINT_URL = N'TCP://<secondary_server>.<fully_qualified_domain_name>:5022', 
           FAILOVER_MODE = AUTOMATIC, 
           AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
           BACKUP_PRIORITY = 50, 
           SECONDARY_ROLE(ALLOW_CONNECTIONS = NO), 
           SEEDING_MODE = AUTOMATIC);
   GO
   

3. Gabungkan instans server sekunder ke grup ketersediaan dan berikan izin buat database ke grup ketersediaan. Perbarui skrip berikut, ganti nilai dalam tanda kurung sudut untuk lingkungan Anda, dan jalankan pada instans <> replika sekunder SQL Server:

   ALTER AVAILABILITY GROUP [<availability_group_name>] JOIN
   GO  
   ALTER AVAILABILITY GROUP [<availability_group_name>] GRANT CREATE ANY DATABASE
   GO
   

SQL Server secara otomatis membuat replika database di server sekunder. Jika database besar, mungkin perlu waktu untuk menyelesaikan sinkronisasi database. Jika database berada dalam grup ketersediaan yang dikonfigurasi untuk seeding otomatis, Anda bisa sys.dm_hadr_automatic_seeding mengkueri tampilan sistem untuk memantau kemajuan seeding. Kueri berikut mengembalikan satu baris untuk setiap database yang berada dalam grup ketersediaan yang dikonfigurasi untuk seeding otomatis.

SELECT start_time,
    ag.name,
    db.database_name,
    current_state,
    performed_seeding,
    failure_state,
    failure_state_desc
FROM sys.dm_hadr_automatic_seeding autos 
    JOIN sys.availability_databases_cluster db 
        ON autos.ag_db_id = db.group_database_id
    JOIN sys.availability_groups ag 
        ON autos.ag_id = ag.group_id

Mencegah penyemaian otomatis setelah grup ketersediaan

Untuk mencegah sementara replika utama menyemai lebih banyak database ke replika sekunder, Anda dapat menolak izin grup ketersediaan untuk membuat database. Jalankan kueri berikut pada instans yang menghosting replika sekunder untuk menolak izin grup ketersediaan untuk membuat database replika.

ALTER AVAILABILITY GROUP [<availability_group_name>] 
    DENY CREATE ANY DATABASE
GO

Mengaktifkan seeding otomatis pada grup ketersediaan yang ada

Anda dapat mengatur penyemaian otomatis pada database yang sudah ada. Perintah berikut mengubah grup ketersediaan untuk menggunakan seeding otomatis. Jalankan perintah berikut pada replika utama.

ALTER AVAILABILITY GROUP [<availability_group_name>] 
    MODIFY REPLICA ON '<secondary_node>' 
    WITH (SEEDING_MODE = AUTOMATIC)
GO

Perintah sebelumnya memaksa database untuk memulai ulang seeding jika diperlukan. Misalnya, jika penyemaian gagal karena ruang disk yang tidak mencukupi pada replika sekunder, jalankan ALTER AVAILABILITY GROUP ... WITH (SEEDING_MODE=AUTOMATIC) untuk memulai ulang penyemaian setelah Anda menambahkan ruang kosong.

Hentikan seeding otomatis

Untuk menghentikan penyemaian otomatis untuk grup ketersediaan, jalankan skrip berikut pada replika utama:

ALTER AVAILABILITY GROUP [<availability_group_name>] 
    MODIFY REPLICA ON '<secondary_node>'   
    WITH (SEEDING_MODE = MANUAL)
GO

Skrip sebelumnya membatalkan replika apa pun yang saat ini sedang disemai, dan mencegah SQL Server menginisialisasi replika apa pun secara otomatis dalam grup ketersediaan ini. Ini tidak menghentikan sinkronisasi untuk replika apa pun yang sudah diinisialisasi.

Memantau grup ketersediaan penyemaian otomatis

Menggunakan tampilan manajemen dinamis sistem untuk memantau penyemaian

Tampilan sistem berikut menunjukkan status seeding otomatis SQL Server.

sys.dm_hadr_automatic_seeding

Pada replika utama, kueri sys.dm_hadr_automatic_seeding untuk memeriksa status proses seeding otomatis. Tampilan mengembalikan satu baris untuk setiap proses seeding. Contohnya:

SELECT start_time, 
    completion_time
    is_source,
    current_state,
    failure_state,
    failure_state_desc
FROM sys.dm_hadr_automatic_seeding

sys.dm_hadr_physical_seeding_stats

Pada replika utama, kueri sys.dm_hadr_physical_seeding_stats DMV untuk melihat statistik fisik untuk setiap proses penyemaian yang saat ini berjalan. Kueri berikut mengembalikan baris saat penyemaian sedang berjalan:

SELECT * FROM sys.dm_hadr_physical_seeding_stats;

Dua kolom total_disk_io_wait_time_ms dan total_network_wait_time_ms dapat digunakan untuk menentukan hambatan performa dalam proses seeding Otomatis. Dua kolom juga ada dalam peristiwa hadr_physical_seeding_progress diperluas.

• total_disk_io_wait_time_ms mewakili waktu yang dihabiskan oleh utas pencadangan/pemulihan saat menunggu di disk. Nilai ini bersifat kumulatif sejak awal operasi penyemaian. Jika disk tidak siap untuk membaca atau menulis aliran cadangan, alur pencadangan/pemulihan beralih ke status tidur dan bangun setiap satu detik untuk memeriksa apakah disk siap.

• total_network_wait_time_ms ditafsirkan secara berbeda untuk replika Primer dan Sekunder. Pada replika utama, penghitung ini mewakili waktu kontrol aliran jaringan. Pada replika sekunder ini mewakili waktu utas pemulihan menunggu pesan tersedia untuk ditulis ke disk.

Mendiagnosis inisialisasi database menggunakan seeding otomatis di log kesalahan

Saat Anda menambahkan database ke grup ketersediaan yang dikonfigurasi untuk seeding otomatis, SQL Server melakukan pencadangan VDI melalui titik akhir grup ketersediaan. Tinjau log kesalahan SQL Server untuk informasi tentang kapan pencadangan selesai dan sekunder disinkronkan.

Mendiagnosis kesehatan tingkat database dengan peristiwa yang diperluas

Penyemaian otomatis memiliki peristiwa baru yang diperluas untuk melacak perubahan status, kegagalan, dan statistik performa selama inisialisasi.

Misalnya, skrip ini membuat sesi peristiwa yang diperluas yang menangkap peristiwa yang terkait dengan penyemaian otomatis:

CREATE EVENT SESSION [AlwaysOn_autoseed] ON SERVER 
    ADD EVENT sqlserver.hadr_automatic_seeding_state_transition,
    ADD EVENT sqlserver.hadr_automatic_seeding_timeout,
    ADD EVENT sqlserver.hadr_db_manager_seeding_request_msg,
    ADD EVENT sqlserver.hadr_physical_seeding_backup_state_change,
    ADD EVENT sqlserver.hadr_physical_seeding_failure,
    ADD EVENT sqlserver.hadr_physical_seeding_forwarder_state_change,
    ADD EVENT sqlserver.hadr_physical_seeding_forwarder_target_state_change,
    ADD EVENT sqlserver.hadr_physical_seeding_progress,
    ADD EVENT sqlserver.hadr_physical_seeding_restore_state_change,
    ADD EVENT sqlserver.hadr_physical_seeding_submit_callback
    ADD TARGET package0.event_file(
        SET filename=N'autoseed.xel',
            max_file_size=(5),
            max_rollover_files=(4)
        )
WITH (
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=ON
    )
GO 

ALTER EVENT SESSION AlwaysOn_autoseed ON SERVER STATE=START
GO 

Tabel berikut ini mencantumkan peristiwa yang diperluas yang terkait dengan penyemaian otomatis:

Nama	Deskripsi
hadr_db_manager_seeding_request_msg	Pesan permintaan penyemaian.
hadr_physical_seeding_backup_state_change	Perubahan status sisi pencadangan seeding fisik.
hadr_physical_seeding_restore_state_change	Perubahan status sisi pemulihan penyemaian fisik.
hadr_physical_seeding_forwarder_state_change	Perubahan status sisi penerus seeding fisik.
hadr_physical_seeding_forwarder_target_state_change	Perubahan status sisi target penerus seeding fisik.
hadr_physical_seeding_submit_callback	Penyemaian fisik mengirimkan peristiwa panggilan balik.
hadr_physical_seeding_failure	Peristiwa kegagalan penyemaian fisik.
hadr_physical_seeding_progress	Peristiwa kemajuan penyemaian fisik.
hadr_physical_seeding_schedule_long_task_failure	aktivitas kegagalan tugas panjang jadwal seeding fisik.
hadr_automatic_seeding_start	Terjadi ketika operasi penyemaian otomatis dikirimkan.
hadr_automatic_seeding_state_transition	Terjadi ketika operasi seeding otomatis berubah status.
hadr_automatic_seeding_success	Terjadi ketika operasi penyemaian otomatis berhasil.
hadr_automatic_seeding_failure	Terjadi ketika operasi seeding otomatis gagal.
hadr_automatic_seeding_timeout	Terjadi ketika waktu operasi penyemaian otomatis habis.

Pertimbangan pemecahan masalah lainnya

Memantau saat penyemaian otomatis

Kueri sys.dm_hadr_physical_seeding_stats untuk proses seeding otomatis yang sedang berjalan. Tampilan mengembalikan satu baris untuk setiap database. Contohnya:

SELECT local_database_name, 
    role_desc, 
    internal_state_desc, 
    transfer_rate_bytes_per_second, 
    transferred_size_bytes, 
    database_size_bytes, 
    start_time_utc, 
    end_time_utc, estimate_time_complete_utc, 
    total_disk_io_wait_time_ms, 
    total_network_wait_time_ms, 
    is_compression_enabled 
FROM sys.dm_hadr_physical_seeding_stats

Memecahkan masalah mengapa database gagal muncul dalam grup ketersediaan yang dikonfigurasi untuk seeding otomatis

Ketika database gagal muncul sebagai bagian dari grup ketersediaan dengan seeding otomatis diaktifkan, penyemaian otomatis kemungkinan gagal. Ini mencegah penambahan database ke grup ketersediaan pada replika primer dan sekunder. Kueri sys.dm_hadr_automatic_seeding pada replika utama dan sekunder. Misalnya, jalankan kueri berikut untuk mengidentifikasi status kegagalan penyemaian otomatis.

SELECT start_time, 
    completion_time, 
    is_source, 
    current_state, 
    failure_state, 
    failure_state_desc, 
    error_code 
FROM sys.dm_hadr_automatic_seeding

Pertimbangan penyemaian dan performa otomatis

SQL Server menggunakan jumlah utas tetap untuk seeding otomatis. Pada instans utama, SQL Server menggunakan satu utas per LUN untuk membaca perubahan. Pada instans sekunder SQL Server menggunakan satu utas per LUN untuk menginisialisasi database.

Atur bendera pelacakan 9567 pada replika utama untuk mengaktifkan pemadatan aliran data selama penyemaian otomatis. Ini dapat secara signifikan mengurangi waktu transfer penyemaian otomatis, namun juga meningkatkan penggunaan CPU. Untuk informasi selengkapnya, lihat Menyetel kompresi untuk grup ketersediaan.

Kapan tidak menggunakan seeding otomatis

Dalam beberapa skenario, penyemaian otomatis mungkin tidak optimal untuk menginisialisasi replika sekunder. Selama penyemaian otomatis, SQL Server melakukan pencadangan melalui jaringan untuk inisialisasi. Proses ini bisa lambat jika database sangat besar, atau replika sekunder jarak jauh. Log transaksi untuk database ini tidak dapat dipotong selama proses pencadangan, sehingga proses inisialisasi yang berkepanjangan pada database yang sibuk dapat mengakibatkan pertumbuhan log transaksi yang signifikan. Sebelum menambahkan database ke grup ketersediaan dengan seeding otomatis, evaluasi ukuran database, muat, dan jarak situs antar replika.

Lihat juga

• BUAT GRUP KETERSEDIAAN (Transact-SQL)
• Memantau dan memecahkan masalah grup ketersediaan