Geekly articles weekly

RabbitMQ - SQL Server

Satu atau dua minggu yang lalu, saya melihat pesan di forum Pengguna RabbitMQ tentang cara mengatur pengiriman pesan dari SQL Server ke RabbitMQ. Karena kami bekerja sama dengan Derivco , saya meninggalkan beberapa saran di sana, dan juga mengatakan bahwa saya menulis blog tentang cara melakukan ini. Sebagian dari pesan saya tidak sepenuhnya benar - setidaknya sampai saat itu (maaf, Bro, dia sangat sibuk).

Hebatnya, ini adalah SQL Server Anda . Menggunakannya sangat mudah untuk memasukkan informasi ke dalam basis data. Mengambil data dari database menggunakan kueri juga mudah. Tetapi mendapatkan data yang baru saja diperbarui atau ditempelkan sudah sedikit lebih sulit. Pikirkan tentang peristiwa waktu nyata; pembelian dilakukan - seseorang harus diberi tahu tentang hal ini segera setelah hal ini terjadi. Mungkin seseorang akan mengatakan bahwa data tersebut tidak boleh muncul dari database, tetapi dari tempat lain. Tentu saja, ini masalahnya, tetapi seringkali kita tidak punya pilihan.

Kami memiliki tugas: mengirim peristiwa dari database di luar untuk diproses lebih lanjut, dan pertanyaannya adalah - bagaimana melakukan ini?

SQL Server dan komunikasi eksternal


Selama keberadaan SQL Server, ada beberapa upaya untuk mengatur komunikasi di luar database; SQL Server Notification Services (NS), yang muncul di SQL Server 2000, dan kemudian, di SQL Server 2005, SQL Server Service Broker (SSB) muncul. Saya menggambarkan mereka dalam buku saya Pandangan Pertama di SQL Server 2005 untuk Pengembang , bersama dengan Bob Boshemen dan Dan Sullivan. NS muncul di SQL Server 2000, seperti yang saya katakan, dan dirancang ulang dalam versi beta SQL Server 2005. Namun, NS sepenuhnya dikecualikan dari versi siap-jual (RTM) SQL Server 2005.
Catatan: Jika Anda membaca buku, Anda akan menemukan ada sejumlah fitur yang tidak ada dalam versi RTM.
SSB selamat, dan Microsoft memperkenalkan Service Broker External Activator (EA) dalam SQL Server 2008 Feature Pack-nya. Itu memungkinkan melalui SSB untuk berinteraksi di luar database lokal. Secara teori, ini terdengar bagus, tetapi dalam praktiknya - ini rumit dan membingungkan. Kami melakukan beberapa tes dan dengan cepat menyadari bahwa itu tidak melakukan apa yang kami butuhkan. Selain itu, SSB tidak memberi kami kinerja yang diperlukan, jadi kami harus menciptakan sesuatu yang lain.

SQLCLR


Apa yang kami dapatkan sebagai hasilnya didasarkan pada teknologi SQLCLR. SQLCLR adalah platform .NET yang dibangun ke dalam inti SQL Server dan dapat digunakan untuk mengeksekusi kode .NET di dalam kernel. Karena kami menjalankan kode .NET, kami dapat melakukan hampir semua hal seperti pada aplikasi .NET biasa.
Catatan: Saya menulis "hampir" di atas, karena sebenarnya ada beberapa batasan. Dalam konteks ini, pembatasan ini hampir tidak berpengaruh pada apa yang akan kita lakukan.
Prinsip operasi SQLCLR adalah sebagai berikut: kode dikompilasi ke perpustakaan dll, dan kemudian perpustakaan ini terdaftar menggunakan alat SQL Server:

Bangun Majelis

CREATE ASSEMBLY [RabbitMQ.SqlServer] AUTHORIZATION rmq FROM 'F:\some_path\RabbitMQSqlClr4.dll' WITH PERMISSION_SET = UNSAFE; GO

Cuplikan Kode 1: Membuat Majelis di sepanjang Jalan Absolut

Kode melakukan tindakan berikut:

  • CREATE ASSEMBLY - Membuat perakitan dengan nama yang diberikan (tidak peduli apa yang seharusnya).
  • AUTHORIZATION - Menunjukkan pemilik rakitan. Dalam hal ini, rmq adalah peran SQL Server yang telah ditentukan.
  • FROM - Menentukan lokasi perakitan asli. Di FROM , Anda juga bisa menentukan lintasan dalam format biner atau UNC. File instalasi untuk proyek ini menggunakan representasi biner.
  • WITH PERMISSION_SET - Menetapkan izin. UNSAFE adalah yang paling tidak ketat dan diperlukan dalam kasus ini.

Catatan: terlepas dari peran atau login yang digunakan dalam klausa AUTHORIZATION , kelas appdomain harus dibuat dengan nama yang sama seperti ketika memuat perakitan ke dalam domain. Disarankan untuk memisahkan rakitan dengan berbagai nama kelas appdomain sehingga ketika satu rakitan gagal, sisanya tidak jatuh. Namun, jika majelis bergantung satu sama lain, mereka tidak dapat dibagi ke dalam kelas yang berbeda.
Ketika perakitan dibuat, kami membuat pembungkus metode .NET di dalamnya:

 CREATE PROCEDURE rmq.pr_clr_PostRabbitMsg @EndpointID int, @Message nvarchar(max) AS EXTERNAL NAME [RabbitMQ.SqlServer].[RabbitMQSqlClr.RabbitMQSqlServer].[pr_clr_PostRabbitMsg]; GO

Kode 2: Pembungkus Metode .NET

Kode melakukan tindakan berikut:

  • Membuat prosedur tersimpan T-SQL bernama rmq.pr_clr_PostRabbitMsg yang mengambil dua parameter; @EndpointID dan @Message .
  • Alih-alih badan prosedur, sumber eksternal digunakan, yang terdiri dari:
    • Majelis yang bernama RabbitMQ.SqlServer , mis. Agregat yang kami buat di atas dalam cuplikan kode 1 .
    • Tipe lengkap (namespace dan kelas): RabbitMQSqlClr.RabbitMQSqlServer
    • Metode dari namespace dan kelas di atas adalah: pr_clr_PostRabbitMsg .

Ketika rmq.pr_clr_PostRabbitMsg , metode pr_clr_PostRabbitMsg akan dipanggil.
Catatan: saat membuat prosedur, nama rakitan tidak peka huruf besar-kecil, tidak seperti nama lengkap jenis dan metode. Tidak perlu bahwa nama prosedur yang dibuat cocok dengan nama metode. Namun, tipe data akhir untuk parameter harus cocok.
Seperti yang saya katakan sebelumnya, kami di Derivco perlu mengirim data di luar SQL Server, jadi kami menggunakan SQLCLR dan RabbitMQ (RMQ).

Rabbitmq


RMQ adalah broker pesan sumber terbuka yang mengimplementasikan Advanced Message Queuing Protocol (AMQP) dan ditulis dalam bahasa Erlang.

Karena RMQ adalah pialang pesan, pustaka klien AMQP diharuskan untuk terhubung dengannya. Aplikasi merujuk ke pustaka klien dan, dengan bantuan mereka, membuka koneksi dan mengirim pesan - seperti, misalnya, ada panggilan melalui ADO.NET ke SQL Server. Tetapi tidak seperti ADO.NET, di mana, kemungkinan besar, koneksi terbuka setiap kali Anda mengakses database, di sini koneksi tetap terbuka untuk seluruh periode aplikasi.

Dengan demikian, untuk dapat berinteraksi dari database dengan RabbitMQ, kita membutuhkan aplikasi dan pustaka klien .NET untuk RabbitMQ.
Catatan: di bagian selanjutnya artikel ini, fragmen kode RabbitMQ akan ditemukan, tetapi tanpa penjelasan rinci tentang apa yang mereka lakukan. Jika Anda baru bekerja dengan RabbitMQ, maka saya sarankan untuk melihat berbagai tutorial RabbitMQ untuk memahami tujuan kode. Tutorial Hello World C # adalah awal yang baik. Salah satu perbedaan antara buku teks dan contoh kode adalah bahwa penukar tidak dinyatakan dalam contoh. Mereka seharusnya sudah ditentukan sebelumnya.

RabbitMQ.SqlServer


RabbitMQ.SqlServer adalah perakitan yang menggunakan perpustakaan .NET klien untuk RabbitMQ dan menyediakan kemampuan untuk mengirim pesan dari database ke satu atau lebih titik akhir RabbitMQ (VHosts dan penukar). Kode dapat diunduh / bercabang dari repositori saya RabbitMQ-SqlServer di GitHub. Ini berisi sumber rakitan dan file instalasi (mis. Anda tidak harus mengompilasinya sendiri).
Catatan: ini hanyalah contoh untuk menunjukkan bagaimana SQL Server dapat berinteraksi dengan RabbitMQ. Ini BUKAN produk jadi atau bahkan bagian dari itu. Jika kode ini menghancurkan otak Anda - jangan salahkan saya, karena ini hanyalah sebuah contoh.

Fungsionalitas


Ketika rakitan dimuat, atau ketika inisialisasi secara eksplisit dipanggil, atau ketika itu disebut secara tidak langsung, pada saat prosedur pembungkus dipanggil, rakitan memuat string koneksi ke dalam database lokal di mana ia dipasang, serta titik akhir RabbitMQ yang terhubung:

Koneksi

 internal bool InternalConnect() { try { connFactory = new ConnectionFactory(); connFactory.Uri = connString; connFactory.AutomaticRecoveryEnabled = true; connFactory.TopologyRecoveryEnabled = true; RabbitConn = connFactory.CreateConnection(); for (int x = 0; x < channels; x++) { var ch = RabbitConn.CreateModel(); rabbitChannels.Push(ch); } return true; } catch(Exception ex) { return false; } }

Cuplikan Kode 3: Sambungkan ke Titik Akhir

Pada saat yang sama, bagian dari koneksi ke titik akhir juga membuat IModels pada koneksi, dan mereka digunakan saat mengirim (menambahkan ke antrian) pesan:

Pengiriman pesan

 internal bool Post(string exchange, byte[] msg, string topic) { IModel value = null; int channelTryCount = 0; try { while ((!rabbitChannels.TryPop(out value)) && channelTryCount < 100) { channelTryCount += 1; Thread.Sleep(50); } if (channelTryCount == 100) { var errMsg = $"Channel pool blocked when trying to post message to Exchange: {exchange}."; throw new ApplicationException(errMsg); } value.BasicPublish(exchange, topic, false, null, msg); rabbitChannels.Push(value); return true; } catch (Exception ex) { if (value != null) { _rabbitChannels.Push(value); } throw; } }

Metode Post dipanggil dari metode pr_clr_PostRabbitMsg(int endPointId, string msgToPost) , yang disajikan sebagai prosedur menggunakan klausa CREATE PROCEDURE dalam fragmen kode 2:

Metode pemanggilan pos

 public static void pr_clr_PostRabbitMsg(int endPointId, string msgToPost) { try { if(endPointId == 0) { throw new ApplicationException("EndpointId cannot be 0"); } if (!isInitialised) { pr_clr_InitialiseRabbitMq(); } var msg = Encoding.UTF8.GetBytes(msgToPost); if (endPointId == -1) { foreach (var rep in remoteEndpoints) { var exch = rep.Value.Exchange; var topic = rep.Value.RoutingKey; foreach (var pub in rabbitPublishers.Values) { pub.Post(exch, msg, topic); } } } else { RabbitPublisher pub; if (rabbitPublishers.TryGetValue(endPointId, out pub)) { pub.Post(remoteEndpoints[endPointId].Exchange, msg, remoteEndpoints[endPointId].RoutingKey); } else { throw new ApplicationException($"EndpointId: {endPointId}, does not exist"); } } } catch { throw; } }

Cuplikan Kode 5: Mewakili Metode sebagai Prosedur

Ketika metode dieksekusi, diasumsikan bahwa penelepon mengirimkan pengidentifikasi titik akhir di mana pesan harus dikirim, dan, pada kenyataannya, pesan itu sendiri. Jika nilai -1 dilewatkan sebagai pengidentifikasi titik akhir, maka kami mengulangi semua titik dan mengirim pesan ke masing-masing. Pesan itu datang dalam bentuk string dari mana kita mendapatkan byte menggunakan Encoding.UTF8.GetBytes . Dalam lingkungan produksi, panggilan Encoding.UTF8.GetBytes harus diganti dengan serialisasi.

Instalasi


Untuk menginstal dan menjalankan contoh, Anda memerlukan semua file di folder src\SQL . Untuk menginstal, ikuti langkah-langkah ini:

  • Jalankan skrip 01.create_database_and_role.sql . Dia akan menciptakan:
    • RabbitMQTest data uji RabbitMQTest tempat perakitan akan dibuat.
    • peran rmq untuk ditugaskan sebagai pemilik majelis
    • skema, yang juga akan disebut rmq . Dalam diagram ini, berbagai objek basis data dibuat.

  • Jalankan file 02.create_database_objects.sql . Dia akan menciptakan:

    • tabel rmq.tb_RabbitSetting , yang akan menyimpan string koneksi ke database lokal.
    • Tabel rmq.tb_RabbitEndpoint , di mana satu atau lebih titik akhir RabbitMQ akan disimpan.

  • Dalam file 03.create_localhost_connstring.sql ubah nilai variabel @connString ke string koneksi yang benar untuk database RabbitMQTest dibuat pada langkah 1 dan jalankan skrip.

Sebelum melanjutkan, Anda harus memiliki instance berjalan broker RabbitMQ dan VHost (secara default, VHost direpresentasikan sebagai /). Sebagai aturan, kami memiliki beberapa VHost, hanya untuk isolasi. Tuan rumah ini juga membutuhkan penukar, dalam contoh kita menggunakan amq.topic . Ketika broker RabbitMQ Anda siap, edit rmq.pr_UpsertRabbitEndpoint prosedur rmq.pr_UpsertRabbitEndpoint , yang terletak di file 04.upsert_rabbit_endpoint.sql :

Endpoint RabbitMQ

 EXEC rmq.pr_UpsertRabbitEndpoint @Alias = 'rabbitEp1', @ServerName = 'RabbitServer', @Port = 5672, @VHost = 'testHost', @LoginName = 'rabbitAdmin', @LoginPassword = 'some_secret_password', @Exchange = 'amq.topic', @RoutingKey = '#', @ConnectionChannels = 5, @IsEnabled = 1

Kode 6: Menciptakan Endpoint di RabbitMQ

Pada titik ini, sekarang saatnya untuk menggelar majelis. Ada perbedaan dalam opsi penyebaran untuk versi SQL Server sebelum SQL Server 2014 (2005, 2008, 2008R2, 2012), dan untuk 2014 dan yang lebih baru. Perbedaannya terletak pada versi CLR yang didukung. Sebelum SQL Server 2014, platform .NET berjalan di CLR versi 2, dan di SQL Server 2014 dan di atasnya, versi 4 digunakan.

SQL Server 2005 - 2012


Mari kita mulai dengan versi SQL Server yang berjalan pada CLR 2, karena mereka memiliki karakteristik sendiri. Kita perlu menggunakan rakitan yang dibuat, dan pada saat yang sama menyebarkan pustaka RabbitMQ klien .NET ( RabbitMQ.Client ). Dari pertemuan kami, kami akan merujuk ke perpustakaan klien RabbitMQ. Karena Karena kami berencana untuk menggunakan CLR 2, perakitan kami dan RabbitMQ.Client harus dikompilasi berdasarkan .NET 3.5. Ada masalah.

Semua versi terbaru dari perpustakaan RabbitMQ.Client dikompilasi untuk lingkungan CLR 4, sehingga mereka tidak dapat digunakan dalam perakitan kami. Versi terbaru dari perpustakaan klien untuk CLR 2 dikompilasi di .NET 3.4.3. Tetapi bahkan jika kami mencoba menggunakan majelis ini, kami mendapatkan pesan kesalahan:


Gambar 1: Sistem Hilang. Perakitan Model Layanan

Versi RabbitMQ.Client merujuk ke perakitan yang bukan bagian dari SQL Server CLR. Ini adalah perakitan WCF, dan ini adalah salah satu keterbatasan dalam SQLCLR yang saya sebutkan di atas: perakitan khusus ini untuk jenis tugas yang tidak diperbolehkan dilakukan dalam SQL Server. Versi terbaru dari RabbitMQ.Client tidak memiliki dependensi ini, sehingga mereka dapat digunakan tanpa masalah, kecuali untuk persyaratan mengganggu dari CLR 4. Apa yang harus saya lakukan?

Seperti yang Anda ketahui, RabbitMQ adalah open source, tetapi kami adalah pengembang, bukan? ;) Jadi mari kita kompilasi ulang! Dalam versi sebelum rilis terbaru (mis. Versi <3.5.0) dari RabbitMQ.Client saya menghapus tautan ke System.ServiceModel dan dikompilasi ulang. Saya harus mengubah beberapa baris kode menggunakan fungsionalitas System.ServiceModel , tetapi ini adalah perubahan kecil.

Dalam contoh ini, saya tidak menggunakan versi klien 3.4.3, tapi saya mengambil rilis stabil 3.6.6 dan dikompilasi ulang menggunakan .NET 3.5 (CLR 2). Itu hampir berhasil :), kecuali bahwa rilis kemudian dari RabbitMQ.Client menggunakan Task 'dan yang awalnya bukan bagian dari. NET 3.5.

Untungnya, ada versi System.Threading.dll untuk .NET 3.5 yang mencakup Task . Saya mengunduhnya, mengatur tautan dan semuanya berjalan! Di sini trik utamanya adalah System.Threading.dll harus diinstal dengan perakitan.
Catatan: sumber RabbitMQ.Client , dari mana saya menyusun versi .NET 3.5, ada di repositori saya di GitHub RabbitMQ Client 3.6.6 .NET 3.5 . Biner dll bersama dengan System.Threading.dll untuk. NET 3.5 juga terletak di lib\NET3.5 repositori (RabbitMQ-SqlServer) .
Untuk menginstal rakitan yang diperlukan ( System.Threading , RabbitMQ.Client dan RabbitMQ.SqlServer ) jalankan skrip instalasi dari direktori src\sql dengan urutan sebagai berikut:

  1. 05.51.System.Threading.sql2k5-12.sql - System.Threading
  2. 05.52.RabbitMQ.Client.sql2k5-12.sql - RabbitMQ.Client
  3. 05.53.RabbitMQ.SqlServer.sql2k5-12.sql - RabbitMQ.SqlServer

SQL Server 2014+


Di SQL Server 2014 dan yang lebih baru, kumpulan mengkompilasi di bawah. NET 4.XX (contoh saya ada di 4.5.2), dan Anda dapat mereferensikan salah satu versi terbaru dari RabbitMQ.Client , yang dapat diperoleh dengan menggunakan NuGet . Dalam contoh saya, saya menggunakan 4.1.1. RabbitMQ.Client , yang juga ada di lib\NET4 repositori (RabbitMQ-SqlServer) .

Untuk menginstal, jalankan skrip dari direktori src\sql dengan urutan sebagai berikut:

  1. 05.141.RabbitMQ.Client.sql2k14+.sql .sql - RabbitMQ.Client
  2. 05.142.RabbitMQ.SqlServer.sql2k14+.sql .sql - RabbitMQ.SqlServer

Pembungkus Metode SQL


Untuk membuat prosedur yang akan digunakan dari perakitan kami (3,5 atau 4), jalankan skrip 06.create_sqlclr_procedures.sql . Dia akan membuat prosedur T-SQL untuk tiga metode .NET:

  • rmq.pr_clr_InitialiseRabbitMq memanggil pr_clr_InitialiseRabbitMq . Digunakan untuk memuat dan menginisialisasi perakitan RabbitMQ.SqlServer.
  • rmq.pr_clr_ReloadRabbitEndpoints memanggil pr_clr_ReloadRabbitEndpoints . Memuat berbagai titik akhir RabbitMQ.
  • rmq.pr_clr_PostRabbitMsg memanggil pr_clr_PostRabbitMsg . Digunakan untuk mengirim pesan ke RabbitMQ.

Script juga membuat prosedur T-SQL sederhana - rmq.pr_PostRabbitMsg , yang berlaku untuk rmq.pr_clr_PostRabbitMsg . Ini adalah prosedur pembungkus yang tahu apa yang harus dilakukan dengan data, menangani pengecualian, dll. Dalam lingkungan produksi, kami memiliki beberapa prosedur serupa yang memproses berbagai jenis pesan. Baca lebih lanjut tentang ini di bawah ini.

Gunakan


Dari semua hal di atas, jelas bahwa untuk mengirim pesan ke RabbitMQ kita sebut rmq.pr_PostRabbitMsg atau rmq.pr_clr_PostRabbitMsg , dengan mengirimkan parameter pengidentifikasi titik akhir dan pesan itu sendiri sebagai string. Semua ini, tentu saja, keren, tetapi saya ingin melihat bagaimana ini akan bekerja dalam kenyataan.

Apa yang kami lakukan di lingkungan produksi adalah bahwa dalam prosedur tersimpan yang memproses data yang harus dikirim ke RabbitMQ, kami mengumpulkan data yang akan dikirim dan di blok koneksi kami memanggil prosedur seperti rmq.pr_PostRabbitMsg . Berikut ini adalah contoh prosedur yang sangat disederhanakan:

Prosedur pemrosesan data

 ALTER PROCEDURE dbo.pr_SomeProcessingStuff @id int AS BEGIN SET NOCOUNT ON; BEGIN TRY --     DECLARE @endPointId int; --    DECLARE @msg nvarchar(max) = '{' --        SET @msg = @msg + '"Id":' + CAST(@id AS varchar(10)) + ',' --  -  SET @msg = @msg + '"FName":"Hello",'; SET @msg = @msg + '"LName":"World"'; SET @msg = @msg + '}'; -- -  --     -,  -  SELECT @endPointId = 1; --    --     EXEC rmq.pr_PostRabbitMsg @Message = @msg, @EndpointID = @endPointId; END TRY BEGIN CATCH DECLARE @errMsg nvarchar(max); DECLARE @errLine int; SELECT @errMsg = ERROR_MESSAGE(), @errLine = ERROR_LINE(); RAISERROR('Error: %s at line: %d', 16, -1, @errMsg, @errLine); END CATCH END

Dalam fragmen kode 7, kita melihat bagaimana data yang diperlukan ditangkap dan diproses dalam prosedur dan dikirim setelah pemrosesan. Untuk menggunakan prosedur ini, jalankan skrip 07.create_processing_procedure.sql dari direktori src\SQL .

Mari kita jalankan semuanya


Pada titik ini, Anda harus siap untuk mengirim beberapa pesan. Sebelum pengujian, pastikan Anda memiliki antrian di RabbitMQ yang terpasang pada penukar titik akhir di rmq.tb_RabbitEndpoint .

Jadi, untuk memulai Anda perlu melakukan hal berikut:
Buka file 99.test_send_message.sql .
Lari

 EXEC rmq.pr_clr_InitialiseRabbitMq;

untuk menginisialisasi perakitan dan memuat titik akhir RabbitMQ. Ini bukan langkah yang diperlukan, tetapi Anda disarankan untuk melakukan preload perakitan setelah membuat atau memodifikasinya.

Lari

 EXEC dbo.pr_SomeProcessingStuff @id = 101

(Anda dapat menggunakan pengenal lain yang Anda suka).

Jika semuanya berfungsi tanpa kesalahan, maka sebuah pesan akan muncul di antrian RabbitMQ! Jadi, Anda menggunakan SQLCLR untuk mengirim pesan ke RabbitMQ.

Selamat!

Source: https://habr.com/ru/post/id419457/

More articles:


All Articles