Lacak Perubahan Data (SQL Server)

SQL Server 2014 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 Fitur yang Didukung oleh Edisi SQL Server 2014.

Manfaat Menggunakan Penangkapan Perubahan 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.

Penggunaan capture data perubahan atau pelacakan perubahan dalam aplikasi untuk melacak perubahan dalam database, alih-alih mengembangkan solusi khusus, memiliki manfaat berikut:

• Ada pengurangan waktu pengembangan. Karena fungsionalitas tersedia di SQL Server 2014, 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.

• Operasi DML memiliki overhead yang rendah. Pelacakan perubahan secara sinkron akan selalu memiliki sejumlah beban. Namun, menggunakan pelacakan perubahan dapat membantu meminimalkan overhead. Overhead seringkali lebih rendah daripada menggunakan solusi alternatif, terutama solusi yang memerlukan pemicu penggunaan.

• 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 timestamp nilai harus dirancang khusus untuk menangani skenario ini.

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

Perbedaan Fitur Antara Penangkapan Perubahan 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 yang dilacak
Data riwayat	Ya	Tidak.
Apakah kolom diubah	Ya	Ya
Jenis DML	Ya	Ya

Ubah 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 perubahanyang disediakan SQL Server memungkinkan data perubahan dikonsumsi dengan mudah dan sistematis.

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 server sysadmin 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 peran database database db_owner .

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

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 juga biasanya dijaga dengan menggunakan akses pemilihan ke tabel sumber yang mendasar, dan dengan keanggotaan dalam peran pembatasan yang sudah ditentukan.

Operasi DDL untuk Mengubah Tabel Sumber yang Diaktifkan Pengambilan Data
Saat tabel diaktifkan untuk mengubah pengambilan data, operasi DDL hanya dapat diterapkan ke tabel oleh anggota peran sysadminserver tetap , anggota database role db_owner, atau anggota database role 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 Ditangkap dalam Tabel Perubahan	Keterbatasan
Kolom Jarang	Ya	Tidak mendukung pengambilan perubahan saat menggunakan kumpulan kolom.
Kolom Terhitung	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.
Penanda 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 Penangkapan Data dan Fitur SQL Server Lainnya

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

• Penggandaan database

• Replikasi transaksional

• Pulihkan atau lampirkan database

Replikasi Basis Data

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

1. Pastikan bahwa SQL Server Agent berjalan di server cermin.

2. Buat tugas penangkapan data dan tugas pembersihan di server cermin setelah server utama dialihkan ke server 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

Pengambilan Data Perubahan dan replikasi transaksional dapat berkoeksistensi dalam database yang sama, tetapi pengisian tabel perubahan data ditangani secara berbeda ketika kedua fitur diaktifkan. Penangkapan perubahan data dan replikasi transaksional selalu menggunakan prosedur yang sama, sp_replcmds, untuk membaca perubahan dari log transaksi. Saat penangkapan data perubahan diaktifkan secara mandiri, pekerjaan SQL Server Agent akan memanggil sp_replcmds. Ketika 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 untuk Replikasi.

Pertimbangkan skenario di mana penangkapan data perubahan diaktifkan pada database AdventureWorks2012, dan dua tabel diaktifkan untuk penangkapan. Untuk mengisi tabel perubahan data, pekerjaan penangkapan memanggil sp_replcmds. Database diaktifkan untuk replikasi transaksional, dan publikasi dibuat. Sekarang, Agen Pembaca Log telah dibuat untuk database dan tugas tangkap 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 pemrosesan data dibuat ulang.

Nota

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 dilakukan bersama-sama. Jika ada latensi saat menulis ke database distribusi, akan ada latensi yang berhubungan 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, penangkapan data perubahan 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 RESTORE.

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

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

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 server atau database serekan, kami sarankan Anda menggunakan pelacakan perubahan di SQL Server 2014 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 Pelacakan Perubahan SQL Server" berisi informasi terperinci dan contoh kode.

Tugas Terkait (diperlukan)

Tugas	Topik
Memberikan gambaran umum tentang penangkapan data perubahan.	Tentang Pengambilan Data Perubahan (SQL Server)
Menjelaskan cara mengaktifkan dan menonaktifkan perubahan pengambilan data pada database atau tabel.	Mengaktifkan dan Menonaktifkan Change Data Capture (CDC) di 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. Topik ini mencakup validasi batas LSN, fungsi kueri, dan skenario fungsi kueri.	Bekerja dengan Perubahan 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 peran pelacakan perubahan ketika failover terjadi dan database harus dipulihkan dari cadangan.	Bekerja dengan Pelacakan Perubahan (SQL Server)

Lihat Juga

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