Melacak perubahan data (SQL Server)

Artikel
09/26/2023

Berlaku untuk:SQL Server Azure SQL Database Azure SQL Managed Instance

SQL Server menyediakan dua fitur yang melacak perubahan pada data dalam database: mengubah penangkapan data dan pelacakan perubahan. Fitur-fitur ini memungkinkan aplikasi menentukan perubahan DML (operasi sisipkan, perbarui, dan hapus) yang dibuat untuk tabel pengguna dalam database. Mengubah penangkapan data dan pelacakan perubahan dapat diaktifkan pada database yang sama; tidak ada pertimbangan khusus yang diperlukan. Untuk edisi SQL Server yang mendukung perubahan penangkapan data dan pelacakan perubahan, lihat Edisi dan fitur yang didukung SQL Server 2022.

Manfaat menggunakan mengubah penangkapan data atau pelacakan perubahan

Kemampuan untuk meminta data yang telah berubah dalam database adalah persyaratan penting agar beberapa aplikasi menjadi efisien. Biasanya, untuk menentukan perubahan data, pengembang aplikasi harus menerapkan metode pelacakan kustom dalam aplikasi mereka dengan menggunakan kombinasi pemicu, kolom tanda waktu, dan tabel tambahan. Pembuatan aplikasi ini biasanya melibatkan banyak pekerjaan untuk diterapkan, mengarah ke pembaruan skema, dan sering kali membawa beban performa tinggi.

Menggunakan mengubah penangkapan data atau pelacakan perubahan dalam aplikasi untuk melacak perubahan dalam database, alih-alih mengembangkan solusi kustom, memiliki manfaat berikut:

Ada pengurangan waktu pengembangan. Karena fungsionalitas tersedia di SQL Server, Anda tidak perlu mengembangkan solusi kustom.
Perubahan skema tidak diperlukan. Anda tidak perlu menambahkan kolom, menambahkan pemicu, atau membuat tabel samping untuk melacak baris yang dihapus atau menyimpan informasi pelacakan perubahan jika kolom tidak dapat ditambahkan ke tabel pengguna.
Ada mekanisme pembersihan bawaan. Pembersihan untuk pelacakan perubahan dilakukan secara otomatis di latar belakang. Pembersihan kustom untuk data yang disimpan dalam tabel samping tidak diperlukan.
Fungsi disediakan untuk mendapatkan informasi perubahan.
Ada overhead rendah untuk operasi DML. Pelacakan perubahan sinkron akan selalu memiliki beberapa overhead. Namun, menggunakan pelacakan perubahan dapat membantu meminimalkan overhead. Overhead akan sering kurang dari menggunakan solusi alternatif, terutama solusi yang memerlukan penggunaan pemicu.
Pelacakan perubahan didasarkan pada transaksi yang dilakukan. Urutan perubahan didasarkan pada waktu penerapan transaksi. Ini memungkinkan hasil yang dapat diandalkan diperoleh ketika ada transaksi yang berjalan lama dan tumpang tindih. Solusi kustom yang menggunakan nilai tanda waktu harus dirancang untuk menangani skenario ini.
Alat standar tersedia yang dapat Anda gunakan untuk mengonfigurasi dan mengelola. SQL Server menyediakan pernyataan DDL standar, SQL Server Management Studio, tampilan katalog, dan izin keamanan.

Perbedaan fitur antara mengubah penangkapan data dan pelacakan perubahan

Tabel berikut mencantumkan perbedaan fitur antara mengubah penangkapan data dan pelacakan perubahan. Mekanisme pelacakan dalam tangkapan data perubahan melibatkan pengambilan perubahan asinkron dari log transaksi sehingga perubahan tersedia setelah operasi DML. Dalam pelacakan perubahan, mekanisme pelacakan melibatkan pelacakan perubahan yang sinkron sejalan dengan operasi DML sehingga informasi perubahan segera tersedia.

Fitur	Mengubah pengambilan data	Pelacakan perubahan
Perubahan terlacak
Perubahan DML	Ya	Ya
Informasi terlacak
Data riwayat	Ya	Tidak
Apakah kolom diubah	Ya	Ya
Jenis DML	Ya	Ya

Mengubah pengambilan data

Penangkapan data perubahan menyediakan informasi perubahan historis untuk tabel pengguna dengan menangkap fakta bahwa perubahan DML dilakukan dan data aktual yang diubah. Perubahan diambil dengan menggunakan proses asinkron yang membaca log transaksi dan memiliki dampak rendah pada sistem.

Seperti yang ditunjukkan dalam ilustrasi berikut, perubahan yang dilakukan pada tabel pengguna diambil dalam tabel perubahan yang sesuai. Tabel perubahan ini menyediakan tampilan historis perubahan dari waktu ke waktu. Fungsi pengambilan data perubahan yang disediakan SQL Server memungkinkan data perubahan dikonsumsi dengan mudah dan sistematis.

Diagram showing the concept of change data capture.

Model keamanan

Bagian ini menjelaskan model keamanan penangkapan data perubahan.

Konfigurasi dan administrasi

Untuk mengaktifkan atau menonaktifkan perubahan pengambilan data untuk database, pemanggil sys.sp_cdc_enable_db (Transact-SQL) atau sys.sp_cdc_disable_db (Transact-SQL) harus menjadi anggota peran sysadmin server tetap. Mengaktifkan dan menonaktifkan perubahan pengambilan data di tingkat tabel memerlukan pemanggil sys.sp_cdc_enable_table (Transact-SQL) dan sys.sp_cdc_disable_table (Transact-SQL) untuk menjadi anggota peran sysadmin atau anggota database database db_owner peran.

Penggunaan prosedur tersimpan untuk mendukung administrasi pekerjaan penangkapan data perubahan dibatasi untuk anggota peran sysadmin server dan anggota peran db_owner database.

Mengubah kueri enumerasi dan metadata

Untuk mendapatkan akses ke data perubahan yang terkait dengan instans pengambilan, pengguna harus diberikan akses SELECT ke semua kolom yang diambil dari tabel sumber terkait. Selain itu, jika peran pembatasan ditentukan ketika instans tangkapan dibuat, pemanggil juga harus menjadi anggota peran pembatasan yang ditentukan, dan skema penangkapan data perubahan (cdc) harus memiliki akses SELECT ke peran pembatasan.

Fungsi pengambilan data perubahan umum lainnya untuk mengakses metadata akan dapat diakses oleh semua pengguna database melalui peran publik, meskipun akses ke metadata yang dikembalikan juga biasanya akan dijaga dengan menggunakan akses SELECT ke tabel sumber yang mendasar, dan dengan keanggotaan dalam peran pembatasan yang ditentukan.

Operasi DDL untuk mengubah tabel sumber yang mendukung pengambilan data

Saat tabel diaktifkan untuk mengubah pengambilan data, operasi DDL hanya dapat diterapkan ke tabel oleh anggota peran server tetap sysadmin, anggota peran database db_owner, atau anggota peran database db_ddladmin. Pengguna yang memiliki pemberian eksplisit untuk melakukan operasi DDL pada tabel akan menerima kesalahan 22914 jika mereka mencoba operasi ini.

Pertimbangan jenis data untuk mengubah pengambilan data

Semua jenis kolom dasar didukung oleh pengambilan data perubahan. Tabel berikut mencantumkan perilaku dan batasan untuk beberapa jenis kolom.

Tipe Kolom	Perubahan yang Diambil dalam Ubah Tabel	Batasan
Kolom Tersebar	Ya	Tidak mendukung pengambilan perubahan saat menggunakan kumpulan kolom.
Kolom komputasi	Tidak	Perubahan pada kolom komputasi tidak dilacak. Kolom muncul dalam tabel perubahan dengan jenis yang sesuai, tetapi akan memiliki nilai NULL.
XML	Ya	Perubahan pada elemen XML individual tidak dilacak.
Tanda Waktu	Ya	Jenis data dalam tabel perubahan dikonversi ke biner.
Jenis data BLOB	Ya	Gambar sebelumnya dari kolom BLOB disimpan hanya jika kolom itu sendiri diubah.

Mengubah pengambilan data dan fitur SQL Server lainnya

Bagian ini menjelaskan bagaimana fitur berikut berinteraksi dengan mengubah tangkapan data:

Pencerminan Database
Replikasi Transaksional
Pemulihan atau lampirkan database

Pencerminan Database

Database yang diaktifkan untuk mengubah pengambilan data dapat dicerminkan. Untuk memastikan bahwa penangkapan dan pembersihan terjadi secara otomatis di cermin, ikuti langkah-langkah berikut:

Pastikan bahwa SQL Server Agent berjalan di cermin.
Buat pekerjaan penangkapan dan pekerjaan pembersihan di cermin setelah perwakilan gagal ke cermin. Untuk membuat pekerjaan, gunakan prosedur tersimpan sys.sp_cdc_add_job (Transact-SQL).

Untuk informasi selengkapnya tentang pencerminan database, lihat Pencerminan Database (SQL Server).

Replikasi Transaksional

Mengubah penangkapan data dan replikasi transaksional dapat berdampingan dalam database yang sama, tetapi populasi tabel perubahan ditangani secara berbeda ketika kedua fitur diaktifkan. Ubah penangkapan data dan replikasi transaksional selalu gunakan prosedur yang sama, sp_replcmds, untuk membaca perubahan dari log transaksi. Saat penangkapan data perubahan diaktifkan sendiri, pekerjaan SQL Server Agent memanggil sp_replcmds. Ketika kedua fitur diaktifkan pada database yang sama, Agen Pembaca Log memanggil sp_replcmds. Agen ini mengisi tabel perubahan dan distribution tabel database. Untuk informasi selengkapnya, lihat Agen Pembaca Log Replikasi.

Pertimbangkan skenario di mana pengambilan data perubahan diaktifkan pada AdventureWorks2022 database, dan dua tabel diaktifkan untuk diambil. Untuk mengisi tabel perubahan, pekerjaan penangkapan memanggil sp_replcmds. Database diaktifkan untuk replikasi transaksional, dan publikasi dibuat. Sekarang, Agen Pembaca Log dibuat untuk database dan pekerjaan pengambilan dihapus. Agen Pembaca Log terus memindai log dari nomor urutan log terakhir yang diterapkan pada tabel perubahan. Ini memastikan konsistensi data dalam tabel perubahan. Jika replikasi transaksional dinonaktifkan dalam database ini, Agen Pembaca Log dihapus, dan pekerjaan penangkapan dibuat ulang.

Catatan

Ketika Agen Pembaca Log digunakan untuk mengubah pengambilan data dan replikasi transaksional, perubahan yang direplikasi pertama kali ditulis ke distribution database. Kemudian, perubahan yang diambil ditulis ke tabel perubahan. Kedua operasi dilakukan bersama-sama. Jika ada latensi dalam penulisan ke distribution database, akan ada latensi yang sesuai sebelum perubahan muncul dalam tabel perubahan.

Memulihkan atau melampirkan database yang diaktifkan untuk mengubah pengambilan data

SQL Server menggunakan logika berikut untuk menentukan apakah pengambilan data perubahan tetap diaktifkan setelah database dipulihkan atau dilampirkan:

Jika database dipulihkan ke server yang sama dengan nama database yang sama, ubah pengambilan data tetap diaktifkan.
Jika database dipulihkan ke server lain, secara default pengambilan data perubahan dinonaktifkan, dan semua metadata terkait dihapus.

Untuk mempertahankan perubahan pengambilan data, gunakan KEEP_CDC opsi saat memulihkan database. Untuk informasi selengkapnya tentang opsi ini, lihat MEMULIHKAN.
Jika database dilepas dan dilampirkan ke server yang sama atau server lain, ubah pengambilan data tetap diaktifkan.

Jika database dilampirkan atau dipulihkan dengan KEEP_CDC opsi ke edisi apa pun selain Standar atau Perusahaan, operasi diblokir karena pengambilan data perubahan memerlukan edisi SQL Server Standard atau Enterprise. Pesan kesalahan 932 ditampilkan:

SQL Server cannot load database '%.*ls' because change data capture is enabled. The currently installed edition of SQL Server does not support change data capture. Either disable change data capture in the database by using a supported edition of SQL Server, or upgrade the instance to one that supports change data capture.

Anda bisa menggunakan sys.sp_cdc_disable_db untuk menghapus pengambilan data perubahan dari database yang dipulihkan atau dilampirkan.

Pelacakan perubahan

Pelacakan perubahan menangkap fakta bahwa baris dalam tabel diubah, tetapi tidak menangkap data yang diubah. Ini memungkinkan aplikasi menentukan baris yang telah berubah dengan data baris terbaru yang diperoleh langsung dari tabel pengguna. Oleh karena itu, pelacakan perubahan lebih terbatas dalam pertanyaan historis yang dapat dijawab dibandingkan dengan perubahan tangkapan data. Namun, untuk aplikasi yang tidak memerlukan informasi historis, ada overhead penyimpanan yang jauh lebih sedikit karena data yang diubah tidak ditangkap. Mekanisme pelacakan sinkron digunakan untuk melacak perubahan. Ini telah dirancang untuk memiliki overhead minimal ke operasi DML.

Ilustrasi berikut menunjukkan skenario sinkronisasi yang akan menguntungkan dengan menggunakan pelacakan perubahan. Dalam skenario, aplikasi memerlukan informasi berikut: semua baris dalam tabel yang diubah sejak terakhir kali tabel disinkronkan, dan hanya data baris saat ini. Karena mekanisme sinkron digunakan untuk melacak perubahan, aplikasi dapat melakukan sinkronisasi dua arah dan dengan andal mendeteksi konflik apa pun yang mungkin terjadi.

Diagram showing the concept of change tracking.

Pelacakan perubahan dan Layanan Sinkronisasi untuk ADO.NET

Layanan Sinkronisasi untuk ADO.NET memungkinkan sinkronisasi antar database, menyediakan API intuitif dan fleksibel yang memungkinkan Anda membangun aplikasi yang menargetkan skenario offline dan kolaborasi. Layanan Sinkronisasi untuk ADO.NET menyediakan API untuk menyinkronkan perubahan, tetapi tidak benar-benar melacak perubahan di server atau database serekan. Anda dapat membuat sistem pelacakan perubahan kustom, tetapi ini biasanya memperkenalkan kompleksitas dan overhead performa yang signifikan. Untuk melacak perubahan dalam server atau database serekan, kami sarankan Anda menggunakan pelacakan perubahan di SQL Server karena mudah dikonfigurasi dan menyediakan pelacakan performa tinggi.

Untuk informasi selengkapnya tentang pelacakan perubahan dan Layanan Sinkronisasi untuk ADO.NET, gunakan tautan berikut:

Tentang Pelacakan Perubahan (SQL Server)

Menjelaskan pelacakan perubahan, memberikan gambaran umum tingkat tinggi tentang cara kerja pelacakan perubahan, dan menjelaskan cara pelacakan perubahan berinteraksi dengan fitur Mesin Database SQL Server lainnya.
Pusat Pengembang Microsoft Sync Framework

Menyediakan dokumentasi lengkap untuk Sync Framework dan Sync Services.

Langkah berikutnya

Tugas	Artikel
Memberikan gambaran umum tentang penangkapan data perubahan.	Tentang Mengubah Penangkapan Data (SQL Server)
Menjelaskan cara mengaktifkan dan menonaktifkan perubahan pengambilan data pada database atau tabel.	Mengaktifkan dan Menonaktifkan Ubah Pengambilan Data (SQL Server)
Menjelaskan cara mengelola dan memantau perubahan pengambilan data.	Mengelola dan Memantau Ubah Pengambilan Data (SQL Server)
Menjelaskan cara bekerja dengan data perubahan yang tersedia untuk mengubah konsumen penangkapan data. Artikel ini membahas memvalidasi batas LSN, fungsi kueri, dan skenario fungsi kueri.	Bekerja dengan Ubah Data (SQL Server)
Memberikan gambaran umum pelacakan perubahan.	Tentang Pelacakan Perubahan (SQL Server)
Menjelaskan cara mengaktifkan dan menonaktifkan pelacakan perubahan pada database atau tabel.	Mengaktifkan dan Menonaktifkan Pelacakan Perubahan (SQL Server)
Menjelaskan cara mengelola pelacakan perubahan, mengonfigurasi keamanan, dan menentukan efek pada penyimpanan dan performa saat pelacakan perubahan digunakan.	Mengelola Pelacakan Perubahan (SQL Server)
Menjelaskan bagaimana aplikasi yang menggunakan pelacakan perubahan dapat memperoleh perubahan terlacak, menerapkan perubahan ini ke penyimpanan data lain, dan memperbarui database sumber. Artikel ini juga menjelaskan pelacakan perubahan peran diputar saat failover terjadi dan database harus dipulihkan dari cadangan.	Bekerja dengan Pelacakan Perubahan (SQL Server)