Mengelola retensi data historis dalam tabel temporal versi sistem
Berlaku untuk:
SQL Server 2016 (13.x) dan yang lebih baru Azure SQL Database Azure SQL Managed Instance
Dengan tabel temporal versi sistem, tabel riwayat dapat meningkatkan ukuran database lebih dari tabel biasa, terutama dalam kondisi berikut:
- Anda menyimpan data historis untuk jangka waktu yang lama
- Anda memiliki pola modifikasi data berat atau pembaruan
Tabel riwayat yang besar dan terus berkembang dapat menjadi masalah baik karena biaya penyimpanan murni serta menerapkan pajak kinerja pada kueri temporal. Oleh karena itu, mengembangkan kebijakan retensi data untuk mengelola data dalam tabel riwayat adalah aspek penting dalam merencanakan dan mengelola siklus hidup setiap tabel temporal.
Manajemen retensi data untuk tabel riwayat
Mengelola retensi data tabel temporal dimulai dengan menentukan periode retensi yang diperlukan untuk setiap tabel temporal. Kebijakan retensi Anda, dalam kebanyakan kasus, harus dianggap sebagai bagian dari logika bisnis aplikasi menggunakan tabel temporal. Misalnya, aplikasi dalam skenario audit data dan perjalanan waktu memiliki persyaratan yang tegas dalam hal berapa lama data historis harus tersedia untuk kueri online.
Setelah Anda menentukan periode retensi data, langkah Anda selanjutnya adalah mengembangkan rencana untuk mengelola data historis bagaimana dan di mana Anda menyimpan data historis dan cara menghapus data historis yang lebih lama dari persyaratan retensi Anda. Empat pendekatan berikut untuk mengelola data historis dalam tabel riwayat temporal tersedia:
Dengan masing-masing pendekatan ini, logika untuk memigrasikan atau membersihkan data riwayat didasarkan pada kolom yang sesuai dengan akhir periode dalam tabel saat ini. Nilai akhir periode untuk setiap baris menentukan momen ketika versi baris menjadi "tertutup", yaitu ketika mendarat di tabel riwayat. Misalnya, kondisi ValidTo < DATEADD (DAYS, -30, SYSUTCDATETIME ()) menentukan bahwa data historis yang lebih lama dari satu bulan perlu dihapus atau dipindahkan dari tabel riwayat.
Catatan
Contoh dalam topik ini menggunakan contoh Tabel Temporal ini.
Menggunakan pendekatan stretch database
Catatan
Menggunakan pendekatan Stretch Database hanya berlaku untuk SQL Server dan tidak berlaku untuk SQL Database.
Stretch Database di SQL Server memigrasikan data historis Anda secara transparan ke Azure. Untuk keamanan tambahan, Anda dapat mengenkripsi data yang bergerak menggunakan fitur Always Encrypted SQL Server. Selain itu, Anda dapat menggunakan Keamanan Tingkat Baris dan fitur keamanan SQL Server tingkat lanjut lainnya dengan Temporal dan Stretch Database untuk melindungi data Anda.
Dengan menggunakan pendekatan Stretch Database, Anda dapat meregangkan beberapa atau semua tabel riwayat temporal Anda ke Azure dan SQL Server akan secara diam-diam memindahkan data historis ke Azure. Meregangkan mengaktifkan tabel riwayat tidak mengubah cara Anda berinteraksi dengan tabel temporal dalam hal modifikasi data dan kueri temporal.
Regangkan seluruh tabel riwayat: Konfigurasikan Stretch Database untuk seluruh tabel riwayat Anda jika skenario utama Anda adalah audit data di lingkungan dengan perubahan data yang sering dan kueri yang relatif jarang terjadi pada data historis. Dengan kata lain, gunakan pendekatan ini jika performa kueri temporal tidak penting. Dalam hal ini, efektivitas biaya yang disediakan oleh Azure mungkin menarik. Saat meregangkan seluruh tabel riwayat, Anda dapat menggunakan Stretch Wizard atau Transact-SQL. Contoh keduanya muncul di bawah ini.
Regangkan sebagian tabel riwayat: Konfigurasikan Stretch Database hanya untuk sebagian tabel riwayat Anda untuk meningkatkan performa jika skenario utama Anda terutama melibatkan kueri data historis terbaru, tetapi Anda ingin mempertahankan opsi untuk mengkueri data historis yang lebih lama saat diperlukan sambil menyimpan data ini dari jarak jauh dengan biaya yang lebih rendah. Dengan Transact-SQL, Anda dapat menyelesaikan ini dengan menentukan fungsi predikat untuk memilih baris yang akan dimigrasikan dari tabel riwayat daripada memigrasikan semua baris. Saat Anda bekerja dengan tabel temporal, biasanya masuk akal untuk memindahkan data berdasarkan kondisi waktu (yaitu berdasarkan usia versi baris dalam tabel riwayat).
Dengan menggunakan fungsi predikat deterministik, Anda dapat menyimpan sebagian riwayat dalam database yang sama dengan data saat ini, sementara sisanya dimigrasikan ke Azure. Untuk contoh dan batasan, lihat Memilih baris untuk dimigrasikan dengan menggunakan fungsi filter (Stretch Database). Karena fungsi non-deterministik tidak valid, jika Anda ingin mentransfer data riwayat dengan cara jendela geser, Anda harus secara teratur mengubah definisi fungsi predikat sebaris sehingga jendela baris yang Anda simpan secara lokal tetap konstan dalam hal usia. Jendela geser memungkinkan Anda untuk terus memindahkan data historis yang lebih lama dari satu bulan ke Azure. Contoh pendekatan ini muncul di bawah ini.
Catatan
Stretch Database memigrasikan data ke Azure. Oleh karena itu, Anda harus memiliki akun Azure dan langganan untuk penagihan. Untuk mendapatkan akun Azure uji coba gratis, klik Uji Coba One-Month Gratis.
Anda dapat mengonfigurasi tabel riwayat temporal untuk Stretch menggunakan Stretch Wizard atau Transact-SQL, dan Anda dapat mengaktifkan tabel riwayat temporal dengan peregangan saat penerapan versi sistem diatur ke AKTIF. Meregangkan tabel saat ini tidak diperbolehkan karena tidak masuk akal untuk meregangkan tabel saat ini.
Menggunakan Wizard Stretch untuk meregangkan seluruh tabel riwayat
Metode termampu untuk pemula adalah menggunakan Stretch Wizard untuk mengaktifkan stretch untuk seluruh database lalu memilih tabel riwayat temporal dalam wizard Stretch (contoh ini mengasumsikan bahwa Anda telah mengonfigurasi tabel Departemen sebagai tabel temporal versi sistem dalam database yang kosong). Di SQL Server 2016 (13.x), Anda tidak dapat mengklik kanan tabel riwayat temporal itu sendiri dan klik Stretch.
Klik kanan database Anda dan arahkan ke Tugas, arahkan ke Stretch, lalu klik Aktifkan untuk meluncurkan panduan.
Di jendela Pilih tabel , pilih kotak centang untuk tabel riwayat temporal dan klik Berikutnya.
Di jendela KonfigurasiKan Azure , berikan kredensial masuk Anda. Masuk ke Microsoft Azure atau daftar untuk akun. Pilih langganan yang akan digunakan, pilih wilayah Azure. Kemudian buat server baru atau pilih server yang sudah ada. Klik Berikutnya.
Di jendela Kredensial aman , berikan kata sandi untuk kunci master database untuk mengamankan kredensial database SQL Server sumber Anda dan klik Berikutnya.
Di jendela Pilih alamat IP , berikan rentang alamat IP untuk SQL Server Anda agar server Azure Anda dapat berkomunikasi dengan SQL Server Anda (jika Anda memilih server yang sudah ada yang aturan firewallnya sudah ada, cukup klik Berikutnya di sini untuk menggunakan aturan firewall yang ada). Klik Berikutnya lalu klik Selesai untuk mengaktifkan Stretch Database dan meregangkan tabel riwayat temporal.
Saat panduan selesai, verifikasi bahwa database Anda berhasil diaktifkan oleh peregangan. Perhatikan ikon di Object Explorer yang menunjukkan database direntangkan.
Catatan
Jika Aktifkan Database untuk Stretch gagal, tinjau log kesalahan. Kesalahan umum adalah mengonfigurasi aturan firewall secara tidak benar.
Lihat juga:
- Aktifkan Stretch Database untuk database
- Memulai dengan menjalankan Wizard Aktifkan Database untuk Stretch
- Aktifkan Stretch Database untuk tabel
Menggunakan Transact-SQL untuk meregangkan seluruh tabel riwayat
Anda juga dapat menggunakan Transact-SQL untuk mengaktifkan Stretch di server lokal dan Mengaktifkan Stretch Database untuk database. Anda kemudian dapat menggunakan Transact-SQL untuk mengaktifkan Stretch Database pada tabel. Dengan database yang sebelumnya diaktifkan untuk Stretch Database, jalankan skrip Transact-SQL berikut untuk meregangkan tabel riwayat temporal versi sistem yang ada:
ALTER TABLE [<history table name>]
SET (REMOTE_DATA_ARCHIVE = ON (MIGRATION_STATE = OUTBOUND));
Menggunakan Transact-SQL untuk meregangkan sebagian tabel riwayat
Untuk hanya meregangkan sebagian tabel riwayat, Anda mulai dengan membuat fungsi predikat sebaris. Untuk contoh ini, mari kita asumsikan bahwa Anda mengonfigurasi fungsi predikat sebaris untuk pertama kalinya pada 1 Desember 2015 dan ingin meregang ke Azure semua tanggal riwayat yang lebih lama dari 1 November 2015. Untuk mencapai hal ini, mulailah dengan membuat fungsi berikut:
CREATE FUNCTION dbo.fn_StretchBySystemEndTime20151101(@systemEndTime datetime2)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS is_eligible
WHERE @systemEndTime < CONVERT(datetime2, '2015-11-01T00:00:00', 101) ;
Selanjutnya, gunakan skrip berikut untuk menambahkan predikat filter ke tabel riwayat dan atur status migrasi ke OUTBOUND untuk mengaktifkan migrasi data berbasis predikat untuk tabel riwayat.
ALTER TABLE [<history table name>]
SET (
REMOTE_DATA_ARCHIVE = ON
(
FILTER_PREDICATE = dbo.fn_StretchBySystemEndTime20151101 (ValidTo)
, MIGRATION_STATE = OUTBOUND
)
)
;
Untuk mempertahankan jendela geser, Anda perlu membuat fungsi predikat agar akurat setiap hari (yaitu mengubah kondisi baris pemfilteran setiap hari satu hari). Skrip berikut adalah skrip yang perlu Anda jalankan pada 2 Desember 2015:
BEGIN TRAN
GO
/*(1) Create new predicate function definition */
CREATE FUNCTION dbo.fn_StretchBySystemEndTime20151102(@systemEndTime datetime2)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS is_eligible
WHERE @systemEndTime < CONVERT(datetime2,'2015-11-02T00:00:00', 101)
GO
/*(2) Set the new function as filter predicate */
ALTER TABLE [<history table name>]
SET
(
REMOTE_DATA_ARCHIVE = ON
(
FILTER_PREDICATE = dbo.fn_StretchBySystemEndTime20151102(ValidTo),
MIGRATION_STATE = OUTBOUND
)
)
GO
COMMIT ;
Gunakan SQL Server Agent atau beberapa mekanisme penjadwalan lainnya untuk memastikan definisi fungsi predikat yang valid sepanjang waktu.
Menggunakan pendekatan partisi tabel
Pemartisian tabel dapat membuat tabel besar lebih mudah dikelola dan dapat diskalakan. Dengan menggunakan pendekatan partisi tabel, Anda dapat menggunakan partisi tabel riwayat untuk menerapkan pembersihan data kustom atau arsip offline berdasarkan kondisi waktu. Pemartisian tabel juga akan memberi Anda manfaat performa saat mengkueri tabel temporal pada subset riwayat data dengan menggunakan penghapusan partisi.
Dengan pemartisian tabel, Anda dapat menerapkan pendekatan jendela geser untuk memindahkan bagian tertua dari data historis dari tabel riwayat dan menjaga ukuran konstanta bagian yang dipertahankan dalam hal usia - mempertahankan data dalam tabel riwayat sama dengan periode retensi yang diperlukan. Operasi mengalihkan data dari tabel riwayat didukung saat SYSTEM_VERSIONING AKTIF, yang berarti Anda dapat membersihkan sebagian data riwayat tanpa memperkenalkan jendela pemeliharaan atau memblokir beban kerja reguler Anda.
Catatan
Untuk melakukan pengalihan partisi, indeks berkluster Anda pada tabel riwayat harus diselaraskan dengan skema partisi (harus berisi ValidTo). Tabel riwayat default yang dibuat oleh sistem berisi indeks berkluster yang menyertakan kolom ValidTo dan ValidFrom, yang optimal untuk pemartisian, menyisipkan data riwayat baru, dan kueri temporal umum. Untuk informasi selengkapnya, lihat Tabel Temporal.
Pendekatan jendela geser memiliki dua set tugas yang perlu Anda lakukan:
- Tugas konfigurasi partisi
- Tugas pemeliharaan partisi berulang
Untuk ilustrasi, mari kita asumsikan bahwa kita ingin menyimpan data historis selama 6 bulan dan bahwa kita ingin menyimpan setiap bulan data dalam partisi terpisah. Selain itu, mari kita asumsikan bahwa kita mengaktifkan penerapan versi sistem pada bulan September 2015.
Tugas konfigurasi partisi membuat konfigurasi partisi awal untuk tabel riwayat. Untuk contoh ini, kami akan membuat partisi angka yang sama dengan ukuran jendela geser, dalam bulan, ditambah satu partisi kosong tambahan yang telah disiapkan sebelumnya (dijelaskan di bawah). Konfigurasi ini memastikan bahwa sistem akan dapat menyimpan data baru dengan benar ketika kita memulai tugas pemeliharaan partisi berulang untuk pertama kalinya dan menjamin bahwa kita tidak pernah membagi partisi dengan data untuk menghindari pergerakan data yang mahal. Anda harus melakukan tugas ini menggunakan Transact-SQL menggunakan contoh skrip di bawah ini.
Gambar berikut menunjukkan konfigurasi partisi awal untuk menyimpan data selama 6 bulan.
Catatan
Lihat Pertimbangan performa dengan partisi tabel di bawah ini untuk implikasi performa penggunaan RANGE LEFT versus RANGE RIGHT saat mengonfigurasi partisi.
Partisi pertama dan terakhir "terbuka" pada batas bawah dan atas masing-masing untuk memastikan bahwa setiap baris baru memiliki partisi tujuan terlepas dari nilai dalam kolom partisi. Seiring berjalannya waktu, baris baru dalam tabel riwayat akan mendarat di partisi yang lebih tinggi. Ketika partisi ke-6 terisi, kita akan mencapai periode retensi yang ditargetkan. Ini adalah saat untuk memulai tugas pemeliharaan partisi berulang untuk pertama kalinya (perlu dijadwalkan untuk berjalan secara berkala, sekali per bulan dalam contoh ini).
Gambar berikut mengilustrasikan tugas pemeliharaan partisi berulang (lihat langkah-langkah terperinci di bawah).
Langkah-langkah terperinci untuk tugas pemeliharaan partisi berulang adalah:
BERALIH KELUAR: Buat tabel penahapan lalu alihkan partisi antara tabel riwayat dan tabel penahapan menggunakan pernyataan ALTER TABLE (Transact-SQL) dengan argumen SWITCH PARTITION (lihat Contoh C. Beralih partisi antar tabel).
ALTER TABLE [<history table>] SWITCH PARTITION 1 TO [<staging table>]Setelah pengalihan partisi, Anda dapat secara opsional mengarsipkan data dari tabel penahapan lalu menghilangkan atau memotong tabel penahapan agar siap untuk waktu berikutnya Anda perlu melakukan tugas pemeliharaan partisi berulang ini.
MERGE RANGE: Gabungkan partisi kosong 1 dengan partisi 2 menggunakan ALTER PARTITION FUNCTION (Transact-SQL) dengan MERGE RANGE (Lihat contoh B). Dengan menghapus batas terendah menggunakan fungsi ini, Anda secara efektif menggabungkan partisi kosong 1 dengan partisi sebelumnya 2 untuk membentuk partisi baru 1. Partisi lain juga secara efektif mengubah ordinal mereka.
RENTANG TERPISAH: Buat partisi kosong baru 7 menggunakan ALTER PARTITION FUNCTION (Transact-SQL) dengan SPLIT RANGE (Lihat contoh A). Dengan menambahkan batas atas baru menggunakan fungsi ini, Anda secara efektif membuat partisi terpisah untuk bulan mendatang.
Menggunakan Transact-SQL untuk membuat partisi pada tabel riwayat
Gunakan skrip Transact-SQL di jendela kode di bawah ini untuk membuat fungsi partisi, skema partisi, dan membuat ulang indeks berkluster agar selaras dengan skema partisi, partisi. Untuk contoh ini, kami akan membuat pendekatan jendela geser enam bulan dengan partisi bulanan mulai September 2015.
BEGIN TRANSACTION
/*Create partition function*/
CREATE PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_ValidTo] (datetime2(7))
AS RANGE LEFT FOR VALUES
(
N'2015-09-30T23:59:59.999'
, N'2015-10-31T23:59:59.999'
, N'2015-11-30T23:59:59.999'
, N'2015-12-31T23:59:59.999'
, N'2016-01-31T23:59:59.999'
, N'2016-02-29T23:59:59.999'
)
/*Create partition scheme*/
CREATE PARTITION SCHEME [sch_Partition_DepartmentHistory_By_ValidTo]
AS PARTITION [fn_Partition_DepartmentHistory_By_ValidTo]
TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
/*Re-create index to be partition-aligned with the partitioning schema*/
CREATE CLUSTERED INDEX [ix_DepartmentHistory] ON [dbo].[DepartmentHistory]
(
[ValidTo] ASC
, [ValidFrom] ASC
)
WITH
(
PAD_INDEX = OFF
, STATISTICS_NORECOMPUTE = OFF
, SORT_IN_TEMPDB = OFF
, DROP_EXISTING = ON
, ONLINE = OFF
, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON
, DATA_COMPRESSION = PAGE
)
ON [sch_Partition_DepartmentHistory_By_ValidTo] ([ValidTo])
COMMIT TRANSACTION;
Menggunakan Transact-SQL untuk mempertahankan partisi dalam skenario jendela geser
Gunakan skrip Transact-SQL di jendela kode di bawah ini untuk mempertahankan partisi dalam skenario jendela geser. Untuk contoh ini, kami akan mengalihkan partisi untuk September 2015 menggunakan MERGE RANGE dan kemudian menambahkan partisi baru untuk Maret 2016 menggunakan SPLIT RANGE.
BEGIN TRANSACTION
/*(1) Create staging table */
CREATE TABLE [dbo].[staging_DepartmentHistory_September_2015]
(
[DeptID] [int] NOT NULL
, [DeptName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
, [ManagerID] [int] NULL
, [ParentDeptID] [int] NULL
, [ValidFrom] [datetime2](7) NOT NULL
, [ValidTo] [datetime2](7) NOT NULL
) ON [PRIMARY]
WITH
(
DATA_COMPRESSION = PAGE
)
/*(2) Create index on the same filegroups as the partition that will be switched out*/
CREATE CLUSTERED INDEX [ix_staging_DepartmentHistory_September_2015]
ON [dbo].[staging_DepartmentHistory_September_2015]
(
[ValidTo] ASC
, [ValidFrom] ASC
)
WITH
(
PAD_INDEX = OFF
, SORT_IN_TEMPDB = OFF
, DROP_EXISTING = OFF
, ONLINE = OFF
, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON
)
ON [PRIMARY]
/*(3) Create constraints matching the partition that will be switched out*/
ALTER TABLE [dbo].[staging_DepartmentHistory_September_2015] WITH CHECK
ADD CONSTRAINT [chk_staging_DepartmentHistory_September_2015_partition_1]
CHECK ([ValidTo]<=N'2015-09-30T23:59:59.999')
ALTER TABLE [dbo].[staging_DepartmentHistory_September_2015]
CHECK CONSTRAINT [chk_staging_DepartmentHistory_September_2015_partition_1]
/*(4) Switch partition to staging table*/
ALTER TABLE [dbo].[DepartmentHistory]
SWITCH PARTITION 1 TO [dbo].[staging_DepartmentHistory_September_2015]
WITH (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 MINUTES, ABORT_AFTER_WAIT = NONE))
/*(5) [Commented out] Optionally archive the data and drop staging table
INSERT INTO [ArchiveDB].[dbo].[DepartmentHistory]
SELECT * FROM [dbo].[staging_DepartmentHistory_September_2015];
DROP TABLE [dbo].[staging_DepartmentHIstory_September_2015];
*/
/*(6) merge range to move lower boundary one month ahead*/
ALTER PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_ValidTo]()
MERGE RANGE(N'2015-09-30T23:59:59.999')
/*(7) Create new empty partition for "April and after" by creating new boundary point and specifying NEXT USED file group*/
ALTER PARTITION SCHEME [sch_Partition_DepartmentHistory_By_ValidTo] NEXT USED [PRIMARY]
ALTER PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_ValidTo]() SPLIT RANGE(N'2016-03-31T23:59:59.999')
COMMIT TRANSACTION
Anda dapat sedikit memodifikasi skrip di atas dan menggunakannya dalam proses pemeliharaan bulanan reguler:
- Pada langkah (1) buat tabel penahapan baru untuk bulan yang ingin Anda hapus (Oktober akan menjadi berikutnya dalam contoh kami).
- Pada langkah (3) buat dan periksa batasan yang cocok dengan bulan data yang ingin Anda hapus:
[ValidTo]<=N'2015-10-31T23:59:59.999'untuk partisi Oktober. - Pada langkah (4) BERALIH partisi 1 ke tabel penahapan yang baru dibuat.
- Dalam langkah (6) ubah fungsi partisi dengan menggabungkan batas bawah:
MERGE RANGE(N'2015-10-31T23:59:59.999'setelah Anda memindahkan data untuk Oktober. - Dalam langkah (7) pisahkan fungsi partisi yang membuat batas atas baru:
SPLIT RANGE (N'2016-04-30T23:59:59.999'setelah Anda memindahkan data untuk Oktober.
Namun, solusi optimalnya adalah menjalankan skrip Transact-SQL generik secara teratur yang mampu melakukan tindakan yang sesuai setiap bulan tanpa modifikasi skrip. Dimungkinkan untuk menggeneralisasi skrip di atas untuk bertindak berdasarkan parameter yang disediakan (batas bawah yang perlu digabungkan dan batas baru yang akan dibuat dengan pemisahan partisi). Untuk menghindari pembuatan tabel penahapan setiap bulan, Anda dapat membuatnya sebelumnya dan menggunakan kembali dengan mengubah batasan pemeriksaan untuk mencocokkan partisi yang akan dialihkan. Lihat halaman berikut untuk mendapatkan ide tentang bagaimana jendela geser dapat sepenuhnya otomatis menggunakan skrip Transact-SQL.
Pertimbangan performa dengan partisi tabel
Penting untuk melakukan operasi MERGE dan SPLIT RANGE untuk menghindari pergerakan data karena pergerakan data dapat menimbulkan overhead performa yang signifikan. Untuk informasi selengkapnya, lihat Mengubah Fungsi Partisi. Anda menyelesaikan ini dengan menggunakan RANGE LEFT daripada RANGE RIGHT saat Anda MEMBUAT FUNGSI PARTISI (Transact-SQL).
Pertama-tama mari kita jelaskan arti opsi RANGE LEFT dan RANGE RIGHT secara visual:
Saat Anda menentukan fungsi partisi sebagai RANGE LEFT, nilai yang ditentukan adalah batas atas partisi. Saat Anda menggunakan RANGE RIGHT, nilai yang ditentukan adalah batas bawah partisi. Saat Anda menggunakan operasi MERGE RANGE untuk menghapus batas dari definisi fungsi partisi, implementasi yang mendasarinya juga menghapus partisi yang berisi batas. Jika partisi tersebut tidak kosong, data akan dipindahkan ke partisi yang merupakan hasil dari operasi MERGE RANGE.
Dalam skenario jendela geser, kami selalu menghapus batas partisi terendah.
- RANGE LEFT case: Dalam kasus RANGE LEFT, batas partisi terendah milik partisi 1, yang kosong (setelah partisi beralih), sehingga MERGE RANGE tidak akan menimbulkan pergerakan data apa pun.
- KASUS RANGE RIGHT: Dalam kasus RANGE RIGHT, batas partisi terendah milik partisi 2, yang tidak kosong karena kami berasumsi bahwa partisi 1 dikosongkan dengan beralih keluar. Dalam hal ini, MERGE RANGE akan menimbulkan pergerakan data (data dari partisi 2 akan dipindahkan ke partisi 1). Untuk menghindari hal ini, RANGE RIGHT dalam skenario jendela geser harus memiliki partisi 1, yang selalu kosong. Ini berarti bahwa jika kita menggunakan RANGE RIGHT, kita harus membuat dan memelihara satu partisi tambahan dibandingkan dengan range left case.
Kesimpulan: Menggunakan RANGE LEFT dalam partisi geser jauh lebih sederhana untuk manajemen partisi dan menghindari pergerakan data. Namun, menentukan batas partisi dengan RANGE RIGHT sedikit lebih sederhana karena Anda tidak perlu berurusan dengan masalah tick waktu tanggalwaktu.
Menggunakan pendekatan skrip pembersihan kustom
Dalam kasus ketika pendekatan Stretch Database dan partisi tabel bukan opsi yang layak, pendekatan ketiga adalah menghapus data dari tabel riwayat menggunakan skrip pembersihan kustom. Menghapus data dari tabel riwayat hanya dimungkinkan saat SYSTEM_VERSIONING = NONAKTIF. Untuk menghindari ketidakkonsistensian data, lakukan pembersihan baik selama jendela pemeliharaan (ketika beban kerja yang memodifikasi data tidak aktif) atau dalam transaksi (secara efektif memblokir beban kerja lain). Operasi ini memerlukan izin CONTROL pada tabel saat ini dan riwayat.
Untuk memblokir aplikasi reguler dan kueri pengguna secara minimal, hapus data dalam potongan yang lebih kecil dengan penundaan saat melakukan skrip pembersihan di dalam transaksi. Meskipun tidak ada ukuran optimal untuk setiap potongan data yang akan dihapus untuk semua skenario, menghapus lebih dari 10.000 baris dalam satu transaksi dapat memberlakukan dampak yang signifikan.
Logika pembersihan sama untuk setiap tabel temporal, sehingga dapat diotomatisasi secara relatif mudah melalui prosedur tersimpan generik yang Anda jadwalkan untuk dijalankan secara berkala untuk setiap tabel temporal yang ingin Anda batasi riwayat datanya.
Diagram berikut menggambarkan bagaimana logika pembersihan Anda harus diatur untuk satu tabel untuk mengurangi dampak pada beban kerja yang sedang berjalan.
Berikut adalah beberapa pedoman tingkat tinggi untuk menerapkan proses. Jadwalkan logika pembersihan untuk dijalankan setiap hari dan ulangi semua tabel temporal yang memerlukan pembersihan data. Gunakan SQL Server Agent atau alat yang berbeda untuk menjadwalkan proses ini:
- Hapus data historis di setiap tabel temporal mulai dari baris terlama hingga terbaru dalam beberapa iterasi dalam gugus kecil dan hindari menghapus semua baris dalam satu transaksi seperti yang ditunjukkan pada gambar di atas.
- Terapkan setiap perulangan sebagai pemanggilan prosedur tersimpan generik yang menghapus sebagian data dari tabel riwayat (lihat contoh kode di bawah ini untuk prosedur ini).
- Hitung berapa banyak baris yang perlu Anda hapus untuk tabel temporal individual setiap kali Anda memanggil proses. Berdasarkan itu dan jumlah perulangan yang ingin Anda miliki, tentukan titik pemisahan dinamis untuk setiap pemanggilan prosedur.
- Rencanakan untuk memiliki periode penundaan antara perulangan untuk satu tabel untuk mengurangi dampak pada aplikasi yang mengakses tabel temporal.
Prosedur tersimpan yang menghapus data untuk satu tabel temporal mungkin terlihat seperti dalam cuplikan kode berikut (tinjau kode ini dengan hati-hati dan sesuaikan sebelum diterapkan di lingkungan Anda):
DROP PROCEDURE IF EXISTS sp_CleanupHistoryData;
GO
CREATE PROCEDURE sp_CleanupHistoryData
@temporalTableSchema sysname
, @temporalTableName sysname
, @cleanupOlderThanDate datetime2
AS
DECLARE @disableVersioningScript nvarchar(max) = '';
DECLARE @deleteHistoryDataScript nvarchar(max) = '';
DECLARE @enableVersioningScript nvarchar(max) = '';
DECLARE @historyTableName sysname
DECLARE @historyTableSchema sysname
DECLARE @periodColumnName sysname
/*Generate script to discover history table name and end of period column for given temporal table name*/
EXECUTE sp_executesql
N'SELECT @hst_tbl_nm = t2.name, @hst_sch_nm = s2.name, @period_col_nm = c.name
FROM sys.tables t1
JOIN sys.tables t2 on t1.history_table_id = t2.object_id
JOIN sys.schemas s1 on t1.schema_id = s1.schema_id
JOIN sys.schemas s2 on t2.schema_id = s2.schema_id
JOIN sys.periods p on p.object_id = t1.object_id
JOIN sys.columns c on p.end_column_id = c.column_id and c.object_id = t1.object_id
WHERE
t1.name = @tblName and s1.name = @schName'
, N'@tblName sysname
, @schName sysname
, @hst_tbl_nm sysname OUTPUT
, @hst_sch_nm sysname OUTPUT
, @period_col_nm sysname OUTPUT'
, @tblName = @temporalTableName
, @schName = @temporalTableSchema
, @hst_tbl_nm = @historyTableName OUTPUT
, @hst_sch_nm = @historyTableSchema OUTPUT
, @period_col_nm = @periodColumnName OUTPUT
IF @historyTableName IS NULL OR @historyTableSchema IS NULL OR @periodColumnName IS NULL
THROW 50010, 'History table cannot be found. Either specified table is not system-versioned temporal or you have provided incorrect argument values.', 1
/*Generate 3 statements that will run inside a transaction:
(1) SET SYSTEM_VERSIONING = OFF,
(2) DELETE FROM history_table,
(3) SET SYSTEM_VERSIONING = ON
On SQL Server 2016, it is critical that (1) and (2) run in separate EXEC statements, or SQL Server will generate the following error:
Msg 13560, Level 16, State 1, Line XXX
Cannot delete rows from a temporal history table '<database_name>.<history_table_schema_name>.<history_table_name>'.
*/
SET @disableVersioningScript = @disableVersioningScript + 'ALTER TABLE [' + @temporalTableSchema + '].[' + @temporalTableName + '] SET (SYSTEM_VERSIONING = OFF)'
SET @deleteHistoryDataScript = @deleteHistoryDataScript + ' DELETE FROM [' + @historyTableSchema + '].[' + @historyTableName + ']
WHERE ['+ @periodColumnName + '] < ' + '''' + convert(varchar(128), @cleanupOlderThanDate, 126) + ''''
SET @enableVersioningScript = @enableVersioningScript + ' ALTER TABLE [' + @temporalTableSchema + '].[' + @temporalTableName + ']
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema + '].[' + @historyTableName + '], DATA_CONSISTENCY_CHECK = OFF )); '
BEGIN TRAN
EXEC (@disableVersioningScript);
EXEC (@deleteHistoryDataScript);
EXEC (@enableVersioningScript);
COMMIT;
Menggunakan pendekatan kebijakan penyimpanan riwayat temporal
Catatan
Menggunakan pendekatan Kebijakan Penyimpanan Riwayat Temporal berlaku untuk Azure SQL Database dan SQL Server 2017 mulai dari CTP 1.3.
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 mulai memeriksa secara teratur jika ada baris historis 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 historis 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
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
)
);
Anda dapat 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));
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
Cara SQL Database menghapus baris lama
Proses pembersihan tergantung pada tata letak indeks tabel riwayat. Penting untuk diperhatikan bahwa hanya tabel riwayat dengan indeks berkluster (pohon B+ atau penyimpan kolom) 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 pembersihan untuk indeks berkluster rowstore (pohon B+) menghapus baris lama dalam gugus yang lebih kecil (hingga 10K) meminimalkan tekanan pada log database dan subsistem I/O. Meskipun logika pembersihan menggunakan indeks pohon B+ yang diperlukan, urutan penghapusan untuk baris yang lebih lama 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.
Silakan periksa Mengelola data historis dalam Tabel Temporal dengan kebijakan penyimpanan untuk detail selengkapnya.