Mengelola data riwayat dalam Tabel temporal dengan kebijakan penyimpanan

Berlaku untuk: Azure SQL Database Azure SQL Managed Instance

Tabel temporal dapat meningkatkan ukuran database lebih dari tabel biasa, terutama jika Anda menyimpan data riwayat untuk jangka waktu yang lebih lama. Oleh karena itu, kebijakan penyimpanan untuk data riwayat adalah aspek penting dalam merencanakan dan mengelola siklus hidup setiap tabel temporal. Tabel temporal di Azure SQL Database dan Azure SQL Managed Instance hadir dengan mekanisme retensi yang mudah digunakan yang membantu Anda menyelesaikan tugas ini.

Retensi riwayat temporal dapat dikonfigurasi pada tingkat tabel individu, yang memungkinkan pengguna untuk membuat kebijakan rentang waktu yang fleksibel. Menerapkan retensi temporal cukup sederhana: hanya memerlukan satu parameter untuk diset selama pembuatan tabel atau perubahan skema.

Setelah Anda menentukan kebijakan penyimpanan, Azure SQL Database dan Azure SQL Managed Instance mulai memeriksa secara teratur jika ada baris riwayat yang memenuhi syarat untuk pembersihan data otomatis. Identifikasi baris yang cocok dan penghapusannya dari tabel riwayat terjadi secara transparan, di proses di latar belakang yang dijadwalkan dan dijalankan oleh sistem. Kondisi rentang waktu untuk baris tabel riwayat dicentang berdasarkan kolom yang mewakili akhir periode SYSTEM_TIME. Jika periode retensi, misalnya, diatur ke enam bulan, baris tabel yang memenuhi syarat untuk pembersihan memenuhi kondisi berikut:

ValidTo < DATEADD (MONTH, -6, SYSUTCDATETIME())

Dalam contoh sebelumnya, kita berasumsi bahwa kolom ValidTo sesuai dengan akhir periode SYSTEM_TIME.

Cara mengonfigurasi kebijakan penyimpanan

Sebelum Anda mengonfigurasi kebijakan penyimpanan untuk tabel temporal, periksa terlebih dahulu apakah retensi riwayat temporal diaktifkan di tingkat database.

SELECT is_temporal_history_retention_enabled, name
FROM sys.databases

Bendera database is_temporal_history_retention_enabled diatur ke ON secara default, tetapi pengguna bisa mengubahnya dengan pernyataan ALTER DATABASE. Ini juga secara otomatis diatur ke OFF setelah operasi pemulihan titik waktu. Untuk mengaktifkan pembersihan retensi riwayat temporal untuk database Anda, jalankan pernyataan berikut:

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION  ON

Penting

Anda dapat mengonfigurasi retensi untuk tabel temporal meskipun is_temporal_history_retention_enabled nonaktif, tetapi pembersihan otomatis untuk baris yang sudah tua tidak dipicu dalam kasus tersebut.

Kebijakan penyimpanan dikonfigurasi selama pembuatan tabel dengan menentukan nilai untuk parameter HISTORY_RETENTION_PERIOD:

CREATE TABLE dbo.WebsiteUserInfo
(  
    [UserID] int NOT NULL PRIMARY KEY CLUSTERED
  , [UserName] nvarchar(100) NOT NULL
  , [PagesVisited] int NOT NULL
  , [ValidFrom] datetime2 (0) GENERATED ALWAYS AS ROW START
  , [ValidTo] datetime2 (0) GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )  
 WITH
 (
     SYSTEM_VERSIONING = ON
     (
        HISTORY_TABLE = dbo.WebsiteUserInfoHistory,
        HISTORY_RETENTION_PERIOD = 6 MONTHS
     )
 );

Azure SQL Database dan Azure SQL Managed Instance memungkinkan Anda menentukan periode retensi dengan menggunakan unit waktu yang berbeda: DAYS, WEEKS, MONTHS, dan YEARS. Jika HISTORY_RETENTION_PERIOD dihilangkan, retensi INFINITE diasumsikan. Anda juga dapat menggunakan kata kunci INFINITE secara eksplisit.

Dalam beberapa skenario, Anda mungkin ingin mengonfigurasi retensi setelah pembuatan tabel, atau mengubah nilai yang dikonfigurasi sebelumnya. Dalam hal ini gunakan pernyataan ALTER TABLE:

ALTER TABLE dbo.WebsiteUserInfo
SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 9 MONTHS));

Penting

Pengaturan SYSTEM_VERSIONING ke OFF tidak mempertahankan nilai periode retensi. Pengaturan SYSTEM_VERSIONING ke ON tanpa HISTORY_RETENTION_PERIOD secara eksplisit menghasilkan periode retensi INFINITE.

Untuk mengulas status kebijakan penyimpanan saat ini, gunakan kueri berikut yang bergabung dengan bendera pengaktifan retensi temporal di tingkat database dengan periode retensi untuk tabel individu:

SELECT DB.is_temporal_history_retention_enabled,
SCHEMA_NAME(T1.schema_id) AS TemporalTableSchema,
T1.name as TemporalTableName,  SCHEMA_NAME(T2.schema_id) AS HistoryTableSchema,
T2.name as HistoryTableName,T1.history_retention_period,
T1.history_retention_period_unit_desc
FROM sys.tables T1  
OUTER APPLY (select is_temporal_history_retention_enabled from sys.databases
where name = DB_NAME()) AS DB
LEFT JOIN sys.tables T2
ON T1.history_table_id = T2.object_id WHERE T1.temporal_type = 2

Bagaimana baris berumur dihapus

Proses pembersihan tergantung pada tata letak indeks tabel riwayat. Penting untuk memperhatikan bahwa hanya tabel riwayat dengan indeks berkluster (pohon B atau penyimpanan) yang dapat memiliki kebijakan penyimpanan terbatas yang dikonfigurasi. Proses di latar belakang dibuat untuk melakukan pembersihan data lama untuk semua tabel temporal dengan periode retensi terbatas. Logika penghapusan untuk indeks kluster rowstore (pohon B) menghapus baris rentang waktu dalam gugus yang lebih kecil (hingga 10K) meminimalkan tekanan pada log database dan subsistem IO. Meskipun logika pembersihan menggunakan indeks pohon B yang diperlukan, urutan penghapusan untuk baris yang lebih tua dari periode retensi tidak dapat dijamin dengan kuat. Oleh karena itu, jangan mengambil dependensi pada urutan pembersihan dalam aplikasi Anda.

Tugas pembersihan untuk penyimpan kolom berkluster menghapus seluruh grup baris sekaligus (biasanya berisi masing-masing 1 juta baris), yang sangat efisien, terutama ketika data riwayat dihasilkan dengan kecepatan tinggi.

Kompresi data yang sangat baik dan penghapusan retensi yang efisien membuat indeks penyimpan kolom berkluster sebuah pilihan sempurna untuk skenario ketika beban kerja Anda dengan cepat menghasilkan data riwayat dalam jumlah tinggi. Pola tersebut biasanya untuk beban kerja pemrosesan transaksional intensif yang menggunakan tabel temporal untuk pelacakan perubahan dan audit, analisis tren, atau konsumsi data IoT.

Pertimbangan indeks

Tugas penghapusan untuk tabel dengan indeks berkluster rowstore memerlukan indeks untuk memulai dengan kolom yang sesuai dengan akhir periode SYSTEM_TIME. Jika indeks tersebut tidak ada, Anda tidak dapat mengonfigurasi periode retensi terbatas:

Msg 13765, Tingkat 16, Status 1

Pengaturan periode retensi terbatas gagal pada tabel temporal versi sistem 'temporalstagetestdb.dbo.WebsiteUserInfo' karena tabel riwayat 'temporalstagetestdb.dbo.WebsiteUserInfoHistory' tidak berisi indeks berkluster yang diperlukan. Pertimbangkan untuk membuat penyimpan kolom terkluster atau indeks pohon B dimulai dengan kolom yang cocok dengan periode SYSTEM_TIME, pada tabel riwayat.

Penting untuk memperhatikan bahwa tabel riwayat default yang dibuat oleh Azure SQL Database dan Azure SQL Managed Instance sudah memiliki indeks berkluster, yang sesuai dengan kebijakan penyimpanan. Jika Anda mencoba menghapus indeks tersebut pada tabel dengan periode retensi terbatas, operasi gagal dengan kesalahan berikut:

Msg 13766, Tingkat 16, Status 1

Tidak dapat menghilangkan indeks berkluster 'WebsiteUserInfoHistory.IX_WebsiteUserInfoHistory' karena sedang digunakan untuk pembersihan otomatis data lama. Pertimbangkan untuk mengatur HISTORY_RETENTION_PERIOD ke INFINITE pada tabel temporal versi sistem yang sesuai jika Anda perlu menghilangkan indeks ini.

Pembersihan pada indeks penyimpan kolom berkluster bekerja secara optimal jika baris riwayat disisipkan dalam urutan naik (diurutkan pada akhir kolom periode), yang selalu terjadi ketika tabel riwayat diisi secara eksklusif oleh mekanisme SYSTEM_VERSIONIOING. Jika baris dalam tabel riwayat tidak diurutkan pada akhir kolom periode (yang mungkin terjadi jika Anda memigrasikan data riwayat yang ada), Anda harus membuat ulang indeks penyimpan kolom berkluster di atas indeks rowstore pohon B yang diurutkan dengan benar, untuk mencapai performa yang optimal.

Hindari membangun kembali indeks penyimpan kolom berkluster pada tabel riwayat dengan periode retensi terbatas, karena dapat mengubah pemesanan dalam grup baris yang secara alami diberlakukan oleh operasi pembuatan versi sistem. Jika Anda perlu membangun kembali indeks penyimpan kolom berkluster pada tabel riwayat, lakukan dengan membuatnya kembali di atas indeks pohon B yang sesuai, mempertahankan pemesanan di grup baris yang diperlukan untuk pembersihan data reguler. Pendekatan yang sama harus diambil jika Anda membuat tabel temporal dengan tabel riwayat yang ada yang memiliki indeks kolom berkluster tanpa jaminan urutan data:

/*Create B-tree ordered by the end of period column*/
CREATE CLUSTERED INDEX IX_WebsiteUserInfoHistory ON WebsiteUserInfoHistory (ValidTo)
WITH (DROP_EXISTING = ON);
GO
/*Re-create clustered columnstore index*/
CREATE CLUSTERED COLUMNSTORE INDEX IX_WebsiteUserInfoHistory ON WebsiteUserInfoHistory
WITH (DROP_EXISTING = ON);

Saat periode retensi terbatas dikonfigurasi untuk tabel riwayat dengan indeks kolom penyimpanan berkluster, Anda tidak dapat membuat indeks pohon B yang tidak berkluster tambahan pada tabel tersebut:

CREATE NONCLUSTERED INDEX IX_WebHistNCI ON WebsiteUserInfoHistory ([UserName])

Upaya untuk mengeksekusi pernyataan di atas gagal dengan kesalahan berikut:

Msg 13772, Tingkat 16, Status 1

Tidak dapat membuat indeks yang tidak berkluster pada tabel riwayat temporal 'WebsiteUserInfoHistory' karena memiliki periode retensi yang terbatas dan indeks penyimpanan kolom berkluster yang ditentukan.

Membuat kueri tabel dengan kebijakan penyimpanan

Semua kueri pada tabel temporal secara otomatis menyaring baris riwayat yang cocok dengan kebijakan penyimpanan terbatas, untuk menghindari hasil yang tidak dapat diprediksi dan tidak konsisten, karena baris yang sudah tua dapat dihapus oleh tugas pembersihan, kapan saja dan dalam urutan arbitrer.

Gambar berikut ini memperlihatkan rencana kueri untuk kueri sederhana:

SELECT * FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

Rencana kueri menyertakan filter tambahan yang diterapkan ke kolom akhir periode (ValidTo) di operator Pemindaian Indeks Berkluster pada tabel riwayat (disorot). Contoh ini mengasumsikan bahwa periode retensi satu BULAN ditetapkan pada tabel WebsiteUserInfo.

Namun, jika Anda mengkueri tabel riwayat secara langsung, Anda mungkin melihat baris yang lebih lama dari periode retensi tertentu, tetapi tanpa jaminan apa pun untuk hasil kueri yang dapat diulang. Gambar berikut ini memperlihatkan rencana eksekusi kueri untuk kueri pada tabel riwayat tanpa filter tambahan diterapkan:

Jangan mengandalkan logika bisnis Anda pada membaca tabel riwayat di luar periode retensi karena Anda mungkin mendapatkan hasil yang tidak konsisten atau tidak terduga. Kami merekomendasikan Anda menggunakan kueri temporal dengan klausul FOR SYSTEM_TIME untuk menganalisis data dalam tabel temporal.

Pertimbangan pemulihan titik waktu

Saat Anda membuat database baru dengan memulihkan database yang ada ke titik waktu tertentu, itu telah menonaktifkan retensi sementara di tingkat database. (Bendera is_temporal_history_retention_enabled diatur ke OFF). Fungsionalitas ini memungkinkan Anda untuk memeriksa semua baris historis pada saat pemulihan, tanpa khawatir bahwa baris lama dihapus sebelum Anda dapat mengkuerinya. Anda bisa menggunakannya untuk memeriksa data riwayat di luar periode retensi yang dikonfigurasi.

Katakanlah tabel temporal memiliki satu periode retensi MONTH yang ditentukan. Jika database Anda dibuat di tingkat Layanan Premium, Anda akan dapat membuat salinan database dengan status database hingga 35 hari yang lalu. Itu secara efektif akan memungkinkan Anda menganalisis baris historis yang berusia hingga 65 hari dengan mengkueri tabel riwayat secara langsung.

Jika Anda ingin mengaktifkan pembersihan penyimpanan sementara, jalankan pernyataan Transact-SQL berikut setelah titik waktu pemulihan:

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION  ON

Langkah berikutnya

Untuk mempelajari cara menggunakan tabel temporal di aplikasi Anda, lihat Memulai Tabel Temporal.

Untuk informasi terperinci tentang tabel temporal, tinjau Tabel temporal.