OrgPad logo

MS SQL Server

Created by Vladimír Roller

Stránky pro výuku produktu MS SQL Server na SPŠS Olomouc

MS SQL Server

Fungování transakčního protokolu

Write-Ahead Logging

 

Checkpointing

- proces, který zapisuje aktuální stav transakčního protokolu na disk
- kdykoli se vytvoří checkpoint, SQL Server zaznamená všechny neuložené změny na disk →   usnadňuje a zrychluje obnovu databáze po výpadku
- checkpoint se může vytvořit automaticky nebo manuálně pomocí příkazu CHECKPOINT

Minimalizace protokolu v režimu Bulk-Logged

V případě velkých objemů dat, které je třeba zapsat najednou (např. operace bulk insert nebo index rebuild), je možné použít Bulk-Logged Recovery Mode. Tento režim minimalizuje množství záznamů v protokolu, což může snížit čas zápisu a velikost protokolu.

Režimy obnovy 

Režimy obnovy SQL Server umožňuje různé režimy obnovy transakčního protokolu, které určují, jak je transakční protokol spravován:

Typy souborů -v SQL Serveru

  1. datové soubory
  2. soubory protokolu/transakčního protokolu

Transakční protokol ↔

Hlavní funkce transakčního logu

  1. obnova databáze - v případě selhání serveru nebo výpadku proudu umožňuje transakční log obnovit databázi do konzistentního stavu
  2. zajištění konzistence - umožňuje vrátit zpět neúspěšné transakce (Rollback)
  3. replikace a zálohování - klíčový pro replikaci dat (přenos provedených změn na další místa) a pro vytváření záloh databáze (full, diferential, incremental)

Struktura transakčního logu

Transakční log je rozdělen do několika částí:

  1. Vstupní záznamy (Log Records)
    - každá operace, která mění data, je zaznamenána jako log record
    - každá taková změna je zpracovávána jako transakce
    - záznamy obsahují informace o typu operace, identifikátoru transakce, časovém razítku a dalších detailech

  2. Vstupní stránky (Log Pages)
    - Log Records jsou organizovány do Log Pages, což jsou základní jednotky ukládání v transakčním logu
    - každá Log Page má pevnou velikost 8 KB

  3. Vstupní soubory (Log Files)
    - Log pages jsou uloženy v log files
    - databáze může mít více log files, které mohou být umístěny na různých fyzických discích pro zvýšení výkonu.

Optimalizace a správa transakčního logu

Truncation: Proces, který odstraňuje staré log records, které již nejsou potřeba pro obnovu databáze. Tento proces zajišťuje, že transakční log neroste nekontrolovaně.

Zálohování logu: Pravidelné zálohování transakčního logu je důležité pro správu jeho velikosti a pro zajištění možnosti obnovy databáze do konkrétního bodu v čase.

Struktura Log Records

  1. Identifikátor transakce - Transaction ID
    - každý log record je spojen s konkrétní transakcí, což umožňuje sledovat všechny operace provedené v rámci této transakce
  2. Log Sequence Number - LSN
    - každý log record má unikátní LSN, který určuje pořadí záznamů v logu
    - používá se k určení, které operace byly provedeny a v jakém pořadí
  3. Typ operace - Operation Type
    - zaznamenává typ operace (např. INSERT, UPDATE, DELETE) a další detaily o změně
  4. Předchozí a nové hodnoty
    - u operací, které mění data, log record obsahuje jak původní hodnoty before image, tak nové hodnoty - after image
    - to umožňuje vrácení změn - rollback nebo jejich opětovné provedení - redo

Struktura Log Pages

  1. Header (Hlavička)
    - každá log page začíná hlavičkou
    - obsahuje metadata - jako je číslo stránky, LSN (Log Sequence Number) první a poslední operace na stránce, a další kontrolní informace
  2. Log Records (Záznamy): Následuje oblast, kde jsou uloženy jednotlivé log records. Tyto záznamy obsahují detaily o transakcích, jako jsou typ operace, identifikátor transakce, časové razítko, a změněné hodnoty.
  3. Trailer (Závěr): Na konci stránky může být závěr, který obsahuje kontrolní součet nebo další informace pro ověření integrity stránky.

Funkce Log Pages

Správa a údržba transakčního protokolu

Transakční protokol může růst až na velmi velké velikosti, pokud není pravidelně spravován. Údržba zahrnuje:

Datové soubory

  1. primární datový soubor
    - každá databáze má jeden primární datový soubor
    - může obsahovat samotná data a odkazy na další soubory v databázi
    - doporučená přípona souboru je .mdf (implicitní přípona)
  2. sekundární datové soubory
    - volitelné uživatelsky definované soubory (nemusí být použity vůbec)
    - používají se např. pokud je potřeba data(bázi) rozšířit na více disků apod.
    - doporučená přípona souborů je .ndf

Soubory protokolu

Vytvoření PK

Při vytváření tabulky

 

 

1. v příkazu CREATE TABLE

CREATE TABLE Tridy (

ID tinyint NOT NULL PRIMARY KEY,

…..

);

2. v prostředí MMC

image

 

 

Změnou stávající tabulky

1. příkazem ALTER TABLE

ALTER TABLE Tridy

ADD PRIMARY KEY (ID);

2. v prostředí MMC

SQL Server integration services

SQL Server management studio

ssms

  1. tvorbu a návrh databází
    -usnadňuje vytváření databází, tabulek a dalších databázových objektů
    - umožňuje navrhovat datová schémata, definovat strukturu datového úložiště ad.
  2.  návrh a provádění dotazů
    - umožňuje uživatelům vytvářet, optimalizovat a spouštět SQL dotazy  a tím získávat požadovaná data
    - umožňuje definované dotazy ukládat a dále s nimi pracovat
  3.  monitorování a správa serveru
    - poskytuje nástroje panel pro sledování stavu serveru, výkonnostních metrik a využití zdrojů
    - umožňuje správcům vyladit nastavení serveru pro optimální výkon
  4. export a import dat
    - poskytuje nástroje pro rychlý, intuitivní a jednoduchý import/export dat z a do různých formátů

SQL Server analysis services

PRIMARY KEY - Primární klíč

ACID transactions

ACID

SQL Server database engine

  1. správa databáze - Database management
     - správa všeho, co databáze obsahují - tabulky, pohledy, indexy, uložené procedury a další..
    - poskytuje strukturované prostředí pro ukládání dat
  2. zpracování dotazů - Query processing
    - pomocí Query Processoru převádí dotazy SQL do akcí, které lze provést
     - optimalizuje dotazy SQL a navrhuje nejúčinnější cestu k získání požadovaných dat z databáze
    - provádí dotazy a získává požadovaná dat
  3. transakce a datová integrita -  Transactions and data integrity
    - zajišťuje integritu dat prostřednictvím principů ACID Transactions
    - zajišťuje, že každá změna dat dodržuje pravidla, která udržují konzistenci a spolehlivost dat

SQL Server reporting services

word-image9

Vytvoření UNIQUE

Při vytváření tabulky

 

 

1. v příkazu CREATE TABLE

CREATE TABLE Tridy (

ID tinyint NOT NULL PRIMARY KEY,

Nazev char(5) NOT NULL UNIQUE

);

nebo s pojmenováním

CREATE TABLE Tridy (

ID tinyint NOT NULL PRIMARY KEY,

Nazev char(5) NOT NULL

CONSTRAINT UQ_nazev UNIQUE (nazev)

);

2. v prostředí MMC

image

UNIQUE

Vytvoření CHECK

Při vytváření tabulky

 

 

1. v příkazu CREATE TABLE

CREATE TABLE Znamky (

ID int NOT NULL PRIMARY KEY,

Znamka tinyint NOT NULL CHECK (Znamky>=1 AND Znamka<=5),

...

);

nebo s pojmenováním

CREATE TABLE Znamky (

ID int NOT NULL PRIMARY KEY,

Znamka tinyint NOT NULL,

CONSTRAINT CHK_znamka CHECK (Znamky>=1 AND Znamka<=5),

...

);

CONSTRAINTS - Omezení

Omezení v SQL jsou pravidla, která definují podmínky pro hodnoty v databázových tabulkách. Tato omezení zajišťují integritu dat a správné chování databáze

Některé základní typy omezení:

  1. Primární klíč (PRIMARY KEY)
  2. Unikátní omezení (UNIQUE)
  3. Cizí klíč (FOREIGN KEY)
  4. Kontrolní omezení (CHECK)
    - kontrolní omezení definuje podmínky, které musí být splněny pro hodnoty v určitém sloupci
  5. NOT NULL
    - sloupec s tímto omezením nesmí obsahovat hodnoty NULL

CHECK

SQL - Data Definition Langauage

Enterprise

Standard

Web

Developer

Vytvoření Foreign Key

Příkazem ALTER TABLE

s pojmenováním FK

ALTER TABLE zaci

ADD CONSTRAINT FK_Tridy

FOREIGN KEY (Trida)

REFERENCES Tridy(ID);

FOREIGN KEY - Cizí klíč

Klíčové komponenty - Key Components

obrazek

Express

CREATE TABLE

CREATE TABLE table_name (

 column1 datatype,

column2 datatype,

column3 datatype,   .…

);

Příklad:

CREATE TABLE Tridy (

ID tinyint,

nazev char(5)

);

Edice MS SQL Serveru

Srovnání jednotlivých edicí

Editions and supported features of SQL Server 2022 - SQL Server | Microsoft Learn

Jak zjistím, jaká jsou omezení na tabulce?

  1. SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

    WHERE table_name = 'nazev_tabulky';

    - INFORMATION_SCHEMA - poskytuje komplexní pohled na metadata spojená s každou databází; Funguje jako katalog, který obsahuje informace o tabulkách, pohledech, uložených procedurách, sloupcích, omezeních a dalších objektech v databázi

  2. Pomocí pohledů do systémového(objektového katalogu);
    např.  SELECT * FROM sys.foreign_keys;

  3. Pomocí MMC v části Keys nebo Constraints

MS SQL Server

Systémové databáze

Odstranění omezení

Odstranění omezení je možno pomocí příkazu

ALTER TABLE Nazev_tabulky

DROP CONSTRAINT Nazev_omezeni

Některá omezení je možno odstranit pomocí MMC konzoly

Služby SQL serveru

SQL Server

SQL Browser

Master

Model

TempDB

MSDB

SQL Server Agent

SQL Agent používá následující komponenty:

  1. Úlohy (Jobs) - eefinují administrativní úkol, který lze spustit jednou nebo vícekrát a sledovat jeho úspěch nebo neúspěch
  2. Kroky úlohy (Job Steps) - každý krok v úloze může obsahovat například spuštění Transact-SQL příkazu, spuštění SSIS balíčku nebo vydání příkazu serveru Analysis Services apod.
  3. Harmonogramy (Schedules): určují, kdy se má úloha spustit. Více úloh může běžet na stejném harmonogramu a na jednu úlohu může být aplikováno více harmonogramů.

Data a inforamce pro SQL Server agenta (Jobs. Tasks, Job Steps, Schedules) jsou uloženy systémové databázi MSDB.

Transact SQL

T-SQL je robustní programovací a dotazovací jazyk používaný v souvislosti s relačními databázemi, zejména v prostředí Microsoft SQL Server. T-SQL nabízí funkce, které umožňují:

  1. Ukládání hodnot do proměnných: umožňuje dočasně ukládat hodnoty a pracovat s nimi v rámci skriptů
  2. Podmíněné spouštění příkazů: umožňuje provádět různé akce na základě podmínek; např. můžete provést určitý příkaz pouze tehdy, pokud platí určitá podmínka apod.
  3. Předávání parametrů uloženým procedurám:  umožňuje definovat a používat parametry při volání uložených procedur (stored procedure)
  4. Řízení toku programů: můžeme používat cykly, podmínky a další konstrukce pro řízení toku programů

Krátký kurz - úvod do jazyka T-SQL

Úvod do jazyka Transact-SQL - Training | Microsoft Learn

SQL-Auth

Datové typy - Data Types

V SQL Serveru má každý sloupec v tabulce, místní proměnná, výraz a parametr nějaký odpovídající datový typ.

Pro ukládání dat je nutné dle jejich povahy, rozsahu ukládáných hodnot apod. zvolit odpovídající vhodný datový typ. Co je nutno zohlednit:

Zdroj ke studiu:

https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver16

Import dat do DB systémů

Všechny systémy a aplikace, které pracují s daty (SQL systémy, Excel apod.) umožňují import (i export) dat z jiných systémů.

Pro přípravu cvičných dat můžeme použít různé online generátory.

Např.

Generate a Random Name - Fake Name Generator

MS SQL - zdroje ke studiu

Číselné - Numeric

Slouží pro ukládání číselných hodnot.

Přesná čísla - Exact numerics

Čísla se při použití těchto datových typů ukládají přesně, neprovádí se žádné zaokrouhlování.

Znakové, textové - Character, text

Slouží pro ukládání textových (znakových) dat

Binární datové typy

Slouží pro ukádání dat v binární poddobě

CSV soubory

Import dat z CSV do Excelu

Import-dat-z-csv-souboru-do-Excelu.pdf

Celá čísla - Integer

Desetinná čísla

Přibližná čísla - Approximate numerics

Datové typy pro práci s datumem a časem

obrazek

Tinyint

Int

Bigint

Money, Smallmoney

Smallmoney

Money

Bit

Float

Char, varchar

char

varchar

nchar, nvarchar

Text, nText

Úvod

SQL Server nabízí dva hlavní ověřovací módy

  1. Windows Authentication Mode (Integrované ověřování)
  2. Mixed Mode (Smíšené ověřování) – který v sobě zahrnuje Windows Authentication i SQL Server Authentication

Best Practices a bezpečnost

  1. Používat co nejvíce Windows Authentication
    - považováno za bezpečnější, protože se spoléhá na mechanismy Active Directory
  2. Silná hesla
    - pokud se použije Mixed Mode, vždy vyžadujte silné heslo (kombinace malých a velkých písmen, číslic a speciálních znaků)
  3. Omezit a monitorovat účet sa
    - účet sa buď zcela zakázat, nebo jej přejmenovat; používat jej pouze v nutných případech a zavést audit přihlášení
  4. Používat skupiny z AD
    - při Windows Authentication je lepší přidělovat přístup AD skupinám než přímo uživatelům - zjednoduší se správa systému
  5. Používat protokol SSL/TLS (kryptované spojení)
    - zabezpečit komunikaci mezi klientem a SQL Serverem, aby nebyly hesla přenášena v otevřené podobě
  6. Pravidelně monitorovat logy
    - sledovat neúspěšné pokusy o přihlášení, podezřelé aktivity; nastavit e-mailové upozornění na útok hrubou silou

Smallint

Decimal, Numeric

obrazek

Real

Binary, Varbinary

Deklarace varbinary(max)

Mixed Mode ↔

Výhody

  1. Flexibilita
    • možnost spravovat doménové uživatele i nezávislé SQL uživatele
    • jedna instance SQL Serveru může obsluhovat jak interní firemní aplikace (Windows Authentication), tak externí (SQL loginy)
  2. Kompatibilita
    • starší aplikace, které nepodporují Windows Authentication, mohou používat SQL loginy
    • v některých scénářích je to jediný způsob, jak aplikace připojit
  3. Autonomie
    • pro vybrané účely (test, vývoj, externí přístupy) může administrátor SQL Serveru spravovat účty, aniž by musel žádat o úpravu AD skupin

Nevýhody

  1. Nižší úroveň zabezpečení (obecně v porovnání s Windows Authentication)
    • SQL Server musí udržovat a chránit hesla uživatelů.
    • uživatel může mít slabé heslo (pokud není v SQL Serveru aktivní politika silného hesla)
    • hesla jsou potencionálně zranitelnější, pokud je někdo uvidí v konfiguračním souboru aplikace
  2. Dvojí správa
    • administrátor SQL Serveru musí udržovat dvě sady přihlašovacích údajů – Windows loginy a SQL loginy
    • větší náchylnost k chybám, pokud se nezavede systematická politika (kdo používá Windows, kdo SQL loginy, jak se hesla mění atd.)
  3. Potenciální riziko sdílených účtů
    • může se stát, že aplikace a uživatelé sdílí jeden SQL login - pak je těžké dohledat, kdo co dělal v databázi

Důvody pro použití Mixed Mode

  1. Externí přístup
    • pokud se k SQL Serveru potřebují připojovat uživatelé či aplikace, které přístup do firemní domény (např. zákazníci, partneři, externí aplikace běžící mimo AD)
  2. Starší nebo nekonfigurovatelné aplikace
    • některé starší aplikace vyžadují explicitně SQL loginy (např. předávají connectionstring s uživatelským jménem a heslem)
    • aplikace, které neumí pracovat s integrovaným ověřováním
  3. Testovací a vývojové scénáře
    • vývojáři často nastaví Mixed Mode, aby mohli rychle vytvářet testovací účty pro různá prostředí, bez nutnosti zasahovat do doménových skupin
  4. Scénáře bez domény
    • v menších firmách nebo v izolovaných sítích, kde není k dispozici Active Directory, se mohou Windows účty spravovat lokálně, ale pro jednodušší administraci se přesto může použít SQL Authentication

Bezpečnostní a provozní aspekty

  1. Silná hesla a politika hesel
    • je nutné mít zapnuté vynucení složitosti hesla a dobu expirace (pokud to odpovídá firemnímu standardu)
    • především heslo pro sa musí být velmi dobře zabezpečené, jelikož sa je výchozí nejvyšší administrátor
  2. Použití integrovaných bezpečnostních prvků
    • SQL Server dokáže vynucovat Windows password policy i na SQL loginy, pokud běží na OS Windows Server
    • možnost kontrolovat minimální délku, složitost a historii hesel
  3. Audit a logování
    • doporučuje se zapnout auditing či aspoň logování neúspěšných pokusů o přihlášení (login auditing) → lze tak odhalit pokusy o útoky hrubou sílou
  4. Uzamčení a přejmenování sa
    • v produkčním prostředí se doporučuje přejmenovat účet sa 
    • někdy se účet sa zcela zakáže (disable)
  5. Přístupové role
    • podobně jako u Windows Authentication je důležité nepřidávat SQL loginům globálně roli sysadmin, pokud to není nutné
    • dodržovat princip nejmenších potřebných oprávnění (least privileges)

Windows Authentication Mode ↔

Výhody

  1. Vyšší bezpečnost
    • samotný SQL Server neuchovává hesla pro uživatele využívající tento mód
    • politika hesel (délka, složitost, platnost atd.) se řídí nastavením v AD (nebo lokálně v OS, pokud nejde o doménu)
    • lze využít Kerberos protokol, který je bezpečnější než NTLM a podporuje pokročilé scénáře (např. delegaci, která se využívá v některých serverových aplikacích)
  2. Centralizovaná správa účtů
    • všechny uživatelské účty a skupiny lze spravovat centrálně v doméně AD
    • přidáním uživatele do skupiny v AD se mu automaticky udělí (či odejme) přístup i v SQL Serveru (pokud je tato skupina mapována na login)
  3. Jednoduchost přihlašování (Single Sign-On)
    • uživatel se jednou přihlásí do Windows a poté se může připojit k SQL Serveru bez opětovného zadávání přihlašovacích údajů
    • toto lze využívat i v některých aplikacích, které pracujíc s SQL serverem, např. MS Excel
    • snižuje se riziko, že uživatelé budou volit slabá hesla (nebo je sdílet)
  4. Audit a sledování
    • když je uživatel ověřen Windows, je v logu SQL Serveru i v auditních stopách vidět skutečný účet uživatele (např. DOMENA\Uzivatel)
    • v případě potřeby je snazší dohledat odpovědnost za akce prováděné na serveru

Nevýhody

  1. Závislost na doménovém prostředí (AD)
    • pro plné využití benefitů (např. Kerberos, jednodušší správa skupin) je ideální, když je SQL Server a klienti ve stejné (nebo důvěryhodné) doméně
    • pokud organizace nemá doménu AD, může to být komplikované – i když Windows Authentication funguje i na lokální účty, správa je pak méně flexibilní
    • nemožnost použití v non Windows prostředích
  2. Komplikovanější scénáře pro externí uživatele
    • pokud potřebujeme povolit přístup do SQL Serveru i uživatelům mimo firemní síť (např. z internetu), bývá to náročnější na nastavení (VPN, federace identit, atd.).
    • V některých případech to vede k potřebě Mixed Mode a k využití SQL loginu.
  3. Kerberos konfigurační nároky
    • aby fungoval Kerberos na 100 % (např. pro delegaci nebo pro dvoucestnou důvěru), je potřeba správně konfigurovat tzv. Service Principal Name (SPN)
    • nevhodné či chybné nastavení SPN může vést k selháním ověřování nebo k přechodu zpět na méně bezpečný NTLM

Jak to funguje

  1. Ověření na úrovni operačního systému
    • uživatel se nejprve přihlásí do operačního systému Windows, typicky ve firemním prostředí do domény Active Directory (AD)
    • při přihlášení Windows generují nebo obnoví přihlašovací token (Access Token), který obsahuje údaje o uživateli, případně o skupinách (Groups), do nichž je uživatel zařazen (platí zejména pro doménu AD)
  2. Předání tokenu
    • když se uživatel pokusí připojit k SQL Serveru, Windows předají tomuto serveru Access Token
    • SQL Server na jeho základě pozná, že uživatel byl již úspěšně ověřen v rámci Windows
  3. Mapování na SQL Server loginy a oprávnění
    • SQL Server interně mapuje ověřenou identitu (uživatele nebo skupiny Windows/AD) na tzv. login v SQL Serveru
    • pokud je tento login (nebo skupina) v SQL Serveru zaregistrovaný, může uživatel provádět akce dle přidělených oprávnění (role, přístup k databázím, apod.)
  4. Bez opětovného zadávání přihlašovacích údajů
    • uživatel v praxi nevidí žádné další přihlašovací okno pro SQL Server - nezadává žádné přihlašovací údaje
    • přihlášení proběhne na pozadí díky tokenu → princip Single Sign-On (SSO)

Bezpečnostní aspekty a doporučení

  1. Silná firemní bezpečnostní politika
    • komplexní hesla, pravidelné změny hesel (pokud to firemní politika vyžaduje), zámek účtu po několika chybných pokusech – to vše je zajištěno na úrovni AD
    • SQL Server se nemusí starat o implementaci a vynucení těchto pravidel
  2. Používat Kerberos, pokud je to možné
    • Kerberos je bezpečnější a podporuje Single Sign-On, ověřování server-to-server, delegaci služeb atd.
    • Při použití Active Directory se Kerberos aktivuje většinou automaticky, pokud je správně nastaven SPN (Service Principal Name)
  3. Logy a audit
    • v SQL Serveru se do logu ukládá přímo doménový účet uživatele, což výrazně ulehčuje auditování
    • je vhodné mít nastavené pravidelné reporty o úspěšných a neúspěšných přihlášeních.
  4. Oddělení privilegovaných rolí
    • doporučuje se vytvořit v AD skupinu pro administrátory SQL Serveru a tu namapovat na roli sysadmin SQL serveru
    • vyhýbejte se přidávání jednotlivých uživatelů na úroveň sysadmin, pokud to není nezbytné
  5. Ošetření připojení z jiných prostředí
    • pokud aplikace běží mimo doménu, je potřeba určit, jak se bude ověřování řešit (VPN, federace AD FS, apod.)

SQL Server Agent ↔

Hlavní funkce SQL Server Agent

  1. Automatizace úloh (Jobs)
    - umožňuje vytvoření, plánování a správu úloh, které mohou zahrnovat T-SQL skripty, systémové příkazy, zálohování databází, údržbu indexů ad.
  2. Časové plánování (Schedules)
    - podporuje komplexní plánování úloh, jako jsou denní, týdenní, měsíční úkoly nebo úkoly na základě specifických událostí
  3. Oznámení a alerty
    - při selhání nebo úspěšném dokončení úlohy může zasílat např. e-mailová upozornění, případně spouštět další akce (např. další job) nebo daný job opakovat apod.
  4. Historie a monitoring
    - uchovává podrobnou historii o prováděných úlohách, včetně informací o chybách a výkonu
  5. Integrace s MS SQL Serverem
    - je pevně integrovaný do SQL Serveru a využívá jeho bezpečnostní model a infrastrukturu
    - může být spravován prostřednictvím SQL Server Management Studia nebo skriptování pomocí T-SQL

Architektura SQL Server Agent

SQL Server Agent je tvořen několika hlavními komponentami:

  1. Jobs (Úlohy)
    • skládají se z jednoho nebo více kroků (steps)
    • každý krok může vykonávat určitou akci, například spuštění T-SQL skriptu, spuštění externího programu, vykonání uložené procedury ad.
  2. Steps (Kroky)
    • jednotlivé kroky, které job vykonává
    • každý krok má definovaný typ (např. T-SQL, CmdExec, PowerShell)
  3. Schedules (Plány)
    • definují, kdy a jak často se job spouští.
  4. Operators (Operátoři)
    • uživatelské účty nebo kontakty, kterým jsou zasílána oznámení
    • tito operátoři mohou s Joby dále pracovat
  5. Alerts (Upozornění)
    • umožňují reagovat na systémové události, jako jsou chyby nebo změny stavu systému.
  6. Target Servers (Cílové servery)
    SQL Server Agent podporuje více instancí serverů, což umožňuje spouštění jobů na různých cílových serverech

Job(y)↔

Komponenty Jobu

  1. Název a popis jobu
    • každý job má unikátní název, který slouží k jeho identifikaci
    • popis poskytuje stručný přehled o účelu jobu
  2. Kroky (Steps):
    • Job se skládá z jednoho nebo více kroků, aždý krok definuje konkrétní úlohu
    • typické kroky
      • Spuštění T-SQL příkazu nebo uložené procedury
      • Spuštění příkazového řádku (CmdExec)
      • Spuštění PowerShell skriptů
  3. Plány (Schedules)
    • určují, kdy a jak často bude job spuštěn
    • Možnosti plánování
      • Jednorázově v určený čas.
      • Opakovaně (denně, týdně, měsíčně)
      • V konkrétní dny v týdnu nebo měsíci
  4. Notifikace (Notifications)
    • upozornění při úspěšném dokončení nebo selhání jobu
    • notifikace mohou být zasílány e-mailem, zapisovány do logu, mohou spouštět alerty apod.
  5. Historie (History)
    • SQL Server uchovává podrobné informace o každém spuštění jobu, včetně úspěšnosti a případných chyb

Vytvoření jobu pomocí SQL Server Management Studio (SSMS)

  1. Otevření SQL Server Agent:
    • V SSMS rozbalte SQL Server Agent.
    • Klikněte pravým tlačítkem na Jobs → New Job.
  2. Nastavení základních informací:
    • V záložce General:
      • Zadejte název jobu.
      • Volitelně přidejte popis.
  3. Definování kroků jobu:
    • Přejděte do záložky Steps a klikněte na New.
    • Zadejte:
      • Název kroku.
      • Typ akce (např. Transact-SQL script (T-SQL)).
      • Skript, který má být vykonán: 
        BACKUP DATABASE [MyDatabase]
        TO DISK = 'C:\sqldata\zalohy\master.bak'
        WITH INIT, COMPRESSION;
    • Nastavte reakci na úspěch nebo selhání kroku (např. přejít na další krok, zastavit job).
  4. Plánování jobu:
    • Přejděte do záložky Schedules a klikněte na New.
    • Zadejte:
      • Název plánu.
      • Typ plánu (např. denní, týdenní, měsíční).
      • Počáteční čas a datum.
      • Frekvenci spuštění.
  5. Nastavení oznámení:
    • V záložce Notifications:
      • Nastavte způsob oznámení (např. e-mail při selhání).
      • Ujistěte se, že je povolen Database Mail.
  6. Uložení a testování:
    • Uložte job kliknutím na OK.
    • Proveďte ruční spuštění jobu, abyste ověřili jeho funkčnost.

2. Vytvoření jobu pomocí T-SQL:

Pomocí následujícího skriptu můžete vytvořit job, který zálohuje databázi

USE msdb;
GO

-- Vytvoření jobu
EXEC sp_add_job 
    @job_name = N'BackupDatabaseJob', 
    @enabled = 1, 
    @description = N'This job performs a daily backup of the database.',
    @start_step_id = 1;

-- Přidání kroku
EXEC sp_add_jobstep 
    @job_name = N'BackupDatabaseJob',
    @step_name = N'BackupDatabase',
    @subsystem = N'TSQL',
    @command = N'BACKUP DATABASE [MyDatabase] TO DISK = ''C:\Backups\MyDatabase.bak'' WITH INIT, COMPRESSION;',
    @on_success_action = 1;

-- Nastavení plánu
EXEC sp_add_jobschedule 
    @job_name = N'BackupDatabaseJob',
    @name = N'DailySchedule',
    @freq_type = 4, -- Denní plán
    @freq_interval = 1, -- Každý den
    @active_start_time = 030000; -- Spuštění ve 03:00

-- Přiřazení jobu SQL Server Agent
EXEC sp_add_jobserver 
    @job_name = N'BackupDatabaseJob';
GO

Správa jobů

  1. Zobrazení a úprava jobů:
    • V SSMS přejděte do SQL Server Agent → Jobs.
    • Klikněte pravým tlačítkem na job → Properties.
  2. Ruční spuštění jobu:
    • Pravým tlačítkem na job → Start Job at Step.
  3. Prohlížení historie jobů:
    • Pravým tlačítkem na job → View History.
    • Historie zobrazuje všechny pokusy o spuštění, jejich výsledky a případné chyby.
  4. Notifikace:
    • Ujistěte se, že jsou notifikace aktivní pro operátory (nastavte v SQL Server Agent → Operators).

Titulek

sysadmin ↔

Bezpečnostní opatření pro tuto roli

Jak si zobrazit, kdo všechno má roli sysadmin?

SELECT pr.name AS [User], sp.type_desc AS [User Type]
FROM sys.server_role_members rm
JOIN sys.server_principals pr ON rm.member_principal_id = pr.principal_id
JOIN sys.server_principals sp ON pr.principal_id = sp.principal_id
WHERE rm.role_principal_id = SUSER_ID('sysadmin');

Jak se stát sysadmin, pokud nemáte oprávnění?

Pokud nemáte přístup k SQL Serveru a potřebujete získat roli sysadmin, můžete zkusit:

Spuštění SQL Serveru v režimu obnovení administrátorského přístupu

  1. Zastavit SQL Server (např. přes SQL Server Configuration Manager)
  2. Spustit SQL Server v režimu single-user
    • otevřete příkazový řádek jako správce
    • spusťte SQL Server s parametrem -m
      net stop MSSQLSERVER
      net start MSSQLSERVER /m
  3. Připojit se pomocí sqlcmd a vytvořit nového sysadmina: 
    sqlcmd -S localhost CREATE LOGIN NewAdmin WITH PASSWORD = 'SecurePass123';
    ALTER SERVER ROLE sysadmin ADD MEMBER NewAdmin;
    GO

Po dokončení restartujte SQL Server normálně

serveradmin

Kdo má roli serveradmin?

SELECT pr.name AS [UserName], pr.type_desc AS [UserType]
FROM sys.server_role_members rm
JOIN sys.server_principals pr ON rm.member_principal_id = pr.principal_id
WHERE rm.role_principal_id = SUSER_ID('serveradmin');

securityadmin

Protože securityadmin může spravovat loginy a jejich oprávnění, existuje riziko eskalace – člen této role může např. přidělit jinému uživateli nebo sobě samotnému role s vyšším oprávněním, pokud to konfigurace serveru dovoluje. Proto je nutné tuto roli udělovat pouze důvěryhodným administrátorům.

Princip rolí

Hlavní výhody

processadmin

Server Roles - Serverové role

Typy serverových rolí:

Přiřazení role

pomocí SSMS

Security → Server Roles->pravé tlačítko na roli → Properties → Add - přidat uživatele nebo skupinu uživatelů

image

nebo příkazem (příklad - přiřazení role sysadmin)

ALTER SERVER ROLE sysadmin ADD MEMBER [user_name]

Odebrání role

ALTER SERVER ROLE sysadmin DROP MEMBER [user_name]

Přehled serverových rolí

image