Práce s recovery modely na SQL Serveru

SQL Server ukládá veškeré informace do dvou typů souborů. Každá databáze je tvořena datovým souborem a souborem pro uložení transakčního logu. Každá databáze na SQL Serveru má ve svých vlastnostech nastaven tzv. režim logování (Recovery Model). Celkem máme k dispozici 3 režimy, které ovlivňují styl práce s transakčním logem a také mění nároky na Disaster Recovery procesy.

V případě manipulace s daty je třeba, aby veškerá data potřebná pro danou operaci byla nejprve načtena do tzv. data bufferu. Následně v bufferu SQL Server uzamkne v několika krocích data, tak, aby zajistil, že uživatel nemůže data, která se právě mění, číst. Toto chování je ovlivnitelné pomocí několika režimů pro izolace transakcí, případně je ovlivnitelné pomocí tzv. hints v rámci dotazů. Po uzamčení veškerých dat může SQL Server provést např. změnu požadovaných dat a zároveň s touto změnou jsou veškeré údaje uloženy do transakčního logu.

Transakční log slouží jako úložiště všech transakcí, které na SQL Serveru probíhají. Pomocí logu je SQL Server schopen při nenadálém výpadku provést re-do nebo roll-back operace. Tedy všechny již dokončené transakce, které nebyly správně uloženy v datovém souboru, mohou být znovu spuštěny a všechny nedokončené transakce, které však byly uloženy do datového souboru, mohou být vráceny zpět. S vytvořením záznamu do transakčního logu totiž není spojena operace uložení dat na disk do datového souboru. Pouze u transakčního logu se informace ukládají pomocí force-write, tedy systém čeká na uložení dat a nejsou žádná data v paměti, ale jsou nutně zapsána na disk. U dat samotných dochází k zápisu datových stránek v rámci check-pointu, kdy jsou zapsány na disk datové stránky z data bufferu, které byly od posledního check-pointu změněny. V rámci check-point jsou na disk zapsány i ty datové stránky z bufferu, které obsahují data nedokončených transakcí, a právě v těchto případech by mohlo dojít k operaci roll-back dle logu po výpadku.

Transakční log je rozdělen do několika částí, tzv. Virtual Log Files (dále jen VLFs). Počet a velikost těchto VLFs se liší dle velikosti růstu logu. U každého souboru v databázi, jak datového tak i logu, můžeme nastavit možnosti pro automatické zvětšení souboru. Můžeme využít poměrové zvětšování o jednotky nebo desítky procent, nebo přírůstek definovaný v MB.

V následující tabulce jsou vypsány počty a velikosti VLFs v závislosti na zvětšení transakčního logu.

Velmi důležitým aspektem automatického růstu transakčního logu je jeho inicializace, kdy celý nový blok logu musí být inicializován na nulové hodnoty. Nefunguje tedy rychlá Instant File Inicialization jako u datových souborů. Jednotlivé nové VLFs jsou označeny jako neaktivní, dokud není nutné využít jejich úložný prostor pro transakce. VLF obsahuje jednotlivé informace, které mohou být využity mnoha funkcemi v rámci databáze:

• Mirroring
• Transakční replikace
• Change Data Capture
• Log Shipping
• Log backup
• Full backup
• Differential backup

Jednotlivé informace o využití logů lze vyčíst pomocí dotazu

SELECT name, log_reuse_wait_desc FROM sys.databases

V rámci transakčního logu zůstávají jednotlivé transakce tak dlouho, dokud není log vyčištěn. K čištění logu může dojít v několika případech v závislosti na úrovni logování a pouze za předpokladu, že daný VLF již neobsahuje aktivní záznamy, které jsou aktuálně využívány. Jednou z možností je záloha transakčního logu, a to v případě kdy používáme Full nebo Bulk-logged recovery model. V případě simple recovery modelu dochází k čištění logu při operaci check-point. Je nutné zdůraznit, že čištění logu „log cleaning“ nebo také „log truncate“ pouze označí daný VLF jako neaktivní a neprovede žádné nulování. Stejně tak se nemění velikost transakčního logu a tento zůstává na disku ve stejné velikosti a stejným počtem VLFs.

Jak tedy vlastně recovery modely ovlivňují práci s databází? V závislosti na nastavení modelu se mění styl čištění logu a možnosti disaster recovery. Což znamená, že při nastavení režimu simple

• Je log čištěn při operaci check-point
• Není možná záloha logu
• Je stejná úroveň logování informací jako u Bulk-logged modelu

Model bulk-logged se používá pro urychlení práce s databází. Jedná se o model s menší úrovní logování než full model, kdy v logu mohou být některé operace logovány pouze minimálně pro urychlení práce. Jedná se zejména o dávkové operace např BULK INSERT, SELECT INTO, CREATE INDEX, ALTER INDEX, WRITETEXT, příkaz bcp. Při využití Bulk-logged recovery režimu je pravděpodobné, že při nutnosti disaster recovery nebude možné vytvořit tail-log backup pro zálohu aktivní části transakčního logu. V rámci tohoto modelu nelze očekávat, že zálohy transakčního logu budou velikostně menší, než zálohy v modelu full. Zálohy mohou dosahovat i větších velikostí než ve full modelu, protože při záloze logu jsou zároveň ukládány i datové stránky, aby byla zajištěna obnovitelnost díky minimálnímu režimu logování. U bulk-logged modelu je navíc nutné zajistit velmi rychlé datové úložiště, protože na rozdíl od full modelu SQL Server vyžaduje, aby data byla při modifikaci uložena na disk ihned a ne až při check-point procesu.

Nejvyšším režimem logování je full recovery model, ve kterém jsou ve výchozím nastavení všechny nové databáze. Toto nastavení se kopíruje z databáze model pří vytvoření nové db. V rámci tohoto režimu je možné provést v případě výpadků zálohu aktivní částí transakčního logu, a tedy obnovit databázi do původního stavu. Tento model je vyžadován například pro AlwaysOn u SQL Server 2012, pro database mirroring a jiné. V rámci tohoto režimu jsou mimo jiné do logu ukládány například i index operace, které jsou ve zbylých dvou zařazeny do seznamu minimálního logování.

V rámci databáze by měl být k dispozici pouze jeden soubor s transakčním logem. SQL Server umí velmi dobře využívat paralelního přístupu k souborům, ale u logu tuto možnost nepoužívá. Nezískáme tím tedy nic na výkonnosti, pouze se ztíží management. Pro práci s logem je vhodné dodržet několik základních pravidel:

• Oddělit data a transakční log
• Pro úložiště logu využít RAID1, RAID10 nebo RAID01
• Volit vhodnou počáteční velikost transakčního logu
• Kontrolovat externí a interní defragmentaci
• Auto-grow by neměl přesáhnout 8GB v jednom procesu
• Nemělo by být využito více než „stovky“ VLFs
• V případě full recovery modelu zálohovat transakční log

Nastavení režimu logování databáze má vliv na výkon, správu i disaster recovery. Není vždy nutné mít databázi nastavenou pro full recovery model. Vše záleží na technických požadavcích v rámci nasazení systému.