SQL Server 2012 przyniósł wiele zmian dla Integration Services. Do największych można zaliczyć wprowadzenie bazy danych SSISDB (SSIS Catalog) oraz nowy model publikowania projektu, czyli Project Deployment Model”. Wraz z tymi zmianami pojawił się również IServerExec, czyli proces, który odpowiedzialny jest za faktyczne wykonywanie operacji na pakietach w tym ich uruchamianie. Jako, że od tamtej pory SQL Server Integration Services z niego korzysta i jest on stosunkowo słabo opisany w Internecie, by nie powiedzieć pominięty, w dzisiejszym poście przyjrzymy się jemu działaniu.

Architektura SSIS

Zacznijmy od architektury SSIS oraz spróbujmy zastanowić się, gdzie znajduje się, wyżej wspomniany, IServerExec. W tym celu moglibyśmy posłużyć się następującą grafiką:

SSISDB jako baza danych przechowuje projekty SSIS wraz z pakietami, metadanymi oraz różnego rodzaju logami. SSIS Catalog jako tak naprawdę dodatek do SQL Server (by nie powiedzieć do Management Studio), korzysta z tej bazy danych i udostępnia użytkownikowi interfejs do zarządzania projektami oraz ich konfiguracją. Oba elementy znajdują się wewnątrz instancji SQL Server. Oprócz SSIS Catalog mamy możliwość zarządzania i interakcji z projektami oraz pakietami z poziomu PowerShell, T-SQL, .NET czy też dodatkowych, gotowych narzędzi jak Deployment Wizard. Oprócz powyższych w architekturze znajduje się również Execution Process (IServerExec) właśnie, oraz jego “starszy brat”, czyli SSIS Service (MsDtsSrvr). Począwszy od wersji 2012 to właśnie IServerExec jest odpowiedzialny za właściwe działanie oraz operacje z pakietami oraz z projektami. O różnicach z MsDtsSrvr oraz o tym, czy tak naprawdę potrzebujemy obu i kiedy napiszemy w następnym poście, a póki co przyjrzyjmy się bliżej procesowi Execution Process.

Klient, SSISDB i IServerExec oraz komunikacja pomiędzy nimi

Proces ten jest standardowym procesem dla systemu Windows. Zostaje on uruchomiony wówczas, gdy użytkownik wywoła odpowiednią procedurę w bazie danych SSISDB. Przeważnie ta procedura będzie wywoływana automatycznie z poziomu narzędzia klienckiego, na przykład, w chwili publikacji projektu w Visual Studio (m.in. procedura [internal].[deploy_packages_internal]) czy w trakcie wykonywania pakietu z wykorzystaniem SQL Server Agent (m.in. procedura [internal].[start_execution_internal]). Tak naprawdę aplikacje klienckie będą korzystały z innych procedur ze schematu [catalog], natomaist koniec końców wywoływane są procedury ze schematu [internal], które odwołują się do biblioteki, która z kolei odpowiada za uruchomienie procesu. BibIioteka ta to SSERVER.

Bardzo ciekawą kwestią w przypadku IServerExec jest komunikacja pomiędzy tym procesem, a bazą SSISDB. Projektanci wewnątrz biblioteki przygotowali mini serwer komunikacyjny “IPC Server”. Komunikacja ta przebiega dwutorowo:

• za pomocą “sql connection” – komunikacja jednostronna. W sytuacji gdy, przykładowo, uruchamiamy dany pakiet, proces loguje informacje do bazy danych SSISDB. Nie ma tutaj potrzeby żadnej interakcji, ruch odbywa się jednostronnie i wykorzystywane jest tutaj zwykłe połączenie “sql connection”
• za pomocą “named pipes” – komunikacja dwustronna. W niektórych przypadkach istnieje jednak potrzeba interakcji oraz wysłania żądania pomiędzy klientem (SSISDB) oraz procesem i to wówczas, gdy proces odpowiedzialny jest za wykonywanie pakietu oraz logowanie zdarzeń. Przykładem może być wysłanie żądania przerwania wykonywania pakietu, pobrania wskaźników wydajnościowych (“Performance Counters”) czy tworzenie zrzutów (“Execution dump”). W takiej sytuacji “IPC Server” będzie odpowiedzialny za komunikację między klientem (SSISDB) oraz procesem niezależnie od tego, iż proces ten wykonuje pakiety.

Biblioteka ISSERVER

Bibliotekę ISSERVER można znaleźć w “Programability” > “Assemblies”.

 

Jeżeli przyjrzymy się bazie danych SSISDB znajdziemy różne obiekty, które będą się odwoływać do tej biblioteki, będą to:

Funkcje;

• [internal].[convert_value]
• [internal].[decrypt_binarydata]
• [internal].[encrypt_binarydata]
• [internal].[get_execution_perf_counters]
• [internal].[get_isserver_processes]
• [internal].[get_package_data]
• [internal].[is_valid_name]

Procedury:

• [internal].[check_schema_version_internal]
• [internal].[create_execution_dump_internal]
• [internal].[create_key_information]
• [internal].[deploy_packages_internal]
• [internal].[deploy_project_internal]
• [internal].[get_server_account]
• [internal].[set_system_informations]
• [internal].[start_execution_internal]
• [internal].[stop_operation_internal]
• [internal].[validate_package_internal]
• [internal].[validate_project_internal]

Przykładowo procedura [internal].[deploy_project_internal] wygląda następująco:

CREATE PROCEDURE [internal].[deploy_project_internal]
	@deploy_id [BIGINT],
	@version_id [BIGINT],
	@project_id [BIGINT],
	@project_name [NVARCHAR](128)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [ISSERVER].[Microsoft.SqlServer.IntegrationServices.Server.ServerApi].[DeployProjectInternal]

Fizycznie jest to biblioteka Microsoft.SqlServer.IntegrationServices.Server.dll. Warto zwrócić uwagę, że w niektórych postach pojawia się informacja, jakoby tą biblioteką była Microsoft.SqlServer.IntegrationServices.Server.Shared.dll, natomiast to właśnie ta pierwsza jest wykorzystywana w tym przypadku. Zawartość tej biblioteki możemy sprawdzić tworząc nowy projekt aplikacji konsolwej w Visual Studio oraz dodając tę bibliotekę do referencji. Wewnątrz zobaczymy:

Jeżeli przyjrzymy się obiektom w bazie danych SSISDB zobaczymy, że obiekty te odwołują się do konkretnych metod w tej bibliotece.

Przykład działania IServerExec. Publikacja pakietu (deep dive)

Poniższa animacja pokazuje co tak naprawdę dzieje się w chwili publikacji pakietu. Na schemacie pokazane są konkretne obiekty w bazie danych SSISDB oraz komunikacja z procesem IServerExec.

W celu sprawdzenia powyższego procesu można uruchomić SQL Server Profiler, aby sprawdzić ruch na serwerze oraz wykorzystać krótki skrypt PowerShell, który będzie sprawdzał czy dany proces jest w danej chwili uruchomiony czy nie.

Clear-Host

while($true)
{
$ErrorOccured = $false
try 
{ 
   #Start-Sleep -Milliseconds 10
   $ErrorActionPreference = 'Stop'
   Get-Process ISServerExec > null
}
catch
{
   "Not running"
   $ErrorOccured=$true
}

if(!$ErrorOccured) {"Running"}
}

Następnie możemy uruchomić powyższy skrypt oraz w tym samym czasie przeprowadzić publikację pakietu.

W chwili wywołania przycisku “Deploy” uruchamiana jest procedura [catalog].[deploy_project], która następnie wywołuje szereg innych funkcji i procedur. Wreszcie wywoływana jest procedura [internal].[deploy_project_internal], która uruchamia proces “IServerExec” oraz dokonuje faktycznej publikacji. Po zakończeniu publikacji proces jest zatrzymywany.

“Execution process” to niezwykle ważny element w Integration Services. Odpowiada on za wszystkie kluczowe operacje związane z pakietami. Zrozumienie jego działania oraz analiza jego wydajności może być bardzo przydatna w przypadku dużych projektów ETL.

Więcej o Integration Services Catalog oraz bazie danych SSISDB mówiłem podczas sesji “SSISDB i SSIS catalog od środka”. Więcej szczegółów oraz link do nagrania dostępny tutaj: https://pl.seequality.net/ssisdb-ssis-catalog-od-srodka-nagranie/