Jak využít PowerShell DSC k automatizaci konfigurace serverů v SQL Server prostředí

PowerShell Desired State Configuration (DSC) představuje silný nástroj pro správu konfigurace, který umožňuje automatizované a opakovatelné nastavení serverů v souladu s definovanými standardy organizace. Tento přístup řeší problémy manuální konfigurace a konfigurace pomocí předem připravených obrazů, které mohou vést k nekonzistenci v prostředí serveru. DSC přináší řešení tím, že nejen zajišťuje počáteční nastavení, ale také udržuje server v požadovaném stavu během jeho životního cyklu.

Existuje několik způsobů, jak lze server konfigurovat. Tradiční manuální konfigurace je časově náročná a náchylná k lidským chybám, což často vede k nejednotnosti v nastavení serverů. I když se použije přístup založený na "golden image" – tedy obraz disku, který je zkopírován na více serverů, stále se mohou objevit problémy s flexibilitou. Každý server musí být 100% identický, což znamená, že jakákoli variace v nastavení vyžaduje správu několika různých obrazů nebo manuální změny, které mohou opět vést k problémům s konzistencí konfigurace v průběhu času.

Konfigurace prostředí pomocí DSC nabízí robustní řešení. Tento přístup se liší od předchozích metod tím, že skript není spuštěn pouze jednou při nasazení, ale pravidelně na základě definovaného plánu. Každý běh skriptu zkontroluje aktuální stav serveru a pokud došlo k jakýmkoli změnám (driftu), automaticky obnoví požadovanou konfiguraci. To znamená, že servery zůstávají vždy v souladu s požadavky organizace, a to bez ohledu na to, jaké změny byly provedeny v mezičase.

Pro implementaci DSC existují dva hlavní přístupy. Prvním je lokální implementace na úrovni jednotlivých strojů, což je ideální pro menší prostředí nebo testování. Tento přístup nevyžaduje žádný dodatečný software, protože DSC je součástí Windows Management Framework 5.1, což umožňuje snadný začátek bez složité konfigurace. Druhou možností je centrální správa, která je vhodná pro větší organizace s mnoha servery. Tato metoda používá centrální server, tzv. Pull Server, který spravuje konfigurace více strojů z jednoho místa. Takové řešení výrazně snižuje administrativní náklady při správě rozsáhlého IT prostředí.

DSC se dělí na dvě hlavní verze. Verze 1.1 je vhodná pro menší prostředí a je podporována v PowerShell 5.1, zatímco verze 2.0 je zaměřena na cloudová řešení, jako je Azure, a vyžaduje PowerShell 7 a vyšší. Je důležité poznamenat, že DSC verze 3.0 je stále v režimu preview, což znamená, že některé funkce mohou být ve vývoji a ne všechny jsou stabilní pro produkční prostředí.

Základními stavebními bloky DSC jsou konfigurace a zdroje. Konfigurace definují požadovaný stav systému, zatímco zdroje jsou implementace těchto stavů. Každý zdroj má tři hlavní metody: Get, Test a Set. Metoda Get zjistí aktuální stav daného prvku, metoda Test ověří, zda je server v požadovaném stavu, a metoda Set aplikuje požadovanou konfiguraci, pokud je to nutné. V rámci DSC existuje široká škála vestavěných zdrojů, které pokrývají širokou paletu nastavení – od správy služeb a uživatelů po specifické konfigurace pro aplikace, jako je SQL Server.

V praxi můžete použít DSC k provádění konfigurací na úrovni operačního systému, například pro sladění s bezpečnostními standardy nebo pro kontrolu stavu služby SQL Server Database Engine. Jakmile je konfigurace definována v DSC, může být tento proces plně automatizován, čímž se zajistí trvalá shoda mezi očekávaným a aktuálním stavem serveru. Takto lze jednoduše zajistit, že servery jsou vždy připraveny na provoz v souladu s organizačními požadavky, aniž by bylo nutné provádět manuální kontroly nebo zásahy.

Jak využít PowerShell pro automatizaci správy SQL Server databází

PowerShell se stal nezbytným nástrojem pro administrátory databází, zejména pro automatizaci úkolů spojených s údržbou SQL Serveru. Pomocí modulu sqlserver mohou správci efektivně vykonávat různé operace přímo z PowerShell skriptů, což značně zjednodušuje a zrychluje správu databázového prostředí. V tomto textu se podíváme na praktické využití těchto nástrojů při správě databází a rekonstrukci indexů.

sql
Copy code
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = (
    SELECT 'ALTER INDEX ' 
        + QUOTENAME(i.name) 
        + ' ON ' + s.name 
        + '.' 
        + QUOTENAME(OBJECT_NAME(i.object_id)) 
        + ' REBUILD ; '

    FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL, NULL,'DETAILED') ps 

    INNER JOIN sys.indexes i 
        ON ps.object_id = i.object_id 
        AND ps.index_id = i.index_id
    INNER JOIN sys.objects o 
        ON ps.object_id = o.object_id 
    INNER JOIN sys.schemas s 
        ON o.schema_id = s.schema_id
    WHERE index_level = 0
    AND avg_fragmentation_in_percent > 25
    FOR XML PATH('')
);
EXEC(@SQL);

Tento skript obnovuje indexy, které mají více než 25% fragmentace. S přidáním této logiky do PowerShell skriptu můžeme dynamicky provádět tuto operaci na všech databázích na našem SQL Serveru. Pro tento účel se hodí následující skript v PowerShellu, který provede rekonstrukci indexů na všech databázích, které máme na serveru.

powershell
Copy code
$Params = @{

    ServerInstance = "localhost"
    Query          = "SELECT name FROM sys.databases"
}
$Databases = Invoke-Sqlcmd @Params
foreach ($Database in $Databases) {
    $Params = @{
        ServerInstance = "localhost"
        Database       = $Database.name
        InputFile      = "c:\scripts\IndexRebuild.sql"
    }
    Invoke-Sqlcmd @Params
}

V tomto skriptu používáme Invoke-Sqlcmd k získání seznamu databází a následně každé databázi provádíme operaci obnovy indexů podle předem definovaného skriptu uloženého v souboru IndexRebuild.sql. Tento přístup má jednu významnou výhodu: nevyžaduje žádnou statickou správu seznamu databází, což zjednodušuje správu ve víceinstančním prostředí.

sql
Copy code
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = (
    SELECT 'ALTER INDEX ' 
        + QUOTENAME(i.name) 
        + ' ON ' + s.name 
        + '.' 
        + QUOTENAME(OBJECT_NAME(i.object_id)) 
        + ' REBUILD ; '

    FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL, NULL,'DETAILED') ps

    INNER JOIN sys.indexes i 
        ON ps.object_id = i.object_id 
        AND ps.index_id = i.index_id
    INNER JOIN sys.objects o 
        ON ps.object_id = o.object_id
    INNER JOIN sys.schemas s 
        ON o.schema_id = s.schema_id
    WHERE index_level = 0
    AND (avg_fragmentation_in_percent > $(Fragmentation) 
        OR avg_page_space_used_in_percent < $(PageSpace))
    FOR XML PATH('')
);
EXEC(@SQL);

V tomto upraveném skriptu jsou do podmínky WHERE přidány nové proměnné Fragmentation a PageSpace, které mohou být definovány při vykonávání skriptu. Pomocí těchto proměnných lze dynamicky řídit chování skriptu, což dává administrátorovi velkou flexibilitu.

Pro předání těchto proměnných do skriptu slouží parametr Variable v příkazu Invoke-Sqlcmd. Pomocí následujícího příkladu můžeme skript spustit s konkrétními hodnotami pro proměnné:

powershell
Copy code
$Variables = "Fragmentation=25", "PageSpace=70"

$Params = @{
    ServerInstance = "localhost"
    Query          = "SELECT name FROM sys.databases"
}
$Databases = Invoke-Sqlcmd @Params
foreach ($Database in $Databases) {
    $Params = @{
        ServerInstance = "localhost"
        Database       = $Database.name
        InputFile      = "c:\scripts\IndexRebuild.sql"
        Variable       = $Variables
    }
    Invoke-Sqlcmd @Params
}

Při tomto přístupu se proměnné Fragmentation a PageSpace použijí k ovládání podmínek pro obnovu indexů. Tento způsob parametrizace skriptů výrazně zjednodušuje jejich použití v různých prostředích, kde se mohou hodnoty fragmentace nebo použitého prostoru měnit.

powershell
Copy code
$Params = @{
    ServerInstance   = "localhost"
    Query            = "SELECT name FROM sys.databases WHERE db_id = 1"
    OutputSqlErrors  = $false
}
$Databases = Invoke-Sqlcmd @Params
$Params = @{
    ServerInstance   = "localhost"
    Query            = "SELECT name FROM sys.databases WHERE db_id = 1"
    OutputSqlErrors  = $true
}
$Databases = Invoke-Sqlcmd @Params

V tomto příkladu bude první vykonání dotazu bez výstupu chyb, zatímco druhé vykonání zobrazí konkrétní chybovou zprávu, která by v tomto případě poukázala na chybu v názvu sloupce.

Výsledky dotazů mohou být také snadno zapisovány do souboru, což je užitečné pro archivaci nebo pro následnou analýzu. K tomu slouží kombinace Invoke-Sqlcmd a Out-File.

powershell
Copy code
$Params = @{

    ServerInstance  = "localhost"
    Query           = "SELECT name FROM sys.databases"
}
Invoke-Sqlcmd @Params | Out-File -FilePath "c:\scripts\DatabasesOut.txt"

Tento příkaz zapíše seznam databází do textového souboru. Pro komplexnější záznamy je možné využít převod do formátu CSV pomocí ConvertTo-Csv.

V rámci administrace SQL Serveru je také důležité mít na paměti, že výchozí kontext vykonání SQL skriptů je založen na Windows autentifikaci. To znamená, že skripty budou vykonávány pod bezpečnostním kontextem uživatele, který skript spustil. Důležité je zajistit, aby tento uživatel měl dostatečná oprávnění pro všechny požadované operace.

Jak správně modelovat data pro inventární databázi a normalizace

Při návrhu databáze je nezbytné nejprve definovat, jaká data budeme uchovávat, a až poté se zaměřit na její strukturu. Tento první krok zahrnuje identifikaci atributů, které je nutné zapsat. Pro databázové vývojáře to obvykle znamená komunikaci s analytiky nebo obchodními zástupci, aby se zjistily konkrétní požadavky. U aplikací je tato úloha často zajišťována rámcem, jakým je například Entity Framework. U administrátorů databází, kteří mají za úkol vytvořit inventární databázi, je nutné, aby se ujali jak role vývojáře, tak i obchodního zástupce.

Mezi klíčové atributy, které by měly být zaznamenány v databázi, patří například název serveru, název instance, port, IP adresa, údaje o účtech, verze SQL Serveru a Windows, specifikace DR serveru, a další specifikace, které souvisejí s konfigurací serveru a jeho správou.

Jakmile máme jasno v požadovaných datech, přichází na řadu proces nazývaný normalizace, který je zásadní pro správné uspořádání dat do vzorců. Normalizace je proces, který v roce 1970 představil Edgar F. Codd a který se stále používá i dnes, byť došlo k několika rozšířením formy normalizace. Celkově existuje osm normalizačních forem, ale pro běžné účely je dostatečné pracovat se třemi základními formami – 1NF, 2NF a 3NF.

Normalizace a její výhody

Normalizace je především určena pro databáze OLTP (Online Transactional Processing). Pro datové sklady a datové martky by měla být použita denormalizace, která je efektivnější pro zajištění vysokého výkonu. ODS (Operational Data Store) bývá modelováno podle různých heterogenních zdrojů, které spojuje.

Normalizační formy

2NF (Druhá normální forma) zajišťuje, že všechny atributy, které nejsou klíčem, jsou závislé na celém klíči entity. Tato fáze je klíčová pro odstranění částečných závislostí mezi atributy.

3NF (Třetí normální forma) pak zabezpečuje, že žádný atribut, který není klíčem, není závislý na jiném atributu, který není klíčem. Tento proces eliminuje transitive závislosti.

Pokud má databáze splněny podmínky 3NF, obvykle se říká, že je v DKNF (Domain Key Normal Form), což je poslední z normalizačních form, která se používá pro specifické případy.

Příklad implementace 1NF

Začneme tím, že zkontrolujeme, zda jsou všechny požadované atributy atomické. Například pokud máme atributy jako „název serveru“ nebo „verze Windows“, je zřejmé, že každá hodnota je jedinečná a nezahrnuje více informací. To splňuje podmínky pro 1NF.

Nicméně, pokud máme atributy jako „název účtu a heslo“ (například pro různé služby), je třeba tyto sloučené hodnoty rozdělit na jednotlivé položky – tedy „název účtu“ a „heslo“. Tento krok je zásadní pro správné uchování a následné zpracování dat.

Modelování entit

Po rozdělení atributů do atomických hodnot je třeba data seskupit do entit. Například máme entitu Server, která bude obsahovat atributy jako název serveru, verze systému, jádra serveru, velikost RAM, virtuální příznak a e-mail vlastníka aplikace. Pro každou instanci serveru by pak existovala samostatná entita.

Pokud se podíváme na specifické příklady, tak atributy jako „IP adresa“, „port“ nebo „název instance“ by patřily do samostatné entity, například Instance. Ta by navíc musela obsahovat odkaz na entitu Server (pomocí cizího klíče).

Důležitost modelování dat

Při vytváření databázových modelů je také důležité pamatovat na specifické potřeby dané aplikace nebo organizace. Když má například aplikace specifické požadavky na výkon, je potřeba při návrhu zohlednit denormalizaci a různé optimalizační techniky, které mohou výrazně zlepšit rychlost operací.

Jak navrhnout a implementovat centralizovaný plán údržby pro SQL servery

Centralizovaný přístup k údržbě SQL serverů je klíčovým krokem k optimalizaci provozu a zajištění efektivity správy databázových instancí. V prostředí, kde jsou SQL instance běžící na sdílených prostředcích, například v privátním cloudu, je důležité zvážit, jak správně naplánovat údržbu, aby se předešlo přeplnění systémových zdrojů. Pokud by se všechny úkoly zálohování a údržby spustily ve stejný čas, mohlo by to vést k výraznému poklesu výkonu a zvýšení rizika konfliktů mezi úkoly.

Tabulka MaintenanceWindows bude klíčovým prvkem pro definování časových oken údržby. Tato tabulka bude obsahovat informace o každém serveru a instanci, včetně dne v týdnu, kdy je možné provádět údržbu, a času, kdy toto okno začíná a končí. Je důležité mít v tabulce definované cizí klíče, které budou propojeny s tabulkami Server a Instance, aby bylo možné jednoznačně identifikovat, které úkoly údržby se vztahují ke kterému serveru a instanci.

Další tabulka, MaintenanceTasks, bude definovat konkrétní úkoly údržby, které mají být provedeny na jednotlivých instancích. Tato tabulka by měla obsahovat sloupce pro označení typu úkolu (např. zálohování, odstranění starých záloh, aktualizace statistik) a také pro zaznamenání časového plánu jejich provádění. Důležitým aspektem je přidání možnosti vypnutí určitých úkolů pro konkrétní servery, což může být užitečné v případě specifických potřeb nebo omezení.

Výhled na plánování údržby, MaintenanceSchedule, bude na základě výše zmíněných tabulek vypočítávat čas, kdy má být každý úkol proveden. Tento pohled bude sloužit jako vstupní bod pro úkoly SQL Server Agentu, které budou volat příslušné PowerShell skripty a vykonávat údržbu na jednotlivých instancích serverů. Pro minimalizaci duplicitního kódu v údržbových úkolech bude tento pohled obsahovat všechny potřebné sloupce pro vyhodnocení, který úkol by měl být spuštěn na kterém serveru.

Při návrhu těchto tabulek a pohledů je třeba mít na paměti, že data, která budou uložena v těchto objektech, by měla být co nejvíce flexibilní, aby bylo možné snadno přizpůsobit plánování různým potřebám organizace. Například v tabulce MaintenanceWindows je třeba vzít v úvahu flexibilitu při definování plánů údržby – denní, týdenní nebo vyjádření plánu v minutách. Pomocí tohoto přístupu lze snadno upravit frekvenci úkolů na základě specifických potřeb organizace.

V tabulce MaintenanceTasks se budou definovat úkoly údržby na úrovni serveru, instance a úkolu, přičemž se bude udržovat historie posledního provedení každého úkolu. Sloupec, který bude udávat plán v minutách, umožní přesné určení frekvence provádění úkolu, což přispívá k flexibilitě celého plánu údržby.

Pokud jde o samotnou implementaci centralizované údržby, SQL Server Agent bude naplánován na spuštění každou minutu, aby mohl zkontrolovat, zda je potřeba provést údržbové úkoly. Každý úkol bude mít svůj vlastní krok v rámci jobu, a každý krok bude volat odpovídající PowerShell skript. PowerShell skripty budou využívat data z pohledu MaintenanceSchedule k určení, které úkoly je potřeba provést na konkrétních instancích.

V tomto kontextu se může vyvstat otázka, jaké úkoly údržby je třeba pravidelně provádět. Naše ukázka zahrnuje následující úkoly: úplné zálohování databází, odstranění starých zálohovacích souborů a aktualizaci statistik. Samozřejmě, seznam úkolů bude záviset na konkrétních požadavcích organizace a jejím IT prostředí. Například je běžnou praxí zahrnout do údržby obnovu indexů, což je důležitý úkol pro zajištění efektivity dotazů.

Při návrhu tohoto systému by měli správci DB (DBA) zvážit jakýkoli specifický hardware nebo prostředí, na kterém systém běží, aby byl systém co nejefektivnější a nejvíce přizpůsobený aktuálním potřebám a omezením.

Jak automatizovat údržbu SQL Serveru pomocí PowerShell a SQL Agent Jobs

Ve světě správy SQL Serveru je nezbytné mít dobře nastavený a efektivní systém údržby, který zajišťuje, že databázové servery běží hladce a bez přerušení. Jedním z nástrojů pro automatizaci údržby je PowerShell, který může být použit k provádění různých údržbových úkolů na serverech SQL. Tento proces obvykle zahrnuje zálohování databází, čištění starých záloh, aktualizaci statistik nebo opravy indexů. V tomto kontextu se zaměříme na tvorbu skriptů a nastavení údržby prostřednictvím SQL Agent Jobů.

PowerShell poskytuje velkou flexibilitu při interakci s SQL Serverem. V tomto scénáři máme několik úkolů, které jsou zautomatizovány skripty a běží na SQL Server Agentovi, což umožňuje centralizovanou správu údržby napříč několika servery.

Údržba SQL Serveru zahrnuje různé kroky, jako je například odstranění starých záloh, aktualizace statistik a pravidelná záloha databází. Každý z těchto úkolů má svůj vlastní skript, který je naplánován ke spuštění v určitou dobu. Tímto způsobem lze zajistit, že každý server v infrastruktuře bude pravidelně udržován bez nutnosti manuálních zásahů.

Odstranění starých záloh
V prvním případě je úkol zajištěn skriptem PowerShell, který provádí následující akce: zjistí všechny zálohy, které jsou starší než stanovený limit (například 3 dny), a odstraní je. Tento úkol se spouští na základě informací uložených v databázi Inventory, která obsahuje plán údržby pro každý server a databázi. Skript nejprve získá úkoly, které jsou plánovány k provedení, následně označí úkol jako "v procesu" a začne provádět samotné odstranění záloh.

Aktualizace statistik
Další skript se zaměřuje na aktualizaci statistik na všech databázích na daném serveru. Tato operace je důležitá pro optimalizaci výkonu SQL Serveru, protože statistiky slouží jako podklady pro optimalizátor dotazů. Skript získá seznam databází na serveru a následně provede příkaz sp_updatestats, který zajišťuje aktualizaci statistik pro každou databázi. I v tomto případě se úkol označuje jako "v procesu" a po dokončení se stav úkolu aktualizuje na "úspěšný".

Vytvoření SQL Agent Jobu
Vytvoření a naplánování úkolů na SQL Serveru pomocí SQL Agent Jobs je klíčové pro automatizaci celého procesu. Skripty PowerShell jsou uloženy na centrálním serveru a spouštěny podle předem definovaného rozvrhu. Pro každý úkol (např. zálohování, odstranění starých záloh, aktualizace statistik) je vytvořen samostatný krok v rámci jednoho jobu. Tento job může být naplánován tak, aby se spouštěl pravidelně, například každou minutu. Pokud není žádný úkol k provedení, job se jednoduše přesune k dalšímu kroku bez provedení jakékoliv akce.

Pokročilé možnosti v nastavení údržby
Pokud je potřeba ještě větší granularita v údržbě, lze rozšířit strukturu tabulky MaintenanceTasks o úroveň jednotlivých databází. To by umožnilo naplánovat různé databáze na stejném serveru pro zálohování v různých časech, což by optimalizovalo celkovou zátěž serveru. Dále lze do pohledu MaintenanceSchedule přidat sloupec pro LastExecStatus, což umožní přidání logiky pro opakování úkolu v případě selhání (například pokus o znovu spuštění úkolu po určitém čase).

Kromě základních úkolů je také možné přidat jiné skripty, které budou provádět údržbu databázových indexů, kontrolu integrity databáze nebo provádění dalších operačních úkolů, které zajišťují optimální výkon SQL Serveru.

Pro tyto operace je důležité, aby měl SQL Server Agent přístup k souborům skriptů a aby správně nastavený účet služby mohl tyto skripty spouštět. V tomto případě jsou skripty umístěny v adresáři C:\scripts, kam má agent přístup.

Důležité poznámky a doporučení
V průběhu implementace takovýchto skriptů a jobů je kladeno důraz na jejich testování v testovacím prostředí před nasazením na produkční servery. Před spuštěním každého úkolu je také nezbytné monitorovat výkon serveru a zkontrolovat, zda údržbové úkoly neovlivňují dostupnost aplikací nebo uživatelskou zkušenost. Také je důležité mít nastavený systém notifikací, který bude informovat administrátora o úspěchu nebo selhání jednotlivých kroků.