Wysyłanie powiadomień w SQL Server – cz. I (konfiguracja)

Wprowadzenie

Microsoft SQL Server oferuje bardzo przydatną funkcjonalność wysyłania e-maili pod nazwą Database Mail (DM). Co istotne, można ją wykorzystywać zarówno w kontekście użytkowników technicznych (np. powodzenie/niepowodzenia joba) jak i użytkowników systemu (np. zmiana statusu zamówienia). Pierwszym krokiem do wykorzystania DM jest przeprowadzenie konfiguracji. Można tego dokonać zarówno za pomocą przygotowanego kreatora lub z wykorzystaniem przygotowanych procedur systemowych.

Zanim przejdziemy do omówienia konfiguracji DM, warto przytoczyć kilka technicznych kwestii. Przede wszystkim należy nadmienić, że działanie DM oparte jest na usłudze Service Brokera, o której można przeczytać np. tutaj (prezentacja Tomka Libery). Wszystkie dane dotyczące DM spoczywają w bazie MSDB, w której znajdują się także dane dotyczące SQL Server Agenta. Domyślnie DM jest wyłączone. DM bazuje na protokole SMTP.

Konfiguracja Database Mail w 10 krokach

  1. Przyjmując, że chcemy skorzystać z graficznego kreatora konfiguracyjnego, pierwszym krokiem będzie połączenie do wybranej instancji SQL Server, a następnie rozwinięcie węzła Management. Następnie należy kliknąć PPM na Database Mail i wybrać opcję Configure Database Mail.
  2. Należy dodać nowe konto. W tym celu klikamy Next, a w kolejnym ekranie wybrać pierwszą opcję i kliknąć Next.
  3. Zostanie wyświetlony komunikat, należy wybrać opcję Yes w celu włączenia Database Mail.
  4. Na kolejnym ekranie należy wybrać przycisk Add w celu dodania nowego konta. Pojawi się okno pozwalające na wpisanie ustawień konta:
  5. Należy wypełnić poszczególne pola uważnie zgodnie ze zrzutem. Podane ustawienia dotyczą konta Gmail. Różne serwery SMTP mogą korzystać z różnych adresów i portów. Bardzo ważne jest by pozyskać poprawne niezbędne dane. Oczywiście w normalnych warunkach korporacyjnych korzysta się z dedykowanego serwera SMTP.
  6. Po dodaniu konta, należy uzupełnić dane dotyczące profilu.
  7. Następnie należy dokonać ustawień publicznych profilu.
  8. Kolejny ekran to ustawienia dotyczące systemu Database Mail. Należy zaakceptować domyślne ustawienia wybierając Next.
  9. Kliknięcie Next potwierdza wypisane zmiany.
  10. Kliknięcie Close zamyka kreator.

W celu weryfikacji, czy można już skorzystać z Database Mail należy kliknąć PPM na Database Mail i wybrać opcję Send Test E-Mail… Po chwili na podany adres powinien zostać wysłany e-mail.

Konfiguracja Database Mail za pomocą procedur

Wskazana konfiguracja może przebiegać zupełnie bez udziału GUI. Konfiguracja DM z wykorzystaniem procedur jest bardzo dobrze opisana na SQLpedii przez Jakuba Kasprzaka. Warto zwrócić uwagę na to, że w przypadku administratorów konfiguracja poprzez GUI nie jest zbyt wygodna, a wykonanie kilku instrukcji w T-SQL pomaga w automatyzacji zadań. Procedury związane z DM to:

  • dbo.sysmail_add_account_sp – dodawanie konta
  • dbo.sysmail_add_profileaccount_sp – dodawanie profilu
  • dbo.sysmail_add_principalprofile_sp – dodawanie konta do profilu
  • dbo.sp_send_dbmail – wysyłanie e-maila

Dzięki konfiguracji DM możemy wysyłać nie tylko e-maile używając bezpośrednio procedury msdb.dbo.sp_send_dbmail. Możemy także skorzystać z funkcji alertów.

Napotkane problemy

Pisząc ten poradnik spotkałem się z błędem, który polegał na tym, że mój Database Mail log był pusty, a wszystkie testowe maile miały status unsent:

Przyczyną tego stanu był brak możliwości uruchomienia DM z uwagi na brak zainstalowanego frameworka .NET w wersji 3.5. Z kolei instalacja frameworka .NET 3.5, jak wiadomo dość powszechnie, nieco problematyczna. Mam na myśli błąd 0x800F081F:

W celu ominięcia problemu należy wykonać z linii poleceń uruchomionej jako administrator następujące polecenie:

Literę d należy podmienić na literę dysku, w którym znajduje się zamontowany nośnik instalacyjny Windows.

Drugie problem, który mnie spotkał, wynikał już bezpośrednio z autoryzacji na serwerze pocztowym Gmail-a i dotyczył dwuetapowej weryfikacji. Dwuetapowa weryfikacja Gmail powodowała następujący błąd:

Źródła:

4 Comments Add yours

  1. Adrian Chodkowski napisał(a):

    Fajny post. Obecnie używam tego mechanizmu praktycznie we wszystkich swoich projektach. Swoją drogą przydałby się wpis o formatowaniu wiadomości wysyłanych w ten sposób 😀

    1. piotrziuzianski napisał(a):

      Wpis taki jest w trakcie opracowania i czeka na przyszły tydzień 😉

  2. Gość napisał(a):

    Napisałeś że: „Oczywiście w normalnych warunkach korporacyjnych korzysta się z dedykowanego serwera SMTP”. Czy chodzi o własny firmowy serwer, czy w takich wypadkach raczej korzysta się z obcych serwerów u jakiś dostawców?

    1. piotrziuzianski napisał(a):

      Przepraszam, że tak późno odpisuję, ale jakoś przeoczyłem ten komentarz 😉 Nie mam doświadczenia w tej kwestii, o którą pytasz, ale myślę, że to zależy od potrzeb organizacji. W wielu przypadkach wydaje mi się, że firmy decydują się na własny serwer poczty SMTP.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *