Bagikan melalui

Menerbitkan Eksekusi Prosedur Tersimpan dalam Replikasi Transaksional

  • Artikel

Berlaku untuk: SQL Server Azure SQL Managed Instance

Jika Anda memiliki satu atau beberapa prosedur tersimpan yang dijalankan di Publisher dan memengaruhi tabel yang diterbitkan, pertimbangkan untuk menyertakan prosedur tersimpan tersebut dalam publikasi Anda sebagai artikel eksekusi prosedur tersimpan. Definisi prosedur (pernyataan CREATE PROCEDURE) direplikasi ke Pelanggan saat langganan diinisialisasi; ketika prosedur dijalankan di Publisher, replikasi menjalankan prosedur yang sesuai di Pelanggan. Ini dapat memberikan performa yang jauh lebih baik untuk kasus di mana operasi batch besar dilakukan, karena hanya eksekusi prosedur yang direplikasi, melewati kebutuhan untuk mereplikasi perubahan individual untuk setiap baris. Misalnya, asumsikan Anda membuat prosedur tersimpan berikut dalam database publikasi:

CREATE PROC give_raise AS  
UPDATE EMPLOYEES SET salary = salary * 1.10

Prosedur ini memberi masing-masing dari 10.000 karyawan di perusahaan Anda peningkatan gaji 10 persen. Ketika Anda menjalankan prosedur tersimpan ini di Publisher, itu memperbarui gaji untuk setiap karyawan. Tanpa replikasi eksekusi prosedur tersimpan, pembaruan akan dikirim ke Pelanggan sebagai transaksi multi-langkah besar:

BEGIN TRAN  
UPDATE EMPLOYEES SET salary = salary * 1.10 WHERE PK = 'emp 1'  
UPDATE EMPLOYEES SET salary = salary * 1.10 WHERE PK = 'emp 2'

Dan ini berulang untuk 10.000 pembaruan.

Dengan replikasi eksekusi prosedur tersimpan, replikasi hanya mengirim perintah untuk menjalankan prosedur tersimpan di Pelanggan, daripada menulis semua pembaruan ke database distribusi dan kemudian mengirimkannya melalui jaringan ke Pelanggan:

EXEC give_raise

Penting

Replikasi prosedur tersimpan tidak sesuai untuk semua aplikasi. Jika artikel difilter secara horizontal, sehingga ada kumpulan baris yang berbeda di Penerbit daripada di Pelanggan, menjalankan prosedur tersimpan yang sama di keduanya mengembalikan hasil yang berbeda. Demikian pula, jika pembaruan didasarkan pada subkueri tabel lain yang tidak direplikasi, menjalankan prosedur tersimpan yang sama di Penerbit dan Pelanggan mengembalikan hasil yang berbeda.

Untuk menerbitkan eksekusi prosedur tersimpan

Memodifikasi Prosedur di Pelanggan

Secara default, definisi prosedur tersimpan di Publisher disebarkan ke setiap Pelanggan. Namun, Anda juga dapat memodifikasi prosedur tersimpan di Pelanggan. Ini berguna jika Anda ingin logika yang berbeda dijalankan di Penerbit dan Pelanggan. Misalnya, pertimbangkan sp_big_delete, prosedur tersimpan di Publisher yang memiliki dua fungsi: ini menghapus 1.000.000 baris dari tabel yang direplikasi big_table1 dan memperbarui tabel yang tidak direplikasi big_table2. Untuk mengurangi permintaan pada sumber daya jaringan, Anda harus menyebarluaskan penghapusan 1 juta baris sebagai prosedur tersimpan dengan menerbitkan sp_big_delete. Di Pelanggan, Anda dapat memodifikasi sp_big_delete untuk menghapus hanya 1 juta baris dan tidak melakukan pembaruan berikutnya ke big_table2.

Catatan

Secara default, setiap perubahan yang dibuat menggunakan ALTER PROCEDURE di Publisher disebarkan ke Pelanggan. Untuk mencegah hal ini, nonaktifkan penyebaran perubahan skema sebelum menjalankan ALTER PROCEDURE. Untuk informasi tentang perubahan skema, lihat Membuat Perubahan Skema pada Database Publikasi.

Jenis Artikel Eksekusi Prosedur Tersimpan

Ada dua cara berbeda di mana eksekusi prosedur tersimpan dapat diterbitkan: artikel eksekusi prosedur yang dapat diserialisasikan dan artikel eksekusi prosedur.

  • Opsi yang dapat diserialisasikan direkomendasikan karena mereplikasi eksekusi prosedur hanya jika prosedur dijalankan dalam konteks transaksi yang dapat diserialisasikan. Jika prosedur tersimpan dijalankan dari luar transaksi yang dapat diserialisasikan, perubahan pada data dalam tabel yang diterbitkan direplikasi sebagai serangkaian pernyataan DML. Perilaku ini berkontribusi membuat data di Pelanggan konsisten dengan data di Publisher. Ini sangat berguna untuk operasi batch, seperti operasi pembersihan besar.

  • Dengan opsi eksekusi prosedur, ada kemungkinan bahwa eksekusi dapat direplikasi ke semua Pelanggan terlepas dari apakah pernyataan individu dalam prosedur tersimpan berhasil. Selain itu, karena perubahan yang dilakukan pada data oleh prosedur tersimpan dapat terjadi dalam beberapa transaksi, data di Pelanggan mungkin tidak konsisten dengan data di Penerbit. Untuk mengatasi masalah ini, pelanggan harus bersifat baca-saja dan Anda menggunakan tingkat isolasi yang lebih besar dari baca yang tidak dikomit. Jika Anda menggunakan baca tidak dikomit, perubahan pada data dalam tabel yang diterbitkan direplikasi sebagai serangkaian pernyataan DML.

Contoh berikut menggambarkan mengapa disarankan agar Anda menyiapkan replikasi prosedur sebagai artikel prosedur yang dapat diserialisasikan.

BEGIN TRANSACTION T1  
SELECT @var = max(col1) FROM tableA  
UPDATE tableA SET col2 = <value>   
   WHERE col1 = @var   
  
BEGIN TRANSACTION T2  
INSERT tableA VALUES <values>  
COMMIT TRANSACTION T2

Dalam contoh sebelumnya, diasumsikan bahwa SELECT dalam transaksi T1 terjadi sebelum INSERT dalam transaksi T2.

Jika prosedur tidak dijalankan dalam transaksi yang dapat diserialisasikan (dengan tingkat isolasi diatur ke SERIALIZABLE), transaksi T2 akan diizinkan untuk menyisipkan baris baru dalam rentang pernyataan SELECT di T1 dan akan diterapkan sebelum T1. Ini juga berarti bahwa itu akan diterapkan di Pelanggan sebelum T1. Ketika T1 diterapkan di Pelanggan, SELECT berpotensi mengembalikan nilai yang berbeda dari di Publisher dan dapat menghasilkan hasil yang berbeda dari UPDATE.

Jika prosedur dijalankan dalam transaksi yang dapat diserialisasikan, transaksi T2 tidak akan diizinkan untuk menyisipkan dalam rentang yang dicakup oleh pernyataan SELECT di T2. Ini akan diblokir sampai T1 menerapkan memastikan hasil yang sama di Pelanggan.

Kunci akan ditahan lebih lama ketika Anda menjalankan prosedur dalam transaksi yang dapat diserialisasikan dan dapat mengakibatkan berkurangnya konkurensi.

Pengaturan XACT_ABORT

Saat mereplikasi eksekusi prosedur tersimpan, pengaturan untuk sesi yang menjalankan prosedur tersimpan harus menentukan XACT_ABORT AKTIF. Jika XACT_ABORT diatur ke NONAKTIF, dan kesalahan terjadi selama eksekusi prosedur di Penerbit, kesalahan yang sama akan terjadi pada Pelanggan, menyebabkan Agen Distribusi gagal. Menentukan XACT_ABORT ON memastikan bahwa kesalahan apa pun yang ditemui selama eksekusi di Publisher menyebabkan seluruh eksekusi digulung balik, menghindari kegagalan Agen Distribusi. Untuk informasi selengkapnya tentang pengaturan XACT_ABORT, lihat MENGATUR XACT_ABORT (Transact-SQL).

Jika Anda memerlukan pengaturan XACT_ABORT NONAKTIF, tentukan parameter -SkipErrors untuk Agen Distribusi. Hal ini memungkinkan agen untuk terus menerapkan perubahan pada Pelanggan meskipun terjadi kesalahan.

Konten terkait