Integracja SQL Server 2005 i CLR z punktu widzenia administratora baz danych, cz. I
cz. I: Common Language Runtime (CLR)
Zawartość strony
 | Wstęp |
| Common Language Runtime |
| Włączanie obsługi CLR w SQL Server 2005 |
| Obiekty CLR w bazach danych |
| Metadane związane z obiektami CLR |
| Przeczytaj pozostałe części tego artykułu |
Wstęp
Microsoft SQL Server 2005 wśród wielu nowych funkcjonalności i mechanizmów oferuje integrację z platformą .NET. Programiści mogą tworzyć obiekty baz danych używając języków takich jak C# czy Visual Basic .NET. Integracja ta nie pozostaje także bez wpływu na życie administratorów serwerów baz danych, którzy muszą być świadomi, jakie udogodnienia, ale i nowe obowiązki, niesie za sobą wykorzystywanie obiektów CLR w bazach danych.
Chyba każdy, kto śledził rozwój systemu SQL Server, żywiołowo zareagował na informację o tym, że SQL Server 2005 będzie oferował możliwość tworzenia obiektów baz danych, takich jak procedury składowane czy funkcje, przy użyciu technologii .NET. Była to jedna z głośniejszych i na pewno najbardziej reklamowanych nowych możliwości SQL Server 2005. W praktyce okazało się, że rzeczywiście integracja systemu z Common Language Runtime (CLR) daje nowe możliwości programistom, którzy czuli się ograniczeni (i tak niewątpliwie niemałymi) możliwościami języka Transact-SQL. Pojawienie się w bazach danych obiektów stworzonych z użyciem .NET postawiło także nowe wyzwania przed administratorami baz danych. Celem niniejszego artykułu jest przedstawienie tych zagadnień dotyczących integracji SQL Server 2005 z CLR, które – naszym zdaniem - powinien poznać administrator baz danych. Celem niniejszego artykułu nie jest natomiast pokazywanie, jak pisać procedury składowane czy funkcje dla baz danych systemu SQL Server2005 przy użyciu technologii .NET.
Common Language Runtime
Common Language Runtime (CLR) to środowisko uruchomieniowe dla aplikacji stworzonych na platformie .NET. Środowisko to bardzo często nazywa się środowiskiem zarządzanym (ang. managed environment) z uwagi na duży stopień automatyzacji zarządzania zasobami. CLR posiada wiele wbudowanych mechanizmów wspomagających programowanie, m.in.:
| • | Garbage Collector – mechanizm automatycznego zarządzania czasem życia obiektów, |
| • | Class Loader – mechanizm zarządzający metadanym i ładowaniem klas, |
| • | Exception Manager – moduł dostarczający strukturalną obsługę wyjątków zintegrowaną ze strukturalną obsługą wyjątków w systemach Microsoft Windows, |
| • | Kompilatory Just-In-Time (JIT) – kompilatory służące do kompilacji kodu będącego w postaci pośredniej (Microsoft Intermediate Language – MSIL) do kodu natywnego charakterystycznego dla platformy, na której został zainstalowany SQL Server, |
| • | Mechanizmy zarządzania bezpieczeństwem – Code Access Security, czyli zarządzanie możliwością uruchamiania kodu w zależności od kontekstu użytkownika i pochodzenia kodu. |
Te mechanizmy zdecydowanie przyspieszają proces tworzenia kodu, ponieważ odciążają programistę i pozwalają mu skupić się na implementowaniu konkretnych funkcjonalności.
Rys. 1. Komponenty Common Language Runtime.
Praca z technologią .NET sprowadza się do tworzenia pewnej struktury klas przy użyciu języków wysokiego poziomu. Produktem kompilacji takiego kodu jest plik (lub zbiór plików) nazywany assembly. Assemlby składa się z dwóch części: kodu skompilowanego do języka pośredniego MSIL oraz tak zwanego manifestu – zbioru metadanych opisujących samo assembly (opis klas, metod i innych elementów kodu znajdujących się w assembly).
Integracja SQL Server z CLR polega na tym, że SQL Server jest hostem dla CLR. Oznacza to, że to SQL Server zarządza takimi procesami jak przyznawanie pamięci obiektom CLR, oczyszczanie pamięci z nieużywanych obiektów (garbage collection), obsługa żądań SQL zgłaszanych do procesora zapytań, zarządzanie domenami aplikacji CLR tak, by owe aplikacje były od siebie niezależne i odseparowane (żeby jedna aplikacja CLR nie miała wpływu na działanie innych aplikacji). Ma to pozytywne konsekwencje, ponieważ dzięki takiemu podejściu możliwe jest chociażby uniknięcie konfliktów w dostępie do pamięci i zasobów procesora, do jakich mogłoby dochodzić, gdyby to system operacyjny, a nie SQL Server przydzielał zasoby obiektom CLR.
Włączanie obsługi CLR w SQL Server 2005
Aby w systemie SQL Server 2005 można było odwołać się z poziomu kodu T-SQL do obiektów CLR, należy włączyć obsługę CLR w instancji SQL Server. Zrobić to może użytkownik, który albo posiada login z bezpośrednio nadanym uprawnieniem ALTER SETTINGS na poziomie serwera, albo posiada login należący do jednej z dwóch ról na poziomie serwera: sysadmin lub serveradmin. Metody włączania obsługi CLR są dwie:
| • | za pomocą narzędzia Surface Area Configuration (SAC), |
| • | przy użyciu systemowej procedury składowanej sp_configure. |
Włączenie obsługi CLR z poziomu SAC sprowadza się do zaznaczenia pola Enable CLR Integration dla opcji CLR integration jak pokazano na rysunku 3.
Rys. 2. SAC - wejście w okno zarządzania funkcjonalnościami serwera.
Rys. 3. SAC - włączenie obsługi obiektów CLR w instancji SQL Server 2005.
Włączenie obsługi CLR za pomocą systemowej procedury składowanej sp_configure wygląda następująco:
EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'clr enabled', 1; GO RECONFIGURE; GO
| Informacja |
Integracja z CLR nie funkcjonuje w instancji SQL Server 2005, gdy włączona jest opcja serwera 'lightweight pooling'. |
Obiekty CLR w bazach danych
SQL Server 2005 daje możliwość uruchamiania zarządzanego kodu napisanego w jednym z języków oferowanych przez platformę .NET Framework z poziomu bazy danych.
Programiści mają możliwość stworzenia następujących obiektów CLR w bazach danych:
| • | funkcji skalarnych lub tablicowych (user-defined functions – UDF), |
| • | procedur składowanych (stored procedures), |
| • | wyzwalaczy (triggers), |
| • | agregatów (user-defined aggregates – UDA), |
| • | typów (user-defined types – UDT), |
Cel utworzenia funkcji, procedury lub wyzwalacza w kodzie zarządzanym jest łatwy do zrozumienia. Kod obiektu CLR jest uruchamiany i wykonywany tak samo, jak jego odpowiednik napisany w T-SQL.
Inaczej jest w przypadku UDA i UDT. Rozszerzają one możliwości oferowane programistom baz danych w następujący sposób:
| • | UDA – Pozwala programistom na zbudowanie własnej funkcji agregującej, którą można używać w połączeniu z klauzulą GROUP BY w zapytaniu T-SQL. To pozwala na przeprowadzenie złożonych operacji statystycznych oraz analizę danych przy użyciu silnika baz danych. Co więcej, kodu agregatów nie można tworzyć inaczej, jak tylko używając technologii .NET. Nie ma możliwości stworzenia ich przy użyciu samego kodu języka T-SQL. |
| • | UDT – Dostarcza programistom możliwość zdefiniowania nowych typów o określonym zachowaniu. Połączenie .NET Framework, bibliotek innych firm oraz SQL Server otwiera nowe możliwości w tworzeniu obiektów zamiast tworzenia ich relacyjnej reprezentacji. Dzięki temu można tworzyć chociażby typy złożone. Warto też dodać, że UDT stoją najwyżej w hierarchii typów w SQL Server 2005, czyli przy operacjach z udziałem UDT każdy typ istniejący w SQL Server będzie konwertowany niejawnie do UDT. |
Dlaczego mówimy o tworzeniu obiektów w relacyjnej bazie danych? Na pierwszy rzut oka to rozwiązanie ma więcej wad niż zalet. Prawdą jest, że do typów UDT system ma dostęp jako całości, a więc użycie skomplikowanych obiektów biznesowych może spowodować obniżenie wydajności serwera. Zgodnie z rekomendacją firmy Microsoft należy przyjąć, że UDT w SQL Server 2005 zostały zaprojektowane dla obsługi:
| • | daty, czasu, waluty oraz rozszerzonych typów numerycznych, |
| • | danych pochodzących np. z systemów GPS, |
| • | szyfrowania / odszyfrowywania danych. |
Tworzenie obiektów w bazach danych odbywa się w trzech krokach, opisanych w kolejnych rozdziałach.
Krok 1 - utworzenie projektu w Visual Studio, kompilacja (programiści)
Programiści mogą wykorzystać dedykowany w Visual Studio 2005 szablon projektu. Szablon ten umożliwia szybkie tworzenie obiektów CLR przez automatyczne dodanie referencji do odpowiednich bibliotek oraz szablony klas odpowiadających za zakodowanie obiektów CLR. Kompilacja projektu nie odróżnia się od tradycyjnej kompilacji aplikacji .NET.
Rys. 4. Szablon projektu związany z tworzeniem obiektów CLR w Visual Studio 2005.
Krok 2 - utworzenie assembly w bazie danych (administratorzy lub programiści)
Visual Studio 2005 oferuje programistom możliwość automatycznego instalowania assembly i tworzenia odpowiednich obiektów w bazie danych. Z powodu pewnych ograniczeń (nie można w łatwy sposób zainstalować assembly, jeżeli typy w nim zawarte są już używane) nie jest to opcja polecana dla rozwiązań produkcyjnych, a raczej powinna być stosowana tylko przez programistów.
Administratorzy tworzą assembly w bazie danych za pomocą składni CREATE ASSEMBLY:
CREATE ASSEMBLY assembly_name
[ AUTHORIZATION owner_name ]
FROM { <client_assembly_specifier> | <assembly_bits> [ ,...n ] }
[ WITH PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE } ]
[ ; ]
<client_assembly_specifier> :: =
'[\\computer_name\]share_name\[path\]manifest_file_name'
| '[local_path\]manifest_file_name'
<assembly_bits> :: =
{ varbinary_literal | varbinary_expression }
| Objaśnienie: |
Assembly name to nazwa assembly, które zostanie utworzone. Ta nazwa musi być unikalna dla bazy danych. AUTHORIZATION owner_name - określa właściciela assembly – należy wskazać nazwę użytkownika bądź rolę. FROM <client_assembly_specifier> | <assembly_bits> - w najprostszym przypadku wskazuje ścieżkę do fizycznej lokalizacji assembly na twardym dysku. PERMISSION_SET - określa poziom zabezpieczeń podczas wykonywania kodu assembly. Przykład utworzenia assembly : USE AdventureWorks; CREATE ASSEMBLY Point FROM '\\Sciezka sieciowa\Sciezka do assembly\Point.dll' WITH PERMISSION_SET = SAFE; Dokładniejszy opis składni CREATE ASSEMBLY wraz z przykładem można znaleźć pod adresem: |
Polecenie CREATE ASSEMBLY ładuje binaria zawarte w podanej bibliotece do wskazanej bazy danych w SQL Server. W momencie ładowania binariów nie ma możliwości załadowania ich kodu źródłowego. Obowiązek zarządzania kodem źródłowym spoczywa więc na administratorze baz danych. Poprzez zarządzanie należy rozumieć odpowiednie jego przechowanie, np. w VSS.
Do modyfikacji assembly służy polecenie ALTER ASSEMBLY. Pozwala ono załadować assembly ponownie do bazy danych (w bibliotece mogą znajdować się poprawione definicje istniejących typów lub zupełnie nowe typy).
Należy pamiętać, iż assembly jest zdefiniowane i widoczne w obrębie jednej bazy danych!
Krok 3 - utworzenie i zarejestrowanie różnego rodzaju obiektów (administratorzy
W zależności od typu obiektu zawartego w assembly należy wykonać jego rejestrację w bazie danych. Należy pamiętać o jednej istotnej rzeczy – najpierw musi zostać załadowane assembly, a dopiero potem można zarejestrować obiekty (agregaty, funkcje, typy użytkownika, wyzwalacze, procedury składowane).
Rejestracja obiektów sprowadza się do wykonania określonej komendy TSQL:
1. | CREATE AGGREGATE – w przypadku rejestrowania agregatu (UDA) – |
2. | CREATE TYPE – dla rejestrowania typów zdefiniowanych przez użytkownika (UDT) - |
3. | CREATE FUNCTION – dla rejestrowania funkcji użytkownika (UDF) – |
4. | CREATE TRIGGER – dla zarejestrowania wyzwalacza (trigger) - |
5. | CREATE PROCEDURE – w przypadku rejestrowania procedury składowanej - |
Cechą wspólną dla każdego typu rejestracji obiektu jest konieczność wskazania assembly (EXTERNAL NAME), z którego będzie pochodził obiekt:
CREATE TYPE [ schema_name. ] type_name
{ FROM base_type [ ( precision [ , scale ] ) ]
[ NULL | NOT NULL ] |
EXTERNAL NAME assembly_name [ .class_name ]
} [ ; ]
Rejestracja innych typów obiektów jest podobna do przykładu pokazanego powyżej.
W sytuacji, w której istnieje konieczność usunięcia assembly, należy najpierw usunąć wszystkie zarejestrowane obiekty, a dopiero na końcu usunąć to assembly:
DROP AGGREGATE agrSuperSum; DROP TYPE typeComplex; DROP ASSEMBLY MathAssembly;
Metadane związane z obiektami CLR
Jednym z najbardziej pracochłonnych zadań, które muszą być wykonane przez administratora baz danych, jest przeglądanie obiektów związanych z użyciem wewnątrz SQL Server zewnętrznych bibliotek (a więc także zadania wynikające z integracji serwera baz danych z CLR). SQL Server 2005 wprowadził ułatwienia, dzięki którym można łatwiej uzyskać wymagane informacje – DMV (dynamic management views) oraz DMF (dynamic management functions). Odpowiednio, dla zadań związanych z integracją CLR z SQL Server interesujące są następujące widoki:
| • | sys.assemblies – każde assembly posiada jeden wiersz, w którym znaleźć można takie informacje, jak: nazwę assembly, identyfikator, opis uprawnień, datę utworzenia oraz ostatniej modyfikacji oraz informację o widoczności (określa, czy assembly i jego typy są widoczne z poziomu zapytań T-SQL). |
| • | sys.assembly_files – podaje nazwę assembly, jego identyfikator, identyfikator pliku oraz jego zawartość. |
| • | sys.assembly_modules – podaje informacje, jakie klasy i metody wchodzą w skład każdego assembly. |
| • | sys.assembly_references – ma charakter tablicy linkującej, ponieważ pokazuje zależności pomiędzy assembly. |
| • | sys.assembly_types – zawiera po jednym wierszu dla każdego zdefiniowanego typu CLR. Zawiera informację o assembly, do którego dany typ należy, o sposobie sortowania oraz np. o identyfikatorze w przypadku wywołania danego typu jako obiektu COM. |
| • | sys.type_assembly_usages – określa zależność pomiędzy zdefiniowanymi typami użytkownika a assembly. |
| • | sys.dm_clr_loaded_assemblies – zawiera informację, kiedy określone assembly zostało załadowane do przestrzeni adresowej wykorzystywanej przez SQL Server. |
Przeczytaj pozostałe części tego artykułu
| • | Integracja SQL Server 2005 i CLR z punktu widzenia administratora baz danych, cz. II |
 | Damian Widera, Project Manager & Team Lead (MCT, MCITP – DBA, MCSD.NET) |