Lacak Perubahan Data (SQL Server)

  • Artikel
  • 9 menit untuk membaca

Berlaku untuk:yes SQL Server (semua versi yang didukung) YesAzure SQL Database

SQL Server 2019 (15.x) menyediakan dua fitur yang melacak perubahan pada data dalam database: mengubah penangkapan data dan pelacakan perubahan. Fitur-fitur ini memungkinkan aplikasi untuk menentukan perubahan DML (operasi sisipkan, perbarui, dan hapus) yang dibuat untuk tabel pengguna dalam database. Mengubah pengambilan data dan pelacakan perubahan dapat diaktifkan pada database yang sama; tidak diperlukan pertimbangan khusus. Untuk edisi SQL Server yang mendukung perubahan pengambilan data dan pelacakan perubahan, lihat Fitur yang Didukung oleh Edisi SQL Server 2016. Pelacakan perubahan didukung oleh Azure SQL Database. Mengubah pengambilan data hanya didukung di SQL Server dan Azure SQL Managed Instance.

Manfaat Menggunakan Change Data Capture atau Change Tracking

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 ubah 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 2019 (15.x), 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 untuk 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 untuk diperoleh ketika ada transaksi yang berjalan lama dan tumpang tindih. Solusi kustom yang menggunakan nilai tanda waktu harus dirancang khusus untuk menangani skenario ini.

  • Alat standar tersedia yang dapat Anda gunakan untuk mengonfigurasi dan mengelola. SQL Server 2019 (15.x) menyediakan pernyataan DDL standar, SQL Server Management Studio, tampilan katalog, dan izin keamanan.

Perbedaan Fitur Antara Mengubah Pengambilan Data dan Pelacakan Perubahan

Tabel berikut ini mencantumkan perbedaan fitur antara mengubah pengambilan 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 Ubah 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

Ubah Pengambilan Data

Tangkapan data perubahan menyediakan informasi perubahan historis untuk tabel pengguna dengan menangkap fakta bahwa perubahan DML dibuat dan data aktual yang diubah. Perubahan ditangkap dengan menggunakan proses asinkron yang membaca log transaksi dan berdampak rendah pada sistem.

Seperti yang diperlihatkan dalam ilustrasi berikut, perubahan yang dibuat pada tabel pengguna diambil dalam tabel perubahan terkait. Tabel perubahan ini memberikan tampilan historis perubahan dari waktu ke waktu. Fungsi pengambilan data perubahan yang SQL Server sediakan memungkinkan data perubahan dikonsumsi dengan mudah dan sistematis.

Conceptual illustration of change data capture

Model Keamanan

Bagian ini menjelaskan model keamanan tangkapan data perubahan.

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

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

Ubah Kueri Enumerasi dan Metadata
Untuk mendapatkan akses ke data perubahan yang terkait dengan instans pengambilan, pengguna harus diberikan akses pilih ke semua kolom yang diambil dari tabel sumber terkait. Selain itu, jika peran pembatasan ditentukan ketika instans pengambilan dibuat, pemanggil juga harus menjadi anggota peran gating yang ditentukan. 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 biasanya juga akan dijaga dengan menggunakan akses tertentu ke tabel sumber yang mendasar, dan dengan keanggotaan dalam peran pembatasan yang ditentukan.

Operasi DDL untuk Mengubah Tabel Sumber yang Diaktifkan Tangkapan Data
Saat tabel diaktifkan untuk mengubah pengambilan data, operasi DDL hanya dapat diterapkan ke tabel oleh anggota sysadmin peran server tetap, 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 tangkapan data perubahan. Tabel berikut ini 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 akan 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.

Ubah Pengambilan Data dan Fitur SQL Server Lainnya

Bagian ini menjelaskan bagaimana fitur berikut berinteraksi dengan pengambilan data perubahan:

  • Pencerminan Database

  • Replikasi Transaksional

  • Pemulihan atau lampirkan database

Pencerminan Database

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

  1. Pastikan SQL Server Agent berjalan di cermin.

  2. Buat pekerjaan penangkapan dan bersihkan pekerjaan 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 tangkapan data dan replikasi transaksional dapat berdampingan dalam database yang sama, tetapi populasi tabel perubahan ditangani secara berbeda ketika kedua fitur diaktifkan. Ubah pengambilan data dan replikasi transaksional selalu gunakan prosedur yang sama, sp_replcmds, untuk membaca perubahan dari log transaksi. Saat tangkapan data perubahan diaktifkan sendiri, pekerjaan SQL Server Agent memanggil sp_replcmds. Saat kedua fitur diaktifkan pada database yang sama, Agen Pembaca Log memanggil sp_replcmds. Agen ini mengisi tabel perubahan dan tabel database distribusi. Untuk informasi selengkapnya, lihat Agen Pembaca Log Replikasi.

Pertimbangkan skenario di mana pengambilan data perubahan diaktifkan pada database AdventureWorks2012 , dan dua tabel diaktifkan untuk diambil. Untuk mengisi tabel perubahan, pekerjaan pengambilan 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 database distribusi. Kemudian, perubahan yang diambil ditulis ke tabel perubahan. Kedua operasi diterapkan bersama-sama. Jika ada latensi secara tertulis ke database distribusi, 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 opsi KEEP_CDC saat memulihkan database. Untuk informasi selengkapnya tentang opsi ini, lihat PULIHKAN.

  • Jika database dilepas dan dilampirkan ke server yang sama atau server lain, ubah pengambilan data tetap diaktifkan.

  • Jika database dilampirkan atau dipulihkan dengan opsi KEEP_CDC ke edisi apa pun selain Standar atau Perusahaan, operasi diblokir karena pengambilan data perubahan memerlukan SQL Server edisi Standar atau Perusahaan. 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 untuk 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.

Conceptual illustration of change tracking

Layanan Pelacakan perubahan dan 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 database server atau serekan, kami sarankan Anda menggunakan pelacakan perubahan di SQL Server 2019 (15.x) 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. Dalam dokumentasi untuk Layanan Sinkronisasi, topik "Cara: Menggunakan SQL Server Pelacakan Perubahan" berisi informasi terperinci dan contoh kode.

Tugas Topik
Menyediakan gambaran umum pengambilan data perubahan. Tentang Change Data Capture (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 Perubahan Pengambilan Data (SQL Server)
Menjelaskan cara bekerja dengan data perubahan yang tersedia untuk mengubah konsumen penangkapan data. Topik ini mencakup 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. Topik ini juga menjelaskan pelacakan perubahan peran diputar ketika failover terjadi dan database harus dipulihkan dari cadangan. Bekerja dengan Pelacakan Perubahan (SQL Server)

Lihat juga

Mengubah Fungsi Pengambilan Data (SQL Tranact)
Fungsi Pelacakan Perubahan (SQL Transact)
Mengubah Prosedur Tersimpan Pengambilan Data (SQL Tranact)
Ubah Tabel Pengambilan Data (SQL Tranact)
Mengubah Tampilan Manajemen Dinamis Terkait Tangkapan Data (SQL Transact)