Opsi failover deteksi kesehatan tingkat database ketersediaan

Berlaku untuk: SQL Server

Mulai dari SQL Server 2016, opsi deteksi kesehatan tingkat database (DB_FAILOVER) tersedia saat mengonfigurasi grup ketersediaan AlwaysOn. Deteksi kesehatan tingkat database melihat ketika database tidak lagi dalam status online, ketika terjadi kesalahan, dan akan memicu failover otomatis grup ketersediaan. Contoh yang dapat memicu deteksi kesehatan termasuk database dalam mode tersangka, database offline, database dalam pemulihan (gagal dipulihkan). Untuk informasi selengkapnya, lihat Kolom status di sys.databases.

Deteksi kesehatan tingkat database diaktifkan untuk grup ketersediaan secara keseluruhan, oleh karena itu deteksi kesehatan tingkat database memantau setiap database dalam grup ketersediaan. Ini tidak dapat diaktifkan secara selektif untuk database tertentu dalam grup ketersediaan.

Manfaat opsi deteksi kesehatan tingkat database

Opsi deteksi kesehatan tingkat database grup ketersediaan sangat disarankan sebagai opsi yang baik untuk membantu menjamin ketersediaan tinggi untuk database Anda. Anda harus mempertimbangkan untuk mengaktifkannya untuk semua grup ketersediaan. Jika aplikasi Anda bergantung pada beberapa database yang sangat tersedia, kelompokkan ke dalam grup ketersediaan dengan opsi kesehatan database diaktifkan.

Misalnya, dengan opsi deteksi kesehatan tingkat database aktif, jika SQL Server tidak dapat menulis ke file log transaksi untuk salah satu database, status database tersebut akan berubah untuk menunjukkan kegagalan, dan grup ketersediaan akan segera gagal, dan aplikasi Anda dapat terhubung kembali dan terus bekerja dengan gangguan minimal setelah database online lagi.

Mengaktifkan deteksi kesehatan tingkat database

Meskipun umumnya disarankan, opsi Kesehatan Database nonaktif secara default, dalam upaya untuk menjaga kompatibilitas mundur dengan pengaturan default dalam versi sebelumnya.

Ada beberapa cara mudah untuk mengaktifkan pengaturan deteksi kesehatan tingkat database:

1. Di SQL Server Management Studio, sambungkan ke mesin database SQL Server Anda. Menggunakan jendela Object Explorer, klik kanan pada simpul Ketersediaan Tinggi AlwaysOn, dan jalankan Wizard Grup Ketersediaan Baru. Centang kotak Deteksi Kesehatan Tingkat Database pada halaman Tentukan Nama. Kemudian selesaikan halaman lainnya dalam wizard.

   

2. Lihat Properti Grup Ketersediaan yang ada di SQL Server Management Studio. Sambungkan ke SQL Server Anda. Menggunakan jendela Object Explorer, perluas simpul Ketersediaan Tinggi AlwaysOn. Perluas Grup Ketersediaan. Klik kanan pada grup ketersediaan, dan pilih Properti. Centang opsi Deteksi Kesehatan Tingkat Database, lalu klik OK atau Skrip perubahan.

   

3. Sintaks T-SQL untuk MEMBUAT GRUP KETERSEDIAAN. Parameter DB_FAILOVER menerima nilai AKTIF atau NONAKTIF.

   CREATE AVAILABILITY GROUP [Contoso-ag]
   WITH (DB_FAILOVER=ON)
   FOR DATABASE [AutoHa-Sample]
   REPLICA ON
       N'SQLSERVER-0' WITH (ENDPOINT_URL = N'TCP://SQLSERVER-0.DOMAIN.COM:5022',
         FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT),
       N'SQLSERVER-1' WITH (ENDPOINT_URL = N'TCP://SQLSERVER-1.DOMAIN.COM:5022',
        FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);
   

4. Sintaks Transact-SQL untuk MENGUBAH GRUP KETERSEDIAAN. Parameter DB_FAILOVER menerima nilai AKTIF atau NONAKTIF.

   ALTER AVAILABILITY GROUP [Contoso-ag] SET (DB_FAILOVER = ON);
   
   ALTER AVAILABILITY GROUP [Contoso-ag] SET (DB_FAILOVER = OFF);
   

Peringatan

Penting untuk dicatat bahwa opsi Deteksi Heath Tingkat Database saat ini tidak menyebabkan SQL Server memantau waktu aktif disk dan SQL Server tidak secara langsung memantau ketersediaan file database. Jika disk drive gagal atau menjadi tidak tersedia, itu saja tidak akan selalu memicu grup ketersediaan untuk melakukan failover secara otomatis.

Sebagai contoh, ketika database tidak aktif tanpa transaksi aktif, dan tanpa penulisan fisik yang terjadi, jika beberapa file database menjadi tidak dapat diakses, SQL Server mungkin tidak melakukan IO baca atau tulis apa pun ke file, dan mungkin tidak segera mengubah status untuk database tersebut, sehingga tidak ada failover yang akan dipicu. Kemudian, ketika titik pemeriksaan database terjadi, atau baca atau tulis fisik terjadi untuk memenuhi kueri, maka SQL Server kemudian dapat melihat masalah file, dan bereaksi dengan mengubah status database, dan kemudian grup ketersediaan dengan deteksi kesehatan tingkat database yang diatur akan gagal karena perubahan kesehatan database.

Sebagai contoh lain, ketika mesin database SQL Server perlu membaca halaman data untuk memenuhi kueri, jika halaman data di-cache dalam memori kumpulan buffer, maka tidak ada disk yang dibaca dengan akses fisik yang mungkin diperlukan untuk memenuhi permintaan kueri. Oleh karena itu, file data yang hilang atau tidak tersedia mungkin tidak segera memicu failover otomatis bahkan ketika opsi kesehatan database diaktifkan, karena status database tidak segera diperbarui.

Failover database terpisah dari kebijakan failover yang fleksibel

Deteksi kesehatan tingkat database menerapkan kebijakan failover fleksibel yang mengonfigurasi ambang batas kesehatan proses SQL Server untuk kebijakan failover. Deteksi kesehatan tingkat database dikonfigurasi menggunakan parameter DB_FAILOVER, sedangkan opsi grup ketersediaan FAILURE_CONDITION_LEVEL terpisah untuk mengonfigurasi deteksi kesehatan proses SQL Server. Dua opsi tersebut bersifat independen.

Mengelola dan memantau deteksi kesehatan tingkat database

Dynamic Management Views

Sistem DMV sys.availability_groups memperlihatkan kolom db_failover yang menunjukkan apakah opsi deteksi kesehatan tingkat database nonaktif (0) atau aktif (1).

select name, db_failover from sys.availability_groups

Contoh output dmv:

nama	db_failover
Contoso-ag	1

ErrorLog

SQL Server Errorlog (atau teks dari sp_readerrorlog) akan menampilkan pesan kesalahan 41653 ketika grup ketersediaan telah gagal, karena pemeriksaan deteksi kesehatan tingkat database.

Misalnya, kutipan errorlog ini menunjukkan bahwa penulisan log transaksi telah gagal karena masalah disk, dan kemudian database bernama AutoHa-Sample dimatikan, yang memicu deteksi kesehatan tingkat database untuk failover grup ketersediaan.

2016-04-25 12:20:21.08 spid1s Kesalahan: 17053, Tingkat Keparahan: 16, Status: 1.

2016-04-25 12:20:21.08 spid1s SQLServerLogMgr::LogWriter: Kesalahan sistem operasi 21(Perangkat belum siap.) ditemui. 2016-04-25 12:20:21.08 spid1s Kesalahan tulis selama flush log.

2016-04-25 12:20:21.08 spid79 Kesalahan: 9001, Tingkat Keparahan: 21, Status: 4.

2016-04-25 12:20:21.08 spid79 Log untuk database 'AutoHa-Sample' tidak tersedia. Periksa log peristiwa untuk pesan kesalahan terkait. Atasi kesalahan apa pun dan mulai ulang database.

2016-04-25 12:20:21.15 spid79 Kesalahan: 41653, Tingkat Keparahan: 21, Status: 1.

2016-04-25 12:20:21.15 spid79 Database 'AutoHa-Sample' mengalami kesalahan (jenis kesalahan: 2 'DB_SHUTDOWN') menyebabkan kegagalan grup ketersediaan 'Contoso-ag'. Lihat log kesalahan SQL Server untuk informasi tentang kesalahan yang ditemui. Jika kondisi ini berlanjut, hubungi administrator sistem.

2016-04-25 12:20:21.17 informasi status spid79 untuk database 'AutoHa-Sample' - Hardened Lsn: '(34:34:664:1)' Penerapan LSN: '(34:656:1)' Waktu Penerapan: '25 Apr 2016 12:19PM'

2016-04-25 12:20:21.19 spid15s Koneksi Grup Ketersediaan AlwaysOn dengan database sekunder dihentikan untuk database utama 'AutoHa-Sample' pada repl ketersediaanica 'SQLServer-0' dengan ID Replika: {c4ad5ea4-8a99-41fa-893e-189154c24b49}. Ini hanya pesan informasi. Tidak diperlukan tindakan pengguna.

2016-04-25 12:20:21.21 spid75 Always On: Replika lokal grup ketersediaan 'Contoso-ag' bersiap untuk beralih ke peran pemecahan sebagai respons terhadap permintaan dari kluster Windows Server Failover Clustering (WSFC). Ini hanya pesan informasi. Tidak diperlukan tindakan pengguna.

2016-04-25 12:20:21.21 spid75 Status replika ketersediaan lokal dalam grup ketersediaan 'ag' telah berubah dari 'PRIMARY_NORMAL' menjadi 'RESOLVING_NORMAL'. Status berubah karena grup ketersediaan sedang offline. Replika akan offline karena grup ketersediaan terkait telah dihapus, atau pengguna telah mengambil grup ketersediaan terkait secara offline di konsol manajemen Windows Server Failover Clustering (WSFC), atau grup ketersediaan gagal ke instans SQL Server lain. Untuk informasi selengkapnya, lihat log kesalahan SQL Server, konsol manajemen Windows Server Failover Clustering (WSFC), atau log WSFC.

Sqlserver.availability_replica_database_fault_reporting Peristiwa yang Diperluas

Ada Peristiwa Diperpanjang baru yang ditentukan mulai dari SQL Server 2016 yang dipicu oleh deteksi kesehatan tingkat database. Nama peristiwa sqlserver.availability_replica_database_fault_reporting

XEvent ini dipicu pada replika utama saja. XEvent ini dipicu ketika masalah kesehatan tingkat database terdeteksi untuk database yang dihosting dalam grup ketersediaan.

Berikut adalah contoh untuk membuat sesi XEvent yang menangkap peristiwa ini. Karena tidak ada jalur yang ditentukan, file output XEvent harus terletak di jalur log kesalahan SQL Server default. Jalankan ini pada replika utama grup ketersediaan Anda:

Contoh Skrip Sesi Peristiwa yang Diperluas

CREATE EVENT SESSION [AlwaysOn_dbfault] ON SERVER
ADD EVENT sqlserver.availability_replica_database_fault_reporting
ADD TARGET package0.event_file(SET filename=N'dbfault.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_dbfault ON SERVER STATE=START
GO

Output Peristiwa yang Diperluas

Menggunakan SQL Server Management Studio, sambungkan ke SQL Server utama, dan perluas simpul Manajemen, lalu perluas Peristiwa yang Diperluas. Temukan sesi (AlwaysOn_dbfault adalah nama dalam sampel di atas) dan perluas untuk melihat file output. Pilih file output dan file peristiwa akan terbuka ke tab baru.

Penjelasan bidang:

Data Kolom	Deskripsi
availability_group_id	ID grup ketersediaan.
availability_group_name	Nama grup ketersediaan.
availability_replica_id	ID replika ketersediaan.
availability_replica_name	Nama replika ketersediaan.
database_name	Nama database yang melaporkan kesalahan.
database_replica_id	ID database replika ketersediaan.
failover_ready_replicas	Jumlah replika sekunder failover otomatis yang disinkronkan.
fault_type	ID kesalahan dilaporkan. Nilai yang memungkinkan: 0 - TIDAK ADA 1 - Tidak diketahui 2 - Matikan
is_critical	Nilai ini harus selalu mengembalikan true untuk XEvent per SQL Server 2016.

Dalam contoh output ini, fault_type menunjukkan bahwa peristiwa penting terjadi pada grup ketersediaan Contoso-ag, pada replika bernama SQLSERVER-1, karena nama database AutoHa-Sample2, dengan jenis kesalahan 2- Matikan.

Bidang	Nilai
availability_group_id	24E6FE58-5EE8-4C4E-9746-491CFBB208C1
availability_group_name	Contoso-ag
availability_replica_id	3EAE74D1-A22F-4D9F-8E9A-DEFF99B1F4D1
availability_replica_name	SQLSERVER-1
database_name	AutoHa-Sample2
database_replica_id	39971379-8161-4607-82E7-098590E5AE00
failover_ready_replicas	1
fault_type	2
is_critical	Benar

Referensi Terkait

• MEMBUAT GRUP KETERSEDIAAN

• MENGUBAH GRUP KETERSEDIAAN

• Kebijakan Failover Fleksibel untuk Failover Otomatis Grup Ketersediaan (SQL Server)

• Meningkatkan Kebijakan Failover AlwaysOn untuk Menguji Data Database SQL Server dan Drive Log

• Aktivitas yang Diperluas

• Cara mengonfigurasi grup ketersediaan AlwaysOn untuk melakukan failover saat file data database tidak tersedia