Tutorial – Działający przykład Service Brokera (MS SQL 2008)

Serwis broker pozwala na pewne przesyłanie danych między różnymi bazami danych, instancjami, czy nawet serwerami. Poszerza on więc możliwości serwera SQL o możliwość niezawodnego przekazywania informacji poza serwer. Samo przekazywanie danych opiera się o odpowiednie protokoły sieciowe i dostęp uzyskany do ENDPOINT-ów na serwerach.

W poniższym przykładzie zademonstruję działanie Service Brokera w obrębie jednej tylko bazy danych, co zazwyczaj w praktyce się nie zdarza. Można by go rozbudowywać dalej, ale… i bez tego sprawa wydaje się już dosyć skomplikowana.

Poniższy skrypt uruchamiany krok po kroku utworzy działający serwis broker. Można go ściągnąć stad. Więc do dzieła!

USE master;

GO

Serwis Broker zostanie utworzony w nowej bazie danych, więc jeżeli już taką bazę masz, to usuń ją:

DROP DATABASE ServiceBrokerTest;

GO

A potem utwórz na nowo:

CREATE DATABASE ServiceBrokerTest;

GO

Żeby określona baza danych mogła używać serwis brokera, trzeba go w tej bazie danych włączyć:

ALTER DATABASE ServiceBrokerTest

 SET ENABLE_BROKER;

GO

Teraz można już tworzyć obiekty:

USE ServiceBrokerTest;

GO

Chcę zaimplementować rozwiązanie, gdzie klient będzie mógł złożyć zamówienie i wysłać do Brokera zapytanie o dostępność towaru. Jeżeli towar będzie na stanie, to zostanie wydane potwierdzenie zamówienia. Jeżeli zaś nie będzie tyle w magazynie, to zostanie zwrócona informacja o błędzie. Komunikacja będzie się odbywać za pośrednictwem Service Brokera, czyli niekoniecznie natychmiast i od razu po zapytaniu będzie udzielona odpowiedź. Można to sobie raczej odnieść do modelu w którym sklepy składają zamówienia na towary w hurtowni. Stąd też utworzę Schemat:

CREATE SCHEMA Magazyn;

GO

W tym schemacie zakładamy tabelę z produktami:

CREATE TABLE Magazyn.Zamowienia

(

IdZamowienia INT NOT NULL PRIMARY KEY IDENTITY(1,1),

IdProduktu INT,

Ilosc INT,

Wynik NVARCHAR(100));

GO

Druga tabela to tabela z zamówieniami. Każde zamówienie ma przyznawany numer zamówienia i zakładam (dla uproszczenia, że w ramach jednego zamówienia będzie można starać się tylko o jeden produkt). W kolumnie wynik będzie zapamiętywane, czy produkt występuje w takiej ilości, by zaspokoić zamówienie, czy też nie. Ta kolumna będzie uzupełniana dopiero po potwierdzeniu zamówienia:

CREATE TABLE Magazyn.Zamowienia

(

IdZamowienia INT NOT NULL PRIMARY KEY IDENTITY(1,1),

IdProduktu INT,

Wynik NVARCHAR(100));

GO

Wypadało by mieć jakieś produkty w magazynie, więc:

 INSERT INTO Magazyn.Produkty

 VALUES

 (’Hoop Cola’,300),

 (’Rybnicki Full’,150);

 GO

Wysyłając zapytanie do serwisu zamówień, trzeba mówić zrozumiałym dla tego serwisu językiem. Dlatego definiujemy pierwszy możliwy do przesyłania komunikat. Ten komunikat to MESSAGE TYPE. Każdy typ wiadomości musi mieć nazwę i może zawierać jakąś dodatkową treść. W tym przypadku ta dodatkowa treść ma mieć poprawną formę XML.  Czy możliwe jest aby wiadomość nie miała dodatkowej treści!? Oczywiście! Jeżeli będąc w odwiedzinach u znajomych Twój partner lub partnerka kopnie Cię pod stołem to masz właśnie wiadomość bez treści, która coś tam oznacza!

CREATE MESSAGE TYPE

       [//Magazyn/Zamowienie]

       VALIDATION = WELL_FORMED_XML;

GO

Drugi rodzaj wiadomości będzie odsyłany do nadawcy zamówienia:

CREATE MESSAGE TYPE

       [//Magazyn/Potwierdzenie]

       VALIDATION = WELL_FORMED_XML;

GO

Dane przesyłane do zaakceptowania będą na bieżąco odkładane do kolejki i tam będą czekać, jak pacjenci w przychodni, aż się nimi ktoś zajmie. Kolejka jest to „prawie tabela”, z tym, że ma ustaloną kolejność. Kto pierwszy wszedł pierwszy też wyjdzie (chyba, że bawisz się jeszcze z priorytetami wiadomości):

CREATE QUEUE Magazyn.KolejkaZamowien;

GO

Podobna kolejka musi istnieć, aby gromadzić odpowiedzi z serwisu zamówień. Tworzymy przecież tak naprawdę parę serwis brokerów „Przyjmujący zlecenia” i „Przyjmujący potwierdzenia”.

CREATE QUEUE Magazyn.KolejkaPotwierdzen;

GO

Przechodzimy do czegoś ciekawszego. Rozmawiając ze sobą serwisy powinny  informować się wiadomościami według ustalonej zasady. Np. kiedy pytam, to potem Ty mi odpowiadasz. Podobnie jest tutaj: Komunikacja odbywa się w oparciu o tzw. Kontrakt. Kontrakt określa jaką wiadomość kto może przesłać. W naszym przypadku kontrakt WysyłkaZamówień dopuszcza, aby Zamowienie było wysyłane przez Inicjatora dialogu, a Potwierdzenie odsyłane przez odbiorcę zlecenia. To trochę jak w szkole. Chcesz coś powiedzieć, to najpierw podnieś rękę:

CREATE CONTRACT [//Magazyn/WysylkaZamowien]

([//Magazyn/Zamowienie] SENT BY INITIATOR,

 [//Magazyn/Potwierdzenie] SENT BY TARGET);

GO

Teraz tworzymy usługi. Najpierw usługa przyjmowania zleceń. Usługa nazywa się SerwisZamowien, odczytuje komunikaty z kolejki KolejkaZamowien i oczekuje dialogu zgodnego z kontraktem WysylkaZamowien

CREATE SERVICE [//Magazyn/SerwisZamowien]

ON QUEUE Magazyn.KolejkaZamowien

([//Magazyn/WysylkaZamowien])

GO

Drugi serwis z kolei czeka na informacje zapisywane do kolejki KolejkaPotwierdzen:

CREATE SERVICE [//Magazyn/SerwisKlienta]

ON QUEUE Magazyn.KolejkaPotwierdzen

GO

Składanie zamówień będzie realizowane poprzez procedurę składowaną, chociaż mógłby to być równie dobrze skrypt, a nie procedura. Ten kawałek kodu będę komentował krok po kroku:

CREATE PROCEDURE Magazyn.SP_ZlozenieZamowienia

@IdZamawianegoProduktu INT,

@ZamawianaIlosc INT

AS

BEGIN

Najpierw zapisujemy do tabeli zamówień informację o tym, że składamy zamówienie. Zapamiętujemy nadany nr zamowienia.

      INSERT INTO Magazyn.Zamowienia VALUES (@IdZamawianegoProduktu,@ZamawianaIlosc,”);

      DECLARE @IdZamowienia INT;

      SET @IdZamowienia = @@IDENTITY;

Deklarujemy zmienne na dialog (tak, dialog jest typu INT) – jest to „uchwyt” (handle) do dialogu:

      DECLARE @IdDialogu UNIQUEIDENTIFIER;

      DECLARE @Wiadomosc NVARCHAR(100);

 I zaczynamy rozmawiać. Rozmawiać będą ze sobą dwa serwisy bazując na konkretnym kontrakcie:

      BEGIN DIALOG @IdDialogu

             FROM SERVICE [//Magazyn/SerwisKlienta]

             TO SERVICE N’//Magazyn/SerwisZamowien’

             ON CONTRACT [//Magazyn/WysylkaZamowien]

             WITH ENCRYPTION = OFF;

Będzie tu wysłana konkretna wiadomość: „Na zamowieniu nr tyle a tyle, chcę zamówić produkt taki a taki w tej ilości”.

      SELECT @Wiadomosc =

N'<Zamowienie><IdZamowienia>’+
CAST(@IdZamowienia AS VARCHAR(10))+
'</IdZamowienia><IdProduktu>’+
CAST(@IdZamawianegoProduktu AS NVARCHAR(10))+
'</IdProduktu><Ilosc>’+
CAST(@ZamawianaIlosc AS NVARCHAR(10))+
'</Ilosc></Zamowienie>’;

Tak przygotowaną wiadomość wysyłamy w ramach naszego dialogu

      SEND ON CONVERSATION @IdDialogu

            MESSAGE TYPE [//Magazyn/Zamowienie]

            (@Wiadomosc);

I to na tyle! Teraz niech ServiceBrokery same się martwią o dalsze przerobienie naszego zlecenia.

END

GO

Możemy teraz złożyć zamówienie:

EXECUTE Magazyn.SP_ZlozenieZamowienia 1,3;

GO

Sprawdźmy co zapisało się w tabelach:

SELECT * FROM Magazyn.Zamowienia;

SELECT * FROM Magazyn.KolejkaZamowien;

SELECT * FROM Magazyn.KolejkaPotwierdzen;

 Efekt powinien być taki, że w kolejce zamówień coś zobaczysz. Kolejka potwierdzeń będzie jeszcze pusta. W tabeli zamówień zobaczysz zamówienie na produkt nr 1 w ilości 3 szt., ale jeszcze bez potwierdzenia. Pora więc, by ktoś łaskawie pobrał dane z kolejki zamówień.

Również i w tym przypadku najpierw przygotujemy procedurę, a dopiero potem ją uruchomimy. Jazda! Procedura nie przyjmuje żadnych parametrów:

 CREATE PROCEDURE Magazyn.SP_ObslugaKolejkiZamowien

AS

BEGIN

Deklarujemy zmienne na:  Identyfikator dialogu (jest obecnie zapisany w kolejce), przekazaną wiadomość (również w kolejce) oraz typ wiadomości (też w kolejce).

      DECLARE @IdDialogu UNIQUEIDENTIFIER;

      DECLARE @Wiadomosc NVARCHAR(100);

      DECLARE @TypWiadomosci sysname;

Pobieramy rekord z kolejki. Jak jest to fajnie, a jak nie ma, to czekamy na to maksymalnie 1 sekundę. Pobrane dane z kolejki zapisujemy w zmiennych:

      WAITFOR

      ( RECEIVE TOP(1)

            @IdDialogu = conversation_handle,

            @Wiadomosc = message_body,

            @TypWiadomosci = message_type_name

        FROM Magazyn.KolejkaZamowien

      ), TIMEOUT 1000;

Jeżeli przekazana do nas wiadomość to zamówienie (już wiesz po co definiowaliśmy różne rodzaje wiadomości), to tą wiadomością się zajmiemy.

      IF @TypWiadomosci = N’//Magazyn/Zamowienie’

      BEGIN

Zaczniemy od wczytania do zadeklarowanych zmiennych tego, co znajduje się w wiadomości. A we wiadomości znajduje się id zamówienia, id produktu oraz zamawiana ilość:

            DECLARE @IdProduktu INT;

            DECLARE @Ilosc INT;

            DECLARE @Dokument INT;

Wiadomość jest „opakowana” w XML-u więc wydobywam te dane za pomocą OPENXML:

            EXECUTE sp_xml_preparedocument @Dokument OUTPUT, @Wiadomosc;

           SELECT @IdProduktu=IdProduktu, @Ilosc=Ilosc
          FROM OPENXML(@Dokument,’Zamowienie’,2)

                        WITH

                        (

                             IdProduktu INT,

                             Ilosc INT

                        );   

           EXECUTE sp_xml_removedocument @Dokument

Zaczniemy od typowych obliczeń w tabeli produkty. Potwierdzę zamówienie tylko jeżeli w magazynie mam odpowiednią ilość:

            DECLARE @StanMagazynu INT

            SELECT @StanMagazynu=Ilosc
            FROM Magazyn.Produkty WHERE IdProduktu=@IdProduktu;

A teraz budujemy odpowiedź. Będzie to oczywiście XML. Zawartość XML zależy od tego, czy wystarczy danego produktu w magazynie, czy nie. Do wiadomości należy włożyć też IdZamowienia, oraz (choć niekoniecznie) IdProduktu i w końcu odpowiedź. Dwie instrukcje poniżej, budujące XML różnią się tylko wynikiem potwierdzenia: jest to albo „OK.” albo „Błąd”:

             –Budowanie odpowiedzi

            DECLARE @Odpowiedz NVARCHAR(100);

         

            IF @StanMagazynu>=@Ilosc

                  BEGIN

                        SET @Odpowiedz = N'<Odpowiedz> <IdZamowienia>’+
                        CAST(@IdZamowienia AS VARCHAR(10))+
                        '</IdZamowienia><IdProduktu>’+
                        CAST(@IdProduktu AS NVARCHAR(10))+
                        '</IdProduktu><Wynik>OK</Wynik></Odpowiedz>’;

                        UPDATE Magazyn.Produkty
                        SET Ilosc=Ilosc-@Ilosc
                        WHERE IdProduktu=@IdProduktu;

                  END

            ELSE

                  BEGIN

                        SET @Odpowiedz = N'<Odpowiedz><IdZamowienia>’+
                        CAST(@IdZamowienia AS VARCHAR(10))+
                        '</IdZamowienia><IdProduktu>’+
                        CAST(@IdProduktu AS NVARCHAR(10))+
                        '</IdProduktu><Wynik>Blad</Wynik></Odpowiedz>’;

                  END;

Teraz pora na wysłanie odpowiedzi:

             SEND ON CONVERSATION @IdDialogu

            MESSAGE TYPE [//Magazyn/Potwierdzenie]

            (@Odpowiedz);

 

            END CONVERSATION @IdDialogu;

      END

END

GO

I to by było na tyle. Teraz tylko pora uruchomić tę procedurę!

EXECUTE Magazyn.SP_ObslugaKolejkiZamowien

GO

Jeżeli przyjrzeć się teraz kolejkom, dostrzeżesz zmianę. Są za pisy w kolejce potwierdzeń, a nie ma w kolejce zamówień.

SELECT * FROM Magazyn.Zamowienia;

SELECT * FROM Magazyn.KolejkaZamowien;

SELECT * FROM Magazyn.KolejkaPotwierdzen;

Teraz napiszemy procedurę pobierającą potwierdzenia. Będzie ona czytać z kolejki potwierdzeń i zapisywać do tabeli Zamowienia fakt potwierdzenia lub błędu.  Procedura nie ma parametrów. Wszystko przeczyta sobie z  kolejki:

CREATE PROCEDURE Magazyn.SP_ObslugaKolejkiPotwierdzen

AS

BEGIN

Deklarujemy zmienne na odczyt danych z kolejki

      DECLARE @IdDialogu UNIQUEIDENTIFIER;

      DECLARE @Wiadomosc NVARCHAR(100);

      DECLARE @TypWiadomosci sysname;

Czytamy jeden zapis z kolejki, albo czekamy przez sekundę i kończymy.

      WAITFOR

      ( RECEIVE TOP(1)

            @IdDialogu = conversation_handle,

            @Wiadomosc = message_body,

            @TypWiadomosci = message_type_name

        FROM Magazyn.KolejkaPotwierdzen

      ), TIMEOUT 1000;

Jeżeli wiadomość jest typu Potwierdzenie, to idziemy dalej:

      IF @TypWiadomosci = N’//Magazyn/Potwierdzenie’

      BEGIN

Pobieramy to co zostało przekazane w wiadomości w postaci XML-a. Tak więc OPENXML idzie w ruch:

            DECLARE @Wynik NVARCHAR(100);

            DECLARE @IdZamowienia INT;

            DECLARE @IdProduktu INT;

            DECLARE @Dokument INT;

            EXECUTE sp_xml_preparedocument @Dokument OUTPUT, @Wiadomosc;

            SELECT @IdZamowienia=IdZamowienia, @Wynik=Wynik,@IdProduktu=IdProduktu FROM OPENXML(@Dokument,’Odpowiedz’,2)

                  WITH

                  (

                        IdZamowienia INT,

                        IdProduktu INT,

                        Wynik NVARCHAR(100)

                  );   

            EXECUTE sp_xml_removedocument @Dokument

Przeczytaliśmy co było do przeczytania, więc pora zakończyć tę konwersacje! Można by wprawdzie podziękować, ale czy komputery to lubią? Na dodatek musiałbym na to stworzyć nowy typ wiadomości…

            END CONVERSATION @IdDialogu;   

Żeby się za moment przekonać, że to działa, każę wyświetlić wiadomość, ale zazwyczaj tego nie będziesz chciał:

            –Wyswietlenie odpowiedzi

            SELECT @Wynik AS 'Wynik’

Pora zapisać wynik w tabeli zamówień:         

            UPDATE Magazyn.Zamowienia

                  SET Wynik = @Wynik

                  WHERE IdZamowienia = @IdZamowienia

           

      END

END

GO

Jeżeli uruchomi my tę procedurę, to wynik powinien się przenieść z kolejki potwierdzeń do tabeli zamówień.

EXECUTE Magazyn.SP_ObslugaKolejkiPotwierdzen

GO

SELECT * FROM Magazyn.Zamowienia;

SELECT * FROM Magazyn.KolejkaZamowien;

SELECT * FROM Magazyn.KolejkaPotwierdzen;

GO

Jeżeli wszystko poszło dobrze, to w kolejkach jest teraz pusto (konwersacja się skończyła), a w tabeli zamówień znajduje się zapis o przyjęciu zamówienia do realizacji. Jednak nie było to zbyt miłe pod względem obsługi. Procedury uruchamialiśmy ręcznie. Jak można by to zorganizować inaczej?

Można procedurę wywołać przez SQL Server Agenta. Niech się sam potroszczy o uruchomienie obsługi serwisów/kolejek w porze mniejszego obciążenia serwera.

Inna metoda, to wywoływanie procedur, kiedy tylko coś trafi do kolejki. Och żeby tak właśnie było w służbie zdrowia. Tam się może nie doczekamy, ale w Service Brokerze to możliwe:

ALTER QUEUE Magazyn.KolejkaZamowien

WITH STATUS = ON,

ACTIVATION(

STATUS=ON,

PROCEDURE_NAME = ServiceBrokerTest.Magazyn.SP_ObslugaKolejkiZamowien,

MAX_QUEUE_READERS =1,

EXECUTE AS OWNER)

GO

Powyższe polecenie mówi, że kolejka zamówień ma być włączona i automatycznie ma się uruchamiać obsługa tej kolejki za pomocą procedury SP_ObsługaKolejkiZamowien. Zapis MAX_QUEUE_READERS pozwala określić ile maksymalnie jednocześnie takich procedur może zostać automatycznie uruchomionych. Rozważ czy w przypadku dużego natężenia zleceń nie zwiększyć tej wartości.

Podobnie można zrobić w przypadku drugiej kolejki:

ALTER QUEUE Magazyn.KolejkaPotwierdzen

WITH STATUS = ON,

ACTIVATION(

STATUS=ON,

PROCEDURE_NAME = ServiceBrokerTest.Magazyn.SP_ObslugaKolejkiPotwierdzen,

MAX_QUEUE_READERS =1,

EXECUTE AS OWNER)

GO

Jak teraz będzie działać obsługa serwisów? Prosto! Klient zleca zamówienie:

EXECUTE Magazyn.SP_ZlozenieZamowienia 2, 130;

I już po chwili (choć niekoniecznie od razu) polecenie:

SELECT * FROM Magazyn.Zamowienia

Pokazuje stan realizacji zamówienia. Zamówienie powędrowało więc do Serwisu zamówień (właściwie do kolejki zamówień), uruchomiła się procedura obsługująca zamówienie, odpowiedziała ona do serwisu potwierdzeń (właściwie do jego kolejki), a ten z kolei przyjął potwierdzenie i zapisał je do tabeli zamówień.

Jeżeli dorzucisz jeszcze utworzenie tabelki:

CREATE TABLE Rejestrator

(Id int Identity(1,1) not null,

Akcja nvarchar(100) not null,

DataCzas DateTime DEFAULT GetDate());

GO

I wzbogacisz swoje procedury o zapis do tej tabeli w kluczowych miejscach procedur składowanych, to możesz się przekonać, że polecenie

EXECUTE Magazyn.SP_ZlozenieZamowienia 2, 130;

Powoduje zapisy rozrzucone w czasie do tej tabeli. Przetwarzanie jest więc dość asynchroniczne. Gotowy skrypt wraz z zapisem do „Rejestratora” można ściągnąć stad.

Dużo informacji znajdziesz też tu: http://technet.microsoft.com/en-us/library/bb839499.aspx