درک اهمیت استفاده از SQL Server Service Broker با یک مثال

درک اهمیت استفاده از SQL Server Service Broker با یک مثال

نوشته شده توسط: میلاد فیروزی
تاریخ انتشار: ۱۴ دی ۱۳۹۴
آخرین بروزرسانی: ۲۶ آبان ۱۴۰۲
زمان مطالعه: 14 دقیقه
۳.۹
(۷)

مقدمه

  • آیا تا به حال به مشکل Transactionهای خیلی بزرگ برخورده اید؟ 
  • آیا تا به حال INSERT و یا UPDATE شما معطل Trigerهای وحشتناک شده است؟
خوب خیلی اوقات بعد از Insert کردن یک سطر در یک جدول نیاز داریم تا یک سری اتفاق ها بیوفتد ، مثلا در جدول فروش یک رکورد زده ایم ، حالا می خواهیم به خریدار اس ام اس یا ایمیل بزنیم که سفارشش ثبت شده است و یا اینکه در یک جدول دیگر نیاز داریم موجودی یک کالا را یک عدد کمتر کنیم و خیلی کارهای دیگر ، حال همیشه همه ی این اتفاق ها در یک Transaction بزرگ اتفاق می افتد که این مساله قطعا دیتابیس را در تعداد درخواست های بالا درگیر خواهد کرد و با مشکلات عدیده Locking و Blocking  برخورد خواهید کرد.
حال جدا از هر راه حلی که شما به عنوان یک توسعه دهنده وب و یا یک برنامه نویس ممکن است به ذهن شما خطور کند ، SQL Server هم برای این موضوع راه حلی ارائه داده که بررسی کردنش نه تنها خالی از لطف نیست بلکه ممکن است شما را مجاب نماید تا از این قابلیت SQL Server  استفاده نمایید.
 
SQL Server Service Broker سرویسی می باشد که SQL به شما ارائه می دهد تا با تعریفی از پیغام ها و صف ها شما بتوانید صفی از کارها تعریف نمایید تا اگر نیاز ندارید یک سری تغییرات آنی و در لحظه انجام پذیرند ، آن کارها را به این سرویس بسپارید تا برای شما به ترتیب کارها را انجام دهد.
این سرویس شاید به صورت آنی (Syncronous) نباشند ولی قطعا خیلی سریع همه ی کارها را انجام خواهد داد ، این نوع سرویس ها به اصطلاح (Asyncronous) نامیده می شوند.

برخی از مزیت های این سرویس عبارتند از

دلیور شدن پیغام و یا به عبارتی انجام شدن کار به صورت تضمین شده ، حتی اگر هدف شما آفلاین باشد سرویس پیغام را تا آنلاین شدن نگهداری می کند.
  • ارتباط Asyncronous
  • قابلیت پشتیبانی از APIهای عمومی
  • Transactionها
  • گزینه های Backup

این سرویس چگونه کار می کند؟

همان طور که شاید حدس زده باشید این سرویس از طریق تعریف یک صف از پیغام ها کار می کند به گونه ای که شما به سادگی یک صف مقصد تعیین کرده و پیغام ها را به آن می فرستید ، این صف مقصد می تواند Local و یا Remote باشد ، حتی اگر این صف خارج از دسترس هم باشد پیغام های شما به یک حالت Pending خواهند ماند تا صف مقصد در دسترس قرار بگیرد و پیغام فرستاده شود. در صف مقصد نیز سرویسی وجود دارد که کارهای تعریف شده را انجام خواهد داد و پیغامی مبنی بر انجام شدن کار به صف مبدا اعلام خواهد کرد.

چرا باید از Service Broker استفاده کنیم؟

به شما کمک خواهد کرد تا وابستگی (Coupling) بین اپلیکیشن های مختلف سیستم شما کمتر شود.
به شما این امکان را می دهد تا خارج از معماری سیستم شما یک سری کارهای پیچیده روی سرورهای دیگری صورت پذیرد.
به شما کمک می کند تا با کمترین تاثیر روی کاربران نرم افزار شما قسمت های مختلف سیستم را به صورت آفلاین Upgrade کنید.
شما می توانید زمان انجام شدن کارها را خودتان تعیین کنید ، به عنوان مثال یک سری پردازش های سنگین را به نیمه شب موکول کنید که بار کمتری روی سرورهای شما قرار دارد.

حال زمان یک مثال فرا رسیده است.

فرض کنید می خواهیم یک وبسایت رزرو بلیط راه بیاندازیم که ۱۰۰۰ کاربر هم زمان دارد و نیاز داریم تا سرعت وبسایت را در بالاترین سطح ممکن نگه داریم. ۲ قسمت از کار هستند که نیاز به آن نیست که به صورت آنی انجام پذیرند و می توانند به زمانی که بار کمتری روی سرور قرار دارد موکول شوند ، چردازش پرداخت ها و پرینت بلیط ها.
به عکس زیر دقت کنید

کاربر دکمه “رزرو بلیط” را می زند.

  1. وب سرور یک رکورد رزرو در دیتابیس INSERT می کند.
  2. وب سرور یک پیغام به پردازشگر پرداخت مقصد ارسال می کند.

سرویس پردازش پرداخت پیغام را گرفته و پردازش می کند.

            ۱ . اگر پرداخت موفقیت آمیز باشد یک پیغام موفقیت به پردازشگر پرداخت مبدا ارسال می کند.
            ۲ . اگر موفقیت آمیز نبود یک پیغام عدم موفقیت ارسال می کند.

SQL Server صف مبدا را برای دریافت پاسخ پرداخت Monitor می کند.

           ۱ . اگر پرداختی موفقیت آمیز نبود در رکورد رزرو پرداخت را Failed ذخیره می کند.
           ۲ . اگر پرداخت موفقیت آمیز بود در رکورد رزرو پرداخت را Success ذخیره می کند و یک پیغام به صف مقصد پرینت می فرستد.

سرویس پرینت بلیط پیغام را از صف پرینت مبدا برداشته پرینت را انجام می دهد.

          ۱ . اگر پرینت موفقیت آمیز بود یک پیغام Success به صف پرینت مبدا می فرستد.
          ۲ . اگر موفقیت آمیز نبود پیغام Failed می فرستد.

SQL Server از صف مبدا پرینت پیغام Success و یا Failed را برمی دارد و بلیط را آپدیت می کند.

با این روش می توانیم پروسه پردازش پرداخت و پرینت را حتی به سرورهای دیگری موکول کنیم و یا حتی تایم اجرا شدن آن ها را تا زمانی که سر سرور خلوت می باشد به تعویق بیاندازیم. حال اگر می خواستید این عملیات را به صورت آنی (Synchronously) انجام دهید قطعا با دردسر های خیلی بزرگتر مواجه می شدید.
اولین کاری که می بایست برای پیاده سازی این سیستم انجام دهیم این است که باید ۴ نوع پیغام (Message) تعریف کنیم ، درخواست و پاسخ (Request/Response) پرینت و Request/Response پردازش پرداخت.به کد زیر توجه کنید ،
CREATE MESSAGE TYPE [PrintRequest] VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [PrintResponse] VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [ProcessPaymentRequest] VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [ProcessPaymentResponse] VALIDATION = WELL_FORMED_XML;
تنها محدودیت برای این پیغام ها این می باشد که می بایستی Well Formed XML باشند که ایرادی ندارد چون تنها نرم افزار ما می باشد که پیغام ها را ارسال می کند.
در مرحله ی بعدی می بایست صف (Queue) هایی را تعریف کنیم تا پیغام ها از یا به آن ها ارسال شوند. در این مثال به ازای هر Message یک Queue داریم ،
CREATE QUEUE PrintInitiatorQueue WITH ACTIVATION
(
STATUS = ON,
PROCEDURE_NAME = [BrokerPrintMessageProcessed],
MAX_QUEUE_READERS = 4,
EXECUTE AS SELF
)
CREATE QUEUE PrintTargetQueue WITH STATUS = ON
CREATE QUEUE ProcessPaymentInitiatorQueue WITH ACTIVATION
(
STATUS = ON,
PROCEDURE_NAME = [BrokerPaymentMessageProcessed],
MAX_QUEUE_READERS = 4,
EXECUTE AS SELF
)
CREATE QUEUE ProcessPaymentTargetQueue WITH STATUS = ON
همانطور که در تصویر اول بحث دیدید Queueهای مقصد (Target) توسط سرویس های جداگانه ای پردازش می شوند ولی Queueها مبدا (Initiator) توسط SQL Server پردازش می شوند.همان طور که در کد بالا مشاهده می کنید برای Initiator Queueها یک Activation تعریف شده است ، Activation راهیست برای این که به Service Broker بگوییم زمانی که یک Message به Queue اضافه می شوذ ، یک Procedure و یا یک CLR Method را اجرا کند.
 
خوب بیایید یک بار دیگر بررسی کنیم ، Target Queueها جایی هستند که Messageها برای اولین بار فرستاده می شوند و Initiator Queueها جایی هستند که Responseها فرستاده می شوند ، از این نقطه یک مکالمه (Conversation) آغاز می شود و تا زمانی ادامه پیدا می کند که هر دو طرف Conversation را پایان دهند.
حال می بایستی چند قرارداد (Contract) برای تعریف نوع Messageهایی که مجاز هستند در Conversation شرکت کنند ، مشخص کنیم ،
CREATE CONTRACT [ProcessPaymentContract]
(
[ProcessPaymentResponse] SENT BY TARGET,
[ProcessPaymentRequest] SENT BY INITIATOR
)
CREATE CONTRACT [PrintContract]
(
[PrintRequest] SENT BY INITIATOR,
[PrintResponse] SENT BY TARGET
)
آخرین مرحله تولید خود سرویس ها می باشد ،
CREATE SERVICE PrintInitiatorService ON QUEUE PrintInitiatorQueue(PrintContract)
CREATE SERVICE PrintTargetService ON QUEUE PrintTargetQueue(PrintContract)
CREATE SERVICE ProcessPaymentInitiatorService ON QUEUE ProcessPaymentInitiatorQueue(ProcessPaymentContract)
CREATE SERVICE ProcessPaymentTargetService ON QUEUE ProcessPaymentTargetQueue(ProcessPaymentContract) 
حال همه چیز آماده است تا بتوانیم Messageها را فرستاده و دریافت کنیم.
در مثال ما رزرو بلیط توسط یک Stored Procedure به نام CreateBooking صورت می پذیرد ، در این Stored Procedure پس از INSERT شدن رکورد یک Message به سرویس ProcessPaymentTarget فرستاده می شود تا پردازش شود ، به کد زیر دقت کنید ،
CREATE PROCEDURE [dbo].[CreateBooking]
(
@EventId INT,
@CreditCard VARCHAR(20)
)
AS
BEGIN TRANSACTION
BEGIN TRY
INSERT INTO Bookings(EventId,CreditCard)
VALUES(@EventId,@CreditCard)
DECLARE @BookingId INT = SCOPE_IDENTITY()
--Send messagge for payment process
DECLARE @ConversationHandle UNIQUEIDENTIFIER
BEGIN DIALOG CONVERSATION @ConversationHandle
FROM SERVICE [ProcessPaymentInitiatorService]
TO SERVICE 'ProcessPaymentTargetService'
ON CONTRACT [ProcessPaymentContract]
WITH ENCRYPTION = OFF
DECLARE @Msg NVARCHAR(MAX)
SET @Msg = '' + CAST(@BookingId AS NVARCHAR(10)) + '' + @CreditCard + '';
SEND ON CONVERSATION @ConversationHandle MESSAGE TYPE [ProcessPaymentRequest](@Msg)
COMMIT
END TRY
BEGIN CATCH
RAISERROR('Booking Failed',1,1)
ROLLBACK
RETURN
END CATCH
همان طور که دیدید تمامی عملیات مشخص کردن منبع و مقصد سرویس ها و قراردادی که با آن می خواهید پیغام بفرتید در این Stored Procedure تعریف شدند .
بعد از سرویس PaymentProcess قرار بود پیغامی به سرویس Print فرستاده شود تا آن سرویس کار را به اتمام برساند ، قرار بود کارهای زیر به ترتیب انجام شوند ،
Message از ProcessPaymentInitiator Queue بیرون کشیده شود.
Message پردازش شده و رکورد رزرو برای پرداخت موفق یا ناموفق Update شود.
End Conversation Message به Payment Conversation فرستاده شود تا این مکالمه پایان یابد.
حال اگر پرداخت موفقیت آمیز بود یک Message به PrintTarget Queue فرستاده شود تا دستور پرینت بلیط صادر شده باشد.
نکته ای که قابل توجه می باشد این است که یک Conversation تنها زمانی خاتمه می باید که هر دو طرف End Conversation Message را ارسال کنند وگرنه Conversation تا ابد باز باقی خواهد ماند.
 
CREATE PROCEDURE [dbo].[BrokerPaymentMessageProcessed]
AS
DECLARE @ConversationHandle UNIQUEIDENTIFIER
DECLARE @MessageType NVARCHAR(256)
DECLARE @MessageBody XML
DECLARE @ResponseMessage XML
WHILE(1=1)
BEGIN
BEGIN TRY
BEGIN TRANSACTION
WAITFOR(RECEIVE TOP(1)
@ConversationHandle = conversation_handle,
@MessageType = message_type_name,
@MessageBody = CAST(message_body AS XML)
FROM
ProcessPaymentInitiatorQueue
), TIMEOUT 1000
IF(@@ROWCOUNT=0)
BEGIN
ROLLBACK TRANSACTION
RETURN
END
SELECT @MessageType
IF @MessageType = 'ProcessPaymentResponse'
BEGIN
--Parse the Message and update tables based on contents
DECLARE @PaymentStatus INT = @MessageBody.value('/Payment[1]/PaymentStatus[1]','INT')
DECLARE @BookingId INT = @MessageBody.value('/Payment[1]/BookingId[1]','INT')
UPDATE Bookings SET Bookings.PaymentStatus = @PaymentStatus WHERE Id = @BookingId
--Close the conversation on the Payment Service
END CONVERSATION @ConversationHandle
--Start a new conversation on the print service
BEGIN DIALOG CONVERSATION @ConversationHandle
FROM SERVICE [PrintInitiatorService]
TO SERVICE 'PrintTargetService'
ON CONTRACT [PrintContract]
WITH ENCRYPTION = OFF
DECLARE @Msg NVARCHAR(MAX) = '' + CAST(@BookingId AS NVARCHAR(10)) + '';
SELECT @ConversationHandle;
SEND ON CONVERSATION @ConversationHandle MESSAGE TYPE [PrintRequest](@Msg)
END
COMMIT
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE()
ROLLBACK TRANSACTION
END CATCH
END

چند نکته . . .

اگر به به TSQLی که برای ایجاد Queueها استفاده کردیم دقت کنید می بینید که Procedure بالا به صورت اتوماتیک زمانی که یک Message به PrinitInitiatorQueue فرستاده می شود فراخوانده می شود.
WHILE(1=1) : این Procedure در یک حلقه ی بی نهایت تا زمانی که صف پیغام ها تماما پردازش شود کار می کند تا مجبور نباشیم هر سری آن را از Activation فراخوانی کنیم.
این حلقه در این حالت یک ثانیه صبر می کند و اگر پیغامی دریافت نشد @@RowCount صفر خواهد شد و از آن جایی که این بدان معناست که پیغامی در صف وجود ندارد از Procedue خارج می شویم.
باقی ماجرا تقریبا واضح می باشد ، پیغام دریافت شده برای آپدیت کردن رکورد رزرو پردازش می شود ، پیغام پایان مکالمه فرستاده می شود ، مکالمه جدید برای پرینت باز خواهد شد ، پرینت صورت می پذیرد و مکالمه پایان می پذیرد.

چه رتبه ای می‌دهید؟

میانگین ۳.۹ / ۵. از مجموع ۷

اولین نفر باش

title sign
معرفی نویسنده
میلاد فیروزی
مقالات
8 مقاله توسط این نویسنده
محصولات
0 دوره توسط این نویسنده
میلاد فیروزی
پروفایل نویسنده
title sign
معرفی محصول
title sign
دیدگاه کاربران

    • مطلب بسیار کاربردی و جالبی بود

    • سلام
      من میخواستم یه برنامه بنویسم که بتونه هر تغییری که توی جدولی خاص اتفاق افتاد رو مطلع بشم و به کلاینتم پیغام ارسال کنه!
      مثلا : جدول نمرات دانشگاه و هر دانشجو به محض ورود نمره اش توی برنامه اش این رو ببینه!
      از همین روش باید استفاده کنم یا راه بهتری هست؟
      ممنون میشم اگر مثالی دارید بهم بدید.

    • سلام
      من میخواستم یه برنامه بنویسم که بتونه هر تغییری که توی جدولی خاص اتفاق افتاد رو مطلع بشم و به کلاینتم پیغام ارسال کنه!
      مثلا : جدول نمرات دانشگاه و هر دانشجو به محض ورود نمره اش توی برنامه اش این رو ببینه!
      از همین روش باید استفاده کنم یا راه بهتری هست؟
      ممنون میشم اگر مثالی دارید بهم بدید.

    • سلام وقت بخیر.من یک مشکلی در سیستمم دارم و این است که زمانی که یک ردیف ایجاد می شود مثلا در ساعت ۱۰ باید نیم ساعت بعد آن ردیف پاک شود برای اینکار یک job نوشتم که هر یک دقیقه یکبار این کار را انجام دهد ولی به شدت سیستم کند شد و مجبور شدم این job را نیم ساعت یکبار کنم.ولی یک مشکل بزرگ دارد و اینکه اگر ردیفی ساعت ۱۰:۰۱ ایجاد شود یجای اینکه در ساعت ۱۰:۳۱ پاک شود ساعت ۱۱ پاک میشود.آیا با این روش می توان آن را مدیریت کرد.لطفا اگرمی شود یک توضیح مختصری بدهید.ممنونم

    •    سلام،
      من یک جدول دارم که هر تغییری که در دیتابیسهای بانک ایجاد بشه توسط تریگر، id و اسم table به این جدول اضافه میشود که بعدا بوسیله یک وب سرویس به کلاینها ارسال شود.
      آیا از این سرویس برای اینکار میتونم استفاده کنم یا راه حل بهتری برای اطلاع از تغییرات در جداول مورد نظرم وجود داره؟

      ممنون

      •     اگر ملزم به آنلاین بودن  (Sync) نباشید این روش بهترین روش است

    •     با سلام  بسیار عالی بود ممنون ار مقاله های بسیار خوبی که ارائه میکنین

    •  سلام

      بسیار عالی بود وتشکر از شما بابت این مقاله کاربردی و خوبی که ارائه دادید. مثالی که ارائه دادید بسیار خوب اینمورد را شفاف سازی کرده و درحقیقت چند روزی هستش که تصمیم دارم از طریق Service Broker امکان ارسال پیامک داشته باشم. 
      به عنوان مثال فرض کنید مقداری محصول توسط کامیونهایی از کارخانه ای خارج میشود که قبل از خروج، کامیون ها باید روی باسکول رفته و برگه خروج برای نگهبانی صادر شود و در ساده ترین حالت فرض کنید به ازای هر کامیون دریافت کننده برگه خروج، باید یک پیامک با مشخصاتی خاص به مدیرکارخانه و یک پیامک هم به خریدار بار ارسال شود. سئوال اینجاست که چگونه میتوان از SERVICE BROKER بدون اینکه سرویس ویندوزی ایجاد و استفاده کنیم، به این هدف برسیم؟ یا بهتره اینطوری سئوال کنم که در اسکوئل سرور چگونه می توان مشخصات سرویس پیامکی را تعریف و لینک به آن شد تا Service Broker هم مابقی کارها را انجام دهد؟
      سئوال دیگه اینکه: در مواقعی که دیتابیس به هر دلیل از دسترس سرور خارج میشود ویا مثلاً Suspect میشود، بعد از رفع مشکل و راه اندازی مجدد بانک اطلاعاتی، آیا Processهای ناتمام بطور اتومات فعال شده وبه روند عادی خود ادامه خواهند داد یا اینکه باید توسط برنامه نویس و مدیر بانک اطلاعاتی اقدامات خاصی در این خصوص انجام شود؟
      • فرشید عزیز سلام

        انجام اینکار در SSSB امکان پذیر است. چنانچه بخواهید Queue شما پردازش سنگینی (مثلا فراخوانی وب سرویس ارسال اس ام اس و…) انجام دهد می توانید از External Activation استفاده کنید. مثالی که میلاد عزیز بررسی کرده Internal Activation است. مبحث External Activation در دوره SQL Server ویژه برنامه نویسان جلسه ۱۵ بررسی شده است
        •  سلام

          روززتون بخیر 
          ممنونم از پاسخگویی شما ومهندس فیروزی در این خصوص.
          دوره هایی که ذکر کردید رو دیدم ولی آیا من باید کل دوره ها رو تهیه کنم، یا امکانش هست که از بخش آموزشی مورد نظرم ((مثلا قسمت ۱۵)) محصول رو دریافت کنم؟
          •    سلام آقای علی اکبری عزیز
            لطفا با شماره دفتر نیک آموز تماس بگیرید ۰۲۱۴۴۲۷۷۶۹۹
            با تشکر

      •     به نظرم شما یه سرویس جداگونه برای اس ام اس می تونید داشته باشید که درخواست ها رو از یک جدول خاص بخونه

        و کاری که Service Broker انجام میده اینه که تو اون جدول رکورد ارسال اس ام اس میزنه
        در مورد سوال دومتون خود Broker پس از راه اندازی دوباره سرویس SQL  پیغام هایی که تو صف داره رو پردازش می کنه و این یکی از ویژگی های این سرویسه
  • 1
  • 2
هر روز یک ایمیل، هر روز یک درس
آموزش SQL Server بصورت رایگان
همین حالا فرم زیر را تکمیل کنید
دانلود رایگان جلسه اول
نیک آموز علاوه بر آموزش، پروژه‌های بزرگ در حوزه هوش تجاری و دیتا انجام می‌دهد.
close-link