MSSQL — Zálohování a obnova dat

Informace uložené v databázi často představují cenný a důležitý majetek. V zájmu společnosti, která data vlastní, je proto tato data organizovat, inventarizovat, zabezpečovat a zálohovat. Je totiž přirozeným požadavkem, aby data byla k dispozici pracovníkům v kterýkoli okamžik (současný i budoucí).

Microsoft SQL Server obsahuje pro zálohování a obnovu dat samostatnou, výkonově optimalizovanou komponentu. Implementací dobře naplánované strategie zálohování a obnovy dat můžeme databázi ochránit před ztrátou dat způsobenou nejrůznějšími příčinami.

Pravidelně vytvářené zálohy nám umožní obnovit data po haváriích jako jsou:

  • chyby úložného média
  • chyby uživatele (např. omylem smazaná tabulka)
  • chyby hardwaru, přírodní katastrofy

Kromě záchrany dat po haváriích slouží zálohy také k rutinním administrativním účelům, jako např. kopírování databází z jednoho serveru na jiný, archivace apod.

Zálohovat lze celou databázi, část databáze nebo jednotlivé soubory a skupiny souborů. Pro každou z těchto variant podporuje SQL Server jak úplnou zálohu (full backup) tak i rozdílovou zálohu (differential backup).

Zálohovací strategie

Zálohovací strategie zahrnuje jednak zálohování (backup) a jednak obnovu (restore) dat.

Zálohování (backup) definuje:

  • typ a frekvenci záloh
  • vyžadovaný hardware (a jeho rychlost)
  • testování záloh
  • umístění zálohovacích médií (vč. bezpečnosti)

Obnova (restore) definuje:

  • kdo je zodpovědný za provádění obnovy
  • jak mají být obnovy prováděny (vzhledem ke snaze o vysokou dostupnost databáze a minimální ztráty dat)

Zálohovací strategie záleží na konkrétní situaci a konfiguraci. Je potřeba zvážit požadovanou dostupnost databáze, její velikost, typická použití, povahu uložených dat a dále omezení týkající se hardwaru, personálu a fyzického zabezpečení zálohovacích médií.

Způsoby zálohování

Z hlediska zálohování je pro databázovou transakci klíčovou vlastností trvalost (durability). Trvalost znamená, že jakmile je proveden commit transakce, musí být tato transakce perzistentní bez ohledu na případné chyby hardwaru. Microsoft SQL Server tuto perzistenci zajišťuje pomocí transakčního žurnálu (transaction log), do něhož je každá transakce zaznamenána ještě před zápisem do datového souboru.

Transakční žurnál:

  • zajišťuje možnost obnovy všech transakcí až do chvíle výpadku serveru
  • umožňuje provádění záloh během zpracování transakcí
  • může zmírnit následky výpadku hardwaru, pokud je umístěn na jiném disku, než datové soubory

S konfigurací transakčního žurnálu úzce souvisí tzv. modely obnovy (recovery models). Microsoft SQL server nabízí tři recovery modely:

  • simple — transakční žurnál je dočasné povahy a transakce jsou v něm uchovávány pouze do té doby, než je potvrzeno úspěšné zapsání do datového souboru; žurnál tedy pozbývá smysl zálohovat
  • full — transakční žurnál je separátně zálohován a jsou v něm uloženy všechny transakce; jedná se o nejrobustnější řešení
  • bulk-logged — transakční žurnál je separátně zálohován, ovšem nejsou do něj ukládány tzv. bulk-logged transakce (viz. níže)

Společně s těmito modely nabízí SQL Server následující možnosti zálohování:

  • full — kompletní záloha veškerých dat
  • differential — záloha pouze těch datových stránek, které byly změněny od posledního full backupu
  • transaction log — záloha transakčního logu
  • file or filegroup — záloha datového souboru nebo skupiny souborů
  • file differential — záloha těch datových stránek, které byly změněny od posledního file nebo filegroup backupu

Simple Recovery model

Tento model je vhodný pro databáze, kde je důležitá pouze atomičnost transakcí, nikoli však tolik jejich perzistence. SQL Server zaznamená transakci do žurnálu, ovšem uchovává ji zde pouze do doby potvrzení úspěšného zapsání do datového souboru. Tzv. bulk-logged transakce vůbec nejsou do žurnálu ukládány.

Záznamy v transakčním žurnálu jsou tedy pouze dočasné povahy a tento log tedy není zálohován. Výhodou je minimalizace velikosti žurnálu, ovšem hrozí ztráta všech transakcí provedených od posledního full nebo differential backupu.

Následuje příklad plánu obnovy v případě použití Simple Recovery modelu. Dvakrát týdně provádíme full backup a všechny ostatní dny v týdnu differential backup:

Tento recovery model zvolíme tak, že nastavíme truncate log at checkpoint option na true.

Obnova se při použití tohoto modelu skládá ze dvou kroků:

  1. Obnovíme nejaktuálnější full backup.
  2. Obnovíme nejaktuálnější differential backup (pokud byl od nejaktuálnějšího full backupu nějaký proveden).

Full Recovery model

Tento model nabízí nejrobustnější plán zotavení. Všechny transakce (vč. tzv. bulk-logged transakcí, viz. níže) i systémové funkce (jako např. vytvoření indexu) jsou ukládány do transakčního žurnálu. Hlavní výhodou z toho plynoucí je, že všechny transakce mohou být obnoveny až do chvíle těsně před výpadkem. Přirozenou nevýhodou je nárůst velikosti transakčního žurnálu, stejně jako zpomalení bulk-logged transakcí.

Jedná se o model doporučovaný pro produkční databáze. Transakční žurnál by měl být uložen na separátním diskovém úložišti.

Následuje příklad plánu obnovy pro Full Recovery Model. Typicky se provádí 2× týdně full backup a všechny ostatní dny differential backup. Kromě toho v průběhu dne zálohujeme transakční žurnál. Frekvence záloh žurnálu závisí na důležitosti perzistence transakcí. V méně kritických situacích postačí zálohovat žurnál 2× denně, více kritické situace si mohou vynutit zálohu žurnálu např. každých 15 minut.

Obnova se při použití tohoto modelu skládá ze čtyř kroků:

  1. Provedeme zálohu transakčního žurnálu (pokud je ten ztracen, pak můžeme provést obnovu pouze do okamžiku jeho poslední zálohy).
  2. Obnovíme nejaktuálnější full backup.
  3. Obnovíme nejaktuálnější differential backup (pokud byl od nejaktuálnějšího full backupu nějaký proveden).
  4. Postupně (dodržujíce pořadí) obnovíme všechny zálohy transakčního logu provedené od posledního full/differential backupu.

Bulk-Logged Recovery model

Zde je nejprve vhodné vysvětlit pojem bulk-logged transkace. Jedná se o transakce obcházející transakční žurnál a ukládající data přímo do datového souboru. Používají se hlavně při kopírování velkých objemů dat (v řádech stovek MB) do databáze. Typickou bulk-logged transakcí je bulk insert umožňující načtení dat z CSV souboru, např.:

BULK INSERT Osoby
FROM ‘C:\osoby.csv’
WITH (FIRSTROW = 1,ROWTERMINATOR =’\n’);

Tento recovery model je velmi podobný předchozímu full recovery modelu, ovšem full recovery model zaznamenává do žurnálu právě i bulk-logged transakce, čímž je razantně zpomaluje.

Protože při použití Bulk-Logged Recovery modelu nejsou bulk-logged transakce ukládány do žurnálu, probíhají tyto transakce velmi rychle. Do transakčního žurnálu jsou uloženy pouze informace o tom, že k transakci došlo, a dále tzv. extents neboli seznam datových stránek, které byly touto transakcí ovlivněny. Při zálohování žurnálu jsou pak tyto stránky do zálohy skutečně zkopírovány.

Pokud dojde k výpadku po bulk-logged transakci, aniž by byla provedena záloha transakčního žurnálu, jsou výsledky této transakce ztraceny. Proto by měla při použití tohoto modelu každá bulk-logged transakce ihned následována zálohou transakčního žurnálu.

Tento recovery model zvolíme tak, že nastavíme Select Into/Bulkcopy option na true.

Provádění záloh

Společně s Microsoft SQL Serverem je dodáváno i Microsoft SQL Server Management Studio umožňující provádět velké množství administrativních úkonů bez znalosti jazyka SQL. Tyto úkony samozřejmě můžeme provádět i “ručně” pomocí SQL příkazů.

Nastavení recovery modelu

Recovery model je databázi přidělen při jejím vzniku, později jej však můžeme změnit.

Následuje ukázka formuláře pro vytvoření databáze, který nám umožňuje zvolit recovery model:

Pomocí SQL změníme recovery model následovně:

ALTER DATABASE jmeno_db SET Recovery (Full|Bulk_Logged|Simple)

Vytvoření zálohy

Jak ukazují následující obrázky, vytvoření zálohy databáze je s využitím SQL Server Management Studia velmi snadné:

Full backup provedeme pomocí SQL následovně:

BACKUP DATABASE [MojeDB]
  TO DISK = N'C:\Backup\MojeDB.bak'
  WITH NAME = 'MojeDB-Full Database Backup'

Differential backup provedeme přidáním klíčového slova DIFFERENTIAL:

BACKUP DATABASE [MojeDB]
  TO DISK = N'C:\Backup\MojeDB-diff1.bak'
  WITH
    DIFFERENTIAL,
    NAME = 'MojeDB-Differential Database Backup'

Chceme-li provést zálohu transakčního žurnálu, musíme použít klíčové slovo LOG:

BACKUP LOG [MojeDB]
  TO DISK = N'C:\Backup\MojeDB.LOG.bak'
  WITH NAME = 'MojeDB-Transaction Log  Backup'
 
referaty/mssql/zalohovani_a_obnova_dat.txt · Last modified: 2009-02-11 22:06 by rehort3
 
Recent changes RSS feed Creative Commons License Donate Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki