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

2 thoughts on “TaskScheduler

  1. LR pisze:

    Jesteś wielki. Nie wiem, czemu się nie reklamujesz 🙂

    1. Michał Gołoś pisze:

      Dziękuję bardzo. Stworzyłem ten blog (wiem, mało jest, ale życie prywatne nie rozpieszcza, tematy mam, gorzej z czasem). Wystąpiłem jako prelegent, pojawiam się na SQL Day i spotkaniach PLSSUG Warszawa. Nie bardzo widzę jak jeszcze można się zareklamować, ale wszelkie sugestie mile widziane 🙂

Dodaj komentarz

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