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 terkait diterapkan ke database sekunder. Untuk replika sekunder yang khas, data, termasuk tabel yang dioptimalkan memori tahan lama, dalam database sekunder mendekati 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 baca-niat ke replika sekunder yang dapat dibaca (perutean baca-saja). Untuk informasi tentang perutean baca-saja, lihat Menggunakan Pendengar untuk Menyambungkan ke Replika Sekunder Read-Only (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 laba atas 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 Read-Only Access Databases, nanti dalam topik ini.
Beban kerja baca-saja untuk tabel berbasis disk menggunakan penerapan versi baris untuk menghapus ketidakcocokan pemblokiran 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 memori yang dioptimalkan 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 tersedia yang dapat dibaca. 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 ketika 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 Read-Only untuk Grup Ketersediaan (SQL Server).
Catatan
Untuk informasi tentang listener grup ketersediaan dan informasi selengkapnya tentang perutean baca-saja, lihat Listener 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 hingga semua transaksi aktif yang ada di replika utama ketika replika sekunder diaktifkan untuk baca selesai. Ini memastikan bahwa tabel berbasis disk dan memori dioptimalkan tersedia untuk beban kerja pelaporan dan kueri baca-saja secara bersamaan.
Pelacakan perubahan dan perubahan pengambilan data tidak didukung pada database sekunder milik 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 rekaman hantu akan secara otomatis membersihkan rekaman 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 yang berjalan lama yang memblokir pembersihan hantu. Perhatikan, bersih hantu dapat diblokir jika replika sekunder terputus atau ketika pergerakan data ditangguhkan pada database sekunder. Rekaman 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.
Mulai 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 oleh 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
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 mengirimkan 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 penyempitan 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
Pada 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. Mulai 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 Read-Only
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 memori dioptimalkan, di primer. Saat utas REDO memproses rekaman log untuk menghilangkan tabel, utas 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 Memory-Optimized Tabel. Hilangnya prosedur tersimpan asli dapat menyebabkan utas REDO memblokir 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 fase pengulangan.
Untuk memantau aktivitas penggunaan indeks pada replika sekunder, kueri kolom user_seeks, user_scans, dan user_lookups dari tampilan manajemen dinamis sys.dm_db_index_usage_stats .
Statistik untuk Database Access Read-Only
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 penerapan 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 di 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
SQL Server mendeteksi kapan statistik permanen pada database sekunder kedaluarsa. 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 kedaluarsa.
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 disediakan 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 tempdb.
Statistik sementara untuk database sekunder dibuat dan dipertahankan dalam tempdb. Statistik sementara dapat menyebabkan sedikit peningkatan ukuran tempdb. Untuk informasi selengkapnya, lihat Statistik untuk Read-Only Database Access, 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. Sebaliknya, database sekunder menghasilkan versi baris. Namun, penerapan versi baris meningkatkan penyimpanan data di database primer 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 Read-Only pada Replika Ketersediaan (SQL Server)
Mengonfigurasi Perutean Read-Only untuk Grup Ketersediaan (SQL Server)
Membuat atau Mengonfigurasi Listener Grup Ketersediaan (SQL Server)
Gunakan Kotak Dialog Grup Ketersediaan Baru (SQL Server Management Studio)
Konten terkait
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
Saran dan Komentar
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat: https://aka.ms/ContentUserFeedback.
Kirim dan lihat umpan balik untuk