SELECT Explanation, Example FROM Pro.Knowledge
FacebookRSS

Konfiguracja Database Mail w SQL Server

Mail_smallWysyłanie powiadomień mailowych z poziomu serwera bazodanowego, jest niezwykle przydatną funkcjonalnością. Typowe zastosowanie to automatyczne przekazywanie informacji o statusie zadań administracyjnych, procesów utrzymaniowych czy ETL (zazwyczaj błędach lub ostrzeżeniach).

Możesz je wykorzystać równie dobrze w typowych aplikacjach klienckich. Informować użytkowników o pojawiających się zdarzeniach np. zleceniach, zmianach statusów czy przekroczeniu określonych wskaźników, bezpośrednio z poziomu SQL Server.

W artykule tym, znajdziesz opis koncepcji Database Mail w SQL Server. Zaprezentuję także przykładową konfigurację za pomocą procedur wbudowanych. Co istotne, Database Mail jest dostępny także w darmowej wersji SQL Server Express, choć bez graficznego wizzarda i widocznej osobnej gałęzi obiektów w Management Studio.


Architektura Database Mail

Konfiguracja, widoki systemowe oraz procedury składowane dotyczące Database Mail, znajdują się są w bazie systemowej MSDB. Działanie oparte jest na usłudze Brokera (Service Broker), czyli wbudowanemu w SQL Server mechanizmowi do przesyłania wiadomości, kolejkowania i obsługi zdarzeń.

Wysyłanie maili, z punktu widzenia użytkownika, sprowadza się do wywołania procedury składowanej sp_send_dbmail, która wrzuca wiadomość do kolejki. Pojawienie się w niej nowego rekordu, wyzwala proces odpowiedzialny za fizyczne wysłanie maila. Tym procesem jest zewnętrzny program DatabaseMail.exe, uruchamiany automatycznie przez proces SQL Server.

Z konfiguracją Database Mail, związane są obiekty kont pocztowych i profili – przedstawione na poniższym schemacie :

Database_Mail_00

Profile – mogą mieć przypisane jedno lub wiele kont pocztowych z których będą wysyłane wiadomości. Jest to proste zwiększenie niezawodności, na wypadek gdy podstawowy serwer (konto) SMTP jest nie dostępny.

W momencie przypisania konta do profilu (procedura sysmail_add_profileaccount_sp), określany jest parametr sequence_number (priorytet) zgodnie z którym, następuje próba wysłania wiadomości. Najpierw przez konto z najniższym sequence numer. W przypadku niepowodzenia, podejmowana jest próba wysłania przez kolejne.

Konta pocztowe, mogą być przypisane do jednego lub wielu profili (współdzielone) np. konto backupowe.

Każdemu użytkownikowi, możemy nadać uprawnienia (proc. sysmail_add_principalprofile_sp
) do wielu profili pocztowych. Jeden z nich, może być określony jako domyślny – wtedy nie trzeba będzie podawać jawnie nazwy profilu w momencie wywoływania procedury wysyłającej maila. Nadanie uprawnień do profilu nie jest jednoznaczne z tym, że już użytkownik będzie mógł wysyłać maile. Musi mieć także uprawnienia do wykonywania procedury msdb.dbo.sp_send_dbmail czyli dostęp do bazy MSDB ze stosownymi uprawnieniami.

Do zarządzania uprawnieniami w tym zakresie, istnieje dedykowana rola w bazie MSDB DatabaseMailUserRole. Najwygodniej jest dopisać do niej każdego użytkownika (grupę), która ma mieć możliwość wysyłania i dostępu do widoków związanych z mailami (np. sysmail_allitems). Widoki te są o tyle bezpieczne, że użytkownik będzie widział tylko te wiadomości, których jest autorem.

Wprowadzona architektura zależności pomiędzy kontami, profilami a użytkownikami daje elastyczność i jest łatwo skalowalna.


Konfiguracja Database Mail w SQL Server

Pierwszym krokiem, jest przygotowanie instancji SQL Server, czyli włączenie obsługi Database Mail.

sp_configure 'show advanced options',1
reconfigure
go
 
sp_configure 'Database Mail XPs',1;
reconfigure
go

Jak już wspominałem, Database Mail bazuje na usłudze Brokera i musi on być również aktywny w bazie MSDB. Jego status możemy sprawdzić za pomocą :

select name ,   is_broker_enabled 
from sys.databases
where name = 'msdb'

Database_Mail_01
W przypadku gdy nie jest aktywny (0), należy go włączyć :

USE master
GO
ALTER DATABASE msdb SET  ENABLE_BROKER WITH NO_WAIT
GO

Teraz, możemy zabrać się za właściwą konfigurację profili i skojarzonych z nimi kont pocztowych. Od razu podpowiem, że większość problemów z wysyłaniem maili, jest związane właśnie z błędami w tym kroku. Należy upewnić się co do parametrów konta SMTP , poprzez które chcemy wysyłać maila.

Na początek dodam dwa nowe konta – na serwerach home.pl oraz Gmail. Konfiguracja portów, konieczność uwierzytelniania SSL są z reguły identyczne dla większości providerów.

Use MSDB
GO
 
EXECUTE msdb.dbo.sysmail_add_account_sp
     @account_name = 'Gmail',
     @email_address = 'mojekonto@gmail.com',
     @display_name = 'Gmail Public', 
     @description = 'Konto Main', 
     @mailserver_name = 'smtp.gmail.com',
     @mailserver_type = 'SMTP',
     @port = 587,
     @username = 'mojekonto@gmail.com',
     @password = 'haslomaslo', 
     @enable_ssl = 1;
 
EXECUTE msdb.dbo.sysmail_add_account_sp
     @account_name = 'SQLPEDIA',
     @email_address = 'adresemail@sqlpedia.pl',
     @display_name = 'backup', 
     @description = 'Konto Main', 
     @mailserver_name = 'domena.home.pl',
     @mailserver_type = 'SMTP',
     @port = 587,
     @username = 'adresemail@sqlpedia.pl',
     @password = 'haslomaslo', 
     @enable_ssl = 1;
 
-- weryfikacja kroku
select * from sysmail_server

Kolejnym krokiem, jest utworzenie profili. Zróbmy dwa – jeden będzie wykorzystywany jako publiczny, drugi prywatny dla procesów utrzymaniowych.

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Public',
@description = 'Dla wszystkich userów' ;
 
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Priv',
@description = 'Tylko admin' ;
 
-- weryfikacja kroku
select * from sysmail_profile

Database_Mail_02
Teraz trzeba powiązać konta pocztowe z profilami. Profil publiczny będzie posiadał jedno konto a prywatny dwa, podstawowe i backupowe.

-- Public
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Public', -- nazwa profilu
@account_name = 'Gmail',  -- nazwa konta
@sequence_number =1 ;
 
-- Priv
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Priv',
@account_name = 'SQLPEDIA',
@sequence_number =1 ;
 
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Priv',
@account_name = 'Gmail',
@sequence_number =2 ;
 
-- weryfikacja kroku
select * from sysmail_profileaccount

Database_Mail_03

Ostatnią rzeczą która trzeba wykonać aby zwykły użytkownik mógł wysyłać maile, jest nadanie stosownych uprawnień. Nadajmy więc uprawnienia do profilu publicznego dla wszystkich (Principal_id = 0 to rola public w bazie MSDB).

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Public',
@principal_id = 0,
@is_default = 1 ;

Poza uprawnieniami do profilu, zwykły użytkownik musi mieć jeszcze dostęp do bazy MSDB oraz przypisanie do roli DatabaseMailUserRole

USE [msdb]
GO
CREATE USER [UserName] FOR LOGIN [UserName]
GO
USE [msdb]
GO
ALTER ROLE [DatabaseMailUserRole] ADD MEMBER [UserName]
GO

I to już wszystko. Teraz możemy na wysłać maila testowego.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Public',
@recipients = 'JakisAdresEmail@wp.pl',
@subject = 'SQL Server Databse Mail - Test',
@body = 'Dzieńdobry i dowidzenia ...';

Weryfikacja poprawności działania i typowe problemy

Do weryfikacji poprawności wysłania maili mamy dostępnych kilka widoków systemowych. Szczególnie użyteczne są dwa :

  • msdb.dbo.sysmail_allitems, pokazujący aktualny status i zawartość wiadomości w kolejce.
  • msdb.dbo.sysmail_event_log – tu znajdziemy szczegółowe informacje o błędach, odpowiedziach serwera SMTP.
USE msdb
go
 
SELECT sent_status, mailitem_id, profile_id, recipients,
	subject, body
FROM sysmail_allitems
ORDER BY mailitem_id desc
 
SELECT * FROM sysmail_event_log
ORDER BY log_id  desc

Database_Mail_04
W przypadku pojawienia się problemów z wysyłaniem wiadomości sprawdź przede wszystkim :

  1. Informację o błędach, odpowiedziach serwera w widoku sysmail_event_log.
  2. Wiem, że to banał ale literówki, nieprawidłowa konfiguracja konta to naprawdę najczęstsza przyczyna problemów (bywa że username to pełna nazwa adresu email ;)).
  3. Sprawdź czy usługa brokera jest aktywna.
  4. Sprawdź status kolejki – powinna być STARTED.

    EXECUTE msdb.dbo.sysmail_help_status_sp

    Jeśli nie jest, możesz uruchomić ją za pomocą

    EXECUTE msdb.dbo.sysmail_start_sp

  5. Ustawienia firewall lub co bardziej kłopotliwe – szczególnie w korporacjach, blokowanie portów pocztowych 587. Łatwo to zweryfikować, telnetując się na np. na port 578 serwera SMTP. Jeśli się nie powiedzie, to znaczy że ruch na dany port jest wycinany.
    Database_Mail_06
  6. Szczególne ustawienia serwerów pocztowych SMTP, np. GMAIL, w których podstawowa autentykacja jest traktowana jako niebezpieczna.

    W Gmail, wprowadzono standard Oauth 2.0 i jeśli chcemy korzystać z Basic Authentication musimy jawnie określić, aby z naszego konta mogły korzystać tzw. mniej bezpieczne aplikacje.
    Więcej na ten temat znajdziesz tutaj : https://support.google.com/accounts/answer/6010255?hl=en
    Akceptację aplikacji mniej bezpiecznych, możesz ustawić po zalogowaniu do GMail tutaj : https://www.google.com/settings/security/lesssecureapps

  7. Sprawdź kto jest ownerem bazy systemowej MSDB. Jeśli jakiś „zwykły” user – warto zmienić na SA :)

    select name, suser_sname(owner_sid) as Owner  
    from sys.databases
     
    USE msdb
    GO
     
    EXEC sp_changedbowner [sa]
    GO


Źródła MSDN :
http://technet.microsoft.com/en-us/library/ms175887(v=SQL.105).aspx

Powyższy artykuł dotyczy wersji SQL Server 2008 R2 – 2014

Automatyzacja zadań w SQL Server Express

Firma Microsoft, podobnie jak inni producenci najważniejszych, komercyjnych systemów bazodanowych (ORACLE, IBM) udostępnia darmową (również do zastosowań komercyjnych) wersję serwera baz danych – SQL Server, edycja Express.

Jest ona okrojona w stosunku do pełnych edycji, przede wszystkim w zakresie możliwości wykorzystania wysoko wydajnego, skalowalnego środowiska, a także jeśli chodzi o zakres funkcjonalności. Nie mniej jednak, od strony możliwości silnika bazodanowego – darmowa wersja Express, sprawdza się znakomicie w wielu zastosowaniach komercyjnych. Pełne porównanie możliwości poszczególnych edycji SQL Server 2012 znajdziesz tutaj.

Najbardziej dotkliwe ograniczenia, związane są z maksymalnym rozmiarem bazy (w SQL Server Express 2012 jest to max 10 GB/bazę) oraz okrojonymi zasobami hardware, które będą wykorzystane do obsługi procesu serwera (max 1 procesor lub 4 rdzenie oraz max 1GB pamięci RAM). Są one szczególne dotkliwe i nieakceptowalne w większych środowiskach, jednak SQL Server Express jest bardzo dobrym rozwiązaniem do małych, mniej wymagających środowisk. Poza tym nic nie stoi na przeszkodzie, aby w miarę rozwoju firmy, przejść do wersji mocniejszej – płatnej.

Poza ograniczeniami związanymi z zasobami, jednym z ograniczeń funkcjonalnych w porównaniu z płatnymi wersjami, jest brak SQL Server Agent. Jest to usługa odpowiedzialna m.in. za wykonywanie zaplanowanych działań. Obojętnie czy będą to zadania (JOBy) serwisowe, administracyjne czy np. synchronizujące, replikujące dane (import, export) – w SQL Server Express musimy użyć innych metod do automatycznego uruchamiania zaplanowanych procesów.

Automatyzacja zadań w środowisku bazodanowym jest bardzo przydatna i praktycznie w każdym wdrożeniu jest stosowana (chociażby do robienia kopii zapasowych). Jednym ze sposobów na obejście braku SQL Agent w SQL Server Express, jest wykorzystanie narzędzia linii poleceń SQLCMD, uruchamianego za pomocą standardowego harmonogramu zadań w Windows (Windows Scheduler). Do realizacji automatyzacji, można zaprząc również usługę Brokera (Service Broker), jednak nie będę tej metody tutaj opisywał.

Artykuł ten jest poświęcony rozwiązaniu bazującego na połączenia możliwości SQLCMD i Harmonogramu zadań Windows.


SQLCMD czyli dostęp do SQL Server z linii poleceń

SQLCMD to proste narzędzie, za pomocą którego możemy łączyć się z instancją SQL Server i wykonywać dowolne polecenia T-SQL. Jest następcą OSQL, znanego z SQL Server 2000 i powinno być używane zawsze, jeśli chcemy wykonywać jakiekolwiek polecenia T-SQL z linii poleceń (w SQL Server 2012, OSQL nie jest już dostępny).

Połączenie do bazy SQL Server za pomocą SQLCMD

Za pomocą SQLCMD możemy łączyć się z wybranym serwerem i wykonywać polecenia, kwerendy T-SQL w trybie interaktywnym lub automatycznym. Użycie tego toola jest proste, wystarczy wskazać instancję, sposób uwierzytelniania i już możemy połączyć się z SQL Server. Pełen zakres parametrów, które możemy użyć, wyświetli standardowa flaga -? (tu SQLCMD w wersji 10.5 czyli z SQL Server 2008 R2):

C:\>sqlcmd -?

sqlcmd_options

Jeśli instancja SQL Server do której chcesz się połączyć jest nazwana i chcesz użyć zintegrowanego uwierzytelniania Windows, trzeba zastosować flagi wskazujące serwer : –S server_name oraz tryb autentykacji –E (trusted connection). W ten prosty sposób, uzyskasz połączenie z serwerem w ramach autentykacji Windows (aktualnego konta). Będziesz połączony ze „swoją” domyślną bazą danych, właściwą dla konta w którego kontekście się logujesz.

C:\sqlcmd -S MyServer\SQLEXPRESS -E

W trybie interaktywnym, możemy wprowadzać polecenia SQL linia po linii, aby je wykonać użyj komendy GO.

sqlcmd_interactive

Nie będę tu opisywał wszystkich możliwości narzędzia SQLCMD, bo nie jest to przedmiotem tego artykułu, ograniczę się tylko do tego co istotne w kontekście automatyzacji, wykonywania zadań.

Wykonywanie skryptów T-SQL za pomocą SQLCMD

Polecenia T-SQL możemy wykonywać za pomocą SQLCMD w trybie interaktywnym lub nazwijmy to automatycznym (bez interakcji użytkownika). W naszym scenariuszu, interesujące będą dwie opcje.

Opcja QUERY : czyli flaga -Q – umożliwia wykonanie dowolnej kwerendy (Query) za pomocą SQLCMD bez konieczności wchodzenia w tryb interaktywny. Po prostu określona kwerenda lub batch (może to być przecież szereg komend T-SQL) zostanie wykonana na danym serwerze w kontekście konta określonego w parametrach połączenia.

C:\sqlcmd -S MyServer\SQLEXPRESS -E -Q "SELECT getdate()"

sqlcmd_query

Opcja INPUT FILE : flaga – i – pozwala na wykonanie skryptu T-SQL, zapisanego w pliku wejściowym (input file). Może to być dowolny skrypt zapisany w pliku tekstowym, wykonujący określone zadania. Dla prostego przykładu, załóżmy że nasz skrypt będzie wykonywał prostą operację backupu. Zawartość pliku backup_script.sql niech wygląda następująco :

DECLARE @FileName varchar(100),  @BackupName varchar(100)
 
SET @FileName = N'D:\Backups\TEST_FULL_BACKUP_' + 
	Replace(Convert(varchar(10),getdate(),120),'-','_') + '.bak'
 
SET @BackupName = N'Test-Full Database Backup from ' + 
          Convert(varchar(10),getdate(),120)
 
-- skrypt uruchamiany codziennie, unikalna nazwa pliku
PRINT @FileName + char(10) + @BackupName + char(10) + REPLICATE('-',50)
 
BACKUP DATABASE [TEST] TO DISK = @FileName 
	WITH FORMAT, 
	NAME = @BackupName

Dwa słowa komentarza. Skrypt ten będzie tworzył pełen backup bazy danych TEST, zapisywał go do pliku o nazwie zawierającej bieżącą datę. Taka forma może być przydatna, jeśli zadanie to będzie wykonywane np. raz dziennie i chcemy, żeby każdy backup był w osobnym pliku (stąd konieczność zapewnienia unikalnej nazwy).
Wywołanie teraz SQLCMD z parametrem –i (input file) będzie następujące :

C:\sqlcmd -S MyServer\SQLEXPRESS -E -i D:\temp\backup_script.sql

sqlcmd_input_file

Zatem wiemy już w jaki sposób działa SQLCMD i jak możemy wywołać dowolne skrypty T-SQL z linii poleceń. Jedyne co nam pozostało do realizacji celu automatyzacji zadań w SQL Server Express to wyzwalanie skryptów automatyczne. Do tego celu idealnie nadaje się Harmonogram Zadań Windows.


Planowanie zadań w Harmonogramie zadań (Windows Scheduler)

Windows Scheduler to usługa pozwalająca na zdefiniowanie zadań w ramach serwera / stacji roboczej, pracującej pod kontrolą systemu operacyjnego Windows. Zdefiniowanie nowego zadania jest bardzo proste. Sprowadza się w zasadzie do wskazania programu lub skryptu, który będzie uruchamiany zgodnie z określonym harmonogramem. Możemy także określić konto (login) w kontekście którego dane zadanie zostanie wykonane.

W naszym scenariuszu, będziemy chcieli wywołać skrypt backupu bazy TEST, za pomocą już znanej składni SQLCMD oraz przygotowanego wcześniej skryptu T-SQL (plik backup_script.sql). Ponadto, będziemy chcieli dokonać kompresji (zip) tegoż i na koniec zrobimy mały porządek. Skasujemy wszystkie starsze niż 14 dni kopie zapasowe oraz inne, niespakowane zipem pliki w katalogu backupów.

Na początek utwórzmy skrypt (batch) o nazwie backup_bazy_TEST.bat. Skrypt ten będzie odpalany automatycznie codziennie. Zawartość pliku :

sqlcmd –S SQLEXPRESS -E –iD:\temp\backup_script.sql
set dt=%DATE%
set dt = %dt:-=_%
zip d:\backs\TEST_FULL_BACKUP_%dt:-=_%. zip d:\backs\TEST_FULL_BACKUP_%dt:-=_%.bak
Forfiles -p D:\backs\ -s -m TEST_FULL_BACKUP*.zip -d -7 -c "cmd /c del /q @path"
del D:\backs\*.bak

To co najważniejsze, dzieje się tak naprawdę w pierwszej linii– uruchomienie skryptu backupu bazy. Następnie przypisanie do zmiennej dt informacji o bieżącej dacie, aby móc odpowiednio obsłużyć (spakować), utworzony plik backupu. Na koniec stosuję narzędzie FORFILES do skasowania wszystkich plików backupu starszych niż 14 dni. Oczywiście można użyć kompresje już w trakcie jego tworzenia, potraktuj ten przykład czysto edukacyjnie – chodziło mi o pokazanie szeregu działań w zadaniu.

Mamy więc plik skryptu, teraz trzeba zaplanować zadanie w ramach którego będzie on uruchamiany.

Poniżej kilka kluczowych screenów.

  • Dodanie nowego zadania w harmonogramie zadań
    Harmonogram_zadan_Plan_backupu_bazy_01
  • Określenie akcji (jaki program lub skrypt ma być uruchomiony)
    Harmonogram_zadan_Plan_backupu_bazy_02
  • Określenie harmonogramu (Wyzwalacze)
    Harmonogram_zadan_Plan_backupu_bazy_03

Podsumowanie

Pomimo swoich ograniczeń funkcjonalnych, w SQL Server Express jesteśmy w stanie wykonywać dowolne zadania automatycznie. Pokazany w tym przykładzie skrypt backupu, mógłby być w prosty sposób rozszerzony o pełne logowanie samego procesu, czy backup zakończył się powodzeniem, ile trwał, wysyłać powiadomienia mailowe w przypadku wystąpienia błędu.