Bagikan melalui

Apa itu penangkapan data perubahan (CDC)?

Berlaku untuk: SQL ServerAzure SQL Managed Instance

Dalam artikel ini, pelajari tentang penangkapan data perubahan (CDC), yang merekam aktivitas pada database ketika tabel dan baris 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

Penangkapan perubahan 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 yang diperlukan untuk menerapkan data perubahan ini ke lingkungan target diambil untuk baris yang dimodifikasi dan disimpan dalam tabel perubahan yang mencerminkan struktur kolom dari tabel sumber yang dilacak. Selain itu, fungsi bernilai tabel tersedia untuk akses sistematis ke data perubahan ini oleh pihak pengguna.

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 perubahan penangkapan data.

Ubah diagram aliran data tangkapan 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 data. Kemudian, ia membaca log dan menambahkan informasi tentang perubahan pada tabel perubahan yang terkait dengan 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 dapat dilacak pada tabel individual dalam database, perubahan penangkapan 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 dari instans penangkapan dipertahankan dalam tabel metadata penangkapan 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 instance tangkapan dibuat dalam skema tangkapan data perubahan dari database yang telah 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 melakukan kueri semua perubahan dinamai dengan menambahkan fn_cdc_get_all_changes_ sebelum 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 tangkapan.

Penting

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

Ubah tabel

Lima kolom pertama dari tabel perubahan penangkapan data 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 commit (LSN) yang ditetapkan ke perubahan. LSN commit mengidentifikasi perubahan yang dilakukan dalam transaksi yang sama dan mengurutkan transaksi-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.

Basis data

Data yang disimpan dalam tabel perubahan tumbuh secara tidak terkelola jika Anda tidak memangkas data secara berkala dan sistematis. Proses pembersihan pendataan perubahan bertanggung jawab untuk menjalankan 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 tingkat yang lebih tinggi, karena proses pencatatan memproses setiap batch baru dari data perubahan, entri baru ditambahkan ke cdc.lsn_time_mapping untuk setiap transaksi yang memiliki entri tabel perubahan. Dalam tabel pemetaan, Nomor Urutan Log Commit (LSN) dan waktu commit transaksi (kolom start_lsn dan tran_end_time) masing-masing dipertahankan. Nilai LSN maksimum yang ditemukan di cdc.lsn_time_mapping mewakili batas atas dari kerangka 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 jangkauan validitas database dan jangkauan validitas dari setiap instans pengambilan individu bertepatan, namun 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 akan memiliki titik batas bawah 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 instance pengambilan data yang ditentukan. Saat proses pembersihan membersihkan entri tabel perubahan, proses ini menyesuaikan nilai start_lsn untuk semua instans tangkapan guna mencerminkan titik batas terendah baru untuk data perubahan yang dapat diakses. Hanya instance pengambilan dengan nilai start_lsn yang saat ini lebih kecil dari ambang batas baru akan 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 tersebut disebarluaskan ke tabel perubahan untuk memastikan bahwa mekanisme pengambilan tidak menyebabkan kehilangan data pada kolom yang dilacak. Proses pengambilan juga memposting setiap perubahan yang terdeteksi pada struktur kolom tabel terlacak ke tabel cdc.ddl_history. Pengguna yang ingin diberi tahu tentang penyesuaian yang mungkin harus dilakukan dalam aplikasi hilir, menggunakan 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 penangkapan 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 dilakukan, instans penangkapan yang 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, sebuah fungsi internal server yang dibangun sebagai bagian dari sqlservr.exe dan juga digunakan oleh replikasi transaksional untuk mengumpulkan perubahan dari log transaksi. Di SQL Server dan Azure SQL Managed Instance, saat hanya change data capture diaktifkan untuk database, Anda membuat pekerjaan penangkapan data perubahan SQL Server Agent untuk menjalankan 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 logika pengambilan data mengharuskan Agen SQL Server dijalankan agar proses dieksekusi.

Tugas utama dari proses penangkapan adalah memindai log dan menulis data kolom serta informasi terkait transaksi ke tabel penangkapan perubahan data. Untuk memastikan batas konsistensi transaksi pada semua tabel capture data perubahan yang diisinya, proses capture memulai dan menyelesaikan 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 pelacakan data perubahan, bahkan jika mode pemulihan diatur pada pemulihan sederhana, titik pemotongan log tidak akan maju sampai semua perubahan yang ditandai untuk pelacakan telah dikumpulkan oleh proses pengambilan. Jika proses penangkapan tidak berjalan dan ada perubahan yang perlu dikumpulkan, melakukan CHECKPOINT tidak akan memotong log.

Proses pengambilan juga digunakan untuk mempertahankan riwayat terhadap perubahan DDL pada tabel yang dilacak. Pernyataan DDL yang terkait dengan pengambilan data berubah membuat entri ke log transaksi database setiap kali database atau tabel yang diaktifkan untuk pengambilan data dihapus, atau kolom dari tabel yang diaktifkan untuk pengambilan data ditambahkan, dimodifikasi, atau dihapus. 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 dihidupkan. 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 tugas SQL Server Agent biasanya dikaitkan dengan database yang diaktifkan pengambilan data perubahan: satu digunakan untuk mengisi tabel perubahan database, dan satu 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 didefinisikan untuk tangkapan data perubahan yang mengimplementasikan logika pekerjaan. Pekerjaan dibuat ketika tabel pertama database diaktifkan untuk pemantauan perubahan data. Tugas Pembersihan selalu dibuat. Pekerjaan pengambilan data hanya akan dibuat jika tidak ada publikasi transaksi yang ditentukan untuk database. Pekerjaan penangkapan juga dibuat ketika tangkapan data perubahan dan replikasi diaktifkan untuk transaksi pada database, dan tugas pembaca log transaksional dihapus karena publikasi yang ditentukan sudah tidak ada dalam database.

Tugas pengambilan 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.

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

Secara internal, pekerjaan agen penangkapan data perubahan dibuat dan dihapus dengan menggunakan prosedur yang disimpan 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.

Pengambilan data perubahan tidak dapat berfungsi dengan baik saat layanan Mesin Database atau layanan SQL Server Agent berjalan di bawah akun NETWORK SERVICE. 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 umum

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

Lihat juga