Catatan
Akses ke halaman ini memerlukan otorisasi. Anda dapat mencoba masuk atau mengubah direktori.
Akses ke halaman ini memerlukan otorisasi. Anda dapat mencoba mengubah direktori.
Berlaku untuk:
SQL ServerAzure SQL Managed Instance
Data perubahan tersedia untuk mengubah konsumen pengambilan data melalui fungsi bernilai tabel (TVF). Semua kueri fungsi ini memerlukan dua parameter untuk menentukan rentang Nomor Urutan Log (LSN) yang memenuhi syarat untuk dipertimbangkan saat mengembangkan kumpulan hasil yang dikembalikan. Nilai LSN atas dan bawah yang mengikat interval dianggap disertakan dalam interval.
Beberapa fungsi disediakan untuk membantu menentukan nilai LSN yang sesuai untuk digunakan dalam mengkueri TVF. Fungsi sys.fn_cdc_get_min_lsn mengembalikan LSN terkecil yang terkait dengan interval validitas instans tangkapan. Interval validitas adalah interval waktu yang data perubahannya saat ini tersedia untuk instans pengambilannya. Fungsi sys.fn_cdc_get_max_lsn mengembalikan LSN terbesar dalam interval validitas. Fungsi sys.fn_cdc_map_time_to_lsn dan sys.fn_cdc_map_lsn_to_time tersedia untuk membantu menempatkan nilai LSN pada garis waktu konvensional.
Karena penangkapan data perubahan menggunakan interval kueri tertutup, terkadang perlu untuk menghasilkan nilai LSN berikutnya secara berurutan untuk memastikan bahwa perubahan tidak diduplikasi di jendela kueri berturut-turut. Fungsi sys.fn_cdc_increment_lsn dan sys.fn_cdc_decrement_lsn berguna saat diperlukan penyesuaian inkremental terhadap nilai LSN.
Memvalidasi Batas LSN
Sebaiknya validasi batas LSN yang akan digunakan dalam kueri TVF sebelum digunakan. Titik akhir null atau titik akhir yang berada di luar interval validitas untuk instans penangkapan akan menyebabkan kesalahan dikembalikan oleh TVF change data capture.
Misalnya, kesalahan berikut dikembalikan untuk kueri untuk semua perubahan saat parameter yang digunakan untuk menentukan interval kueri tidak valid, atau berada di luar rentang, atau opsi filter baris tidak valid.
Msg 313, Level 16, State 3, Line 1
An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_ ...
Kesalahan terkait yang dikembalikan untuk kueri net changes adalah sebagai berikut:
Msg 313, Level 16, State 3, Line 1
An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_net_changes_ ...
Catatan
Diakui bahwa pesan untuk Msg 313 menyesatkan dan tidak menyampaikan penyebab kegagalan yang sebenarnya. Penggunaan yang janggal ini disebabkan oleh ketidakmampuan untuk memunculkan kesalahan eksplisit dari dalam TVF. Namun demikian, mengembalikan kesalahan yang dapat dikenali, meskipun tidak akurat, dianggap lebih baik daripada sekadar mengembalikan hasil kosong. Kumpulan hasil kosong tidak akan dapat dibedakan dari kueri yang valid yang tidak mengembalikan perubahan.
Kegagalan otorisasi akan menghasilkan kegagalan saat membuat kueri untuk semua perubahan, seperti yang ditunjukkan:
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'fn_cdc_get_all_changes_...', database 'MyDB', schema 'cdc'.
Hal yang sama berlaku saat mengkueri perubahan bersih:
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object fn_cdc_get_net_changes_...', database 'MyDB', schema 'cdc'.
Di SQL Server Management Studio, lihat templat Menghitung Perubahan Bersih Menggunakan TRY CATCH untuk demonstrasi tentang cara mencegat kesalahan TVF yang diketahui ini dan mengembalikan informasi yang lebih bermakna tentang kegagalan tersebut.
Petunjuk / Saran
Untuk menemukan perubahan templat pengambilan data di SQL Server Management Studio, pada menu Tampilan , pilih Penjelajah Templat, perluas Templat SQL Server lalu perluas folder Ubah Pengambilan Data .
Fungsi Kueri
Bergantung pada karakteristik tabel sumber yang dilacak dan cara instans pengambilan dikonfigurasi, satu atau dua TVF untuk membuat kueri atas data perubahan akan dibuat.
Fungsi cdc.fn_cdc_get_all_changes_<capture_instance> mengembalikan semua perubahan yang terjadi untuk interval yang ditentukan. Fungsi ini selalu dihasilkan. Entri selalu dikembalikan dalam urutan, pertama berdasarkan LSN commit transaksi dari perubahan tersebut, kemudian berdasarkan nilai yang menentukan urutan perubahan di dalam transaksinya. Bergantung pada opsi filter baris yang dipilih, baris akhir dikembalikan pada pembaruan (opsi filter baris "semua") atau nilai baru dan lama dikembalikan pada pembaruan (opsi filter baris "semua perbarui lama"').
Fungsi cdc.fn_cdc_get_net_changes_<capture_instance> dihasilkan saat parameter
@supports_net_changesdiatur ke1saat tabel sumber diaktifkan.Catatan
Opsi ini hanya didukung jika tabel sumber memiliki kunci primer yang ditentukan atau jika parameter @index_name telah digunakan untuk mengidentifikasi indeks unik.
Fungsi
netchangesmengembalikan satu perubahan per baris tabel sumber yang dimodifikasi. Jika lebih dari satu perubahan dicatat untuk baris selama interval yang ditentukan, nilai kolom akan mencerminkan konten akhir baris. Untuk mengidentifikasi secara tepat operasi yang diperlukan guna memperbarui lingkungan target, TVF harus mempertimbangkan baik operasi awal pada baris tersebut selama interval maupun operasi akhir pada baris tersebut. Saat opsi filter baris 'all' ditetapkan, operasi yang dikembalikan oleh kueri net changes dapat berupa penyisipan, penghapusan, atau pembaruan (nilai baru). Opsi ini selalu mengembalikan masker pembaruan sebagai null karena ada biaya yang terkait dengan komputasi masker agregat. Jika Anda memerlukan masker agregat yang mencerminkan semua perubahan pada baris, gunakan opsi 'semua dengan masker'. Jika pemrosesan downstream tidak perlu membedakan penyisipan dan pembaruan, gunakan opsi 'all with merge'. Dalam hal ini, nilai operasi hanya akan mengambil dua nilai: 1 untuk penghapusan dan 5 untuk operasi yang bisa berupa sisipan atau pembaruan. Opsi ini menghilangkan pemrosesan tambahan yang diperlukan untuk menentukan apakah operasi turunan harus berupa penyisipan atau pembaruan, dan dapat meningkatkan performa kueri ketika diferensiasi ini tidak diperlukan.
Masker pembaruan yang dikembalikan dari fungsi kueri adalah representasi ringkas yang mengidentifikasi semua kolom yang berubah dalam baris data perubahan. Biasanya, informasi ini hanya diperlukan untuk subset kecil kolom yang diambil. Fungsi tersedia untuk membantu mengekstrak informasi dari masker dalam bentuk yang lebih langsung dapat digunakan oleh aplikasi. Fungsi sys.fn_cdc_get_column_ordinal mengembalikan posisi ordinal kolom bernama untuk instans pengambilan tertentu, sedangkan fungsi sys.fn_cdc_is_bit_set mengembalikan paritas bit dalam masker yang disediakan berdasarkan ordinal yang diteruskan dalam panggilan fungsi. Bersama-sama, kedua fungsi ini memungkinkan informasi dari masker pembaruan diekstraksi secara efisien dan dikembalikan dengan permintaan perubahan data. Di SQL Server Management Studio, lihat templat Menghitung Perubahan Bersih Menggunakan Semua Dengan Masker untuk demonstrasi tentang bagaimana fungsi-fungsi ini digunakan.
Skenario Penggunaan Fungsi Kueri
Bagian berikut menjelaskan skenario umum untuk melakukan kueri pengambilan data perubahan dengan menggunakan fungsi kueri cdc.fn_cdc_get_all_changes_<capture_instance> dan cdc.fn_cdc_get_net_changes_<capture_instance>.
Kueri untuk Semua Perubahan Dalam Interval Validitas Instans Pengambilan
Permintaan data perubahan yang paling sederhana adalah permintaan yang mengembalikan seluruh data perubahan saat ini dalam interval validitas instans pengambilan. Untuk membuat permintaan ini, pertama-tama tentukan batas LSN bawah dan atas dari interval validitas. Kemudian, gunakan nilai-nilai ini untuk mengidentifikasi parameter @from_lsn dan @to_lsn diteruskan ke fungsi cdc.fn_cdc_get_all_changes_<capture_instance> kueri atau cdc.fn_cdc_get_net_changes_<capture_instance>. Gunakan fungsi sys.fn_cdc_get_min_lsn untuk mendapatkan batas bawah, dan sys.fn_cdc_get_max_lsn untuk mendapatkan batas atas. Di SQL Server Management Studio, lihat templat Menghitung Semua Perubahan untuk Rentang Valid untuk kode sampel untuk mengkueri semua perubahan valid saat ini dengan menggunakan fungsi cdc.fn_cdc_get_all_changes_<capture_instance>kueri . Di SQL Server Management Studio, lihat templat Menghitung Perubahan Bersih untuk Rentang Valid untuk contoh serupa menggunakan fungsi cdc.fn_cdc_get_net_changes_<capture_instance>.
Kueri untuk Semua Perubahan Baru Sejak Kumpulan Perubahan Terakhir
Untuk aplikasi umum, mengkueri data perubahan akan menjadi proses yang sedang berlangsung, membuat permintaan berkala untuk semua perubahan yang terjadi sejak permintaan terakhir. Untuk kueri tersebut, Anda dapat menggunakan fungsi sys.fn_cdc_increment_lsn untuk mendapatkan batas bawah kueri saat ini dari batas atas kueri sebelumnya. Metode ini memastikan bahwa tidak ada baris yang diulang karena interval kueri selalu diperlakukan sebagai interval tertutup di mana kedua titik akhir disertakan dalam interval. Kemudian, gunakan fungsi sys.fn_cdc_get_max_lsn untuk mendapatkan titik akhir tinggi untuk interval permintaan baru. Di SQL Server Management Studio, lihat templat Menghitung Semua Perubahan Sejak Permintaan Sebelumnya untuk kode sampel untuk memindahkan jendela kueri secara sistematis untuk mendapatkan semua perubahan sejak permintaan terakhir.
Kueri untuk semua Perubahan Terbaru Hingga Sekarang
Batasan umum yang ditempatkan pada perubahan yang dikembalikan oleh fungsi kueri adalah hanya menyertakan perubahan yang terjadi antara permintaan sebelumnya hingga tanggal dan waktu saat ini. Untuk kueri ini, terapkan fungsi sys.fn_cdc_increment_lsn ke @from_lsn nilai yang digunakan dalam permintaan sebelumnya untuk menentukan batas bawah. Karena batas atas pada interval waktu dinyatakan sebagai titik waktu tertentu, itu harus dikonversi ke nilai LSN sebelum dapat digunakan oleh fungsi kueri. Sebelum nilai tanggalwaktu dapat dikonversi ke nilai LSN yang sesuai, Anda harus memastikan bahwa proses pengambilan telah memproses semua perubahan yang dilakukan melalui batas atas yang ditentukan. Ini diperlukan untuk memastikan bahwa semua perubahan yang memenuhi syarat telah disebarluaskan ke tabel perubahan. Salah satu cara untuk melakukannya adalah dengan membuat loop tunggu yang secara berkala memeriksa apakah commit lsn maksimum saat ini yang tercatat untuk tabel perubahan database mana pun melebihi waktu akhir yang diinginkan dari interval permintaan tersebut.
Setelah loop penundaan memverifikasi bahwa proses penangkapan telah memproses semua entri log yang relevan, gunakan fungsi sys.fn_cdc_map_time_to_lsn untuk menentukan titik akhir atas yang baru, yang dinyatakan sebagai nilai LSN. Untuk memastikan bahwa semua entri yang dilakukan melalui waktu yang ditentukan diambil, panggil fungsi sys.fn_cdc_map_time_to_lsn, dan gunakan opsi 'terbesar kurang dari atau sama dengan'.
Catatan
Dalam periode tidak aktif, entri dummy ditambahkan ke tabel cdc.lsn_time_mapping untuk menandakan bahwa proses penangkapan telah memproses perubahan hingga waktu komit tertentu. Ini mencegah timbulnya kesan bahwa proses pengambilan telah tertinggal, padahal memang tidak ada perubahan terbaru untuk diproses.
Templat Menghitung Semua Perubahan Hingga Sekarang menunjukkan cara menggunakan strategi sebelumnya untuk mengkueri perubahan data.
Menambahkan Waktu Penerapan ke Kumpulan Hasil Semua Perubahan
Waktu penerapan setiap transaksi dengan entri terkait dalam tabel perubahan database tersedia dalam tabel cdc.lsn_time_mapping. Dengan menggabungkan nilai __$start_lsn yang dikembalikan dalam permintaan untuk semua perubahan dengan nilai start_lsn dari sebuah entri tabel cdc.lsn_time_mapping, Anda dapat mengembalikan tran_end_time bersama dengan data perubahan untuk memberi stempel perubahan dengan waktu komitmen transaksi di sumber. Templat Tambahkan Waktu Penerapan ke Semua Kumpulan Hasil Perubahan menunjukkan cara melakukan gabungan ini.
Menggabungkan Ubah Data dengan Data Lain dari Transaksi yang Sama
Terkadang, berguna untuk menggabungkan data perubahan dengan informasi lain yang dikumpulkan terkait transaksi saat transaksi tersebut dikomitkan di sumber. Kolom tran_begin_lsn dalam tabel cdc.lsn_time_mapping menyediakan informasi yang diperlukan untuk melakukan gabungan tersebut. Ketika pembaruan sumber terjadi, nilai untuk database_transaction_begin_lsn dari tampilan dinamis sistem sys.dm_tran_database_transactions harus disimpan bersama dengan informasi lain untuk digabungkan dengan data perubahan. Gunakan fungsi fn_convertnumericlsntobinary untuk membandingkan database_transaction_begin_lsn nilai dan tran_begin_lsn . Kode untuk membuat fungsi ini tersedia dalam templat Buat Fungsi fn_convertnumericlsntobinary. Templat Mengembalikan Semua Perubahan dengan Parameter Tertentu tran_begin_lsn menunjukkan cara memengaruhi penggabungan.
Kueri Menggunakan Fungsi Pembungkus Tanggal dan Waktu
Skenario aplikasi umum untuk mengkueri data perubahan adalah meminta perubahan data secara berkala dengan menggunakan jendela geser yang dibatasi oleh nilai tanggalwaktu. Untuk jenis pengguna ini, penangkapan data perubahan menyediakan prosedur tersimpan sys.sp_cdc_generate_wrapper_function yang menghasilkan skrip untuk membuat fungsi pembungkus khusus bagi fungsi kueri penangkapan data perubahan. Pembungkus kustom ini memungkinkan interval kueri dinyatakan sebagai pasangan tanggalwaktu.
Opsi panggilan untuk prosedur tersimpan memungkinkan pembungkus dibuat untuk semua instans tangkapan yang dapat diakses pemanggil, atau hanya instans tangkapan tertentu. Opsi yang didukung juga mencakup kemampuan untuk menentukan apakah titik akhir tinggi interval penangkapan harus terbuka atau tertutup, mana dari kolom yang diambil yang tersedia yang harus disertakan dalam kumpulan hasil dan kolom mana yang disertakan yang harus memiliki bendera pembaruan terkait. Prosedur ini mengembalikan kumpulan hasil dengan dua kolom: nama fungsi yang dihasilkan, yang dapat diturunkan dari nama instance capture, dan pernyataan CREATE untuk prosedur tersimpan pembungkus. Fungsi untuk membungkus semua kueri perubahan selalu dihasilkan. Jika parameter @supports_net_changes ditetapkan saat instans penangkapan dibuat, fungsi untuk membungkus fungsi perubahan neto juga dihasilkan.
Merupakan tanggung jawab perancang aplikasi untuk memanggil prosedur tersimpan pembuat skrip guna menghasilkan pernyataan CREATE untuk prosedur tersimpan pembungkus, serta menjalankan skrip CREATE yang dihasilkan untuk membuat fungsi. Ini tidak terjadi secara otomatis saat instans capture dibuat.
Pembungkus tanggalwaktu dimiliki oleh pengguna, dan tidak dibuat dalam skema default pemanggil. Fungsi yang dihasilkan cocok tanpa modifikasi untuk sebagian besar pengguna. Namun, penyesuaian lebih lanjut selalu dapat diterapkan ke skrip yang dihasilkan sebelum membuat fungsi.
Nama fungsi untuk membungkus kueri semua perubahan adalah fn_all_changes_ diikuti dengan nama instance penangkapan. Awalan untuk pembungkus perubahan bersih yang digunakan adalah fn_net_changes_. Kedua fungsi menerima tiga argumen, sama seperti TVF change data capture yang terkait. Namun, interval kueri untuk wrapper dibatasi oleh dua nilai datetime, bukan oleh dua nilai LSN. Parameter @row_filter_option untuk kedua set fungsi sama.
Fungsi pembungkus yang dihasilkan mendukung konvensi berikut untuk menelusuri linimasa penangkapan data perubahan secara sistematis: Parameter @end_time dari interval sebelumnya diharapkan digunakan sebagai parameter @start_time untuk interval berikutnya. Fungsi pembungkus mengurus pemetaan nilai tanggalwaktu ke nilai LSN dan memastikan bahwa tidak ada data yang hilang atau diulang jika konvensi ini diikuti.
Pembungkus dapat dihasilkan untuk mendukung batas atas tertutup atau batas atas terbuka pada jendela kueri yang ditentukan. Artinya, pemanggil dapat menentukan apakah entri yang memiliki waktu commit yang sama dengan batas atas dari interval ekstraksi akan disertakan dalam interval tersebut. Secara default, batas atas disertakan.
Meskipun TVF kueri yang dihasilkan gagal jika diberi nilai null untuk nilai @from_lsn atau nilai @to_lsn, fungsi pembungkus datetime menggunakan null agar fungsi pembungkus datetime dapat mengembalikan semua perubahan terkini. Artinya, jika null diteruskan sebagai titik akhir rendah dari jendela kueri ke pembungkus datetime, titik akhir rendah dari interval validitas instance pengambilan digunakan dalam pernyataan yang mendasari SELECT yang diterapkan pada TVF kueri. Demikian pula, jika null diteruskan sebagai titik akhir tinggi jendela kueri, titik akhir tinggi dari interval validitas instans pengambilan digunakan saat memilih dari TVF kueri.
Tataan hasil yang dikembalikan oleh fungsi pembungkus mencakup semua kolom yang diminta diikuti oleh kolom operasi, dikodekan ulang sebagai satu atau dua karakter untuk mengidentifikasi operasi yang terkait dengan baris. Jika bendera pembaruan telah diminta, bendera tersebut muncul sebagai kolom bit setelah kode operasi, dalam urutan yang ditentukan dalam @update_flag_list parameter. Untuk informasi tentang opsi pemanggilan untuk menyesuaikan wrapper datetime yang dihasilkan, lihat sys.sp_cdc_generate_wrapper_function (Transact-SQL).
Templat Menginstansiasi Wrapper TVF Dengan Penanda Pembaruan menunjukkan bagaimana mengkustomisasi fungsi pembungkus yang dihasilkan untuk menambahkan penanda pembaruan untuk kolom tertentu ke kumpulan hasil yang dikembalikan oleh kueri perubahan bersih. Templat Menginisialisasi CDC Wrapper TVF untuk Skema menunjukkan cara menginisialisasi Pembungkus Waktu dan Tanggal untuk TVF Kueri dari semua instans penangkapan yang dibuat untuk tabel sumber dalam skema database tertentu.
Untuk contoh yang menggunakan pembungkus datetime untuk meminta data perubahan, di SQL Server Management Studio, lihat templat Dapatkan Perubahan Net Menggunakan Pembungkus Dengan Bendera Pembaruan. Template ini menunjukkan cara melakukan kueri terhadap perubahan netto menggunakan fungsi pembungkus saat fungsi tersebut dikonfigurasi untuk mengembalikan flag pembaruan. Opsi penyaring baris 'semua dengan masker' diperlukan agar fungsi kueri yang mendasar dapat mengembalikan masker pembaruan yang tidak bernilai null pada pembaruan. Nilai null diteruskan untuk batas interval tanggal waktu yang lebih rendah dan atas untuk memberi sinyal kepada fungsi agar menggunakan titik batas bawah dan titik batas atas dari interval validitas untuk instance pencatatan saat melakukan kueri berbasis LSN yang mendasarinya. Kueri mengembalikan satu baris untuk setiap perubahan pada baris sumber yang terjadi dalam rentang valid untuk instans penangkapan.
Gunakan Fungsi Pembungkus DateTime untuk Transisi Antar Instans Pengambilan
Change data capture mendukung hingga dua instans penangkapan untuk satu tabel sumber yang dilacak. Penggunaan utama kemampuan ini adalah untuk mengakomodasi transisi antara beberapa instans pengambilan ketika bahasa definisi data (DDL) berubah ke tabel sumber memperluas kumpulan kolom yang tersedia untuk pelacakan. Saat beralih ke instance capture yang baru, salah satu cara untuk melindungi lapisan aplikasi yang lebih tinggi dari perubahan nama fungsi kueri yang mendasarinya adalah dengan menggunakan fungsi pembungkus untuk membungkus panggilan dasarnya. Kemudian, pastikan bahwa nama fungsi pembungkus tetap sama. Saat peralihan akan dilakukan, fungsi pembungkus lama dapat dihapus, dan fungsi baru dengan nama yang sama dapat dibuat yang merujuk ke fungsi kueri baru. Dengan terlebih dahulu memodifikasi skrip yang dihasilkan untuk membuat fungsi pembungkus dengan nama yang sama, Anda dapat beralih ke instans tangkapan baru tanpa memengaruhi lapisan aplikasi yang lebih tinggi.