Databázový vývoj pomocí SSDT

08.08.2022

Slovníček

SQL Server Databázový server původně vyvinutý společností Sybase a poté koupený a rozvíjený společností Microsoft. První verze je z roku 1989 a jedná se o jednu z nejrozšířenějších databází.
T-SQL Transact SQL je rozšíření jazyka SQL, které používá SQL Server. Má vlastní syntaxi a funkce, podobně jako PL/SQL od společnosti Oracle.
SSDT SQL Server Data Tools je rozšíření do Visual Studia, které nám výrazně usnadňuje vývoj SQL Server databází.
SSMS SQL Server Management Studio je databázový klient, pomocí kterého se můžeme připojit k SQL Serveru, spravovat databáze a volat T-SQL scripty.
SQL CLR Common Language Runtime nám umožňuje spustit kód napsaný v .NET z T-SQL.
Linked Server Zaregistrování dalšího SQL Serveru, databázové instance nebo OLE DB zdroje do našeho serveru tak, abychom mohli přistupovat ke vzdáleným datům.
Schema Schema je buď konkrétní typ databázového objektu, který pomáhá seskupovat další objekty za účelem řízení oprávnění (občas se používá jako namespace pro lepší organizaci objektů, ale není to doporučená praktika) nebo se jedná o obecný pojem označující všechny databázové objekty v dané databázi.
Trustworthy Databázová vlastnost, která nám říká, jestli SQL Server důvěřuje této databázi. Pokud ano, tak například procedura s příznakem 'with execute as owner' může přistupovat do dalších databází na stejném serveru podle nastaveného oprávnění v cílových databázích. Propojovat databáze touto metodou bychom ale měli pouze v případě, že k tomu máme důvod, databáze máme plně pod kontrolou a je to v souladu s architekturou. Nastavení Trustworthy má také vliv na spouštění CLR a dalších funkcí. Obecně se nedoporučuje Trustworthy nastavovat na True.
Synonym Alias pro jiný databázový objekt. Osvědčilo se mi vytvářet pro objekty v jiných databázích, abych měl na jednom místě přehled o všech databázových závislostech.
tSQLt Open source framework pro vytváření a spouštění databázových testů. SSDT má také možnost vytvářet testy, ale zdaleka nenabízí takové možnosti jako tSQLt. tSQLt je možné integrovat do SSDT projektu. Další alternativou jak psát databázové testy může být testovací framework xUnit, který se používá pro .NET projekty. V dnešní době se doménová logika již běžně nedává do uložených procedur a proto dnes již význam databázových unit testů není tak velký, ale vždy samozřejmě záleží na konkrétním projektu. Databáze také může být pokrytá integračními testy aplikační vrstvy, byť použítí unit testů by mělo být přednější.
DACPAC Data-tier applications (DAC) package je soubor, který obsahuje informace o všech databázových objektech a SSDT ho na pozadí používá pro deployment. Pomocí DACPAC balíčku se také do projektu přidávají závislosti na další databáze.

Obsah

Úvod

K databázovému vývoji patří verzování, deployment, testování, import počátečních dat, řízení přístupů apod. Pravděpodobně budeme potřebovat několik prostředí, minimálně například vývojové a produkční a databáze tedy budeme mít minimálně dvě. Spravovat databáze pouze pomocí SQL příkazů by bylo náročné a náchylné na chyby, proto je velmi vhodné zvolit nějaký tooling, který nám pomůže zautomatizovat nejčastější operace.

Pokud vyvíjíme aplikaci nebo se pouštíme do datově analytického projektu a máme možnost využít SQL Server, tak můžeme zvolit SSDT nástroje. Další možností je použít databázové migrace, které jsou například součástí EF Core ORM frameworku a umožňují nám pracovat i s jinýmy databázovými systémy.

SSDT a databázové migrace fungují velmi odlišně a je potřeba zvážit, kterou technologii použít a případně zvolit i kombinaci. V tomto článku se budu věnovat databázovému projektu ve Visual Studiu, ale nikoli SSAS nebo SSIS, které jsou také součástí SSDT.

Příprava prostředí

Pro databázový vývoj budeme potřebovat databázový server a databázového klienta. Server nainstalujeme do Docker kontejneru (SQL Server od verze 2017 podporuje instalaci na Linux) a jako klienta použijeme SSMS, ale případně se k databázi lze připojit i přímo z Visual Studia. Ve Visual Studiu budeme mít databázový projekt, který bude obsahovat nastavení databáze, všechny databázové objekty a SQL scripty. Místo Docker kontejneru lze SQL Server nainstalovat i standardně přes instalátor. Vše doporučuji instalovat v angličtině, protože se pak snadněji řeší případné problémy.

Pokud zvolíme Visual Studio Community edici, SQL Server Express edici a Docker personal licenci, tak získáme profesionální prostředí zdarma, které můžeme s omezením používat i pro komnerční účely. Například Visual Studio má velmi volnou licenci, co se týká databázového vývoje 🙂

Instalace Visual Studia

Pokud máte Visual Studio již nainstalované, zkontrolujte v Tools > Get Tools and Features..., že máte nainstalovanou podporu pro SSDT. V případě nové instalace nezapomeňte tuto podporu zaškrtnout.

Instalace serveru

Nainstalujeme Docker a spustíme následující příkazy v příkazové řádce nebo PowerShellu. V prvním kroku stáhneme SQL Server image, potom spustíme instalaci, ve které zvolíme Express edici a heslo pro server administrátora (heslo musí splňovat požadavky na komplexitu). V posledním kroku spustíme kontejner, který lze pojmenovat libovolně (MSSQL2019).

docker pull mcr.microsoft.com/mssql/server
docker run --name MSSQL2019 -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Pa$$w0rd" -e "MSSQL_PID=Express" -p 1433:1433 -d mcr.microsoft.com/mssql/server
docker start MSSQL2019

Připojení k serveru

Po instalaci a spuštění SQL Serveru, se můžeme přihlásit pomocí SSMS. Adresa serveru je localhost a heslo jsme zvolili při vytváření docker kontejneru.

Vytvoření databáze

Po připojení k serveru vytvoříme databázi, to můžeme provést pomocí průvodce.

Databázi jsem pojmenoval CukGek jako vzpomínku na Čuka a Geka, což je pohádka o dvou klucích, které náš profesor na databázové systémy vždy používal v příkladech stejně jako američani mají svého Boba s Alicí 😂

Databázový projekt

Nyní, když máme připravenou databázi, tak můžeme začít prozkoumávat SSDT.

Založení projektu

Spustíme Visual Studio a založíme nový projekt

Import z existující databáze

Pokud bychom měli již existující databázi, která by obsahovala databázové objekty, tak je můžeme do našeho projektu naimportovat.

Struktura projektu

Na začátku projektu je dobré si zvolit jak organizovat soubory v projektu. Můžeme se řídit například výchozí strukturou schema/object type, která vypadá následovně (několik objektů jsem již vytvořil).

Deployment

V momentě kdy máme vytvořené tabulky, procedury, funkce apod. a chceme je nasadit na cílový databázový server, můžeme použít tzv. publish profil Projekt > Publish..., který nám provede buď přímo aktualizaci cílové databáze podle nastavení databázového projektu nebo si můžeme aktualizační script uložit do samostatného souboru. Například pokud v projektu vytvoříme novou tabulku, tak se nám tabulka při aktualizaci vytvoří i v databázi. Pokud by při nasazování mělo dojít k tzv. breaking změně, která by mohla způsobit ztrátu dat, tak takovou aktualizaci musíme povolit.

Publish profil je dobré uložit přímo do projektu a pro každé prostředí zvlášť. Díky tomu pak můžeme nasazovat naše změny na jednotlivá prostředí velmi jednoduše. Zde je potřeba dát pozor na dvě věci, v publish profilu by nemělo být uložené heslo a zároveň ho doporučuji ukládat do GIT repositáře. Při použití GITu je potřeba zkontrolovat soubor .gitignore, jestli náhodou publish profily neignoruje.

Databázové vlastnosti

V Projekt > Properties je možné nastavit mnoho vlastností, které ve výchozím stavu přepisují nastavení cílové databáze. Tady je potřeba být domluvený s Administrátorem serveru a vědět jaké databázové vlastnosti chceme mít nastavené a jestli je spravovat v projektu. Z mojí zkušenosti dávám pozor na nastavení především následujících vlastností

  • Target platform
  • Database collation
  • Compatibility level
  • Trustworthy

Scripty

V projektu můžeme mít uložené i scripty, které nám slouží pouze pro servisní zásahy nebo jednorázové operace. Takové scripty pak musíme vyloučit z buildu. V případě potřeby můžeme pomocí build action vlastnosti vytvořit i tzv. pre-deployment a post-deployment scripty, které se spustí před nebo po publikaci.

SQL CLR

Máme možnost napsat C# kód a ten volat jako funkci nebo proceduru z T-SQL. Jedná se o velmi mocnou funkcionalitu a SSDT vytváření takových kódů velmi zjednodušuje. Například si můžeme vytvořit funkci, která nám zjistí jestli se jedná o přestupný rok.

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [SqlFunction]
    public static SqlBoolean IsLeapYear(int year)
    {
        return DateTime.IsLeapYear(year);
    }
}
Abychom mohli takové funkce spouštět tak musí mít databáze nastavenou konfigurační hodnotu clr enabled na 1. Konfiguraci můžeme nastavit ručně nebo pomocí pre-deployment scriptu.
if ((select value_in_use from sys.configurations where [name] = 'clr enabled') = 0)
begin
  EXEC sp_configure 'clr enabled', 1;  
  RECONFIGURE;   
end
Dále je potřeba v databázi buď vypnout CLR strict security nastavením hodnoty na 0
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'clr strict security', 0; 
RECONFIGURE;
nebo databázi přepnout do Trustworthy módu a nebo podepsat .dll knihovnu. Detaily jsou popsány v dokumentaci.
Potom nám už nic nebrání naší C# funkci použít následovně
select dbo.IsLeapYear(2022)

Schema compare

Další zajímavou funkcí je porovnání databáze, projektu nebo dacpac balíčku. Funkci vyvoláme ze solution explorera Projekt > Schema Compare. Pokud jsme například dělali změny přímo v cílové databázi, tak si můžeme zkontrolovat jestli je náš projekt aktuální nebo jestli v databázi není něco navíc. Například podle obrázku nám v projektu chybí uživatel DatabaseOperatorBackup, máme nepublikovanou novou tabulku dbo.Bar a liší se nám view dbo.vwTopFoos. Pokud bychom se rozhodli pro automatickou aktualizaci tak musíme být velmi opatrní a vědět co jsme označili za zdroj a cíl.

Reference na další databáze

Do projektu můžeme přidat odkaz na jiný databázový projekt, systémovou databázi (msdb nebo master) a nebo DACPAC balíček. Při přidávání zvolíme kde se databáze nachází a tím se nám vytvoří proměnné, které můžeme použít kdekoliv ve scriptech. Hodnota proměnných se pak nastavuje v publish profilu. Při používání externích databázích mě vyhovuje odkazy na vzdálené tabulky vytvářet jako synonyma, protože pak vidíme závislosti na jednom místě. Synonyma se také hodí v případě, že chceme mít pouze jeden databázový kontext v Entity Frameworku a chceme se vyhnout distribuovaným transakcím, pokud nejsou nutné. Nevýhodou je, že bohužel přijdeme o IntelliSense nápovědu.

Závěr

V tomto článku jsme si nainstalovali prostředí pro databázový vývoj a prošli jsme si hlavní funkce databázového projektu ve Visual Studiu. Teď už stačí jen navrhovat a kódit 🙂 Pokud máte zkušenost s databázovým vývojem ve Visual Studiu, dejte vědět do komentářů 😉

Odkazy