Mengelola pemulihan database yang dipercepat

  • Artikel

Berlaku untuk: SQL Server 2019 (15.x)

Artikel ini berisi informasi tentang praktik terbaik untuk mengelola dan mengonfigurasi pemulihan database terakselerasi (ADR) di SQL Server 2019 (15.x) dan yang lebih baru. Untuk informasi selengkapnya tentang ADR di Azure SQL, lihat Pemulihan Database Yang Dipercepat di Azure SQL.

Catatan

Di Azure SQL Database dan Azure SQL Managed Instance, pemulihan database terakselerasi (ADR) diaktifkan pada semua database dan tidak dapat dinonaktifkan. Jika Anda mengamati masalah baik dengan penggunaan penyimpanan, transaksi pembatalan tinggi, dan faktor lain, tinjau Memecahkan masalah pemulihan database yang dipercepat atau hubungi Dukungan Azure.

Siapa harus mempertimbangkan pemulihan database yang dipercepat

Banyak pelanggan menemukan pemulihan database yang dipercepat (ADR) teknologi yang berharga untuk meningkatkan waktu pemulihan. Akumulasi faktor-faktor di bawah ini harus dipertimbangkan sebelum memutuskan database mana yang harus menggunakan ADR, mengevaluasi faktor-faktor di bawah ini dan jika akumulasi faktor-faktor menimbang mendukung atau melawan penggunaan ADR.

  • ADR direkomendasikan untuk beban kerja dengan transaksi jangka panjang yang tidak dapat dihindari. Misalnya, dalam kasus di mana transaksi jangka panjang berisiko digulung balik, ADR dapat membantu.

  • Beban kerja yang telah melihat kasus di mana transaksi aktif menyebabkan log transaksi tumbuh secara signifikan.

  • ADR direkomendasikan untuk beban kerja yang telah mengalami periode panjang tidak tersedianya database karena pemulihan SQL Server yang berjalan lama (seperti mulai ulang SQL Server yang tidak terduga atau pembatalan transaksi manual).

  • ADR tidak didukung untuk database yang terdaftar dalam pencerminan database.

  • ADR tidak disarankan untuk database yang lebih besar dari 100 terabyte karena pembersih versi penyimpanan versi persisten (PVS) berulir tunggal.

  • Jika aplikasi Anda melakukan banyak pembaruan non-batch bertahap, seperti memperbarui rekaman setiap kali ada baris yang diakses/disisipkan, beban kerja Anda mungkin tidak optimal untuk ADR. Pertimbangkan untuk menulis ulang kueri aplikasi ke pembaruan batch, jika memungkinkan, hingga akhir perintah dan mengurangi sejumlah besar transaksi pembaruan kecil.

Mengevaluasi apakah beban kerja Anda cocok untuk ADR

Setelah Anda mengaktifkan ADR dalam beban kerja Anda, cari tanda bahwa penyimpanan versi persisten (PVS) tidak dapat mengikuti. Disarankan untuk memantau kesehatan ADR menggunakan metode yang ditemukan dalam Pemecahan Masalah pemulihan database yang dipercepat.

ADR tidak disarankan untuk lingkungan database dengan jumlah pembaruan/penghapusan yang tinggi, seperti OLTP volume tinggi, tanpa periode istirahat/pemulihan untuk proses pembersihan PVS untuk mengklaim kembali ruang. Biasanya, siklus operasi bisnis memungkinkan untuk saat ini, tetapi dalam beberapa skenario Anda mungkin ingin memulai proses pembersihan PVS secara manual untuk memanfaatkan kondisi aktivitas aplikasi.

  • Jika proses pembersihan PVS berjalan untuk waktu yang lama, Anda mungkin menemukan bahwa jumlah transaksi yang dibatalkan akan tumbuh yang juga akan menyebabkan ukuran PVS meningkat. sys.dm_tran_aborted_transactions Gunakan DMV untuk melaporkan jumlah transaksi yang dibatalkan, dan gunakan sys.dm_tran_persistent_version_store_stats untuk melaporkan waktu mulai/akhir pembersihan bersama dengan ukuran PVS. Untuk informasi selengkapnya, lihat sys.dm_tran_persistent_version_store_stats.

  • Untuk mengaktifkan proses pembersihan PVS secara manual antara beban kerja atau selama jendela pemeliharaan, gunakan sys.sp_persistent_version_cleanup. Untuk informasi selengkapnya, lihat sys.sp_persistent_version_cleanup.

  • Beban kerja yang menampilkan kueri jangka panjang dalam mode isolasi SNAPSHOT atau READ COMMITTED SNAPSHOT dapat menunda pembersihan PVS ADR di database lain, menyebabkan file PVS bertambah. Untuk informasi selengkapnya, lihat bagian tentang pemindaian rekam jepret aktif panjang di Memecahkan masalah pemulihan database yang dipercepat. Ini berlaku untuk instans SQL Server dan Azure SQL Managed Instance, atau di kumpulan elastis Azure SQL Database.

Praktik terbaik untuk pemulihan database yang dipercepat

Bagian ini berisi panduan dan rekomendasi untuk ADR.

  • Untuk SQL Server, isolasi penyimpanan versi PVS ke grup file pada penyimpanan tingkat yang lebih tinggi, seperti SSD kelas atas atau SSD tingkat lanjut atau Memori Persisten (PMEM), terkadang disebut sebagai Storage Class Memory (SCM). Untuk informasi selengkapnya, lihat Mengubah lokasi PVS ke grup file yang berbeda. Opsi ini tidak tersedia untuk Azure SQL Database dan Azure SQL Managed Instance.

  • Pastikan ada cukup ruang pada database untuk memperhitungkan penggunaan PVS. Jika database tidak memiliki cukup ruang bagi PVS untuk tumbuh, ADR akan gagal menghasilkan versi. ADR menghemat ruang di penyimpanan versi dibandingkan tempdb dengan penyimpanan versi.

  • Hindari beberapa transaksi yang berjalan lama dalam database. Meskipun salah satu tujuan ADR adalah untuk mempercepat pemulihan database karena pengulangan, beberapa transaksi yang berjalan lama dapat menunda pembersihan versi dan meningkatkan ukuran PVS.

  • Hindari transaksi besar dengan perubahan definisi data atau operasi DDL. ADR menggunakan mekanisme SLOG (system log stream) untuk melacak operasi DDL yang digunakan dalam pemulihan. SLOG hanya digunakan saat transaksi aktif. SLOG diperiksa, sehingga menghindari transaksi besar yang menggunakan SLOG dapat membantu kinerja secara keseluruhan. Skenario ini dapat menyebabkan SLOG mengambil lebih banyak ruang:

    • Banyak DDLs dieksekusi dalam satu transaksi. Misalnya, dalam satu transaksi, dengan cepat membuat dan menjatuhkan tabel temp.

    • Tabel memiliki sejumlah besar partisi/indeks yang dimodifikasi. Misalnya, operasi DROP TABLE pada tabel tersebut akan memerlukan reservasi besar memori SLOG, yang akan menunda pemotongan log transaksi dan menunda operasi undo/redo. Solusinya adalah menjatuhkan indeks satu per satu dan bertahap, lalu menghilangkan tabel. Untuk informasi selengkapnya tentang SLOG, lihat komponen pemulihan ADR.

  • Mencegah atau mengurangi situasi yang tidak perlu dibatalkan. Tingkat abort yang tinggi akan memberi tekanan pada kinerja PVS yang lebih bersih dan ADR yang lebih rendah. Aborts mungkin berasal dari tingkat kebuntuan yang tinggi, kunci duplikat, atau pelanggaran kendala lainnya.

    • DMV sys.dm_tran_aborted_transactions menunjukkan semua transaksi yang dibatalkan pada instans SQL Server. Kolom nested_abort menunjukkan bahwa transaksi dilakukan tetapi ada bagian yang dibatalkan (titik simpan atau transaksi bersarang) yang dapat memblokir proses pembersihan PVS. Untuk informasi selengkapnya, lihat sys.dm_tran_aborted_transactions (Transact-SQL).

Mengaktifkan dan mengontrol ADR

Catatan

Di Azure SQL Database dan Azure SQL Managed Instance, pemulihan database terakselerasi (ADR) diaktifkan pada semua database dan tidak dapat dinonaktifkan atau dipindahkan ke grup file yang berbeda.

ADR nonaktif secara default di SQL Server 2019 (15.x), dan dapat dikontrol menggunakan sintaks DDL:

ALTER DATABASE [DB] SET ACCELERATED_DATABASE_RECOVERY = {ON | OFF};

Gunakan sintaks ini untuk mengontrol apakah fitur aktif atau nonaktif, dan tentukan grup file tertentu untuk data penyimpanan versi persisten (PVS). Jika tidak ada grup file yang ditentukan, PVS akan disimpan dalam grup file PRIMER.

Kunci eksklusif diperlukan pada database untuk mengubah status ini. Itu berarti bahwa perintah ALTER DATABASE akan mengulur waktu sampai semua sesi aktif hilang, dan bahwa setiap sesi baru akan menunggu di belakang perintah ALTER DATABASE. Jika penting untuk menyelesaikan operasi dan menghapus kunci, Anda dapat menggunakan klausa penghentian, WITH ROLLBACK [IMMEDIATE | AFTER {number} SECONDS | NO_WAIT] untuk membatalkan sesi aktif apa pun dalam database. Untuk informasi selengkapnya, lihat MENGUBAH opsi kumpulan DATABASE.

Mengelola grup file penyimpanan versi persisten

Fitur ADR didasarkan pada memiliki perubahan versi, dengan versi elemen data yang berbeda yang disimpan di PVS. Ada pertimbangan untuk menemukan lokasi PVS dan cara mengelola ukuran data di PVS.

Untuk mengaktifkan ADR tanpa menentukan grup file

Operasi ini memerlukan akses eksklusif ke database.

ALTER DATABASE [MyDatabase] SET ACCELERATED_DATABASE_RECOVERY = ON;
GO

Dalam hal ini, ketika grup file PVS tidak ditentukan, PRIMARY grup file menyimpan data PVS.

Untuk mengaktifkan ADR dan menentukan bahwa PVS harus disimpan dalam grup file

Anda dapat mengonfigurasi ADR untuk menggunakan grup file lain, selain dari grup file default PRIMARY , untuk menyimpan data PVS.

Sebelum mengaktifkan ADR dalam grup file selain PRIMARY, Anda harus membuat grup file dan file data.

VersionStoreFG Buat grup file dan buat file data baru di grup file. Contohnya:

ALTER DATABASE [MyDatabase] ADD FILEGROUP [VersionStoreFG];
GO
ALTER DATABASE [MyDatabase] ADD FILE ( NAME = N'VersionStoreFG'
, FILENAME = N'E:\DATA\VersionStore.ndf'
, SIZE = 8192KB , FILEGROWTH = 65536KB )
TO FILEGROUP [VersionStoreFG];
GO

Hanya setelah grup file dan file data sekunder dibuat, aktifkan ADR dan tentukan bahwa PVS harus disimpan dalam grup file tertentu. Operasi ini memerlukan akses eksklusif ke database.

ALTER DATABASE [MyDatabase] SET ACCELERATED_DATABASE_RECOVERY = ON
(PERSISTENT_VERSION_STORE_FILEGROUP = [VersionStoreFG]);

Untuk menonaktifkan fitur ADR

ALTER DATABASE [MyDatabase] SET ACCELERATED_DATABASE_RECOVERY = OFF;
GO

Bahkan setelah fitur ADR dinonaktifkan, akan ada versi yang disimpan di penyimpanan versi persisten yang masih diperlukan oleh sistem untuk kembali logis.

Mengubah lokasi PVS ke grup file yang berbeda

Di SQL Server, Anda mungkin perlu memindahkan lokasi PVS ke grup file yang berbeda karena berbagai alasan. Misalnya, PVS mungkin memerlukan lebih banyak ruang, atau penyimpanan yang lebih cepat.

Mengubah lokasi PVS adalah proses tiga langkah.

  1. Nonaktifkan fitur ADR.

    ALTER DATABASE [MyDatabase] SET ACCELERATED_DATABASE_RECOVERY = OFF;
GO

  2. Tunggu hingga semua versi yang disimpan dalam PVS dapat dibebaskan.

    Agar dapat mengaktifkan ADR dengan lokasi baru untuk penyimpanan versi persisten, Anda harus terlebih dahulu memastikan bahwa semua informasi versi telah dihapus menyeluruh dari lokasi PVS sebelumnya. Untuk memaksa pembersihan tersebut terjadi, jalankan perintah:

    EXEC sys.sp_persistent_version_cleanup [database name];

    sys.sp_persistent_version_cleanup prosedur tersimpan sinkron, yang berarti tidak akan selesai sampai semua informasi versi dibersihkan dari PVS saat ini. Setelah selesai, Anda dapat memverifikasi bahwa informasi versi memang dihapus dengan mengkueri DMV sys.dm_tran_persistent_version_store_stats dan memeriksa nilai persistent_version_store_size_kb.

    SELECT DB_Name(database_id), persistent_version_store_size_kb 
FROM sys.dm_tran_persistent_version_store_stats where database_id = [MyDatabaseID];

    Ketika nilai persistent_version_store_size_kb adalah 0, Anda dapat mengaktifkan kembali fitur ADR, mengonfigurasi PVS untuk ditempatkan di grup file baru.

  3. Aktifkan ADR, tentukan lokasi baru untuk PVS:

    ALTER DATABASE [MyDatabase] SET ACCELERATED_DATABASE_RECOVERY = ON
(PERSISTENT_VERSION_STORE_FILEGROUP = [VersionStoreFG]);

Langkah berikutnya