Membongkar beban kerja baca-saja ke replika sekunder grup ketersediaan AlwaysOn

Berlaku untuk: SQL Server

Kemampuan sekunder aktif grup ketersediaan AlwaysOn mencakup dukungan untuk akses baca-saja ke satu atau beberapa replika sekunder (replika sekunder yang dapat dibaca). Replika sekunder yang dapat dibaca dapat berada dalam mode ketersediaan penerapan sinkron, atau mode ketersediaan penerapan asinkron. Replika sekunder yang dapat dibaca memungkinkan akses baca-saja ke semua database sekundernya. Namun, database sekunder yang dapat dibaca tidak diatur ke baca-saja. Mereka dinamis. Perubahan database sekunder tertentu saat perubahan pada database utama yang sesuai diterapkan ke database sekunder. Untuk replika sekunder yang khas, data, termasuk tabel memori tahan lama yang dioptimalkan, dalam database sekunder hampir real time. Selain itu, indeks teks lengkap disinkronkan dengan database sekunder. Dalam banyak keadaan, latensi data antara database utama dan database sekunder yang sesuai hanya beberapa detik.

Pengaturan keamanan yang terjadi di database utama dipertahankan ke database sekunder. Ini termasuk peran pengguna, peran database, dan aplikasi bersama dengan izin masing-masing dan enkripsi data transparan (TDE), jika diaktifkan pada database utama.

Catatan

Meskipun Anda tidak dapat menulis data ke database sekunder, Anda dapat menulis ke database baca-tulis pada instans server yang menghosting replika sekunder, termasuk database pengguna dan database sistem seperti tempdb.

Grup ketersediaan AlwaysOn juga mendukung perutean ulang permintaan koneksi niat baca ke replika sekunder yang dapat dibaca (perutean baca-saja). Untuk informasi tentang perutean baca-saja, lihat Menggunakan Listener untuk Menyambungkan ke Replika Sekunder Baca-Saja (Perutean Baca-Saja).

Keuntungan

Mengarahkan koneksi baca-saja ke replika sekunder yang dapat dibaca memberikan manfaat berikut:

• Membongkar beban kerja baca-saja sekunder Anda dari replika utama Anda, yang menghemat sumber dayanya untuk beban kerja misi penting Anda. Jika Anda memiliki beban kerja baca penting misi atau beban kerja yang tidak dapat mentolerir latensi, Anda harus menjalankannya di primer.

• Meningkatkan pengembalian investasi Anda untuk sistem yang menghosting replika sekunder yang dapat dibaca.

Selain itu, sekunder yang dapat dibaca memberikan dukungan yang kuat untuk operasi baca-saja, sebagai berikut:

• Statistik sementara otomatis pada database sekunder yang dapat dibaca mengoptimalkan kueri baca-saja pada tabel berbasis disk. Untuk tabel yang dioptimalkan memori, statistik yang hilang dibuat secara otomatis. Namun, tidak ada pembaruan otomatis statistik kedaluarsa. Anda harus memperbarui statistik secara manual pada replika utama. Untuk informasi selengkapnya, lihat Statistik untuk Database Akses Baca-Saja, nanti dalam topik ini.

• Beban kerja baca-saja untuk tabel berbasis disk menggunakan penerapan versi baris untuk menghapus pemblokiran ketidakcocokan pada database sekunder. Semua kueri yang berjalan terhadap database sekunder secara otomatis dipetakan ke tingkat transaksi isolasi rekam jepret, bahkan ketika tingkat isolasi transaksi lainnya diatur secara eksplisit. Selain itu, semua petunjuk penguncian diabaikan. Ini menghilangkan ketidakcocokan pembaca/penulis.

• Beban kerja baca-saja untuk tabel tahan lama yang dioptimalkan memori mengakses data dengan cara yang sama persis seperti yang diakses pada database utama, menggunakan prosedur tersimpan asli atau Interoperabilitas SQL dengan batasan tingkat isolasi transaksi yang sama (Lihat Tingkat Isolasi di Mesin Database). Melaporkan beban kerja atau kueri baca-saja yang berjalan pada replika utama dapat dijalankan pada replika sekunder tanpa memerlukan perubahan apa pun. Demikian pula, beban kerja pelaporan atau kueri baca-saja yang berjalan pada replika sekunder dapat dijalankan pada replika utama tanpa memerlukan perubahan apa pun. Mirip dengan tabel berbasis disk, semua kueri yang berjalan terhadap database sekunder secara otomatis dipetakan ke tingkat transaksi isolasi rekam jepret, bahkan ketika tingkat isolasi transaksi lainnya diatur secara eksplisit.

• Operasi DML diizinkan pada variabel tabel baik untuk jenis tabel berbasis disk maupun yang dioptimalkan memori pada replika sekunder.

Prasyarat untuk Grup Ketersediaan

• Replika sekunder yang dapat dibaca (diperlukan)

  Administrator database perlu mengonfigurasi satu atau beberapa replika sehingga, saat berjalan di bawah peran sekunder, mereka mengizinkan semua koneksi (hanya untuk akses baca-saja) atau hanya koneksi baca-niat.

  Catatan

  Secara opsional, administrator database dapat mengonfigurasi salah satu replika ketersediaan untuk mengecualikan koneksi baca-saja saat berjalan di bawah peran utama.

  Untuk informasi selengkapnya, lihat Tentang Akses Koneksi Klien ke Replika Ketersediaan (SQL Server).

  Peringatan

  Hanya replika yang berada di build utama SQL Server yang sama yang dapat dibaca. Lihat Dasar-dasar peningkatan bergulir untuk informasi selengkapnya.

• Pendengar grup ketersediaan

  Untuk mendukung perutean baca-saja, grup ketersediaan harus memiliki pendengar grup ketersediaan. Klien baca-saja harus mengarahkan permintaan koneksinya ke pendengar ini, dan string koneksi klien harus menentukan niat aplikasi sebagai "baca-saja." Artinya, mereka harus menjadi permintaan koneksi baca-niat.

• Perutean baca saja

  Perutean baca-saja mengacu pada kemampuan SQL Server untuk merutekan permintaan koneksi baca-niat masuk, yang diarahkan ke pendengar grup ketersediaan, ke replika sekunder yang dapat dibaca yang tersedia. Prasyarat untuk perutean baca-saja adalah sebagai berikut:

  • Untuk mendukung perutean baca-saja, replika sekunder yang dapat dibaca memerlukan URL perutean baca-saja. URL ini hanya berlaku ketika replika lokal berjalan di bawah peran sekunder. URL perutean baca-saja harus ditentukan berdasarkan replika demi replika, sesuai kebutuhan. Setiap URL perutean baca-saja digunakan untuk merutekan permintaan koneksi niat baca ke replika sekunder tertentu yang dapat dibaca. Biasanya, setiap replika sekunder yang dapat dibaca diberi URL perutean baca-saja.

  • Setiap replika ketersediaan yang mendukung perutean baca-saja saat replika utama memerlukan daftar perutean baca-saja. Daftar perutean baca-saja yang diberikan hanya berlaku ketika replika lokal berjalan di bawah peran utama. Daftar ini harus ditentukan berdasarkan replika demi replika, sesuai kebutuhan. Biasanya, setiap daftar perutean baca-saja akan berisi setiap URL perutean baca-saja, dengan URL replika lokal di akhir daftar.

    Catatan

    Permintaan koneksi baca-niat dapat diseimbangkan beban di seluruh replika. Untuk informasi selengkapnya, lihat Mengonfigurasi penyeimbangan beban di seluruh replika baca-saja.

  Untuk informasi selengkapnya, lihat Mengonfigurasi Perutean Baca-Saja untuk Grup Ketersediaan (SQL Server).

Catatan

Untuk informasi tentang pendengar grup ketersediaan dan informasi selengkapnya tentang perutean baca-saja, lihat Pendengar Grup Ketersediaan, Konektivitas Klien, dan Failover Aplikasi (SQL Server).

Batasan dan Pembatasan

Beberapa operasi tidak didukung sepenuhnya, sebagai berikut:

• Segera setelah replika yang dapat dibaca diaktifkan untuk dibaca, replika dapat mulai menerima koneksi ke database sekundernya. Namun, jika ada transaksi aktif pada database utama, versi baris tidak akan sepenuhnya tersedia pada database sekunder yang sesuai. Setiap transaksi aktif yang ada pada replika utama ketika replika sekunder dikonfigurasi harus diterapkan atau digulung balik. Sampai proses ini selesai, pemetaan tingkat isolasi transaksi pada database sekunder tidak lengkap dan kueri diblokir sementara.

  Peringatan

  Menjalankan transaksi panjang berdampak pada jumlah baris versi yang disimpan, baik untuk tabel berbasis disk maupun memori yang dioptimalkan.

• Pada database sekunder dengan tabel yang dioptimalkan memori, meskipun versi baris selalu dihasilkan untuk tabel yang dioptimalkan memori, kueri diblokir sampai semua transaksi aktif yang ada di replika utama ketika replika sekunder diaktifkan untuk baca selesai. Ini memastikan bahwa tabel berbasis disk dan memori yang dioptimalkan tersedia untuk beban kerja pelaporan dan kueri baca-saja secara bersamaan.

• Pelacakan perubahan dan perubahan pengambilan data tidak didukung pada database sekunder yang termasuk dalam replika sekunder yang dapat dibaca:

  • Pelacakan perubahan secara eksplisit dinonaktifkan pada database sekunder.

  • Ubah Pengambilan Data tidak dapat diaktifkan hanya pada database replika sekunder. Ubah Pengambilan Data dapat diaktifkan pada database replika utama dan perubahan dapat dibaca dari tabel CDC menggunakan fungsi pada database replika sekunder.

• Karena operasi baca dipetakan ke tingkat transaksi isolasi rekam jepret, pembersihan catatan hantu pada replika utama dapat diblokir oleh transaksi pada satu atau beberapa replika sekunder. Tugas pembersihan catatan hantu akan secara otomatis membersihkan catatan hantu untuk tabel berbasis disk pada replika utama ketika mereka tidak lagi diperlukan oleh replika sekunder apa pun. Ini mirip dengan apa yang dilakukan ketika Anda menjalankan transaksi pada replika utama. Dalam kasus ekstrem pada database sekunder, Anda harus membunuh kueri baca jangka panjang yang memblokir pembersihan hantu. Perhatikan, bersih hantu dapat diblokir jika replika sekunder terputus atau ketika pergerakan data ditangguhkan pada database sekunder. Catatan hantu menggunakan ruang fisik dalam file data, ini dapat menyebabkan masalah penggunaan kembali ruang, silakan lihat pembersihan hantu untuk informasi selengkapnya. Status ini juga mencegah pemotongan log, jadi jika status ini berlanjut, kami sarankan Anda menghapus database sekunder ini dari grup ketersediaan. Tidak ada masalah pembersihan rekaman hantu dengan tabel yang dioptimalkan memori karena versi baris disimpan dalam memori dan independen dari versi baris pada replika utama.

• Operasi DBCC SHRINKFILE pada file yang berisi tabel berbasis disk mungkin gagal pada replika utama jika file berisi rekaman hantu yang masih diperlukan pada replika sekunder.

• Dimulai di SQL Server 2014 (12.x), replika sekunder yang dapat dibaca dapat tetap online bahkan ketika replika utama offline karena tindakan pengguna atau kegagalan, misalnya, sinkronisasi ditangguhkan karena perintah pengguna atau kegagalan, atau replika menyelesaikan status karena WSFC sedang offline. Namun, perutean baca-saja tidak berfungsi dalam situasi ini karena pendengar grup ketersediaan juga offline. Klien harus terhubung langsung ke replika sekunder baca-saja untuk beban kerja baca-saja.

Catatan

Jika Anda mengkueri tampilan manajemen dinamis sys.dm_db_index_physical_stats pada instans server yang menghosting replika sekunder yang dapat dibaca, Anda mungkin mengalami masalah pemblokiran REDO. Ini karena tampilan manajemen dinamis ini memperoleh kunci IS pada tabel pengguna atau tampilan yang ditentukan yang dapat memblokir permintaan dengan utas REDO untuk kunci X pada tabel atau tampilan pengguna tersebut.

Pertimbangan Performa

Bagian ini membahas beberapa pertimbangan performa untuk database sekunder yang dapat dibaca

Di bagian ini:

• Latensi Data

• Dampak Beban Kerja Baca-Saja

• Pengindeksan

• Statistik untuk Database Akses Baca-Saja

Latensi Data

Menerapkan akses baca-saja ke replika sekunder berguna jika beban kerja baca-saja Anda dapat mentolerir beberapa latensi data. Dalam situasi di mana latensi data tidak dapat diterima, pertimbangkan untuk menjalankan beban kerja baca-saja terhadap replika utama.

Replika utama mengirim catatan log perubahan pada database utama ke replika sekunder. Pada setiap database sekunder, utas pengulangan khusus menerapkan catatan log. Pada database sekunder akses baca, perubahan data tertentu tidak muncul dalam hasil kueri hingga rekaman log yang berisi perubahan telah diterapkan ke database sekunder dan transaksi telah dilakukan pada database utama.

Ini berarti bahwa ada beberapa latensi, biasanya hanya hitungan detik, antara replika primer dan sekunder. Namun, dalam kasus yang tidak biasa, misalnya jika masalah jaringan mengurangi throughput, latensi dapat menjadi signifikan. Latensi meningkat ketika hambatan I/O terjadi dan ketika pergerakan data ditangguhkan. Untuk memantau pergerakan data yang ditangguhkan, Anda dapat menggunakan Dasbor AlwaysOn atau tampilan manajemen dinamis sys.dm_hadr_database_replica_states .

Latensi Data pada database dengan tabel yang dioptimalkan memori

Di SQL Server 2014 (12.x) ada pertimbangan khusus sekeliling latensi data pada sekunder aktif - lihat SQL Server 2014 (12.x) Sekunder Aktif: Replika Sekunder yang Dapat Dibaca. Memulai SQL Server 2016 (13.x) tidak ada pertimbangan khusus sekeliling latensi data untuk tabel yang dioptimalkan memori. Latensi data yang diharapkan untuk tabel yang dioptimalkan memori sebanding dengan latensi untuk tabel berbasis disk.

Dampak Beban Kerja Baca-Saja

Saat Anda mengonfigurasi replika sekunder untuk akses baca-saja, beban kerja baca-saja Anda pada database sekunder menggunakan sumber daya sistem, seperti CPU dan I/O (untuk tabel berbasis disk) dari utas pengulangan, terutama jika beban kerja baca-saja pada tabel berbasis disk sangat intensif I/O. Tidak ada dampak IO saat mengakses tabel yang dioptimalkan memori karena semua baris berada dalam memori.

Selain itu, beban kerja baca-saja pada replika sekunder dapat memblokir perubahan bahasa definisi data (DDL) yang diterapkan melalui rekaman log.

• Meskipun operasi baca tidak mengambil kunci bersama karena penerapan versi baris, operasi ini mengambil kunci stabilitas skema (Sch-S), yang dapat memblokir operasi pengulangan yang menerapkan perubahan DDL. Operasi DDL mencakup tabel ALTER/DROP dan Tampilan tetapi tidak DROP atau ALTER prosedur tersimpan. Jadi misalnya, jika Anda menjatuhkan tabel berbasis disk atau dioptimalkan memori, pada primer. Saat utas REDO memproses catatan log untuk menghilangkan tabel, alur tersebut harus memperoleh kunci SCH_M pada tabel dan bisa diblokir oleh tabel akses kueri yang sedang berjalan. Ini adalah perilaku yang sama pada replika utama kecuali bahwa penurunan tabel dilakukan sebagai bagian dari sesi pengguna dan bukan utas REDO.

• Ada pemblokiran tambahan Tabel yang Dioptimalkan Memori. Penurunan prosedur tersimpan asli dapat menyebabkan utas REDO diblokir jika ada eksekusi bersamaan dari prosedur tersimpan asli pada replika sekunder. Ini adalah perilaku yang sama pada replika utama kecuali bahwa penurunan prosedur tersimpan dilakukan sebagai bagian dari sesi pengguna dan bukan utas REDO.

Waspadai praktik terbaik sekeliling membangun kueri, dan lakukan praktik terbaik tersebut di database sekunder. Misalnya, jadwalkan kueri yang berjalan lama seperti agregasi data selama waktu aktivitas rendah.

Catatan

Jika utas pengulangan diblokir oleh kueri pada replika sekunder, sqlserver.lock_redo_blocked XEvent akan dinaikkan.

Pengindeksan

Untuk mengoptimalkan beban kerja baca-saja pada replika sekunder yang dapat dibaca, Anda mungkin ingin membuat indeks pada tabel di database sekunder. Karena Anda tidak dapat membuat perubahan skema atau data pada database sekunder, buat indeks di database utama dan izinkan perubahan untuk ditransfer ke database sekunder melalui proses pengulangan.

Untuk memantau aktivitas penggunaan indeks pada replika sekunder, kueri kolom user_seeks, user_scans, dan user_lookups tampilan manajemen dinamis sys.dm_db_index_usage_stats .

Statistik untuk Database Akses Baca-Saja

Statistik pada kolom tabel dan tampilan terindeks digunakan untuk mengoptimalkan rencana kueri. Untuk grup ketersediaan, statistik yang dibuat dan dikelola pada database utama secara otomatis bertahan pada database sekunder sebagai bagian dari menerapkan catatan log transaksi. Namun, beban kerja baca-saja pada database sekunder mungkin memerlukan statistik yang berbeda dari yang dibuat pada database utama. Namun, karena database sekunder dibatasi untuk akses baca-saja, statistik tidak dapat dibuat pada database sekunder.

Untuk mengatasi masalah ini, replika sekunder membuat dan mempertahankan statistik sementara untuk database sekunder dalam tempdb. Akhiran _readonly_database_statistic ditambahkan ke nama statistik sementara untuk membedakannya dari statistik permanen yang dipertahankan dari database utama.

Hanya SQL Server yang dapat membuat dan memperbarui statistik sementara. Namun, Anda dapat menghapus statistik sementara dan memantau propertinya menggunakan alat yang sama dengan yang Anda gunakan untuk statistik permanen:

• Hapus statistik sementara menggunakan pernyataan DROP STATISTICS Transact-SQL.

• Pantau statistik menggunakan tampilan katalog sys.stats dan sys.stats_columns . sys_stats menyertakan kolom, is_temporary, untuk menunjukkan statistik mana yang permanen dan mana yang bersifat sementara.

Tidak ada dukungan untuk pembaruan statistik otomatis untuk tabel yang dioptimalkan memori pada replika primer atau sekunder. Anda harus memantau performa dan rencana kueri pada replika sekunder dan memperbarui statistik secara manual pada replika utama saat diperlukan. Namun, statistik yang hilang secara otomatis dibuat baik pada replika primer maupun sekunder.

Untuk informasi selengkapnya tentang statistik SQL Server, lihat Statistik.

Di bagian ini:

• Statistik Permanen Kedaluarsa pada Database Sekunder

• Batasan dan Pembatasan

Statistik Permanen Kedaluarsa pada Database Sekunder

SQL Server mendeteksi kapan statistik permanen pada database sekunder basi. Tetapi perubahan tidak dapat dilakukan pada statistik permanen kecuali melalui perubahan pada database utama. Untuk pengoptimalan kueri, SQL Server membuat statistik sementara untuk tabel berbasis disk pada database sekunder dan menggunakan statistik ini alih-alih statistik permanen basi.

Ketika statistik permanen diperbarui pada database utama, statistik tersebut secara otomatis bertahan ke database sekunder. Kemudian SQL Server menggunakan statistik permanen yang diperbarui, yang lebih terkini daripada statistik sementara.

Jika grup ketersediaan gagal, statistik sementara akan dihapus pada semua replika sekunder.

Batasan dan Pembatasan

• Karena statistik sementara disimpan dalam tempdb, restart layanan SQL Server menyebabkan semua statistik sementara menghilang.

• Akhiran _readonly_database_statistic dicadangkan untuk statistik yang dihasilkan oleh SQL Server. Anda tidak dapat menggunakan akhiran ini saat membuat statistik pada database utama. Untuk informasi selengkapnya, lihat Statistik.

Mengakses tabel yang dioptimalkan memori pada Replika Sekunder

Tingkat isolasi transaksi yang dapat digunakan dengan tabel yang dioptimalkan memori pada replika sekunder sama seperti pada replika utama. Rekomendasinya adalah mengatur tingkat isolasi tingkat sesi ke READ COMMITTED dan mengatur opsi tingkat database MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT ke AKTIF. Contohnya:

ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON  
GO  
SET TRANSACTION ISOLATION LEVEL READ COMMITTED  
GO  
SELECT SUM(UnitPrice*OrderQty)   
FROM Sales.SalesOrderDetail_inmem  
GO  
  

Pertimbangan Perencanaan Kapasitas

• Dalam kasus tabel berbasis disk, replika sekunder yang dapat dibaca dapat memerlukan ruang di tempdb karena dua alasan:

  • Tingkat isolasi rekam jepret menyalin versi baris ke dalam tempdb.

  • Statistik sementara untuk database sekunder dibuat dan dikelola dalam tempdb. Statistik sementara dapat menyebabkan sedikit peningkatan ukuran tempdb. Untuk informasi selengkapnya, lihat Statistik untuk Database Akses Baca-Saja, nanti di bagian ini.

• Saat Anda mengonfigurasi akses baca untuk satu atau beberapa replika sekunder, database utama menambahkan 14 byte overhead pada baris data yang dihapus, dimodifikasi, atau disisipkan untuk menyimpan penunjuk ke versi baris pada database sekunder untuk tabel berbasis disk. Overhead 14 byte ini dibawa ke database sekunder. Karena overhead 14 byte ditambahkan ke baris data, pemisahan halaman mungkin terjadi.

  Data versi baris tidak dihasilkan oleh database utama. Sebagai gantinya, database sekunder menghasilkan versi baris. Namun, penerapan versi baris meningkatkan penyimpanan data di database utama dan sekunder.

  Penambahan data versi baris tergantung pada pengaturan tingkat isolasi rekam jepret atau isolasi rekam jepret yang diterapkan baca (RCSI) pada database utama. Tabel di bawah ini menjelaskan perilaku penerapan versi pada database sekunder yang dapat dibaca di bawah pengaturan yang berbeda untuk tabel berbasis disk.

  Replika sekunder yang dapat dibaca?	Isolasi rekam jepret atau tingkat RCSI diaktifkan?	Database Utama	Database Sekunder
  Tidak	Tidak	Tidak ada versi baris atau overhead 14-byte	Tidak ada versi baris atau overhead 14-byte
  Tidak	Ya	Versi baris dan overhead 14-byte	Tidak ada versi baris, tetapi overhead 14-byte
  Ya	Tidak	Tidak ada versi baris, tetapi overhead 14-byte	Versi baris dan overhead 14-byte
  Ya	Ya	Versi baris dan overhead 14-byte	Versi baris dan overhead 14-byte

Tugas Terkait

• Mengonfigurasi Akses Baca-Saja pada Replika Ketersediaan (SQL Server)

• Mengonfigurasi Perutean Baca-Saja untuk Grup Ketersediaan (SQL Server)

• Membuat atau Mengonfigurasi Listener Grup Ketersediaan (SQL Server)

• Memantau Grup Ketersediaan (Transact-SQL)

• Lihat Properti Replika Ketersediaan (SQL Server)

• Gunakan Kotak Dialog Grup Ketersediaan Baru (SQL Server Management Studio)

Konten Terkait

• Blog SQL Server Always On Team: Blog resmi SQL Server Always On Team

Lihat Juga

Gambaran Umum Grup Ketersediaan AlwaysOn (SQL Server)
Tentang Akses Koneksi Klien ke Replika Ketersediaan (SQL Server)
Listener Grup Ketersediaan, Konektivitas Klien, dan Kegagalan Aplikasi (SQL Server)
Statistik