Bekerja dengan Ubah Data

  • Artikel

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure 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 yang sys.fn_cdc_increment_lsn dan sys.fn_cdc_decrement_lsn berguna ketika diperlukan penyesuaian inkremental pada 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 pengambilan akan memaksa kesalahan dikembalikan oleh TVF pengambilan data perubahan.

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 perubahan bersih 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 canggung ini berasal dari ketidakmampuan untuk meningkatkan kesalahan eksplisit dari dalam TVF. Namun demikian, nilai mengembalikan hasil yang dapat dikenali, jika tidak akurat, kesalahan dianggap lebih disukai untuk hanya mengembalikan hasil kosong. Kumpulan hasil kosong tidak akan dapat dibedakan dari kueri yang valid yang tidak mengembalikan perubahan.

Kegagalan otorisasi akan mengembalikan kegagalan saat mengkueri 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'.

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.

Catatan

Untuk menemukan templat ubah pengambilan data di SQL Server Management Studio, pada menu Tampilan , klik Penjelajah Templat, perluas Templat SQL Server lalu perluas folder Ubah Pengambilan Data.

Fungsi Kueri

Bergantung pada karakteristik tabel sumber yang dilacak dan cara instans penangkapannya dikonfigurasi, baik satu atau dua TVF untuk mengkueri data perubahan dihasilkan.

  • 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 diurutkan, pertama-tama oleh LSN penerapan transaksi perubahan, lalu dengan nilai yang mengurutkan perubahan 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_changes diatur ke 1 saat 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 netchanges mengembalikan 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 operasi yang diperlukan dengan benar untuk memperbarui lingkungan target, TVF harus mempertimbangkan operasi awal pada baris selama interval dan operasi akhir pada baris. Saat opsi filter baris 'semua' ditentukan, operasi yang dikembalikan oleh kueri perubahan bersih akan disisipkan, dihapus, atau diperbarui (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 hilir tidak memerlukan sisipan dan pembaruan untuk dibedakan, gunakan opsi 'semua dengan penggabungan'. 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. Lihat templat Menghitung Perubahan Bersih Menggunakan Semua Dengan Masker untuk demonstrasi tentang bagaimana fungsi-fungsi ini digunakan.

Skenario Fungsi Kueri

Bagian berikut ini menjelaskan skenario umum untuk mengkueri mengubah data pengambilan data dengan menggunakan fungsi kueri cdc.fn_cdc_get_all_changes_<capture_instance> dan cdc.fn_cdc_get_net_changes_<capture_instance>.

Mengkueri untuk Semua Perubahan Dalam Interval Validitas Instans Pengambilan

Permintaan paling mudah untuk data perubahan adalah permintaan yang mengembalikan semua data perubahan saat ini dalam interval validitas instans tangkapan. 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 kueri cdc.fn_cdc_get_all_changes_<capture_instance> 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. Lihat templat Menghitung Semua Perubahan untuk Rentang Valid untuk kode sampel yang akan dikueri untuk semua perubahan yang valid saat ini dengan menggunakan fungsi kueri cdc.fn_cdc_get_all_changes_<capture_instance>. Lihat templat Menghitung Perubahan Bersih untuk Rentang Valid untuk contoh serupa menggunakan fungsi cdc.fn_cdc_get_net_changes_<capture_instance>.

Mengkueri 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. Lihat templat Menghitung Semua Perubahan Sejak Permintaan Sebelumnya untuk kode sampel untuk memindahkan jendela kueri secara sistematis untuk mendapatkan semua perubahan sejak permintaan terakhir.

Mengkueri semua Perubahan Baru 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 melakukan ini adalah dengan menyusun perulangan tunggu yang secara berkala memeriksa untuk melihat apakah penerapan maksimum saat ini lsn yang direkam untuk tabel perubahan database apa pun melebihi waktu akhir yang diinginkan dari interval permintaan.

Setelah perulangan penundaan memverifikasi bahwa proses pengambilan telah memproses semua entri log yang relevan, gunakan fungsi sys.fn_cdc_map_time_to_lsn untuk menentukan titik akhir tinggi 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 menandai fakta bahwa proses penangkapan telah memproses perubahan hingga waktu penerapan tertentu. Ini mencegahnya muncul bahwa proses penangkapan telah tertinggal ketika tidak ada perubahan baru-baru ini 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 entri tabel cdc.lsn_time_mapping, Anda dapat mengembalikan tran_end_time bersama dengan data perubahan untuk memberi stempel perubahan dengan waktu penerapan 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 tentang transaksi ketika diterapkan di sumbernya. 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 fn_convertnumericlsntobinary fungsi untuk membandingkan nilai database_transaction_begin_lsn dan tran_begin_lsn. Kode untuk membuat fungsi ini tersedia dalam templat Buat Fungsi fn_convertnumericlsntobinary. Templat Mengembalikan Semua Perubahan dengan tran_begin_lsn yang Diberikan menunjukkan cara memengaruhi gabungan.

Mengkueri Menggunakan Fungsi Pembungkus TanggalWaktu

Skenario aplikasi umum untuk mengkueri data perubahan adalah meminta perubahan data secara berkala dengan menggunakan jendela geser yang dibatasi oleh nilai tanggalwaktu. Untuk kelas konsumen ini, tangkapan data perubahan menyediakan prosedur tersimpan sys.sp_cdc_generate_wrapper_function yang menghasilkan skrip untuk membuat fungsi pembungkus kustom untuk 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 tataan hasil dengan dua kolom: nama fungsi yang dihasilkan, yang dapat diturunkan dari nama instans tangkapan, dan pernyataan buat untuk prosedur tersimpan pembungkus. Fungsi untuk membungkus semua kueri perubahan selalu dihasilkan. @supports_net_changes Jika parameter diatur saat instans pengambilan dibuat, fungsi untuk membungkus fungsi perubahan bersih juga dihasilkan.

Adalah tanggung jawab perancang aplikasi untuk memanggil prosedur tersimpan pembuatan skrip untuk menghasilkan pernyataan buat untuk prosedur tersimpan pembungkus, dan untuk menjalankan skrip buat yang dihasilkan untuk membuat fungsi. Ini tidak terjadi secara otomatis ketika instans pengambilan 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 semua kueri perubahan fn_all_changes_ diikuti dengan nama instans pengambilan. Awalan yang digunakan untuk pembungkus perubahan bersih fn_net_changes_. Kedua fungsi mengambil tiga argumen, seperti halnya TVF pengambilan data perubahan terkait. Namun, interval kueri untuk pembungkus dibatasi oleh dua nilai tanggalwaktu alih-alih oleh dua nilai LSN. Parameter @row_filter_option untuk kedua set fungsi sama.

Fungsi pembungkus yang dihasilkan mendukung konvensi berikut untuk berjalan secara sistematis garis waktu pengambilan data perubahan: Diharapkan parameter @end_time interval sebelumnya digunakan sebagai @start_time parameter 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 penerapan sama dengan batas atas interval ekstraksi akan disertakan dalam interval. Secara default, batas atas disertakan.

Meskipun TVF kueri yang dihasilkan gagal jika diberikan nilai null untuk @from_lsn nilai atau @to_lsn nilai, fungsi pembungkus tanggalwaktu menggunakan null untuk memungkinkan pembungkus tanggalwaktu mengembalikan semua perubahan saat ini. Artinya, jika null diteruskan sebagai titik akhir rendah jendela kueri ke pembungkus tanggalwaktu, titik akhir rendah dari interval validitas instans pengambilan digunakan dalam pernyataan SELECT yang mendasarinya yang diterapkan ke 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 panggilan untuk menyesuaikan pembungkus tanggalwaktu yang dihasilkan, lihat sys.sp_cdc_generate_wrapper_function (Transact-SQL).

Templat Membuat Instans Wrapper TVF Dengan Bendera Pembaruan menunjukkan cara mengkustomisasi fungsi pembungkus yang dihasilkan untuk menambahkan bendera pembaruan untuk kolom tertentu ke kumpulan hasil yang dikembalikan oleh kueri perubahan bersih. Templat Membuat Instans CDC Wrapper TVF untuk Skema menunjukkan cara membuat instans Pembungkus TanggalWaktu untuk TVF Kueri untuk semua instans pengambilan yang dibuat untuk tabel sumber dalam skema database tertentu.

Untuk contoh yang menggunakan pembungkus tanggalwaktu untuk mengkueri perubahan data, lihat templat Dapatkan Perubahan Bersih Menggunakan Pembungkus Dengan Bendera Pembaruan. Templat ini menunjukkan cara mengkueri perubahan bersih dengan fungsi pembungkus ketika pembungkus dikonfigurasi untuk mengembalikan bendera pembaruan. Perhatikan bahwa opsi filter baris 'semua dengan masker' diperlukan untuk fungsi kueri yang mendasar untuk mengembalikan masker pembaruan non-null saat pembaruan. Nilai null diteruskan untuk batas interval tanggalwaktu yang lebih rendah dan terbaru untuk memberi sinyal fungsi untuk menggunakan titik akhir rendah dan titik akhir tinggi dari interval validitas untuk instans pengambilan saat melakukan kueri berbasis LSN yang mendasarinya. Kueri mengembalikan satu baris untuk setiap modifikasi ke baris sumber yang terjadi dalam rentang yang valid untuk instans pengambilan.

Menggunakan Fungsi Pembungkus TanggalWaktu ke Transisi Antara Instans Pengambilan

Mengubah pengambilan data mendukung hingga dua instans pengambilan untuk satu tabel sumber terlacak. 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 bertransisi ke instans tangkapan baru, salah satu cara untuk melindungi tingkat aplikasi yang lebih tinggi dari perubahan nama fungsi kueri yang mendasar adalah dengan menggunakan fungsi pembungkus untuk membungkus panggilan yang mendasar. Kemudian, pastikan bahwa nama fungsi pembungkus tetap sama. Ketika sakelar terjadi, fungsi pembungkus lama dapat dihilangkan, dan yang baru dengan nama yang sama dibuat yang mereferensikan 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.

Konten terkait