Slovníček

Obsah

• Úvod
• Příprava prostředí
  • Instalace Visual Studia
  • Instalace serveru
  • Pripojeni k serveru
  • Vytvoření databáze
• Databázový projekt
  • Založení projektu
  • Import z existující databáze
  • Struktura projektu
  • Deployment
  • Databázové vlastnosti
  • Scripty
  • SQL CLR
  • Reference na další databáze
• Závěr

Ú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.

• Visual Studio
• SQL Server Management Studio
• Docker desktop

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ázový projekt

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.

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);
    }
}

if ((select value_in_use from sys.configurations where [name] = 'clr enabled') = 0)
begin
  EXEC sp_configure 'clr enabled', 1;  
  RECONFIGURE;   
end

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'clr strict security', 0; 
RECONFIGURE;

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

• SQL Server Data Tools
• Migrations Overview
• Entity Framework Core
• SQL Server
• Visual Studio
• SQL Server Management Studio (SSMS)
• Docker Desktop
• Visual Studio Community 2022 licenční podmínky
• SQL CLR
• DACPAC