HAVIT Knowledge Base

SQL Server 2005 přichází s novou funkčností tzv. Service Brokera - komplexní message-based komunikační platformy, s frontami a dalšími prvky potřebnými pro stavbu robustních SOA aplikací (Service Oriented Architecture).

Podrobněji o Service Brokeru viz SQL Books Online, pro nás je pro tuto chvíli podstatné, že prostřednictvím Service Brokera lze implementovat rozumnou podobu notifikací z SQL serveru do .NET aplikací, kdy jsou .NET aplikace SQL serverem informovány o určitých událostech (např. různých CREATE, DROP, ALTER DDL příkazech), o změnách dat (tzv. query notifications) či jakýchkoliv jiných zprávách, které si sami iniciujeme (např. z trigerů, atp.). Jak dále uvidíme, dotnetovské SqlCacheDependency či SqlDependency nejsou nic jiného, než konkrétním využitím Service Brokera.

Základní principy

Podívejme se nejprve na základní principy práce Service Brokera:

1. Komunikace prostřednictvím Service Brokera je založena na zprávách (MESSAGE). Každá jednotlivá zpráva představuje příslušnou událost, příkaz, notifikaci, prostě atomickou komunikační jednotku.
2. Zprávy jsou uchovávány ve frontách (QUEUE). Fronta je určitým bufferem mezi odesílatelem a příjemcem zprávy.
3. Formální požadavky, které musí zpráva splňovat (např. XML schema), určuje typ zprávy (MESSAGE TYPE).
4. Zprávy se posílají v konverzaci (DIALOG CONVERSATION) mezi dvěma endpointy, které představují služby (SERVICE). Cesty Mezi různými instancemi SQL serveru lze zprávy předávat pomocí routů (ROUTE).
5. Formální požadavky na konverzaci/dialog určuje CONTRACT, který mj. určuje typ zpráv vyměňovaných mezi službami a směr, kterým se posílají.
6. Zpráva může mimo explicitní konverzace vzniknout např. i událostí (EVENT) na straně SQL Serveru (např. DDL události CREATE, ALTER, DROP, nebo trace události, jak je známe z Profileru), nebo prostřednictvím sledování aktualizací dat (Query Notification), kdy SQL Server sleduje výsledky určitého SQL dotazu a oznámí jejich změnu (klasicky využíváno pro expiraci datové cache ASP.NET).
7. Podstatné je, že Service Broker sám neinicializuje komunikaci mimo SQL Server, do .NET aplikace, naopak .NET aplikace si musí zprávu sama vyzvednout z fronty (RECEIVE), resp. může využít konstrukce WAITFOR, čímž v případě prázdné fronty pasivně vyčkává, než se zpráva objeví.

Výše uvedené není ani zdaleka vyčerpávajícím popisem fungování Service Brokeru, pro naše účely však postačuje jako úvod do problematiky.

Pro dále popisovanou funkčnost je potřeba mít Service Brokera na databázi zapnutého:

ALTER DATABASE AdventureWorks SET ENABLE_BROKER

Query Notifications - SqlNotificationRequest, SqlDependency, SqlCacheDependency

Nejběžnějším způsobem využití Service Brokera v .NET aplikaci jsou Query Notification Services - novinka SQL Serveru 2005 spočívají v možnosti vyžádat (subscribe) sledování výsledku určitého SQL dotazu (query). V případě změny pak dojde k vytvoření příslušné zprávy (message), jejímu uložení do fronty (queue), odkud si ji z naší .NET aplikace vyzvedneme a o aktualizaci na straně SQL Serveru se tak dozvíme (a může tak například dojít k vyřazení určitých dat z cache atp.).

Základní principy Query Notifications

1. Query Notifications nelze aktivovat z T-SQL, ani CLR kódu hostovaného v SQL Serveru. Query Notifications lze aktivovat pouze prostřednictvím klientské aplikace, v případě .NET Frameworku prostřednictvím třídy SqlNotificationRequest (vlastnost SqlCommand.Notification).
2. SqlNotificationRequest je svázán s příkazem (SqlCommand), kterému je nastaven a teprve vykonáním tohoto příkazu se provede příslušná subscription QN na straně SQL serveru.
3. SQL Server sleduje pouze první změnu výsledku dotazu, tím QN končí a případný další notification request je potřeba iniciovat novým vykonáním příkazu.
4. V žádném případě se nejedná o komunikaci iniciovanou SQL Serverem, nýbrž si musíme z příslušné fronty sami zprávu o aktualizaci vyzvednout, resp. si obsah fronty průběžně hlídat.
5. V případě SqlDependency a SqlCacheDependency za nás vyzvednutí zprávy z příslušné fronty Service Brokera zajišťuje samotný .NET Framework prostřednictvím opakovaného volání příkazu WAITFOR (RECEIVE ...) TIMEOUT v kontinuálně otevřené samostatné SqlConnection ze samostatného threadu naší aplikace. Přesvědčit se o tom můžete v SQL Profileru.

Ilustrační příklad SqlDependency:

    class Program
    {
        static void Main(string[] args)
        {
            const string connString = "Server=localhost;Database=AdventureWorks;Integrated Security=true;";
            using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();

                SqlCommand cmd = new SqlCommand("SELECT Bonus FROM Sales.SalesPerson");
                cmd.Connection = conn;

                SqlDependency dependency = new SqlDependency(cmd);
                dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
                SqlDependency.Start(connString);

                cmd.ExecuteNonQuery();

                Console.ReadLine();
            }

        }

        static void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            Console.WriteLine("Bonus changed...");
        }
    }

SqlNotificationRequest

Pokud nám nestačí vyšší programátoská abstrakce SqlDependency či SqlCacheDependency a potřebujeme řídit Query Notifications podrobněji, můžeme využít třídu SqlNotificationRequest, resp. instanční vlastnost SqlCommand.Notification, kam instanci třídy SqlNotificationRequest přiřadíme a zajistíme tak subscribování daného příkazu do Query Notifications Service. Na rozdíl od SqlDependency pak ale musíme sami zajistit vyzvedávání zpráv z příslušné fronty a jejich zpracování (viz níže).

Požadavky na sledovaný SQL dotaz

Dotaz, ke kterému chceme QueryNotification aktivovat, musí splňovat určité nemalé restrikce:

• jména tabulek musí být uváděna včetně schématu, tedy Sales.SalesPerson, dbo.MojeTabulka, atp.
• nelze použít SELECT *, vždy musíme udělat výčet sloupců
• nelze použít agregační funkce
• nelze používat subqueries, outer-joins, self-joins

Podrobný výčet omezení viz např. http://msdn2.microsoft.com/en-US/library/ms181122.aspx.

Event Notifications

Dalším typem zpráv, které můžeme od SQL Serveru prostřednictvím Service Brokera odebírat, jsou tzv. Event Notifications. V zásadě se jedná o DDL a trace události na straně serveru, ať už na úrovni databáze nebo serveru jako celku. Např. CREATE_DATABASE, ALTER_PROCEDURE, DROP_ASSEMBLY, Audit_Login, SP_Recompile, atp. atp. Události jsou hiearchicky uspořádány a lze se přihlásit i k odběru celé skupiny najednou.

Na rozdíl od Query Notifications, kdy se nám příslušné prvky Service Brokera vytvářely převážně automaticky, zde již musíme provést základní inicializaci ručně. Na rozdíl od Query Notifications se Event Notifications celé řídí prostřednictvím T-SQL příkazů. Jednoduchý příklad by mohl vypadat nějak takto:

CREATE QUEUE MyNotifyQueue;

CREATE SERVICE MyNotifyService
    ON QUEUE MyNotifyQueue
    (
    [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
    )


CREATE ROUTE MyNotifyRoute WITH SERVICE_NAME = 'MyNotifyService', ADDRESS = 'LOCAL';

CREATE EVENT NOTIFICATION Notify_AlterProcedure
    ON DATABASE
    FOR ALTER_PROCEDURE
    TO SERVICE 'MyNotifyService', 'current database'MESSAGE TYPE pro události je již v systému pod názvem PostEventNotification připraven, vytvoříme tedy jen frontu (CREATE QUEUE), službu (CREATE SERVICE), routu (CREATE ROUTE) a pak už samotnou event-notifikace (CREATE EVENT NOTIFICATION). Tím je na straně SQL Serveru vše připraveno a nyní už můžeme jen vyzvedávat zprávy na straně .NET aplikace:

    class Program
    {
        static bool done = false;

        static void Main(string[] args)
        {
            Thread t = new Thread(ReceiveEvent);
            t.Start();
            
            while (!done)
            {
                Thread.Sleep(1000);
                Console.Write(".");
            }

            Console.ReadLine();
        }

        static void ReceiveEvent()
        {
            const string connString = "Server=localhost;Database=AdventureWorks;Integrated Security=true;";

            using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "WAITFOR (RECEIVE TOP(1) * FROM MyNotifyQueue), TIMEOUT @Timeout";
                cmd.Parameters.AddWithValue("@Timeout", 60000); // 60s

                do
                {
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        OnEvent();
                    }
                    done = true;
                }
                while (!done);
            }
        }

        static void OnEvent()
        {
            Console.WriteLine("Event received...");
        }
    }Obdobně jako interní implementace SqlDependency zde v samostatném threadu provádíme průběžný polling WAITFOR(RECEIVE ...) s příslušným timeoutem, zatímco v hlavním vlákně běží aplikace dál (obsluha UI, atp.). Receiving-vláknu bychom také měli nastavit vlastnost IsBackground na true, aby při skončení hlavního vlákna došlo k ukončení tohoto pollingu.

Vlastní zprávy odesíláné z SQL Serveru (např. z triggerů)

Dalším zdrojem zpráv předávaných prostřednictvím Service Brokera mohou být i zprávy, které sami vytvoříme, jejichž odeslání sami inicializujeme - např. z různých triggerů (DML i DDL), nebo uložených procedur.

Pro tuto chvíli přesahuje podrobný popis zamýšlený rozsah tohoto článku, nicméně jak je už z výše uvedeného zřejmé, nejde opět o nic jiného než o inicializaci jednotlivých prvků Service Brokera (vše T-SQL) a dále posílání zpráv prostřednictvím T-SQL příkazů BEGIN DIALOG a SEND. Samotná .NET aplikace by pak byla stejný princip, jak u Event Notifikacions, jen formát zpráv se bude lišit podle toho, jak si ho zadefinujeme (zprávy mohou být i prázdné, nebo plain-text).

Mnohé příklady odesílání zpráv přes Service Brokera viz SQL Books Online. Možná tento článek rozšířím někdy později.