Tentang Change Data Capture (SQL Server)

Ubah rekaman pengambilan data menyisipkan, memperbarui, dan menghapus aktivitas yang diterapkan ke tabel SQL Server. Ini membuat detail perubahan yang tersedia dalam format relasional yang mudah dikonsumsi. Informasi kolom dan metadata yang diperlukan untuk menerapkan perubahan pada lingkungan target diambil untuk baris yang dimodifikasi dan disimpan dalam tabel perubahan yang mencerminkan struktur kolom tabel sumber terlacak. Fungsi bernilai tabel disediakan untuk memungkinkan akses sistematis ke data perubahan oleh konsumen.

Contoh yang baik dari konsumen data yang ditargetkan oleh teknologi ini adalah aplikasi ekstraksi, transformasi, dan pemuatan (ETL). Aplikasi ETL secara bertahap memuat data perubahan dari tabel sumber SQL Server ke gudang data atau data mart. Meskipun representasi tabel sumber dalam gudang data harus mencerminkan perubahan dalam tabel sumber, teknologi end-to-end yang me-refresh replika sumber tidak sesuai. Sebagai gantinya, Anda memerlukan aliran data perubahan yang andal yang terstruktur sehingga konsumen dapat menerapkannya ke representasi target data yang berbeda. SQL Server mengubah tangkapan data menyediakan teknologi ini.

Mengubah Aliran Data Pengambilan Data

Ilustrasi berikut menunjukkan aliran data utama untuk mengubah pengambilan data.

Sumber data perubahan untuk pengambilan data perubahan adalah log transaksi SQL Server. Seiring diterapkannya sisipan, pembaruan, dan penghapusan ke tabel sumber terlacak, entri yang menjelaskan perubahan tersebut ditambahkan ke log. Log berfungsi sebagai input untuk proses pengambilan. Ini membaca log dan menambahkan informasi tentang perubahan pada tabel perubahan terkait tabel terlacak. Fungsi disediakan untuk menghitung perubahan yang muncul di tabel perubahan pada rentang tertentu, menampilkan informasi dalam bentuk tataan hasil yang difilter. Tataan hasil yang difilter biasanya digunakan oleh proses aplikasi untuk memperbarui representasi sumber di beberapa lingkungan eksternal.

Memahami Change Data Capture dan Capture Instance

Sebelum perubahan pada tabel individual apa pun dalam database dapat dilacak, tangkapan data perubahan harus diaktifkan secara eksplisit untuk database. Ini dilakukan dengan menggunakan prosedur tersimpan sys.sp_cdc_enable_db. Saat database diaktifkan, tabel sumber dapat diidentifikasi sebagai tabel terlacak dengan menggunakan prosedur tersimpan sys.sp_cdc_enable_table. Saat tabel diaktifkan untuk mengubah pengambilan data, instans pengambilan terkait dibuat untuk mendukung penyebaran data perubahan dalam tabel sumber. Instans pengambilan terdiri dari tabel perubahan dan hingga dua fungsi kueri. Metadata yang menjelaskan detail konfigurasi instans pengambilan dipertahankan dalam tabel cdc.change_tablesmetadata tangkapan data perubahan , , cdc.index_columnsdan cdc.captured_columns. Informasi ini dapat diambil dengan menggunakan prosedur tersimpan sys.sp_cdc_help_change_data_capture.

Semua objek yang terkait dengan instans pengambilan dibuat dalam skema pengambilan data perubahan dari database yang diaktifkan. Persyaratan untuk nama instans pengambilan adalah nama objek yang valid, dan unik di seluruh instans pengambilan database. Secara default, namanya adalah <nama>skema name_table dari tabel sumber. Tabel perubahan yang terkait diberi nama dengan menambahkan _CT ke nama instans pengambilan. Fungsi yang digunakan untuk mengkueri semua perubahan dinamai dengan menambahkan fn_cdc_get_all_changes_ ke nama instans pengambilan. Jika instans pengambilan dikonfigurasi untuk mendukung net changes, net_changes fungsi kueri juga dibuat dan dinamai dengan menambahkan fn_cdc_get_net_changes_ ke nama instans pengambilan.

Ubah Tabel

Lima kolom pertama dari tabel perubahan tangkapan data perubahan adalah kolom metadata. Ini memberikan informasi tambahan yang relevan dengan perubahan yang direkam. Kolom yang tersisa mencerminkan kolom yang diambil yang diidentifikasi dari tabel sumber dalam nama dan, biasanya, dalam jenis. Kolom ini menyimpan data kolom yang diambil yang dikumpulkan dari tabel sumber.

Setiap operasi sisipkan atau hapus yang diterapkan ke tabel sumber muncul sebagai baris tunggal dalam tabel perubahan. Kolom data dari baris yang dihasilkan dari operasi penyisipan berisi nilai kolom setelah penyisipan. Kolom data dari baris yang dihasilkan dari operasi penghapusan berisi nilai kolom sebelum penghapusan. Operasi pembaruan memerlukan satu entri baris untuk mengidentifikasi nilai kolom sebelum pembaruan, dan entri baris kedua untuk mengidentifikasi nilai kolom setelah pembaruan.

Setiap baris dalam tabel perubahan juga berisi metadata tambahan untuk memungkinkan interpretasi aktivitas perubahan. Kolom __$start_lsn mengidentifikasi nomor urutan log penerapan (LSN) yang ditetapkan ke perubahan. LSN penerapan mengidentifikasi perubahan yang dilakukan dalam transaksi yang sama, dan memesan transaksi tersebut. Kolom __$seqval dapat digunakan untuk memesan lebih banyak perubahan yang terjadi dalam transaksi yang sama. Kolom __$operation mencatat operasi yang terkait dengan perubahan: 1 = penghapusan, 2 = penyisipan, 3 = pembaruan (sebelum gambar), dan 4 = pembaruan (setelah gambar). Kolom __$update_mask adalah masker bit variabel dengan satu bit yang ditentukan untuk setiap kolom yang diambil. Untuk entri sisipkan dan hapus, masker pembaruan akan selalu memiliki semua bit yang ditetapkan. Perbarui baris, namun, hanya akan memiliki bit yang diatur yang sesuai dengan kolom yang diubah.

Mengubah Interval Validitas Pengambilan Data untuk Database

Interval validitas tangkapan data perubahan untuk database adalah waktu di mana data perubahan tersedia untuk instans pengambilan. Interval validitas dimulai ketika instans pengambilan pertama dibuat untuk tabel database, dan berlanjut hingga saat ini.

Data yang disimpan dalam tabel perubahan akan tumbuh tidak terkendali jika Anda tidak memangkas data secara berkala dan sistematis. Proses pembersihan pengambilan data perubahan bertanggung jawab untuk memberlakukan kebijakan pembersihan berbasis retensi. Pertama, ini memindahkan titik akhir rendah interval validitas untuk memenuhi pembatasan waktu. Kemudian, menghapus entri tabel perubahan yang kedaluwarsa. Secara default, data tiga hari dipertahankan.

Di ujung atas, saat proses pengambilan menerapkan setiap batch data perubahan baru, entri baru ditambahkan ke cdc.lsn_time_mapping untuk setiap transaksi yang memiliki entri tabel perubahan. Dalam tabel pemetaan, baik Nomor Urutan Log penerapan (LSN) dan waktu penerapan transaksi (kolom start_lsn dan tran_end_time, masing-masing) dipertahankan. Nilai LSN maksimum yang ditemukan di cdc.lsn_time_mapping mewakili tanda air tinggi dari jendela validitas database. Waktu penerapan yang sesuai digunakan sebagai dasar dari mana pembersihan berbasis retensi menghitung tanda air rendah baru.

Karena proses pengambilan mengekstrak data perubahan dari log transaksi, ada latensi bawaan antara waktu perubahan dilakukan pada tabel sumber dan waktu perubahan muncul dalam tabel perubahan terkait. Meskipun latensi ini biasanya kecil, namun penting untuk diingat bahwa data perubahan tidak tersedia sampai proses penangkapan telah memproses entri log terkait.

Mengubah Interval Validitas Pengambilan Data untuk Instans Pengambilan

Meskipun umum untuk interval validitas database dan interval validitas instans pengambilan individu bertepatan, ini tidak selalu benar. Interval validitas instans pengambilan dimulai ketika proses pengambilan mengenali instans pengambilan dan mulai mencatat perubahan terkait pada tabel perubahannya. Akibatnya, jika instans pengambilan dibuat pada waktu yang berbeda, masing-masing pada awalnya akan memiliki titik akhir rendah yang berbeda. Kolom start_lsn dari kumpulan hasil yang dikembalikan oleh sys.sp_cdc_help_change_data_capture menunjukkan titik akhir rendah saat ini untuk setiap instans pengambilan yang ditentukan. Ketika proses pembersihan membersihkan entri tabel perubahan, proses ini menyesuaikan nilai start_lsn untuk semua instans tangkapan untuk mencerminkan tanda air rendah baru untuk data perubahan yang tersedia. Hanya instans tangkapan yang memiliki nilai start_lsn yang saat ini kurang dari tanda air rendah baru yang disesuaikan. Seiring waktu, jika tidak ada instans pengambilan baru yang dibuat, interval validitas untuk semua instans individual akan cenderung bertepatan dengan interval validitas database.

Interval validitas penting bagi konsumen data perubahan karena interval ekstraksi untuk permintaan harus sepenuhnya dicakup oleh interval validitas tangkapan data perubahan saat ini untuk instans pengambilan. Jika titik akhir rendah interval ekstraksi berada di sebelah kiri titik akhir rendah interval validitas, mungkin ada data perubahan yang hilang karena pembersihan agresif. Jika titik akhir tinggi interval ekstraksi berada di sebelah kanan titik akhir tinggi interval validitas, proses penangkapan belum diproses melalui periode waktu yang diwakili oleh interval ekstraksi, dan data perubahan juga dapat hilang.

Fungsi sys.fn_cdc_get_min_lsn digunakan untuk mengambil LSN minimum saat ini untuk instans pengambilan, sementara sys.fn_cdc_get_max_lsn digunakan untuk mengambil nilai LSN maksimum saat ini. Saat mengkueri data perubahan, jika rentang LSN yang ditentukan tidak terletak di dalam dua nilai LSN ini, fungsi kueri penangkapan data perubahan akan gagal.

Menangani Perubahan pada Tabel Sumber

Untuk mengakomodasi perubahan kolom dalam tabel sumber yang sedang dilacak adalah masalah yang sulit bagi konsumen hilir. Meskipun mengaktifkan pengambilan data perubahan pada tabel sumber tidak mencegah perubahan DDL seperti itu terjadi, tangkapan data perubahan membantu mengurangi efek pada konsumen dengan memungkinkan kumpulan hasil yang dikirimkan yang dikembalikan melalui API untuk tetap tidak berubah bahkan saat struktur kolom tabel sumber yang mendasar berubah. Struktur kolom tetap ini juga tercermin dalam tabel perubahan yang mendasar yang diakses fungsi kueri yang ditentukan.

Untuk mengakomodasi tabel perubahan struktur kolom tetap, proses pengambilan yang bertanggung jawab untuk mengisi tabel perubahan akan mengabaikan kolom baru yang tidak diidentifikasi untuk diambil ketika tabel sumber diaktifkan untuk mengubah pengambilan data. Jika kolom terlacak dihilangkan, nilai null akan disediakan untuk kolom dalam entri perubahan berikutnya. Namun, jika kolom yang ada mengalami perubahan dalam jenis datanya, perubahan disebarluaskan ke tabel perubahan untuk memastikan bahwa mekanisme penangkapan tidak menyebabkan kehilangan data ke kolom terlacak. Proses pengambilan juga memposting setiap perubahan yang terdeteksi pada struktur kolom tabel terlacak ke tabel cdc.ddl_history. Konsumen yang ingin diberi tahu tentang penyesuaian yang mungkin harus dilakukan dalam aplikasi hilir, gunakan prosedur tersimpan sys.sp_cdc_get_ddl_history.

Biasanya, instans pengambilan saat ini akan terus mempertahankan bentuknya ketika perubahan DDL diterapkan ke tabel sumber terkait. Namun, dimungkinkan untuk membuat instans pengambilan kedua untuk tabel yang mencerminkan struktur kolom baru. Ini memungkinkan proses pengambilan untuk membuat perubahan pada tabel sumber yang sama menjadi dua tabel perubahan berbeda yang memiliki dua struktur kolom yang berbeda. Dengan demikian, sementara satu tabel perubahan dapat terus memberi umpan program operasional saat ini, yang kedua dapat mendorong lingkungan pengembangan yang mencoba menggabungkan data kolom baru. Mengizinkan mekanisme penangkapan untuk mengisi kedua tabel perubahan bersamaan berarti bahwa transisi dari satu ke tabel lainnya dapat dicapai tanpa kehilangan data perubahan. Ini dapat terjadi kapan saja dua garis waktu pengambilan data perubahan tumpang tindih. Ketika transisi terpengaruh, instans pengambilan usang dapat dihapus.

Catatan

Jumlah maksimum instans pengambilan yang dapat dikaitkan secara bersamaan dengan satu tabel sumber adalah dua.

Hubungan Antara Pekerjaan Pengambilan dan Logreader Replikasi Transaksional

Logika untuk proses pengambilan data perubahan disematkan dalam prosedur tersimpan sp_replcmds, fungsi server internal yang dibangun sebagai bagian dari sqlservr.exe dan juga digunakan oleh replikasi transaksional untuk memanen perubahan dari log transaksi. Saat mengubah pengambilan data saja diaktifkan untuk database, Anda membuat tangkapan data perubahan SQL Server Agent mengambil pekerjaan sebagai kendaraan untuk memanggil sp_replcmds. Ketika replikasi juga ada, logreader transaksional saja digunakan untuk memenuhi kebutuhan data perubahan untuk kedua konsumen ini. Strategi ini secara signifikan mengurangi ketidakcocokan log ketika replikasi dan mengubah tangkapan data diaktifkan untuk database yang sama.

Peralihan antara kedua mode operasional ini untuk menangkap data perubahan terjadi secara otomatis setiap kali ada perubahan status replikasi database yang diaktifkan tangkapan data perubahan.

Penting

Kedua instans logika penangkapan mengharuskan SQL Server Agent dijalankan agar proses dijalankan.

Tugas utama dari proses pengambilan adalah memindai data kolom log dan tulis serta informasi terkait transaksi ke tabel perubahan tangkapan data perubahan. Untuk memastikan batas yang konsisten secara transaksional di semua tabel perubahan tangkapan data perubahan yang diisinya, proses pengambilan terbuka dan melakukan transaksinya sendiri pada setiap siklus pemindaian. Ini mendeteksi kapan tabel baru diaktifkan untuk mengubah pengambilan data, dan secara otomatis menyertakannya dalam kumpulan tabel yang secara aktif dipantau untuk entri perubahan dalam log. Demikian pula, menonaktifkan tangkapan data perubahan juga akan terdeteksi, menyebabkan tabel sumber dihapus dari kumpulan tabel yang dipantau secara aktif untuk data perubahan. Saat pemrosesan untuk bagian log selesai, proses pengambilan memberi sinyal logika pemotongan log server, yang menggunakan informasi ini untuk mengidentifikasi entri log yang memenuhi syarat untuk pemotongan.

Catatan

Ketika database diaktifkan untuk mengubah pengambilan data, bahkan jika mode pemulihan diatur ke pemulihan sederhana, titik pemotongan log tidak akan maju sampai semua perubahan yang ditandai untuk pengambilan telah dikumpulkan oleh proses pengambilan. Jika proses pengambilan tidak berjalan dan ada perubahan yang akan dikumpulkan, menjalankan CHECKPOINT tidak akan memotong log.

Proses pengambilan juga digunakan untuk mempertahankan riwayat pada perubahan DDL pada tabel terlacak. Pernyataan DDL yang terkait dengan tangkapan data perubahan membuat entri ke log transaksi database setiap kali database atau tabel yang mendukung pengambilan data perubahan dihilangkan atau kolom tabel yang mendukung tangkapan data perubahan ditambahkan, dimodifikasi, atau dihilangkan. Entri log ini diproses oleh proses pengambilan, yang kemudian memposting peristiwa DDL terkait ke tabel cdc.ddl_history. Anda dapat memperoleh informasi tentang peristiwa DDL yang memengaruhi tabel terlacak dengan menggunakan prosedur tersimpan sys.sp_cdc_get_ddl_history.

Mengubah Pekerjaan Agen Penangkapan Data

Dua pekerjaan SQL Server Agent biasanya dikaitkan dengan database yang diaktifkan tangkapan data perubahan: yang digunakan untuk mengisi tabel perubahan database, dan yang bertanggung jawab untuk pembersihan tabel perubahan. Kedua pekerjaan terdiri dari satu langkah yang menjalankan perintah Transact-SQL. Perintah Transact-SQL yang dipanggil adalah prosedur tersimpan yang ditentukan tangkapan data perubahan yang mengimplementasikan logika pekerjaan. Pekerjaan dibuat saat tabel pertama database diaktifkan untuk mengubah pengambilan data. Pekerjaan Pembersihan selalu dibuat. Pekerjaan pengambilan hanya akan dibuat jika tidak ada publikasi transaksi yang ditentukan untuk database. Pekerjaan pengambilan juga dibuat ketika tangkapan data perubahan dan replikasi transaksional diaktifkan untuk database, dan pekerjaan logreader transaksional dihapus karena database tidak lagi memiliki publikasi yang ditentukan.

Pekerjaan penangkapan dan pembersihan dibuat dengan menggunakan parameter default. Pekerjaan penangkapan segera dimulai. Ini berjalan terus menerus, memproses maksimum 1000 transaksi per siklus pemindaian dengan menunggu 5 detik antar siklus. Pekerjaan pembersihan berjalan setiap hari pada pukul 2 pagi. Ini mempertahankan entri tabel perubahan selama 4320 menit atau 3 hari, menghapus maksimum 5000 entri dengan satu pernyataan penghapusan.

Pekerjaan agen penangkapan data perubahan dihapus saat tangkapan data perubahan dinonaktifkan untuk database. Pekerjaan pengambilan juga dapat dihapus ketika publikasi pertama ditambahkan ke database, dan mengubah pengambilan data dan replikasi transaksional diaktifkan.

Secara internal, mengubah pekerjaan agen penangkapan data dibuat dan dihilangkan dengan menggunakan prosedur tersimpan sys.sp_cdc_add_job dan sys.sp_cdc_drop_job. Prosedur tersimpan ini juga diekspos sehingga administrator dapat mengontrol pembuatan dan penghapusan pekerjaan ini.

Administrator tidak memiliki kontrol eksplisit atas konfigurasi default pekerjaan agen penangkapan data perubahan. Prosedur tersimpan sys.sp_cdc_change_job disediakan untuk memungkinkan parameter konfigurasi default dimodifikasi. Selain itu, prosedur tersimpan sys.sp_cdc_help_jobs memungkinkan parameter konfigurasi saat ini untuk dilihat. Pekerjaan penangkapan dan pekerjaan pembersihan mengekstrak parameter konfigurasi dari tabel msdb.dbo.cdc_jobs saat startup. Setiap perubahan yang dilakukan pada nilai-nilai ini dengan menggunakan sys.sp_cdc_change_job tidak akan berlaku sampai pekerjaan dihentikan dan dimulai ulang.

Dua prosedur tersimpan tambahan disediakan untuk memungkinkan pekerjaan agen penangkapan data perubahan dimulai dan dihentikan: sys.sp_cdc_start_job dan sys.sp_cdc_stop_job.

Catatan

Memulai dan menghentikan pekerjaan pengambilan tidak mengakibatkan hilangnya data perubahan. Ini hanya mencegah proses penangkapan secara aktif memindai log untuk entri perubahan untuk disimpan dalam tabel perubahan. Strategi yang wajar untuk mencegah pemindaian log menambahkan beban selama periode permintaan puncak adalah menghentikan pekerjaan penangkapan dan memulai ulang ketika permintaan berkurang.

Kedua pekerjaan SQL Server Agent dirancang agar cukup fleksibel dan cukup dapat dikonfigurasi untuk memenuhi kebutuhan dasar lingkungan penangkapan data perubahan. Namun, dalam kedua kasus, prosedur tersimpan yang mendasar yang menyediakan fungsionalitas inti telah diekspos sehingga penyesuaian lebih lanjut dimungkinkan.

Mengubah tangkapan data tidak dapat berfungsi dengan baik ketika layanan Mesin Database atau layanan SQL Server Agent berjalan di bawah akun NETWORK SERVICE. Ini dapat mengakibatkan kesalahan 22832.

Lihat juga

Lacak Perubahan Data (SQL Server)
Aktifkan dan Nonaktifkan Ubah Pengambilan Data (SQL Server)
Bekerja dengan Ubah Data (SQL Server)
Mengelola dan Memantau Perubahan Pengambilan Data (SQL Server)