Deployment projektu bazodanowego SQL Server przy pomocy SQLPACKAGE.EXE

Kontynuujemy naszą przygodę z projektem bazodanowym Visual Studio. Dziś przyjrzymy się narzędziu sqlpackage.exe, które pozwoli nam na automatyzację deploymentu projektu na serwer docelowy, serdecznie zapraszam i z tego miejsca zachęcam do subskrypcji aby być na bieżąco z naszymi materiałami.

Zanim przejdziemy do przedstawienia tego narzędzia to pokażmy w jaki sposób w ogóle go zdobyć. Jeśli nie macie go w swoim systemie to wszelkie instalatory (w tym również pod Linux i MacOS) znajdziecie na stronie dokumentacji dedykowanej temu narzędziu (link).

Sqlpackage.exe jest narzędziem linii komend pozwalającym na wykonanie szeregu operacji związanych z bazą danych SQL Server czy też Azure SQL Database. SQLPackage przydaje się we wszelkich procesach automatyzacji deploymentów ale nie tylko. Dzięki niemu wykonać następujące operacje:

• stworzenie pliku DACPAC czyli artefaktu zawierającego strukturę na podstawie bazy SQL Server lub Azure SQL DB,
• stworzenie pliku BACPAC czyli artefaktu zawierającego strukturę oraz dane na podstawie bazy SQL Server lub Azure SQL Db,
• opublikować na serwerze strukturę bazy na podstawie pliku DACPAC,
• opublikować na serwerze strukturę bazy oraz dane na podstawie pliku BACPAC,
• stworzyć skrypt SQL zawierający operacje potrzebne do zdeployowania struktury źródłowego dacpaca na serwer docelowy,
• wygenerować raporty XML zawierające podsumowanie jakie zmiany trafią na serwer docelowy.

Jak widać wachlarz możliwości jest całkiem spory. Łatwo dostrzec, że większość operacji opiera się na artefaktach takich jak DACPAC, które powstają w wyniku operacji Build przeprowadzonej na projekcie bazodanowym Visual Studio. O tym jak zbudować projekt z linii komend pisałem już w ramach artykułu o budowaniu modeli tabelarycznych i deployment utility. Wiedza ta nadal jest aktualna w przypadku projektu bazodanowego, który możemy zbudować przy pomocy tego samego narzędzia którym jest MSBuild.

Jeżeli ktoś chciałby sobie odświeżyć to jak zbudować projekt bazodanowy odsyłam do artykułu poświęconego temu zagadnieniu, który znajdziecie tutaj. Przygotowałem sobie taki projekt nieco wcześniej i teraz go zbuduje przy pomocy wspomnianego MSBuild w następujący sposób:

Set-Location "C:\Program Files (x86)\Microsoft Visual Studio\2019\Community\MSBuild\Current\Bin"

.\MSBuild.exe "C:\Users\adria\source\repos\MyDatabase\MyDatabase\MyDatabase.sqlproj"  /t:Clean,Build

Całość wykonuje oczywiście z konsoli Powershell poprzez Visual Studio Code ale nic nie stoi na przeszkodzie aby wywołać MSBuild z poziomu zwykłej linii komend.

W wyniku wywołania powyższej komendy otrzymałem następujące informacje zwrotne, które świadczą o poprawności struktury bazy:

W wyniku powyższej operacji powstał DACPAC do którego ścieżkę znajdziemy w informacji zwrotnej od MSBuild:

SqlPrepareForRun:
  MyDatabase -> C:\Users\adria\source\repos\MyDatabase\MyDatabase\bin\MyDatabase.dacpac

To gdzie wyląduje nasz pakiet zależy od aktualnej konfiguracji projektu bazodanowego, którą znajdziemy po kliknięciu prawym przyciskiem myszy na projekt i wybraniu Properties:

Tam w sekcji Build znajdziemy ustawienia aktywnej konfiguracji gdzie możemy wskazać gdzie ma wylądować wspomniany DacPac i jak ma się nazywać:

Mając już do dyspozycji artefakt możemy przejść do jego wrzucenia na serwer przy pomocy SQLPackage. W tym przypadku składnia wywoływana z Powershell jest dosyć prosta i wygląda następująco:

Set-Location "C:\Program Files\Microsoft SQL Server\150\DAC\bin"

.\SqlPackage.exe /Action:Publish /SourceFile:"C:\Users\adria\source\repos\MyDatabase\MyDatabase\bin\MyDatabase.dacpac" /TargetDatabaseName:MyDatabase/TargetServerName:"."

pomijając całkowicie przejście do lokalizacji gdzie znajduje się narzędzie mamy kilka parametrów.

• Action – wskazuje jaką operację chcemy wykonać czyli Publish,
• SourceFile – wskazanie DACPACa,
• TargetDatabaseName – wskazanie nazwy docelowej bazy danych,
• TargetServerName – wskazanie nazwy docelowego serwera.

Po wykonaniu takiej komendy dostaniemy na ekran zwrotkę z informacją które obiekty zostały stworzone ile upłynęło czasu itp. Poniżej możecie zobaczyć część tej informacji:

Baza docelowa została stworzona od nowa czy może wrzucona w sposób inkrementalny? Czy wrzucone zostały właściwości samej bazy? Na te kilka pytań możemy sobie odpowiedź, że wszystko zależy od ustawień projektowych. Aby zbyt długo się nad tym nie zastanawiać podepnijmy wcześniej utworzony profilu (jak to zrobić pisałem tutaj). Operacja ta nie powinna nikomu sprawić problemu gdyż wiąże się po prostu z użyciem parametru /Profile:

.\SqlPackage.exe /Action:Publish /SourceFile:"C:\Users\adria\source\repos\MyDatabase\MyDatabase\bin\MyDatabase.dacpac" /Profile:"C:\Users\adria\source\repos\MyDatabase\MyDatabase\MyDatabase.publish.xml"

Jak można zauważyć w powyższej komendzie nie mamy już parametrów TargetDatabaseName i TargetServerName ponieważ wskazanie docelowej bazy danych jest zawarte w samym pliku profilu. Miałem tam również ustawienie mówiące o tym, że baza ma być wrzucona w sposób inkrementalny (tylko zmiany) dlatego też na ekranie dostałem dosyć krótką i treściwą informację o rzeczywistych zmianach:

Ogólnie rzecz biorąc polecam używać profili bo wtedy jeśli zajdzie taka potrzeba możemy w łatwy sposób manipulować profilami i zmieniać zawarte w nich ustawienia. To właściwie byłoby na tyle jeśli chodzi o wrzucanie bazy na serwer z poziomu linii komend. Jak można się domyśleć większość pracy jest związana z przygotowaniem samego projektu bazodanowego i profilu bo SQLPackage jest jedynie komendą pozwalającą na wskazanie jego lokalizacji oraz wybranie określonego DACPACa.

Dopełnijmy nieco informacji o narzędziu przykładem tego w jaki sposób wyodrębnić DACPAC z już istniejącej bazy na serwerze. Jest to bardzo proste – tym razem w parametrze /Action przekazujemy wartość Extract, a następnie dane połączeniowe bazy, która nas interesuje oraz to gdzie ma wylądować wygenerowany przez nas DACPAC:

.\SqlPackage.exe /Action:Extract /SourceDatabaseName:"AdventureWorksDW2017" /SourceServerName:. /TargetFile:"C:\temp\MyDatabase.dacpac"

Jeśli chcemy wyodrębnić nie tylko strukturę, ale również dane to komenda wygląda bardzo podobnie tylko zamiast Extract używamy słowa kluczowego Export, a efektem jest powstanie pliku o rozszerzeniu BACPAC:

Operacja eksportu będzie oczywiście znacznie bardziej czasochłonna niż ekstrakcja i zależy od tego ile danych mamy w naszej bazie.  Wspomniane operacje można również porównać poprze rozmiary powstałych artefaktów:

Oczywiście każda z powyższych operacji posiada jeszcze szereg parametrów, które można użyć m.in po to aby w prawidłowy sposób uwierzytelnić się do bazy danych – nie będę jednak wszystkich ich opisywał tylko odsyłam zainteresowanych do dokumentacji. Na ten moment wiemy już jak automatyzować deployment – w najbliższym czasie pokażemy sobie dalszej kroki potrzebne w celu automatyzacji naszego projektu. Pozdrawiam!