Zine.net online

Witaj na Zine.net online Zaloguj się | Rejestracja | Pomoc
w Szukaj

dev2dev

SQL Server Event Notifiations - porady praktyczne

   SQL Server Event Notifications (EN) to właściwość wprowadzona już w wersji 2005 ale niedoceniona przeze mnie wówczas i myślę, że przez wielu entuzjastów SQL-a. Funkcjonalnie działanie opiera się na SQL Server Service Broker i dlatego implementując EN warto wesprzeć się na moim add-in do SSMS czyli Service Broken, którego źródła są tutaj. Wersja tam znajdująca działa z wersją SQL Server 2008 (również Express).

   Dlaczego warto mieć takie narzędzie do wsparcia? Dlatego, że Service Broker bywa bardzo kapryśny w implementowaniu. Kiedy wydaje się, że już wszystko zostało zrobione i następuję moment końcowego odliczania do uruchomienia implementacji nagle okazuje się, że po naciśnięciu klawisza F5 następuje głucha cisza. Wówczas niezbędne okazuje się sprawne przejrzenie właściwości i infrastruktury Service Broker'a aby odkryć możliwą przyczynę braku działania. Szczególnie przydatna w takich momentach jest grupa opcji Show oraz Other options, która w prosty i szybki sposób dostarcza szeregu informacji w postaci okienkowej bez konieczności znajomości wywołań widoków systemowych związanych z Service Broker.

   Z moich doświadczeń wynika, że najlepszym rozwiązaniem jest posiadanie zaimplementowanego jednego bazowego rozwiązania, które tworzyłoby środowisko nasłuchujące zdarzenia generowane przez SQL Server. Taka implementacja powinna mieć dosyć szeroką klasę event'ów do nasłuchiwania. Dosyć szeroką ale nie za szeroką. W moich doświadczeniach skupiłem się głównie na klasie TRC_ERRORS_AND_WARNINGS, która obejmuje głownie sytuacje wyjątkowe oraz informacje o zachowaniu się SQL Server (interesowała mnie głownie ta klasa a nie klasa DDL_EVENTS ponieważ interesowały mnie głównie błędy generowane prze implementację posadowione na SQL Server). Napisałem, że nie powinna być za szeroka ponieważ w doświadczeniach posunąłem się do zastosowania klasy TRC_ALL_EVENTS co pokazało, że tabela do przechowywania komunikatów "pompowanych" przez SQL Server w błyskawicznym tempie zaczęła się zapełniać masą rekordów z komunikatami zdarzeń. Ta implementacja bazowa byłaby wznawiana na czas uruchomień nowych implementacji EN nasłuchując możliwych sytuacji wyjątkowych, w tym związanych z tworzoną właśnie nową implementacją. Poz zakończeniu wdrażania nowej implementacji EN tę bazową należałoby wyłączyć do czasu uruchomienia następnej.

   Nie chcę tu szczegółowo opisywać jak zaimplementować EN. Jest to niezwykle proste. Przykład jest tutaj. Na co jednak warto zwrócić szczególną uwagę?
  1. Najważniejsze jest aby baza danych, której dotyczy EN miała włączony Service Broker. Bez tego nie mam mowy o działaniu EN. Tworzenie nowej bazy danych przez GUI z SSMS domyślnie wyłącza Service Broker. Więc jest to punkt krytyczny. Mój add-in pozwala na włączenie i wyłączenie go z zakładki Brokers, która pokazuje wszystkie Service Broker's aktualnie dostępnego servera SQL. Odblokowanie i zablokowanie odbywa się poprzez naciśnięcie kontrolki check box.
  2. Tworząc subskrypcje zdarzeń przez CREATE EVENT NOTIFICATION należy bezwzględnie pamiętać, że nazwa serwisu jest literałem a nie sysname i do tego jest case sesitive i jest to błąd czasami trudny do zauważenia (dlatego tworząc tę subskrypcję należy do nazwy serwisu zastosować metodę Copy-Paste nazwy serwisu z polecenia CREATE SERVICE).
  3. Jeżeli w tworzeniu subskrypcji EN korzystamy ze składni 'current database' informującą, że subskrypcja odnosi się do bieżącej bazy danych to literał ten jest również case sensitive.
  4. Poza stosowaniem metody Copy-Paste do nazwy serwisu zdecydowanie odradzam stosowanie tej metody to tworzenia nowej implementacji na podstawie już istniejącej. Wbrew pozorom trzeba dokonać zmian w wielu miejscach i pominięcie choćby jednej zmiany owocuje w braku działania implementacji EN. Schemat tworzenia EN jest prosty i można go szybko opanować: tworzenie kolejki, tworzenie serwisu w oparciu o kontrakt http://schemas.microsoft.com/SQL/Notifications/EventNotification, utworzenie implementacji EN w oparciu o wcześniej utworzony serwis z wyborem określonych klas zdarzeń, utworzenie procedury przetwarzającej komunikaty z kolejki, aktywowanie kolejki z utworzoną wcześniej procedurą. Korzystając z Service Broken do SSMS można to zrobić bez znajomości poleceń T-SQL, korzystając głównie z GUI tego dodatku (poza oczywiście tworzeniem procedury aktywującej, którą trzeba napisać dostosowując do do konkretnej implementacji).
  5. EN generuje zdarzenia w postaci dokumentów XML i tworząc tabelę rejestrującą komunikaty warto utworzyć kolumnę, której zawartość będzie pobierana elementu z EVENT_INSTANCE/EventType oraz kolumnę na moment powstania zdarzenia, której zawartość pochodzi od elementu EVENT_INSTANCE/PostTime. Pierwsza informacja pozwala nam szybko zorientować się z jakim zdarzeniem mamy do czynienia a druga pozwala zgrupować szereg rekordów dotyczących tej samej sytuacji wyjątkowej. No i oczywiście kolumna na sam dokumenty XML. Jak może wyglądać jego konkretny wygląd dla poszczególnych zdarzeń można przeczytać ten dokument (jeśli ktoś lubi czytać dokumenty XSD).
  6. W przypadku problemów zobaczyć co zwraca zapytanie: SELECT * FROM sys.server_event_notifications. Może się okazać, że w zestawie odpowiedzi nie będzie naszego EN. Wówczas należy przyjrzeć się całej strukturze tworzonej w pkt. 4 czy nie zawiera błędów.
Podsłuchowywując :) SQL Server, szczególnie poprzez TRC_ALL_EVENTS można si wiele dowiedzieć o tym jak funkcjonuje SQL Server. Widać jak ważna jest baza master oraz jak często używany jest SHOWPLAN_XML.

 W SSMS wpisując polecenie:

RAISERROR('Uuuuuuuuuus!", 16, 1)

dowiadujemy się, że idzie za tym zdarzenie USER_ERROR_MESSAGE. Natomiast polecenie

DELETE FROM [blablabla]

generuje najpierw zdarzenie EXCEPTION (w SSMS, na zakładce Messages jest pierwsza linia komunikatu zawierająca treść Msg 208, Level 16, State 1, Line 1), natomiast drugim zdarzeniem jest USER_ERROR_MESSAGE i odpowiada to drugiej linii komunikatu na zakładce Messages: Invalid object name 'blablabla'.

Jak to z podsłuchowywaniem bywa można się dowiedzieć rzeczy niekoniecznie fajnych. Pisałem już o tym, że postawienie bazy w tryb offline powoduje dziwne komunikaty przy generowaniu skryptów z bazy danych. Okazuje się, że ten wyjątek jest generowany stale w EN przy każdej sytuacji gdy server używa używa do jakichś celów listy baz podpiętych do server'a. Błąd jest chyba w metodzie ConnectionInfo.

EN są świetnym narzędziem, które pozwala na lekką implementacje systemu wczesnego ostrzegania o sytuacjach wyjątkowych dziejących się na bazie danych obsługiwanych przez nasza aplikację. EN nie generują dodatkowego narzutu na działanie server'a ponieważ są one generowane stale niezależnie czy mamy zaimplementowane EN czy nie. Jeżeli log zdarzeń postawimy na odrębnym wolumenie niż baza produkcyjna to może to być rozwiązanie bardzo optymalne.

Dla zainteresowanych podaję linki do najbardziej interesujących przypadków śledzenia sytuacji wyjątkowych przy pomocy EN:
  1. Alertowanie zdarzenia DEADLOCK_GRAPH poprzez e-mail opisane na blogu ReSQueL.
  2. Alertowanie zdarzenia BLOCKED_PROCESS_REPORT opisane na blogu Tony Rogerson'a.
Hmm, udało sie zrobić notkę na blogu bez obrazków :)
Opublikowane 20 grudnia 2009 12:04 przez marekpow

Komentarze:

Brak komentarzy
Komentarze anonimowe wyłączone
W oparciu o Community Server (Personal Edition), Telligent Systems