Referencje pomiędzy bazami w projekcie bazodanowym Visual Studio

W dzisiejszym artykule po krótkiej przerwie wracamy do tematyki projektu bazodanowego w Visual Studio. Dziś powiemy sobie kilka słów na temat referencji pomiędzy projektami bazodanowymi. Jeśli ktoś jest zainteresowany tym tematem polecam zapoznać się z innymi wpisami poświęconymi Visual Studio, które znajdziecie tutaj.

Jak zapewne wielu z Was wie, często spotyka się rozwiązania oparte o SQL Server, które zawierają więcej niż jedną bazę danych. Wyobraźmy sobie typowy scenariusz hurtowni danych gdzie obszar przejściowy czyli tzw. “staging” to jedna fizyczna baza danych, a hurtownia sama w sobie jest osobną bazą. W swojej karierze widziałem wiele rozwiązań i prawie zawsze opierały się one na więcej niż jednej bazie danych.

W zależności od przyjętych założeń architektonicznych referencje pomiędzy takimi bazami występują lub nie. Jeżeli do wszelkich transformacji danych używamy dedykowanego narzędzia jak np. Integration Services czy Data Factory to wtedy jako takich referencji w samym kodzie SQL nie potrzebujemy bo wszystko jest zawarte wewnątrz tych narzędzi. Jeżeli jednak odwołujemy się do obiektów między różnymi bazami danych w ramach kodu SQL no to wtedy tego typu referencje są kluczowe aby odpowiednio móc walidować i buildować naszą solucję.

Na wstępie przedstawmy nieco sytuację jaką postaram się zobrazować w niniejszym wpisie. Poniżej możecie zobaczyć bardzo klasyczną i uproszczoną architekturę hurtowni danych:

Mamy zatem trzy warstwy  i w naszym przykładzie dane pomiędzy poszczególnymi warstwami będą przekazywane za pomocą zapytań TSQL – schemat działania wraz z kolejnością wygląda następująco:

Jak coś takiego zamodelować za pomocą projektu bazodanowego w Visual Studio? Przejdźmy do konkretnego przykładu. Aby móc wszystko zademonstrować stworzyłem nową solucję o nazwie MySolution w ramach, której mam dwa projekty bazy danych SQL Server o nazwach: Datawarehouse oraz Staging. Baza Staging zawiera dwie tabele:

• dbo.Customer
• dbo.CustomerType

CREATE TABLE [dbo].[Customer]
(
	[Id]				INT			 NOT NULL PRIMARY KEY,
	[CustomerName]		NVARCHAR(50) NOT NULL,
	[FK_CustomerType_ID]	INT			 NOT NULL,
	[InsertedAt]		DATETIME	 NULL
);
GO

CREATE TABLE [dbo].[CustomerType]
(
	[Id]			INT NOT NULL PRIMARY KEY,
	[CustomerType]	NVARCHAR(25) NOT NULL,
	[InsertedAt]	DATETIME	 NULL
);
GO

Baza Datawarehouse zawiera jedną tabelę w schemacie dbo oraz schemat etl służący do tworzenia procedur ładujących:

• tabela:
  • dbo.DimCustomer
• schemat:
  • etl

CREATE TABLE [dbo].[DimCustomer]
(
	[Id]				INT			 NOT NULL PRIMARY KEY,
	[CustomerName]		NVARCHAR(50) NOT NULL,
	[CustomerType]		INT			 NOT NULL,
	[InsertedAt]		DATETIME	 NULL
);
GO

Z punktu widzenia Visual Studio projekt przedstawia się w następujący sposób:

Jeśli ktoś nie wie jak stworzyć projekt i odpowiednie obiekty to odsyłam do jednego z poprzednich artykułów z tej serii, który znajdziecie tutaj. W przedstawionym scenariuszu będziemy chcieli stworzyć procedurę, która poprzez odpowiednią konstrukcję INSERT SELECT pobierze dane z bazy Staging i znajdujących się tam tabel i umieści je w bazie Datawarehouse w tabeli DimCustomer. Standardowo podchodząc do tematu moglibyśmy napisać następującą procedurę i umieścić ją w przygotowanym wcześniej schemacie ETL (widocznym na powyższym zrzucie):

CREATE PROCEDURE [etl].[usp_LoadCustomer]
AS
BEGIN
	TRUNCATE TABLE [dbo].[DimCustomer];

	INSERT INTO [dbo].[DimCustomer]
	(
		 [Id]				
		,[CustomerName]		
		,[CustomerType]	
		,[InsertedAt]		
	)
	SELECT
		 C.[ID]
		,C.[CustomerName]
		,CT.[CustomerType]
		,C.[InsertedAt]
	FROM [Staging].[dbo].[Customer] AS C
	JOIN [Staging].[dbo].[CustomerType] AS CT
		ON CT.[ID]=C.[FK_CustomerType_ID];
END
GO

Problem jest taki, że jeżeli wykonamy Build bazy Datawarehouse dostaniemy szereg ostrzeżeń mówiących o tym, że referencja jest nieprawidłowa i Visual Studio nie potrafi zwalidować w poprawny sposób utworzonej procedury:

Stało się tak dlatego, że na ten moment nie mamy zdefiniowanej żadnej referencji pomiędzy poszczególnymi projektami bazy danych. Dodatkowo problem może okazać się jeszcze większy np. wtedy gdy nad projektem pracuje wielu deweloperów i każdy z nich na środowisku deweloperskim chciałby mieć swoją własną kopię baz Staging i Datawarehouse. Z tego też powodu moja kopia baz mogłaby się nazywać Staging_Adrian i Datawarehouse_Adrian, a moich kolegów już odpowiednio inaczej. Idąc dalej tym tropem na środowisku testowym bazy nazywałyby się Staging_TEST i Datawarehouse_TEST itd. Jak zatem wyglądałyby odwołania między tymi bazami? Czy każdy musiałby je zmieniać ręcznie za każdym razem podczas deploymentu? Brzmi jak bardzo trudny do zrealizowania scenariusz ale na szczęście nie musimy tego robić poprzez manualne dostosowanie naszego kodu. Rozwiązaniem na tego typu problemy są wspomniane referencje i ich implementacja w kodzie za pomocą zmiennych projektowych.

Przejdźmy zatem do meritum, aby dodać referencję należy kliknąć prawym przyciskiem myszy w wybranej bazie danych na węzeł References i tam z menu kontekstowego wybrać Add Database Reference (zrobiłem to dodając referencję do bazy Datawarehouse):

Okno definiowania referencji do innej bazy nie powinno przysporzyć większych problemów i wygląda następująco:

W górnej części okna możemy wybrać do jakiej bazy chcemy się odnosić. Mamy kilka możliwości:

• wybrać bazę, która znajduje się w tej samej solucji (tak też zrobimy wskazując na bazę Staging),
• wybrać bazę systemową ( do wyboru mamy master oraz msdb),
• wskazać plik dacpac (jeśli np. baza do której się odnosimy pochodzi z innej solucji to wskazanie na dacpaca daje możliwość odwołania się do obiektów w niej zawartych).

W dalszej sekcji możemy wskazać czy projekt bazodanowy do którego się odnosimy:

• to ta sama baza danych na na tym samym serwerze (oznaczałoby to, że obiekty z projektu Datawarehouse i Staging fizycznie znajdują się w tej samej bazie ale w solucji są podzielone na oddzielne projekty – wrócimy do tego)
• osobna baza danych na tym samym serwerze,
• osobna baza danych na innym serwerze.

My na potrzeby naszych testów wybierzemy opcję osobna baza danych na tym samym serwerze. Dzięki temu wyborowi możemy zacząć używać zmiennej (na zrzucie widocznej jako $(Staging)) aby odnosić się do bazy Staging. Przykład jak tej zmiennej używać w skryptach w ramach projektu bazodanowego można zobaczyć w Example usage:

czyli zamiast:

SELECT * FROM [Staging].[Schema1].[Table1]

używamy:

SELECT * FROM [$(Staging)].[Schema1].[Table1]

Jeżeli wskazalibyśmy, że baza do której odnosimy się na innym serwerze to dodatkowo dostaniemy zmienną wskazującą na ten serwer i potem w skryptach możemy odnosić się do niej poprzez nazwę składającą się z czterech członów:

SELECT * FROM [$(OtherServer)].[$(Staging)].[Schema1].[Table1]

Ostatnią opcją do zdefiniowania jest Supress errors caused by unresolved references in the referenced project czyli nic innego jak ignorowanie błędów związanych z błędnymi referencjami. Raczej tę opcję chcielibyśmy w większości przypadków zostawić odznaczoną.

Dodajmy zatem referencję w taki sposób jak przedstawiona na zrzucie. W naszej procedurze również podmieńmy nazwę bazy Staging na odpowiednią zmienną:

CREATE PROCEDURE [etl].[usp_LoadCustomer]
AS
BEGIN
	TRUNCATE TABLE [dbo].[DimCustomer];

	INSERT INTO [dbo].[DimCustomer]
	(
		 [Id]				
		,[CustomerName]		
		,[CustomerType]	
		,[InsertedAt]		
	)
	SELECT
		 C.[ID]
		,C.[CustomerName]
		,CT.[CustomerType]
		,C.[InsertedAt]
	FROM [$(Staging)].[dbo].[Customer] AS C
	JOIN [$(Staging)].[dbo].[CustomerType] AS CT
		ON CT.[ID]=C.[FK_CustomerType_ID];
END
GO

Wszystko powinno odbyć się bez problemu, widoczne wcześniej ostrzeżenia zniknęły, a Intellisense powinien zacząć podpowiadać składnię w momencie odwoływania się do obiektów z połączonej bazy. Definiowanie referencji w ten sposób daje jeszcze jedną możliwość, chodzi mianowicie o sam proces publikacji. Publish script pozwala zdefiniować wartość zmiennej i podczas publikacji wszystkie odwołania do tej zmiennej zostaną zastąpione podaną wartością:

Dzięki temu możemy przygotować publish skrypty dla każdego ze środowisk, zapisać je w ramach solucji i nie przejmować się tym, że mamy różnego rodzaju referencje pomiędzy bazami. Przejdźmy dalej i dodajmy jeszcze jeden projekt bazodanowy do naszej solucji – tym razem będzie to DataMart:

W ramach nowej bazy danych mamy jedynie widok, który odnosi się do bazy Datawarehouse. Zanim pokażemy sobie ciało tego skryptu dodajmy w standardowy sposób referencję. Po jej dodaniu i próbie wykonania operacji Build dostajemy dosyć ciekawy błąd:

Błąd wskazuje na to, że brakuje nam referencji do bazy Staging. Ktoś może słusznie zauważyć, że przecież nie odwołujemy się do bazy Staging z poziomu bazy DataMart. Odpowiedź jest jednak taka, że musimy dodać referencje do wszystkich baz do których istnieją referencje w bazie do której się odnosimy lub zignorować błędy…. Spróbujmy jednak dodać referencje do bazy Staging i przy buildzie bazy Datamart rzeczywiście wszystkie błędy zniknęły:

To co może być problematyczne to fakt, że w bardziej złożonych strukturach publish skrypty będą coraz bardziej skomplikowane jednakże rozwiązaniem takiego problemu może być podział obiektów na osobne projekty bazodanowe.

Deploy różnych projektów bazodanowych do tej samej bazy danych

W powyższym przykładzie w bazie DataMart dodaliśmy referencje do bazy Staging ponieważ musieliśmy dodać wszystkie referencje bazy Datawarehouse do której się odnosiliśmy. Istnieje jednak alternatywna technika, którą warto użyć, chodzi mianowicie o wydzielenie skryptów z referencjami od obiektów kluczowych. W przypadku naszego prostego scenariusza może to być zrealizowane w taki sposób, że procedury ładujące będą w jednym projekcie bazodanowym, a tabele i inne obiekty statyczne w drugim. Coś takiego zaimplementowałem i otrzymałem bazę Datawarehouse (zawierającą tabelę) oraz Datawarehouse_ETL (zawierającą procedurę ładującą):

Mamy zatem oddzieloną logicznie część ładującą od części przechowującej dane. W dalszej kolejności musimy w bazie Datawarehouse_ETL dodać referencje do bazy Datawarehouse (nasza procedura ładująca odnosi się do bazy DimCustomer zawartej w tej samej bazie co procedura ładująca). Definicja referencji wygląda standardowo z tym, że zaznaczamy iż projekt do którego się odnosimy fizycznie będzie w tej samej bazie danych:

Oprócz dodania referencji do projektu EDWH musimy również dodać referencję do bazy Staging (jednocześnie usuwając tą właśnie referencje z odpowiadającymi im zmiennymi z bazy EDWH oraz DataMart gdzie nie będą już potrzebne). W przypadku takiej konfiguracji przy publikacji bardzo ważna jest opcja Include Composite objects, która domyślnie jest zaznaczona:

Pozostawienie tego w taki sposób powoduje, że podczas publikacji bazy Datawarehouse_ETL uwzględnione zostaną również powiązane obiekty z projektu Datawarehouse (czyli powiązane obiekty z tej samej bazy danych, które fizycznie znajdują się w oddzielnym projekcie) jeżeli takowych obiektów na docelowym serwerze nie ma. Na przykład podczas publikacji projektu Datawarehouse_ETL na pusty serwer gdzie nie ma jeszcze bazy Datawarehouse w wygenerowanym skrypcie różnicowym oprócz samej procedury znalazła się również definicja tabeli DimCustomer z projektu Datawarehouse:

Czyli mówiąc w skrócie przy zaznaczeniu opcji Include composite objects mając więcej niż jeden projekt bazodanowy wskazujący na tą samą bazę docelową to Visual Studio jest w stanie dorzucić do skryptu publikującego obiekty z innego projektu. Co się stanie jeśli wspomnianą opcję wyłączymy i spróbujemy opublikować nasz kod? Otrzymamy stosowny błąd:

Dobrym pomysłem może być wyłączenie Include composite objects i zadbanie o deploy projektów bazodanowych w odpowiedniej kolejności. Wtedy też mamy pewność co i w jakim momencie jest wrzucane na serwer.

Rozdzielenie obiektów na różne projekty jest dobrą praktyką, która pomaga zadbać o czystość deploymentu, skryptów publikujących itp ale również rozwiązać kilka problemów jak chociażby Circular Dependency o którym napisze w jednym z przyszłych wpisów. Na ten moment to wszystko co chciałem przedstawić w ramach niniejszego wpisu. Dla tych którzy nie pracowali z referencjami w ramach projektu bazodanowego polecam przetestować różne scenariusze z jego użyciem gdyż daje to bardzo duże możliwości i po przyswojeniu tej techniki pozwala znacznie usprawnić naszą pracę. Mam nadzieję, że wpis okazał się przydatny, a Was zapraszam do subskrypcji bloga.