SQL Server – Konfigurowanie powiadomień

SQLSrv_log

Specyfika pracy administratorów (w tym baz danych, których dotyczy ten artykuł), a czasem również projektantów czy nawet developerów wymaga bycia na bieżąco poinformowanym odnośnie stanu systemów znajdujących się pod ich opieką. Regularny monitoring i powiadomienia stają się szczególnie istotne, gdy zachodzą w nich niepokojące zjawiska i trzeba zapobiec, bądź przerwać awarię.

W tym artykule przedstawiono proces konfiguracji usługi Database Mail (w formie wideoklipu w formacie .mp4) dla SQL Server oraz alternatywną metodę wysyłania e-maili z wykorzystaniem kodu CLR przydatną zwłaszcza w wersji SQLEXPRESS, której licencja nie pozwala na korzystanie z usługi Database Mail. Konfigurację Database Maila przedstawiono w oparciu o SQL Server 2008 R2 Enterprise, ale we wszystkich środowiskach poza SQLExpress przebiega ona podobnie. Proces ten obejmuje dwa etapy:

  • utworzenie profilu, a także stworzenie przynajmniej jednego konta i skojarzenie go z profilem tj. wypełnienie m.in: nazwa konta, adres email, nazwa serwera smtp i czy wymaga autentykacji SSL oraz podanie numer portu, a także sposób autentykacji;
  • wysłanie testowego e-maila i sprawdzenie czy działa (jeśli nie, to sprawdzanie error loga Database Maila, w dalszej kolejności odpytanie odpowiednich Dynamic Management Objects i podjęcie dalszych działań na podstawie uzyskanych informacji)

A oto w jaki sposób się to odbywa:

W tym miejscu czytelnikowi należy się  wyjaśnienie: konfiguracja na przedstawionym wideo opierała się na koncie pocztowym w publicznym serwisie o2.pl. Konfigurując Database Maila wewnątrz organizacji, mamy zazwyczaj do dyspozycji własny SMTP, dlatego należy zaznaczyć pole ‘Windows Authentication using Database Engine service Credentials’ zamiast ‘Basic Authentication’ co jest korzystne między innymi ze względów bezpieczeństwa.

Po chwili na firmowej skrzynce odnajdujemy naszą testową wiadomość z Database Maila.

 


Ze względu na to, że większość osób czytających ten artykuł i wykorzystujących silnik baz danych SQL Server dla celów prywatnych, zapewne będzie bardziej zainteresowana w jaki sposób można uzyskać podobną funkcjonalność wykorzystując całkowicie darmową wersję SQLEXPRESS, poniżej przedstawiono w jaki sposób można skonfigurować wysyłanie powiadomień z wersji SQLEXPRESS z wykorzystaniem CLR.

Zadanie obejmuje następujące etapy:

  • przygotowanie odpowiedniego kodu w jednym z języków dostępnych na platformie .NET (tutaj posłużymy się jednym z gotowych rozwiązań dostępnych w internecie);
  • skompilowanie tego kodu do postaci biblioteki;
  • utworzenie assembly w SQL Serverze na podstawie skompilowanej wcześniej biblioteki (plik .dll);
  • utworzenie nowej procedury na bazie załadowanego assembly i wykorzystanie jej.

Zatem zaczynamy.

1) Najpierw należy przygotować plik z kodem, który zostanie skompilowany do postaci biblioteki (w internecie można odnaleźć trochę gotowych rozwiązań). Zawartość pliku, używanego tutaj wygląda następująco:

  1 Imports System.Net  
  2 Imports System.Net.Mail  
  3  
  4 Public Class StoredProcedure  
  5     <Microsoft.SqlServer.Server.SqlProcedure()> _  
  6     Public Shared Sub spSendMail(ByVal recipients As String, 
  7     ByVal subject As String, ByVal from As String,
  8     ByVal body As String)
  9      
 10         Dim mySmtpClient As SmtpClient 
 11  
 12         Using myMessage As New MailMessage(from, recipients) 
 13  
 14         myMessage.Subject = subject  
 15         myMessage.Body = body  
 16         myMessage.IsBodyHtml = True  
 17  
 18     mySmtpClient = New SmtpClient("TWOJ_SERWER_SMTP")  
 19         mySmtpClient.Credentials = 
 20     New NetworkCredential("TWOJ_ADRES_EMAIL","TWOJE_HASLO")  
 21         mySmtpClient.Send(myMessage)  
 22  
 23     /* 
 24         mySmtpClient = New SmtpClient("poczta.o2.pl")  
 25         mySmtpClient.Credentials = 
 26     New NetworkCredential("szymon_plaszczyk@o2.pl",  
 27                   "HASLO_ZOSTALO_UKRYTE")  
 28         mySmtpClient.Send(myMessage)  
 29     */ 
 30         End Using  
 31     End Sub  
 32 End Class

przy czym jeśli ktoś chce wypróbować i stworzyć własną bibliotekę, w powyższym kodzie należy podmienić pola: TWOJ_SERWER_SMTP, TWOJ_ADRES_EMAIL, TWOJE_HASLO na wzór taki jak kilka linijek dalej w zakomentowanym przeze mnie fragmencie kodu. Plik zapisujemy z wybraną nazwą i rozszerzeniem odpowiadającym danemu językowi platformy .NET – mój jest w VB.NET więc został nazwany SendEmail.vb
Tutaj dodam, że konfiguracja powiadomień opiera się tutaj na publicznym serwisie o2.pl (być może nie na każdym publicznym serwisie działa, ale na o2.pl działa bez zarzutu – podobno na Gmail też).
Jeżeli przeprowadzamy konfigurację wewnątrz organizacji, wówczas mamy zazwyczaj do dyspozycji własny serwer smtp – wtedy najlepiej podmienić ten kawałek kodu:

  1 mySmtpClient = New SmtpClient("poczta.o2.pl")  
  2 mySmtpClient.Credentials = New NetworkCredential
  3 ("szymon_plaszczyk@o2.pl","HASLO_ZOSTALO_UKRYTE")

Na następujący:

  1 mySmtpClient = New SmtpClient("NASZ_SERWER_SMTP") 
  2  mySmtpClient.Credentials =  
  3       CredentialCache.DefaultNetworkCredentials

Spowoduje to, że domyślnie podczas wywołania procedury służącej do wysłania e-maila używane będą credentiale z Windows – brak konieczności wyspecyfikowania ich w pliku kompilowanym do .dll jest pożądany ze względów bezpieczeństwa.

2) W kolejnym kroku należy stworzony plik skompilować spod linii poleceń przez wywołanie w lokalizacji, gdzie znajduje się kompiler języka, w którym napisany jest kod (u nas kompilator VB zlokalizowany w C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727) polecenia vbc dla języka VB.NET, jak w tym przypadku lub csc dla kodu napisanego w C#, wskazanie /target:library co spowoduje utworzenie na wyjściu pliku z rozszerzeniem .dll zamiast domyślnego .exe oraz lokalizacji docelowej dla pliku.dll np. w sposób taki jak przedstawiono na rysunku poniżej

3) Następnie na podstawie stworzonej kilka chwil wcześniej biblioteki należy utworzyć assembly oraz procedurę wykorzystywaną później do wysyłania e-maili. Przykładowy kod zaprezentowano poniżej.

USE [master]
GO
sp_configure 'clr enabled',1;
GO
RECONFIGURE;
GO

ALTER DATABASE msdb
SET TRUSTWORTHY ON
GO

USE msdb
GO
CREATE ASSEMBLY SendEmail FROM 'C:\SendEmail.dll' WITH PERMISSION_SET = UNSAFE GO
CREATE PROCEDURE [dbo].[spSendMail] @recipients  [nvarchar](4000), @subject  [nvarchar](4000), @FROM  [nvarchar](4000), @body  [nvarchar](4000) WITH EXECUTE AS  CALLER AS  EXTERNAL NAME [SendEmail].[StoredProcedure].[spSendMail]

4) Najprostsza forma wywołania procedury może odbyć się w następujący sposób.

EXEC [msdb].[dbo].[spSendMail] 
@recipients = 'szymon.plaszczyk@atena.pl',
@subject = 'Testowy Email z SQL Express przy użyciu CLR'@from = 'szymon_plaszczyk@o2.pl',
@body = 'sprawdzamy wykorzystanie CLR do wysyłki maili'

A rezultat możemy zobaczyć ponownie na firmowej skrzynce …



PODSUMOWANIE

Otrzymywanie powiadomień jest bardzo istotnym elementem w pracy administratorów i jak widać również wersję SQLEXPRESS można „zmusić” do ich wysyłania i dostarczania na nasze konto pocztowe. Zaprezentowany przykład jest jedynie prostym wariantem tego, co można osiągnąć, mając możliwość wysyłania e-maili z motoru bazy danych w formacie HTML. Doskonałym przykładem wykorzystującym tą funkcjonalność może być używana w zespole administratorów baz danych procedura wyświetlająca łańcuch blokad, bazująca na niesamowicie użytecznej procedurze sp_send_dbmail – rezultat jej działania (pewne dane zostały celowo usunięte) został przedstawiony poniżej.

A to i tak nie wyczerpuje wszystkich możliwości …

Dodaj komentarz

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