Apa itu ubah pengambilan data (CDC)?

  • Artikel

Berlaku untuk:SQL ServerAzure SQL Managed Instance

Dalam artikel ini, pelajari tentang mengubah penangkapan data (CDC), yang merekam aktivitas pada database saat tabel dan baris telah dimodifikasi.

Artikel ini menjelaskan cara kerja CDC dengan SQL Server dan Azure SQL Managed Instance. Untuk Azure SQL Database, lihat CDC dengan Azure SQL Database.

Gambaran Umum

Mengubah pengambilan data menggunakan SQL Server Agent untuk mencatat penyisipan, pembaruan, dan penghapusan yang terjadi dalam tabel. Jadi, ini membuat perubahan data ini dapat diakses dengan mudah digunakan menggunakan format relasional. Data kolom dan metadata penting perlu menerapkan data perubahan ini ke lingkungan target diambil untuk baris yang dimodifikasi dan disimpan dalam tabel perubahan yang mencerminkan struktur kolom tabel sumber yang dilacak. Selain itu, fungsi bernilai tabel tersedia untuk akses sistematis ke data perubahan ini oleh konsumen.

Contoh yang baik dari konsumen data yang ditargetkan 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. Tangkapan data perubahan SQL Server menyediakan teknologi ini.

Aliran Data

Ilustrasi berikut menunjukkan aliran data utama untuk mengubah pengambilan data.

Change data capture data flow diagram.

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. Kemudian, ia 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.

Mengambil instans

Sebelum perubahan pada tabel individual dalam database dapat dilacak, ubah pengambilan data 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 metadata pengambilan data perubahan cdc.change_tables, cdc.index_columns, dan 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 penangkapan data perubahan database yang diaktifkan. Persyaratan untuk nama instans pengambilan adalah nama objek yang valid, dan unik di seluruh instans pengambilan database. Secara default, nama tersebut adalah <nama skema_table> dari tabel sumber. Tabel perubahan 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_ sebelumnya ke nama instans pengambilan. Jika instans tangkapan dikonfigurasi untuk mendukung perubahan bersih, fungsi kueri net_changes juga dibuat dan dinamai dengan menambahkan fn_cdc_get_net_changes_ ke nama instans pengambilan.

Penting

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

Ubah tabel

Lima kolom pertama dari tabel perubahan pengambilan 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 satu baris 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 entri satu 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 lain 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 mengurutkan 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 memiliki semua bit yang ditetapkan. Namun, Perbarui baris akan memiliki kumpulan bit yang sesuai dengan kolom yang diubah.

Interval validitas

Interval validitas pengambilan 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.

Database

Data yang disimpan dalam tabel perubahan tumbuh secara tidak terkelola jika Anda tidak memangkas data secara berkala dan sistematis. Proses pembersihan penangkapan 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, tiga hari data dipertahankan.

Di ujung atas, karena proses penangkapan 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, 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 penangkapan mengekstrak data perubahan dari log transaksi, ada latensi bawaan antara waktu perubahan diterapkan pada tabel sumber dan waktu perubahan muncul dalam tabel perubahan terkait. Meskipun latensi ini biasanya kecil, namun penting untuk diingat bahwa perubahan data tidak tersedia sampai proses penangkapan telah memproses entri log terkait.

Mengambil instans

Meskipun umum untuk interval validitas database dan interval validitas instans pengambilan individu bertepatan, namun, 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 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. Saat 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 individu 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, itu menunjukkan bahwa proses penangkapan belum diproses melalui waktu yang diwakili oleh interval ekstraksi, dan mungkin juga ada data perubahan yang 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 Anda mengkueri data perubahan, jika rentang LSN yang ditentukan tidak berada dalam dua nilai LSN ini, fungsi kueri penangkapan data perubahan gagal.

Menangani perubahan pada tabel sumber

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 mengurangi efek pada konsumen dengan mempertahankan kumpulan hasil yang dikirimkan yang dikembalikan melalui API, bahkan saat struktur kolom tabel sumber yang mendasarinya berubah. Struktur kolom tetap ini juga tercermin dalam tabel perubahan yang mendasar yang diakses oleh fungsi kueri yang ditentukan.

Proses penangkapan yang bertanggung jawab untuk mengisi tabel perubahan mengakomodasi tabel perubahan struktur kolom tetap dengan mengabaikan kolom baru yang tidak diidentifikasi untuk diambil ketika tabel sumber diaktifkan untuk mengubah pengambilan data. Jika kolom terlacak dihilangkan, nilai null 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 memperkenalkan 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 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. Opsi ini memungkinkan proses penangkapan 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. Memungkinkan mekanisme penangkapan mengisi kedua tabel perubahan secara 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.

Penting

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

Hubungan dengan agen pembaca log

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. Di SQL Server dan Azure SQL Managed Instance, saat mengubah tangkapan data saja diaktifkan untuk database, Anda membuat pekerjaan pengambilan data perubahan SQL Server Agent sebagai kendaraan untuk memanggil sp_replcmds. Ketika replikasi juga ada, pembaca log transaksional saja digunakan untuk memenuhi kebutuhan data perubahan untuk kedua konsumen ini. Strategi ini secara signifikan mengurangi ketidakcocokan log ketika replikasi dan tangkapan data perubahan diaktifkan untuk database yang sama.

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

Catatan

Di SQL Server dan Azure SQL Managed Instance, kedua instans logika penangkapan mengharuskan Agen SQL Server berjalan agar proses dijalankan.

Tugas utama dari proses penangkapan 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 penangkapan 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 dipantau secara aktif untuk entri perubahan dalam log. Demikian pula, menonaktifkan penangkapan data perubahan juga akan terdeteksi, menyebabkan tabel sumber dihapus dari kumpulan tabel yang dipantau secara aktif untuk data perubahan. Saat memproses bagian log selesai, proses pengambilan menandakan logika pemotongan log server, yang menggunakan informasi ini untuk mengidentifikasi entri log yang memenuhi syarat untuk pemotongan.

Penting

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 penangkapan telah dikumpulkan oleh proses pengambilan. Jika proses penangkapan 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 yang dilacak. Pernyataan DDL yang terkait dengan perubahan pengambilan data membuat entri ke log transaksi database setiap kali database atau tabel yang diaktifkan pengambilan data diubah atau kolom tabel yang diaktifkan pengambilan 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.

Peringatan

  • MaxCmdsInTran tidak dirancang untuk selalu diaktifkan. Ada untuk mengatasi kasus di mana seseorang secara tidak sengaja melakukan sejumlah besar operasi DML dalam satu transaksi (menyebabkan keterlambatan dalam distribusi perintah sampai seluruh transaksi berada dalam database distribusi, kunci ditahan, dll.). Jika Anda secara rutin jatuh ke dalam situasi ini, tinjau logika aplikasi Anda untuk menemukan cara mengurangi ukuran transaksi.
  • MaxCmdsInTran tidak didukung jika database publikasi yang diberikan mengaktifkan CDC dan replikasi. Menggunakan MaxCmdsInTran dalam konfigurasi ini dapat menyebabkan kehilangan data dalam tabel perubahan CDC. Ini juga dapat menyebabkan kesalahan PK jika parameter MaxCmdsInTran ditambahkan dan dihapus saat mereplikasi Transaksi besar.

Pekerjaan agen

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 mengubah pembersihan tabel. 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 ketika tabel pertama database diaktifkan untuk mengubah pengambilan data. Pekerjaan Pembersihan selalu dibuat. Pekerjaan penangkapan hanya akan dibuat jika tidak ada publikasi transaksi yang ditentukan untuk database. Pekerjaan penangkapan juga dibuat ketika tangkapan data perubahan dan replikasi transaksional diaktifkan untuk database, dan pekerjaan pembaca log transaksional dihapus karena database tidak lagi menentukan publikasi.

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 pengambilan data perubahan dinonaktifkan untuk database. Pekerjaan penangkapan juga dapat dihapus ketika publikasi pertama ditambahkan ke database, dan mengubah pengambilan data dan replikasi transaksional diaktifkan.

Secara internal, pekerjaan agen penangkapan data perubahan 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 lainnya 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 ke deposit 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 pengambilan data tidak dapat berfungsi dengan baik saat layanan Mesin Database atau layanan SQL Server Agent berjalan di bawah akun LAYANAN JARINGAN. Ini dapat mengakibatkan kesalahan 22832.

Interoperabilitas dengan fitur lain

Mengubah pengambilan data memiliki beberapa batasan saat bekerja dengan fitur SQL Server lainnya. Tinjau Interoperabilitas untuk mempelajari lebih lanjut.

Masalah yang diketahui

Untuk masalah umum dan kesalahan yang terkait dengan perubahan pengambilan data, tinjau Masalah yang diketahui dengan CDC.

Baca juga