Administrowanie serwerem bazy

danych MS SQL 2005

(wykłady 15 godz.)

Dr inż. Andrzej Czerepicki

WSM, 2009

Wykład 6.

Automatyzacja administrowania

SQL Server.

SQL Server.

Usługą SQL Server Agent.

Zadania, Operatorzy i Powiadomienia.

Konfigurowanie poczty e-mail.

Plan wykładu

• 1. SQL Server Agent

• 2. Konfiguracja SQL Server Agent

• 3. Zadania SQL Server Agent

• 4. Harmonogramy wykonania zadań

• 5. Alerty SQL Server Agent

• 6. Operatorzy SQL Server Agent

• 7. Poczta elektroniczna SQL Server 2005

• 8. Database Mail

Wstęp

• Zadania administracyjne często są powtarzalne!

– Wykonanie kopii zapasowej

– Sprawdzenie integralności bazy

– Przebudowa indeksów, etc.

– Przebudowa indeksów, etc.

• SQL Server wymaga szybkiej ingerencji

administratora w sytuacjach krytycznych:

– Awaria bazy danych lub części systemu

– Nieuprawniony dostęp

– Modyfikacja istotnych tabel danych

Wstęp (c.d.)

• Rutynowe zadania powtarzające się oraz

śledzenie wydarzeń w systemie można i
trzeba automatyzować

• Czas roboczy administratora należy cenić i nie

• Czas roboczy administratora należy cenić i nie

poświęcać go dla rozwiązania zadań, które
mogą być wykonane automatycznie

Wstęp (c.d.)

• Metody automatyzacji wykonania zadań

administracyjnych:

– SQL Server Agent

– Aplikacje skryptowe wykorzystujące SQL DMO/SMO

– Aplikacje skryptowe wykorzystujące SQL DMO/SMO

oraz technologię COM (VBScript, C++, .NET, Java
itp.)

• Nie wszystkie wersje SQL Server posiadają możliwość

działania z SQL Server Agent (np. Express), więc niekiedy
może to być jedynym wyjściem

1. SQL Server Agent

• SQL Server Agent – usługa SQL Server

przeznaczona do zarządzania obiektami:

– Zadania (Jobs)

• Pakiety poleceń mające własną nazwę, które mogą być

wykonywane ręcznie lub automatycznie zgodnie z

wykonywane ręcznie lub automatycznie zgodnie z
harmonogramem

– Alerty (Alerts)

• Czynności wykonywane po wystąpieniu pewnego

zdarzenia w SQL Server lub systemie Windows

– Operatorzy (Operators)

• Konta operatorów, na które są rozsyłane informacje o

wydarzeniach w systemie

1. SQL Server Agent (c.d.)

• Przed rozpoczęciem konfigurowania SQL

Server Agent należy:

– Upewnić się że usługa jest uruchomiona
– Uprawnienia konta SQL Server Agent są nie

– Uprawnienia konta SQL Server Agent są nie

mniejsze niż uprawnienia konta SQL Server

• Zazwyczaj są stosowane uprawnienia

Administratora

– Wykonać kopię zapasowa bazy danych

msdb (przechowuje konfigurację SQL Server
Agent)

2. Konfiguracja SQL Server Agent

• SSMS (preferowany)

– [Server] | SQL Server Agent | Properties

• T-SQL

– Wyłącznie za pomocą procedur

– Wyłącznie za pomocą procedur

składowanych (sp_set_sqlagent_...)

– Praktycznie jest wykorzystywany tylko do

uruchomienia skryptów wcześniej
wygenerowanych przez SSMS

2. Konfiguracja SQL Server Agent (c.d.)

• 2.1. Zakładka [General]

– Error log: ścieżka dostępu do pliku dziennika

– Net send recipient: adresat otrzymujący

powiadamiania w postaci net send (wymaga

powiadamiania w postaci net send (wymaga
usługi Messenger, która domyślnie jest
wyłączona); ostrożnie z ilością wysyłanych
komunikatów, która potrafi zablokować
komputer odbiorcy (spam)

– Write OEM file: format pliku wyjściowego

ANSI (OEM) zamiast Unicode

2. Konfiguracja SQL Server Agent (c.d.)

• 2.2. Zakładka [Advanced]

– Forward events to a different server: skierowanie

zdarzeń na inny serwer SQL; używamy tylko w
przypadku zarządzania kilkoma serwerami z jednego
stanowiska

stanowiska

– Events: określamy jakie zdarzenia będą skierowane

na inny serwer (wszystkie, nieobsłużone bądź w
powyżej pewnego krytycznego poziomu)

– Idle CPU Condition: warunki przy spełnieniu których

można uznać, iż serwer jest w trybie bezczynności

2. Konfiguracja SQL Server Agent (c.d.)

• 2.3. Zakładka [Alert system]

– Ogólne ustawienia systemu alertów (bez możliwości

wskazania konkretnych odbiorców!)

– Enable mail profile: zezwala na powiadamianie

operatora za pomocą e-mail

operatora za pomocą e-mail

• Wymaga skonfigurowanej usługi Database Mail (rozdział 7)
• Wybieramy Database Mail

– Pager e-mails: konfigurujemy numery telefoniczne

odbiorców (SMS) oraz treść powiadomień

• Fail-safe operator: „operator ostatniej nadziej” (kto będzie

odpowiadał za awarię jeśli zdarzyła się ona poza godzinami
pracy wszystkich operatorów)

2. Konfiguracja SQL Server Agent (c.d.)

• 2.4. Zakładka | [Job system]

– Ogólne ustawienia wykonywanych zadań

– [Job step proxy account]

– [Job step proxy account]

•możliwość zdefiniowania dla zadania

uprawnień innych niż domyślne

•Głownie w celu kompatybilności ze

starszymi wersjami SQL Server

2. Konfiguracja SQL Server Agent (c.d.)

• 2.5. Zakładka [Connection]

– Parametry podłączenia SQL Agent do SQL

Server

– Zmieniamy tylko jeśli działamy w

– Zmieniamy tylko jeśli działamy w

niestandardowej konfiguracji

• 2.6. Zakładka | [History]

– Zarządzamy przechowaniem dzienników SQL

Server Agent w zależności od rozmiaru plików
oraz przedawnienia

3. Zadania SQL Server Agent

• Zadanie (ang. Job) – pakiet poleceń SQL

Server, posiadający własną nazwę i
składający się z poszczególnych kroków
(ang. Steps)

(ang. Steps)

Job

Step 1

Step 1

Step N

3. Zadania SQL Server Agent (c.d.)

• Zadanie może być wykonane:

– Ręcznie (na żądanie użytkownika)

• Można wykonać zadanie w całości lub tylko

wybrany jego krok

– Automatycznie (zgodnie z harmonogramem)

• Można wykonać tylko cały pakiet

– W odpowiedzi na zdarzenie w systemie SQL

Server (Alert)

3. Zadania SQL Server Agent (c.d.)

• Każdy krok zadania może wykonywać jedną z

wymienionych niżej operacji:

ActiveX

Operating

ActiveX

Script

T-SQL

Operating

System

SSIS

Package

OLAP

Command

Replication

Commands

3. Zadania SQL Server Agent (c.d.)

• A) ActiveX Script

– Bardzo funkcjonalny sposób wykonania

praktycznie dowolnej czynności

– Wykonanie zadań odbywa się za pomocą:

• Obiektowych modeli SQL Server(SMO, DMO, WMI)

• Obiektowych modeli SQL Server(SMO, DMO, WMI)
• Skryptowych języków programowania (VBScript,

JavaScript, lub innych – pod warunkiem posiadania
interpretatora)

– Wykonanie zadań na lokalnym lub zdalnym (!)

komputerze

• Windows Script Host, Scripting Runtime, WMI

3. Zadania SQL Server Agent (c.d.)

• B) Transact-SQL Script

– Bardzo popularny i uniwersalny sposób

definiowania etapu zadania w postaci
polecenia SQL

– Typowe zastosowanie

– Typowe zastosowanie

• kopie zapasowe
• Przebudowa indeksów
• Sprawdzenie integralności bazy danych
• Inne czynności obsługowe bazy danych

3. Zadania SQL Server Agent (c.d.)

• C) Operating System (CmdExec)

– Prosty sposób wykonania polecenia

systemu operacyjnego, bez sięgania po
skrypty ActiveX

– Typowe zastosowanie:

– Typowe zastosowanie:

• kopiowanie plików
• mapowanie dysków sieciowych
• Inne proste polecenia systemu operacyjnego

3. Zadania SQL Server Agent (c.d.)

• D) SQL Server Integration Package

– Wykonuje pakiety modułu SSIS usług

integracyjnych SQL Server

• E) SQL Server Analysis Services Command

– Zapytania do bazy danych przetwarzania

informacji analitycznych OLAP

• F) Replication …..

– Szereg poleceń związanych z zarządzaniem

replikacją bazy danych

3.1. Sposoby zarządzania zadaniami

• SSMS

– Upewnić się że usługa SQL Server Agent

działa

– Object Explorer | SQL Server Agent | Jobs

• T-SQL (procedury składowane)

• T-SQL (procedury składowane)

– Bardzo skomplikowany i praktycznie rzadko

używany sposób

• Głównie jako skrypt wcześniej przygotowany

3.2. Tworzenie nowego zadania

• Jobs | New Job | General

• Istotne właściwości:

– Name: nazwa zadania

– Owner: właściciel zadania (uprawnienia oraz

filtrowanie)

– Category: kategoria zadania (parametr opisowy)

– Description: opis zadania

– Enabled: możliwość zaprzestania wykonania

zadania poprzez jego wyłączenie

• Również można zablokować harmonogram zadania

3.3. Przeniesienie zadania na inny

serwer SQL

• Wygenerować skrypt SQL i wykonać go

na docelowym serwerze

3.4. Dodanie nowych kroków zadania

• Job | Steps | New …

– Step Name: nazwa kroku

– Type: rodzaj wykonywanej operacji (p. dalej)

– Run As: konto z którego będzie wykonana

– Run As: konto z którego będzie wykonana

operacja (wymaga definiowania tzw. Proxy)

– Database: baza danych której dotyczy operacja

– Command: skrypt wykonywalny

• W zależności od rodzaju może to być T-SQL, Visual

Basic script, JavaScript itp..

3.5. Zarządzanie wykonaniem zadań

• Wykonanie każdego kroku zadania może

być uzależnione od wyników wykonania
poprzednich

• Dla każdego kroku można ustawić tę

opcję indywidualnie

opcję indywidualnie

– Job | Steps | Properties
– Zakładka [Advanced]

• On success action
• On failure action
• Ilość prób wykonania zadania

3.6. Powiadomienie o wykonaniu

zadania

• Zakładka [Job | Notifications ]

– E-mail, Page, Net Send

• Dostarczenie wiadomości przez e-mail, na

pager oraz za pomocą polecenia net send

– Kiedy wysyłamy:

– Kiedy wysyłamy:

• Przy wystąpieniu błędu (job fails)
• Przy pomyślnym zakończeniu (job success)
• Zawsze (job complete)

– Można również zapisać informację do

dziennika zdarzeń Windows oraz usunąć
zadanie po wykonaniu

3.7. Bezpieczeństwo wykonania zadań

• Domyślnie zadania są wykonywane z

uprawnieniami konta SQL Server Agent

• Można skonfigurować indywidualny poziom

uprawnień dla każdego kroku zadania:

– Stworzyć nowy obiekt Credential

– Stworzyć nowy obiekt Credential

• Server | Security | Credentials

– Stworzyć nowy rekord Proxy

• SQL Server Agent | Proxies

– Przypisać etap wykonania zadania do Proxy

• Job | Step | Run As …

3.7. Bezpieczeństwo wykonania

zadań (c.d.)

• Dla etapów typu T-SQL można zmienić

kontekst wykonania za pomocą:

– EXECUTE AS (T-SQL)

– Na zakładce [Advanced] w polu [Run as user]

– Na zakładce [Advanced] w polu [Run as user]

obiektu Proxy

• Operacja jest dozwolona jeśli właściciel

zadania posiada uprawnienia sysadmin

3.8. Historia wykonania zadań

• Dzienniki SQL Server Agent

– Przez menu kontekstowe zadania [View History]
– Za pomocą Job Activity Monitor

• SQL Server Agent | Context menu | View Job

Activity

Activity

• Bardzo szczegółowa informacja:

– sp_help_jobactivity

• Za pomocą raportowania poszczególnych

etapów zadania

– Zapis wyników wykonania etapu zadania do pliku

(dla typów CmdExec, T-SQL)

4. Harmonogramy

• Harmonogramy (ang. Schedules) –

obiekty SQL Server Agent pozwalające na
automatyczne wykonanie zadań (Jobs)

pn

wt

sr

cz

pt

….

BACKUP
FULL

BACKUP
LOG

BACKUP
LOG

BACKUP
DIFF

INDEX
REBUILD

…

COPY
DATABASE

….

8:00

10:00

22:15

4. Harmonogramy (c.d.)

• Harmonogramy są osobnymi obiektami

w bazie danych, niezależnymi od zadań

– Dla każdego serwera można zdefiniować

kilka podstawowych harmonogramów a

kilka podstawowych harmonogramów a
następnie przypisać do nich dowolną ilość
zadań

•właściciel zadania musi być również

właścicielem harmonogramu

4. Harmonogramy (c.d.)

• Zarządzanie harmonogramami:

– Jobs | Context menu | Manage schedules

• Obsługa harmonogramów wymaga

uruchomionej usługi SQL Server Agent

uruchomionej usługi SQL Server Agent

• Dla jednego zadania można

skonfigurować kilka harmonogramów

• Harmonogram może być wyłączony

– Np. wyłączenie kopiowania zapasowego

przez weekend

4. Harmonogramy (c.d.)

• Rodzaje harmonogramów:

– Recurring (powtarzający się)

– One time (jednorazowy)

– Start automatically … (uruchamiający się

– Start automatically … (uruchamiający się

przy starcie SQL Server Agent)

– Start whenever CPU become idle

(uruchamiający się kiedy obciążenie
procesora spada poniżej określonego
poziomu

• zob. zakładkę SQL Server Agent | Advanced

5. Alerty SQL Server Agent

• Alerty (alerts) pozwalają na wywołanie

zadań (jobs) w odpowiedzi na określone
zdarzenia SQL Server

Database

Alert

Job

5.1. Rodzaje alertów SQL Server

Agent

SQL Server

Event

• Predefiniowane

zdarzenia SQL

Performance

Condition Alert

• Wykorzystuje

informacji

WMI Event

• Zdarzenia

systemu

zdarzenia SQL
Server

• Zdarzenia

definiowane
przez
użytkownika

informacji
System Monitor

• Zdarzenia

przekroczenia
wartości
liczników
systemowych

systemu
zarządzania
Windows
Management
Instrumentation

5.2. Tworzenie nowego alertu

• SQL Server Agent | Alerts | New Alert

– Name: nazwa alertu
– Enable: aktywność w danej chwili
– Type: rodzaj zdarzenia
– Zakładka [Response]

– Zakładka [Response]

• Możliwość wykonania określonego zadania

(Execute Job) lub poinformowania operatora
(Notify Operators)

– Zakładka [Options]

• Definiowanie dodatkowego tekstu wysyłanego

na adres e-mail lub pager operatora

5.3. Alert typu SQL Server Event

• Do danej grupy odnosi się każde zdarzenie w

systemie SQL Server, które można
zarejestrować i przechwycić

• Zdarzenie takie w terminologii SQL Server

nazywa się błędem

nazywa się błędem

• Błąd niekoniecznie oznacza problem, może to

być zwykła informacja o wydarzeniu na
serwerze

• Administrator może zdefiniować nowe błędy o

własnym kodzie

5.3. Alert typu SQL Server Event (c.d.)

• 5.3.1. Konfiguracja

– Database name: alert zostanie wygenerowany, jeśli

błąd wystąpi w określonej bazie (można również
wybrać wszystkie bazy)

– Alert will be raised on: alert zostanie

– Alert will be raised on: alert zostanie

wygenerowany w przypadku:

• Error number: wystąpienia błędu o wskazanym numerze

• Severity: wystąpienia błędu o wskazanym priorytecie

– Raise alert when message contains: alert zostanie

wygenerowany, jeśli opis błędu zawiera tekst

• Nie jest zalecane ze względu na wolne działanie

5.3. Alert typu SQL Server Event (c.d.)

• 5.3.2. Kody wbudowanych błędów SQL Server

–

http://www.microsoft.com/technet/support/ee/e
e_advanced.aspx

– Najczęściej przechwytywane błędy:

• 1205 (zakleszczenie transakcji)

• 1205 (zakleszczenie transakcji)

• 1204 (brak pamięci dla blokad)

• 3041 (błąd wykonania kopii zapasowej)

• 6103 (awaryjne rozłączenie użytkownika)

• 9002 (brak miejsca na dysku dla dziennika transakcji)

5.3. Alert typu SQL Server Event (c.d.)

• 5.3.3. Błędy definiowane przez użytkownika

– Są wykorzystywane do śledzenia istotnych zmian

lub operacji w bazie danych

– Pociągają za sobą wysokie nakłady systemowe

– Mogą być użyte do debugowania skryptów TSQL

• Konstrukcja TRY … CATCH

– Mają kody rozpoczynające się od 50 000

5.3. Alert typu SQL Server Event (c.d.)

• 5.3.3 Błędy definiowane przez użytkownika (c.d.)

– Tworzenie błędu użytkownika

• sp_addmessage 50005, 16, ‘Zabroniona operacja!’

– 50005: kod błędu
– 16: kategoria ważności błędu (User Message)

– Tworzenie wyzwalacza generującego błąd

• Treść kodu SQL wyzwalacza powinna zawierać

– RAISERROR ( 50005, 16, 1 ) WITH LOG
– Ostatni parametr (kod stanu błędu) 1…127 służy do

rozróżniania identycznych błędów występujących w
różnych częściach skryptu

– Definiowanie alertu informującego o błędzie

5.4. Alert typu Performance condition

• Konfiguracja

– Object: nazwa obiektu w Monitorze Systemowym

• Np. databases

– Counter: nazwa licznika wartość którego nas

interesuje

interesuje

• Np. Percent Log Used

– Instance: egzemplarz obiektu który nas interesuje

• Np. nazwa bazy danych

– Alert if counter … : poinformować jeśli wartość

licznika spadnie poniżej / wzrośnie powyżej /
będzie równa określonej wartości

6. Operatorzy SQL Server Agent

• Operator SQL Server Agent – konto

adresata, otrzymującego alert o
zdarzeniach w systemie SQL Server oraz
o wykonaniu zadań

o wykonaniu zadań

Database

alert

Job

complete

Operator

6. Operatorzy SQL Server Agent (c.d.)

• Server | SQL Server Agent | Operators | New

Operator …

• Właściwości Operatora:

– Nazwa (Name)
– Aktywność (Enabled)

– Aktywność (Enabled)
– Adres e-mail (e-mail name)
– Adres komunikatów sieciowych (Net Send

address)

• Nazwa konta domenowego Windows
• Adres IP lub nazwa komputera w sieci

– Adres pagera (Pager e-mail name)

7. Poczta elektroniczna SQL Server

• Zastosowanie e-mail w SQL Server 2005:

– Automatyzacja zadań administracyjnych

– Powiadamianie użytkownika o wynikach

wykonania zapytań T-SQL

– Informowanie o zmianach w bazie danych (wraz z

wyzwalaczami)

– Pobieranie danych z korespondencji za pomocą

skryptów ActiveX i wykorzystanie ich w
zapytaniach

– Itp..

7. Poczta elektroniczna SQL Server (c.d.)

• Database Mail (zalecany!)

– Oparty na protokole SMTP
– Inna nazwa: SQLiMail

• SQLMail (wycofywany)

– Protokół MAPI + MS Outlook

– Protokół MAPI + MS Outlook

• Biblioteka CDO + procedury składowane

– Tylko wersje serwerowe OS Windows

• Wysłanie e-mail z wiersza poleceń

– Z wykorzystaniem procedury xp_cmdshell

• SQL Server Notification Services

8. Database Mail

• Korzysta z protokołu SMTP

– Działa z każdym serwerem poczty (SQL Server

2000 - tylko z Exchange)

– Można korzystać z wielu serwerów i kont

• Działa jako osobny proces

• Działa jako osobny proces

– Problemy z pocztą nie wpływają na SQL Server

• Działa w trybie asynchronicznym

– Nie blokuje zapytań T-SQL

• Przechowuje ustawienia w bazie SQL Server

– Nie ma potrzeby konfigurowania kont pocztowych

w systemie operacyjnym Windows

8.1. Profile i konta Database Mail

• W ramach jednego profilu Database Mail

można skonfigurować kilka kont SMTP

Profil

Profil

Konto SMTP

8.2. Konfigurowanie Database Mail

• SQL Server | Management | Database Mail

– Set up Database Mail

– Manage Database Mail account and profiles

– View or change system parameters

• Potwierdzamy aktywację usługi która

• Potwierdzamy aktywację usługi która

domyślnie jest wyłączona

• Dodajemy nowy profil pocztowy (New profile)

– W ramach jednego profilu pocztowego można

skonfigurować kilka kont e-mail

– Dla każdego konta podajemy: nazwę, login oraz

hasło

8.2. Konfigurowanie Database Mail (c.d.)

• Konfigurujemy dostęp do profili

– Public:

• Każdy użytkownik bazy danych ma dostęp do

profilu

– Private:

• Tylko wskazany użytkownik bazy danych ma dostęp

do profilu

• Musimy wskazać użytkowników w jawnej postaci

– Domyślnym profilem może być jak publiczny

tak i prywatny

8.2. Konfigurowanie Database Mail (c.d.)

Konfigurowanie parametrów systemowych

• Account retry attempts

– Ile razy serwer będzie próbował skontaktować się z

serwerem pocztowym w razie błędu

• Account retry delay

• Account retry delay

– Po jakim czasie nastąpi ponowna próba połączenia

• Maximum file size

– Maksymalny rozmiar pliku załącznika

8.2. Konfigurowanie Database Mail (c.d.)

Konfigurowanie parametrów systemowych

• Prohibited attachment file extensions

– Rozszerzenia plików zabronione do wysłania (*.exe,

*.dll. *.vbs, *.js)

• Database Mail Executable Minimum Lifetime

– Minimalny czas znajdowania się w pamięci

– Minimalny czas znajdowania się w pamięci

operacyjnej procesu Database Mail po wysłaniu
wszystkich wiadomości (skraca czas ponownego
ładowania bibliotek pocztowych)

• Logging Level (Normal / Extended / Verbose)

– Rejestrować w dzienniku tylko błędy, informacje

szczegółowe o błędach lub wszystko

8.3. Wysłanie wiadomości testowej

• SSMS

• [SQL Server] | Management | Database Mail
• Properties | Send Test E-Mail

• Procedura składowana SP_SEND_DBMAIL

• Procedura składowana SP_SEND_DBMAIL

• EXEC msdb.dbo.sp_send_dbmail

@profile_name = ‘ACWP’,
@recipients = ‘[e-mail]’,
@subject = ‘Powitanie’, @body = ‘Witam!’

8.3. Wysłanie wiadomości testowej

(c.d.)

• Uwaga

• Konto logowania powinno posiadać

uprawnienia do wysłania wiadomości (!)

– Administrator domyślnie ma pełne

– Administrator domyślnie ma pełne

uprawnienia, innym użytkownikom należy je w
jawnej postaci przydzielić

• Uprawnienia można uzyskać przypisując

konto do roli DatabaseMailUserRole w
bazie danych msdb

8.4. Konfigurowanie SQL Server Agent

do wysłania wiadomości

• Włączyć Database Mail (Properties | Alert System)

• Przypisać konto logowania SQL Server Agent w

SQL Server (zakładka Logins) do roli
DatabaseMailUserRole w bazie msdb

DatabaseMailUserRole w bazie msdb

• Ustawić profil Database Mail jako domyślny

• We właściwościach SQL Server Agent (zakładka

Alert System) zaznaczyć [x] Enable mail profile a
następnie wybrać odpowiedni profil

• Uruchomić ponownie SQL Server Agent

8.5. Monitorowanie Database Mail

• Dzienniki Database Mail

– SERVER | Management | Database Mail

– Properties | View Database Mail Log

• Zalecane jest włączenie logowania rozszerzonego

rejestrującego nie tylko błędy, lecz również

rejestrującego nie tylko błędy, lecz również
sukcesywne wysłania

• Widoki

– sysmail_allitems (wszystkie)

– sysmail_faileditems (tylko błędy)

– sysmail_sentitems (tylko wysłane)

– sysmail_unsentitems (czekające na wysyłkę)