SQL Server Maintenance Plans – prosty sposób na utrzymanie bazy danych

Bardzo ważnym zadaniem przy administracji każdą bazą danych jest prawidłowe zadbanie o sprawy utrzymaniowe takie jak reorganizacja, przebudowanie indeksów, tworzenie kopii zapasowych, optymalizacja i szeroko pojęty monitoring baz danych. Zadania te mogą się kojarzyć z pracą administratorów baz danych i w istocie tak jest. Bardzo często jednak jako konsultanci musimy zdawać sobie sprawę, że na naszych barkach spoczywa opieka nad bazą i musimy mieć rozeznanie w tym jak podejść do kwestii utrzymania. Część z Was zapewne pomyśli o tym, że istnieje cały szereg możliwości związanych właśnie z tego typu zadaniami jak np. uruchamianie w usłudze SQL Server Agent zestawu skryptów języka TSQL czy chociażby odpowiednio przygotowany pakiet Integration Services, który przecież jest wyposażony w odpowiednie zadania przeznaczone do tego celu. Oba podejścia są jak najbardziej prawidłowe i z całą pewnością nie jedyne – dziś chciałbym przedstawić Wam funkcjonalność będącą niejako pochodną tego drugiego podejścia związanego z SSIS, chodzi mianowicie o Maintenance Plans.

Funkcjonalność planów utrzymania pojawiła się już bardzo dawno temu i daje możliwość budowania przepływów pracy i harmonogramów pozwalających na tworzenie sekwencji kroków wykonujących typowe zadania utrzymaniowe. Przechodząc do meritum – do samej funkcjonalności możemy dostać się z poziomu Management Studio w Object Explorer rozwijając węzeł Management i tam odnajdując Maintenance Plans:

Póki co węzeł jest pusty jednakże po kliknięciu na niego prawym przyciskiem myszy możemy wybrać z menu kontekstowego New Maintenance Plan lub Maintenance Plan Wizard. Jak możecie się domyślać pierwsza opcja pozwala stworzyć plan ręcznie natomiast druga to prosty kreator pozwalający nam przejść krok po kroku przez proces tworzenia takowego planu. W ramach niniejszego artykułu zajmiemy się pierwsza opcją, która w prawidłowy sposób pozwoli nam zrozumieć cały mechanizm. Pierwszym oknem jakie ukaże się naszym oczom jest okno pozwalające nadać nazwę dla naszego planu – jak we wszystkim w świecie IT nazwa ma duże znaczenie więc warto ją przemyśleć aby później nie było wątpliwości co do tego co dany obiekt robi i za co odpowiada:

W dalszej kolejności zobaczymy okno designera planu oraz toolbox z dostępnymi zadaniami. Cały designer jest niczym innym jak podzbiorem zadań dostępnych w usłudze Integration Services:

Wśród dostępnych zadań mamy do dyspozycji:

• Back Up Database Task – zadanie umożliwiające wykonanie kopii zapasowej bazy danych i dziennika transakcyjnego
• Check Database Integrity Task – zadanie umożliwiające sprawdzenie integralności bazy danych
• Execute SQL Server Agent Task – zadanie umożliwiające wykonanie wcześniej zdefiniowanego joba SQL Server Agent
• Execute T-SQL Statement Task – zadanie umożliwiające wykonanie zapytania TSQL
• History Cleanup Task – zadanie umożliwiające wyczyszczenie informacji zawartych w MSDB o historii backupów i restore, wykonania jobów i samych Maintenance Planów.
• Maintenance Cleanup Task -zadanie umożliwiające usunięcie plików związanych z Maintenance Planami i plików backupu.
• Notify Operator Task – zadanie umożliwiające powiadomienie operatora, wymaga poprawnej konfiguracji usługi database mail.
• Rebuild Index Task – zadanie umożliwiające wykonanie przebudowy indeksu
• Reorganize Index Task – zadanie umożliwiające wykonanie reorganizacji indeksu
• Shrink Database Task – zadanie umożliwiające wykonanie operacji SHRINK DATABASE (zapomnijcie o istnieniu tego elementu!)
• Update Statistics Task – zadanie umożliwiające aktualizację statystyk

Do wyboru jest całkiem sporo możliwości – w większości przypadków dostępne zadania w pełni nam wystarczą, a jeżeli czegoś brakuje to zawsze możemy napisać skrypt w TSQL wykorzystując element Execute TSQL Statement Task lub stworzyć job SQL Server Agent co daje nam w praktyce niemal nieograniczone możliwości. Samo tworzenie planu jest bardzo proste i sprowadza się do przeciągnięcia odpowiednich zadań na pole planu, ich konfiguracji i stworzenia za pomocą strzałek odpowiadającej naszym potrzebom sekwencji zadań. Nie będę w tym miejscu opisywać konfiguracji każdego z podanych zadań gdyż każdy z nich wiąże się określoną teorią na temat zadania, które wykonuje – dla przykładu wybierzmy zadanie Check Database Integrity Task i klikając na niego otwórzmy okno konfiguracji:

Jak możecie zauważyć na powyższym zrzucie ekranowym sama konfiguracja jest bardzo prosta i sprowadza się do wskazania połączenia do serwera, wybrania bazy danych, wyspecyfikowania opcji polecenia (w przypadku wybranego zadania są to właściwości mówiące o tym czy mają być sprawdzane indeksu, czy sprawdzana ma być jedynie fizyczna konsystencja stron danych, czy ma być zakładany TABLOCK oraz ilość wątków jakie mają być użyte podczas sprawdzania bazy). Po poprawnej konfiguracji możemy jeszcze podejrzeć zapytanie TSQL odpowiadające naszemu zapytaniu – w przypadku zaprezentowanego zadania zapytanie TSQL przedstawia się następująco:

USE [AdventureWorksDW]
GO
DBCC CHECKDB(N'AdventureWorksDW')  WITH  PHYSICAL_ONLY

Po konfiguracji tego zadania dodajmy jeszcze Rebuild Index Task – który pozwala na “wizualną” konfigurację wszystkich aspektów związanych z przebudową indeksów

Zapytanie TSQL wygenerowane przez to zadanie wygląda następująco (oczywiście analogiczne zapytania zostaną wygenerowane dla każdego indeksu):

USE [AdventureWorksDW]
GO
ALTER INDEX [PK_FactInternetSales_SalesOrderNumber_SalesOrderLineNumber] 
ON [dbo].[FactInternetSales] REBUILD PARTITION = ALL 
WITH 
(PAD_INDEX = ON, 
STATISTICS_NORECOMPUTE = OFF, 
SORT_IN_TEMPDB = ON, 
ONLINE = OFF, 
ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON, 
FILLFACTOR = 90)
GO
USE [AdventureWorksDW]
GO

Sam skrypt jest bardzo długi i możemy podejrzeć tylko jego część, natomiast nie widzimy mechanizmu sprawdzania poziomu fragmentacji indeksu, który ustawiliśmy w oknie konfiguracyjnym, ale możemy przypuszczać, że opiera się ono standardowo na metadanych zawartych w DMV. Po konfiguracji obu zadań możemy je połączyć strzałką, która definiuje kolejność wykonania zadań – mechanizm ten jest identyczny z tym co widzimy w Integration Services. W przypadku gdy zadania nie będą połączone żadną strzałką to wykonają się równolegle.

Powyższy plan wykona najpierw przebudowę indeksu następnie sprawdzi konsystencję bazy danych. Klikając dwukrotnie na strzałkę możemy ją skonfigurować:

Konfiguracja ta polega na tym, że możemy ustawić czy wykonanie sprawdzenia konsystencji ma być wykonane w przypadku tego czy wykonanie przebudowy indeksu zakończyło się sukcesem, porażką lub skończyło się niezależnie z jakim statusem. Ponadto w przypadku gdy stworzymy bardziej zaawansowaną logikę to możemy połączyć klika zadań do jednego docelowego. Dzięki takiemu podejściu możemy zdecydować czy zadanie docelowe ma być zakończone gdy oba wcześniejsze zakończą się lub jeden z nich zakończy się z określonym statusem. W zależności od konfiguracji strzałki będą graficznie odzwierciedlały naszą konfigurację tj. kolor oznacza status z jakim ma zostać zakończone zadanie aby przejść dalej i tak:

• czerwony oznacza, iż przepływ przejdzie dalej gdy zadanie zakończy się błędem
• zielony oznacza, iż przepływ przejdzie dalej gdy zadanie zakończy się sukcesem
• niebieski oznacza, iż przepływ przejdzie dalej gdy zadanie zakończy się sukcesem lub błędem

Ponadto zachowanie zależy również od stylu strzałki:

• linia ciągła oznacza logiczne AND czyli wszystkie wejściowe muszą zakończyć się określonym statusem
• linia przerywana oznacza logiczne OR czyli jedno z zadań wejściowych musi zakończyć się określonym statusem

Aby lepiej to zobrazować posłużmy się przykładem -do stworzonego planu dodałem zadanie aktualizujące statystyki – cały przepływ działa w następujący sposób:

• Rebuild Index oraz Update Statistics startują równolegle
• Check Database Integrity startuje w przypadku gdy Rebuild Index skończy się sukcesem bądź porażką lub wtedy gdy aktualizacja statystyk zakończy się niepowodzeniem. Zadanie nie będzie czekało aż oba zadania poprzedzające się skończą – wystarczy że choć jedno z nich zakończy się pożądanym statusem.

W tym miejscu nie możemy użyć wyrażenia (expression) – jest to jednakże możliwe w pełnej wersji Integration Services gdzie logikę możemy oprzeć np. o zmienne, bieżący czas itd.

Dosyć ciekawe zachowanie prawda? Po konfiguracji poszczególnych elementów planu możemy przejść dalej. W górnej części okna mamy możliwość zdefiniowania harmonogramu konta na którym ma działać nasz plan oraz takie rzeczy jak nazwa podplanu oraz opis. W tym miejscu warto zauważyć, że pojedynczy Maintenance plan może składać się z kilku podplanów – można np. na jednym serwerze zdefiniować plan dla wielu instancji SQL Server  składający się z szeregu podplanów odpowiadający każdej instancji.

Ustalenie harmonogramu sprowadza się do wybrania odpowiednich dat w jakich plan ma być wykonywany – posłuży do tego znane z SQL Server Agent okno widoczne na poniższym zrzucie ekranowym:

Ustalenie konta przy użyciu którego ma być wykonywany plan wymaga tego aby mieć zdefiniowane Proxy (czyli konta z zapisanymi danymi logowania w SQL Server) w SQL Server Agent przypisanego do zadań typu Integration Services w innym przypadku będzie używane konto na którym działa usługa Agent.

Po zakończeniu tworzenia planu wystarczy go zapisać. Warto w tym miejscu wspomnieć, że całość jest nadzorowana przez SQL Server Agent bo to właśnie ta usługa standardowo uruchamia stworzony przez nas plan (warto upewnić się czy jest ona uruchomiona bo w innym przypadku nasz plan nie zostanie uruchomiony według wskazanego harmonogramu) – w momencie zapisania powstanie odpowiedni job z ustalonym przez nas harmonogramem i kontem, który możemy dostrzec np. poprzez SSMS w Object Explorer:

Teraz jedyne co musimy zrobić to uruchomić nowopowstałego joba lub kliknąć prawym przyciskiem myszy na maintenance plan i z menu kontekstowego wybrać Execute. Po zakończeniu działania z menu kontekstowego planu możemy wybrać opcję View History aby zobaczyć historię wykonania. Historia ta jest standardowym widokiem na dziennik, wyświetlającym tylko zdarzania związane z planami utrzymania:

Tak jak już wspomniałem analogiczne rozwiązanie możemy stworzyć przy pomocy jobów agenta i Integration Services -Miantenance Plan jest niczym innym jak interfejsem opartym o te dwie usługi pozwalającym wykonać analogiczne zadania. Mimo wszystko warto znać ten mechanizm bo w wielu przypadkach jego prostota obsługi i szybkość konfiguracji może okazać się przydatna. Pamiętajcie o tym, że tak naprawdę nieważne jakiej technologii użyjemy aby stworzyć strategię utrzymania – ważne żeby ona była, wspomagała działanie naszej bazy i zapewniała zaplecze bezpieczeństwa w przypadku wystąpienia awarii.