Backup & recovery, SQL Server Agent, Alerts Jiří Tomeš Administrace MS SQL Serveru (NDBI039)
O čem to dnes bude
Úvodní motivace
Strategie zálohování
Typy zálohování
SQL Server Agent
Modely pro obnovení
Alerts
Zálohovací zařízení
Použité prameny a zdroje
Automatická záloha
Prostor pro dotazy
Úvodní motivace
Smysl zálohování databází :
Je třeba umět s daty nejen pracovat, ale také se o ně i starat
Může se totiž stát, že o ně můžeme nenávratně přijít (např. odejde disk, …)
Zálohováním se zmenšují transakční logy
Zálohovaná data můžeme v případě havárie obnovit Podíváme se, jaké možnosti zálohování a obnovy nabízí SQL server !!!
O zálohovacích strategiích Strategie týkající se zálohování :
Obsahuje záložní a obnovovací část
Definuje se typ, četnost záloh, povaha a rychlost hardwaru Definuje, jak se mají zálohy testovat a na jaká media se mají ukládat
Zálohu
je nutné si „vyzkoušet“
Kdo je zodpovědný za provádění a jak ho provést s minimalizací ztráty dat
Doporučené je používání dokumentace a komentářů
Vybíráme vhodnou strategii Poté, co jsme si vybrali vhodný model, je třeba danou strategii realizovat. Abychom dosáhli optima, je třeba zvážit :
Jak často přistupujeme k databázím Jak často je měníme a aktualizujeme Kolik potřebného místa na zálohu budeme potřebovat
sp_spaceused
http://msdn.microsoft.com/en-us/library/ms188776.aspx
Vybrat si správný typ zálohy (ruční rozhodně NE)
Typy zálohování SQL server rozlišuje následující typy:
Úplná záloha (FULL)
Rozdílová záloha (DIFFERENCIAL)
Záloha kompletní databáze
Záloha změn po provedení úplné zálohy dat
Záloha transakčního logu (TRANSACTION LOG) Lze provést i v případě, že není k dispozici soubor s daty Např. při selhání disku
Záloha souborů a skupin (FILES & FILEGROUPS)
Zálohujeme vybrané soubory nebo celé skupiny
Offline data není možné zálohovat !!!
Modely obnovení Základní typy modelů pro SQL server:
Full Recovery model Kompletní
ochrana proti selhání Možnost obnovení dat až do nějakého časového okamžiku (k bodu selhání) V případě obnovení z transakčního logu je třeba, aby byl neporušený
Bulk – Logged model obsahuje
všechny informace jako „FULL“, ale ve zjednodušené podobě obnovení dat vždy jen na konci zálohy (nepodporuje libovolný časový bod)
Simple Recovery model stejný
jako „BULK“, pouze jiné chování v kontrolních bodech (CHECKPOINTS)
Obnovení do časového okamžiku
Jak zjistit / nastavit model
Přes SQL server Management Studio Databases
-> User (System) database -> Properties -> Database properties -> Options -> Recovery model list box
Pomocí dotazu (Transact –SQL) SELECT name, recovery_model_desc FROM sys.databases WHERE name = ‘model’ ; Zjištění GO modelu
Nastavení modelu
ALTER DATABASE nazev_db SET RECOVERY FULL | BULK_LOGGED | SIMPLE ;
FULL backup záloha 1/2
Simple Recovery model bere
v úvahu potenciální pracovní ztráty v případě havárie každá aktualizace = zvýšení ztráty
záloha = ztráty se vynulují
TSQL příkaz
BACKUP DATABASE [db_name] TO DISK=‘c:\zaloha.bak’ WITH FORMAT; GO
FULL backup záloha 2/2
FULL & Bulk-logged Recovery model Záloha
transakčního logu je zde též potřebná TSQL příkaz
BACKUP DATABASE | LOG [db_name] TO DISK=‘c:\zaloha.bak’ WITH FORMAT; GO
FULL B&R pomocí SSMS 1/3
Záloha
database -> kontextové menu -> task -> backup jméno (povinně) + popis (volitelně)
Obnova database -> restore database
1) Zvolíme zdroj (FROM device) 2) Procházet -> vyberu soubor se zálohou 3) Jméno nové databáze (TO database) 4) Zvolíme vybranou zálohu, co bude obnovena
FULL B&R pomocí SSMS 2/3 Záloha (backup)
FULL B&R pomocí SSMS 3/3 Obnova (recovery, restore)
DIFFERENCIAL backup
Zaznamenávají se pouze změněná data od vytvoření plné zálohy (je to rychlejší než plná záloha) Pro obnovení rozdílné zálohy je třeba mít obnovený i její příslušný základ Hodí se, je-li podmnožina databáze upravována častěji než zbytek (spotřeba menší režie) Její použití může snížit počet zálohování logu, které bude potřeba obnovit Zachycují stav všech rozsahů (kolekce 8 fyzicky souvislých stránek)
TRANSACTION LOG backup
Pouze pro FULL nebo BULK-LOGGED Recovery model
Je potřeba mít před tím vytvořenou alespoň 1 plnou zálohu
Doporučená je zejména častá záloha Mnohem kratší log Minimalizace možných ztrát
Optimální interval zálohy závisí především na Velikost databáze Význam dat Zatížení serveru
FILE & FILEGROUP backup
Cílem je zajistit zálohu 1 nebo více souborů bez zálohy celé databáze Pro SIMPLE recovery model se to týká pouze READ-ONLY souborů
Základní typy obnovení:
OFFLINE (podpora u SQL 2012) Databáze
musí být v době obnovení OFFLINE, pak je provede obnovení a může být znovu ONLINE
ONLINE Je-li
databáze ONLINE v době obnovení, v době zpracování souborů (skupin) je daná instance OFFLINE a hned po jejich dokončení znovu ONLINE
FILE & FILEGROUP recovery Scénář (pro SIMPLE model) :
Obnovit každý poškozený soubor z jeho poslední zálohy Obnovit poslední relevantní DIFFERENCIAL backup pro každý obnovený soubor a obnovit celou databázi RESTORE DATABASE name, name1, ... FILE = filename,finaname1, … FROM backup_of_file_and_file1_and_… WITH (NO)RECOVERY;
RECOVERY = jiné zálohací zařízení
Zálohovací zařízení
Je to vlastně soubor, kam lze zálohy ukládat SP_ADDUMPDEVICE sp_addumpdevice [@devtype=] ‘device_type’ --možné hodnoty DISK (pevný disk) PIPE (roura v síťovém prostředí) TAPE (magnetická páska) [@logicalname =] ‘logical_name’ --název zálohovacího zařízení [@physicalname=] ‘physical_name’ --fyzická cesta k souboru, kam se bude ukládat
Příklad: sp_addumpdevice ‘DISK’ , ‘test_dev’, ‘c:\test.bak’
Automatická záloha
Použijeme plánovač úloh Windows pro spouštění skriptu se zálohou Skript s příponou .cmd vyvolá vytvoření zálohy Vytvoření zálohy provedeme pomocí nástroje SQLCMD
ProgramFiles\Microsoft SQL server\verze\Tools\Binn\SQLCMD.exe 90 pro SQL Server 2005
100 pro SQL Server 2008
ProgramFiles\..\SQLSCMD.exe –U jmeno –P heslo –q “BACKUP DATABASE [db_name] TO DISK=‘c:\zaloha.bak’ WITH NAME= N ‘jmeno’”
SQL Server Agent Je služba systému Windows pro vykonávání naplánovaných administrativních úloh („JOBS“) Používá SQL server pro ukládání informací k úloze 1 JOBS= jeden či více JOB kroků 1 JOB KROK= vlastní task (např. záloha databáze) Puštění je buď v reakci na nějakou událost nebo na požádání V případě, že nastane nějaká problém upozorní nás (zaznamená událost)
Komponenty SQL Server Agenta
JOBS
Sada akcí, které SQL server vykoná Lze spustit 1 i vícekrát, podle 1 či více plánů, jako reakce na 1 či více záznamů. sledovat úspěch / neúspěch,… Úloha může běžet na 1 lokálním serveru nebo na více vzdálených serverech Pro spuštění lze též zavolat proceduru sp_start_job Každý JOB krok má svůj kontext zabezpečení
SCHEDULES Určuje, kdy job běží 1 či více jobů může běžet na stejném plánovači Plánovač může definovat podmínky
Kdykoliv se SQL server Agent spustí Jednou v určitý den a čas Při opakujícím se plánu Využití CPU počítače je definováno jako „nečinné“
Další komponenty SQL SA
ALLERTS Ještě
o nich bude řeč
OPERATORS Definují
kontaktní informace pro zodpovědnost za udržování 1 nebo více instancí SQL Serveru Operátor nemá informace o bezpečnosti a ani o možných principech zabezpečení SQL Server může upozornit operátory například prostřednictvím
Emailu Pageru (pomocí služby emailu) NET SENDu
Server Agent a bezpečnost SQL Agent používá k řížení přístupu mimo sysadmin tyto ROLE:
SQLAgentUserRole SQLAgentReaderRole SQLAgentOperatorRole
Všichni mají přístup k Server Agentovi
Subsystémy (pro zajištění bezpečnosti) : Předdefinovaný objekt (funkce) zodpovědný za JOB krok Proxy server má přístup k 1 či více subsystémům
Server Agent a SSMS
Jak se k němu dostaneme Object
Explorer -> SQL Server Agent
Alerts
Události generované SQL serverem vstupující do logu Windows aplikace SQL Server Agent přečte aplikační log a srovná tyto události s těmi, které již definovaly alerty Když agent najde shodu odstraní daný alert (zodpovědný za událost)
Definování alertu : Jméno
alertu (nejvýše 128 znaků) Událost nebo podmínka, která alert spustí Akce, které při shodě provede SQL Server Agent
Alerts – typy událostí
SQL Server Events Error
number (dojde k konkrétní chybě-má číslo) Severity level (upozornění pro danou chybu) Database (nastane-li v konkrétní databázi) Event text (událost obsahuje konkrétní řetezec)
SQL Server performance conditions Object
(sledovaná oblast výkonu) Counter (atribut monitorované oblasti) Instance (monitorované instance SQL serveru)
Windows Management Introduction events (WMI) Namespace Query
Závěrečné shrnutí
Existuje i celá řada dalších typů zálohy, které se od uvedených liší jen velmi nepatrně – třeba jen parametrem (např. READ-ONLY backup)
Zálohovat má vždy smysl, ale stojí nás i určitou režii
Nutno vždy počítat s tím, že kdykoliv může dojít k nějakému neočekávanému chování či katastrofě
Je vždycky dobré znát vlastnosti a přesné možnosti konkrétního serveru (podle používané verze)
Použité zdroje a prameny
BACKUP & RECOVERY http://msdn.microsoft.com/enus/library/ms187048.aspx http://www.dbsvet.cz/view.php?cisloclanku=200504 1801 http://www.sqlteam.com/article/backup-and-restorein-sql-server-full-backups http://www.sqlservervideos.com/player/backupswith-managementstudio/
SQL Server Agent
http://msdn.microsoft.com/en-us/library/ms189237(v=sql.105).aspx http://learnsqlwithbru.com/2011/12/15/start-sql-server-agent-from-sqlserver-management-studio/
Alerts
http://msdn.microsoft.com/en-us/library/ms180982.aspx
Prostor pro dotazy
Zeptejte se na to, co Vás zajímá
DÍKY za pozornost během prezentace
