TaskScheduler

Nie chodzi tu o usługę dostępną w Windows, jest to autorski mechanizm do cyklicznego przetwarzania zadań stworzony bez użycia zewnętrznych komponentów czy bibliotek CLR, wyłącznie T-SQL. Można go wykorzystać nawet w edycji Express.

W działaniu przypomina SQL Agenta, umożliwia tworzenie zadań, które są następnie wykonywane w odpowiednim, wcześniej zdefiniowanym, czasie. Z powodzeniem może zastąpić SQL Agenta tam gdzie nie możemy go użyć, np. w edycji Express.

Prototyp powstał wiele lat temu, kiedy odkryłem usługę Service Broker w SQL Server, jak każdy dobry projekt potrzebował czasu żeby dojrzeć i dopiero od kilku lat jest używany produkcyjnie w firmie, w której pracuję. Mechanizm jest używany na jednej instancji SQL Server Express Edition oraz kilku instancjach Enterprise 2008R2 i 2012, a suma zadań sięga już 50.

Dlaczego używam go w edycji Enterprise skoro mogę używać SQL Agenta? SQL Agent jest dla całej instancji, pełny zadań utrzymaniowych, duża część zadań nie dotyczy bazy, w której pracuję, do tego dochodzi problem z dostępem do takich zadań. O ile dostęp do bazy produkcyjnej projektowanej w moim obszarze mogę dostać, to do SQL Agenta, w którym znajdują się wszystkie zadania ze wszystkich obszarów już trudniej. Dodatkowym utrudnieniem jest monitorowanie zadań tylko z mojego obszaru, a gdy potrzebuję kopii bazy produkcyjnej do dalszego rozwoju trzeba wyciągać zadania z bazy msdb, aby je przenieść razem z kopią. Jest to nie lada problemem przy setkach zadań. Żeby rozwiązać ten problem użyliśmy TaskScheduler do obsługi zadań biznesowych na poziomie bazy danych, a SQL Agent pozostał do obsługi zadań administracyjnych. Proponowany podział wygląda następująco:

SQL Agent (utrzymanie)

• Przebudowa indeksów
• Przebudowa statystyk
• Backup
• Replikacja

TaskScheduler – procesy biznesowe

• Usuwanie logu aplikacji z bazy
• Czyszczenie tokenów sesji
• Denormalizacje
• Wstępne wyliczenia i agregacje przyspieszające działanie systemu w ciągu dnia
• Cykliczne przeliczenia, np. limity klientów, pensje pracowników
• Inne

Budowa

Mechanizm TaskScheduler składa się z dwóch tabel:

• [tsks].[tsksx_task_scheduler] – tabela konfiguracyjna
• [tsks].[tsklg_task_scheduler_log] – tabela logu działania mechanizmu

Dodatkowo mamy kolejkę Service Broker [tsks].[TaskSchedulerQueue] oraz procedurę [tsks].[sp_RunScheduledTask], tabela konfiguracyjna posiada wyzwalacze a na potrzeby obsługi kolejki powstał serwis i kontrakt. Wszystko zostało zamknięte w schemie [tsks].

W tabeli konfiguracyjnej mamy następujące atrybuty:

Atrybut	Opis
tsksx_id	identyfikator zadania, bez autoinkrementacji
tsksx_name	nazwa, krótki opis zadania
tsksx_start_date	data i czas, od kiedy zadanie ma działać, dla zadań godzinnych, dziennych, tygodniowych i miesięcznych czas jest brany pod uwagę przy ustawianiu kolejnego wykonania
tsksx_end_date	opcjonalna data i czas zakończenia wykonywania zadania
tsksx_last_run_date	pole informacyjne, przechowuje informację o ostatnim starcie zadania (nie jego zakończeniu)
tsksx_last_run_success	pole informacyjne, czy ostatnie uruchomienie zakończyło się sukcesem
tsksx_execution_status	pole informacyjne, status zadania
tsksx_repeatable	pole konfiguracyjne, czy zadanie ma być powtarzalne czy jednorazowe
tsksx_repeatable_on_error	pole konfiguracyjne, czy TS ma kontynuować kolejne cykle pomimo wystąpienia błędu
tsksx_interval_type_chk	pole konfiguracyjne, typ cyklu, do dyspozycji mamy: • S – sekundy • N – minuty • H – godziny • D – dni • W – tygodnie • M – miesiące
tsksx_interval	pole konfiguracyjne, interwał wykonywanego zadania
tsksx_force_start	atrybut umożliwia wymuszenie startu zadania nawet gdy zadanie jest nie aktywne
tsksx_active	pole konfiguracyjne, czy zadanie jest aktywne
tsksx_log_work	pole konfiguracyjne, czy TS ma logować pracę dla wybranego zadania, błędy są zawsze logowane
tsksx_sql	pole z poleceniem T-SQL, które wykona TS
tsksx_parameters	pole opcjonalne do przechowywania parametrów zadania w postaci XML
tsksx_conversation_handle	pole techniczne, przechowuje guid konwersacji

Jak zacząć?

UWAGA: Skrypt w celu włączenia usługi Service Broker na bazie na chwilę ustawia ją w tryb SINGLE_USER, nie zalecam uruchamiania go na produkcji poza oknem serwisowym i bez wcześniejszego przetestowania działania.

1. Pobierz skrypt SQL udostępniony na tej stronie TaskScheduler
2. Otwórz go i ustaw tryb SQLCMD sesji
3. Ustaw parametr DatabaseName podając nazwę bazy danych na której ma być utworzony mechanizm
4. Uruchom skrypt
5. Edytuj tabele konfiguracyjną w celu dodania nowych zadań

Używanie parametrów zadania i logowanie pracy

Istnieje możliwość wykorzystania parametrów zadania oraz parametru log_work w naszej procedurze, aby przekazać taką informację nasza procedura musi przyjmować takie parametry na wejściu:

CREATE PROCEDURE dbo.spProcName
       @tsksx_parameters XML
     , @tsksx_id INT
     , @tsksx_log_work BIT

W wybranym miejscu procedury możemy wykorzystać te parametry do rozszerzenia logowania:

IF @tsksx_log_work = 1
BEGIN
    INSERT INTO tsks.tsklg_task_scheduler_log (tsksx_id, tsklg_error, tsklg_message)
    SELECT @tsksx_id, 0, N'Rozpoczęcie ładowania danych z importu.'
END

Następnie w polu tsksx_sql należy dodać do naszego wywołania procedury parametry, które chcemy przekazać:

EXEC dbo.spProcName
     @tsksx_parameters = @tsksx_parameters
   , @tsksx_id = @tsksx_id
   , @tsksx_log_work = @tsksx_log_work

Napotkane problemy

• Monitorowanie działania zadań może być utrudnione, Service Broker jako mechanizm systemowy wykonuje zadania na sesjach poniżej SPID 50, trzeba o tym pamiętać podczas monitorowania.
• Backup/restore bazy na wersjach niższych niż SQL Server 2014 wymaga podania dodatkowej klauzuli przy restore bazy

  WITH ENABLE_BROKER

  lub późniejszego włączenia usługi przez polecenie

  ALTER DATABASE [$(DatabaseName)] SET ENABLE_BROKER

• Ręcznie zatrzymane zadania przez polecenie KILL lub nieskończone w trakcie wykonywania backupu bazy, należy ręcznie ponownie uruchomić, w tym celu należy wymusić start zadania poprzez parametr tsksx_force_start lub poprzez deaktywację i ponowną aktywację zadania w tabeli konfiguracyjnej.

Do pobrania

Zachęcam do pobierania i używania tego mechanizmu. Plik do pobrania: TaskScheduler