LAB OF SOFTWAREARCHITECTURES AND INFORMATION SYSTEMS FACULTY OF INFORMATICS MASARYK UNIVERSITY 3.Zálohování aobnova po havárii Recoverymodels Recovery Model Popis Simple • Záloha transakčního logu není podporována • Nepotřebné části transakčního logu se automaticky odstraňují po dokončení transakce Full • Záloha transakčního logu je podporována • Při havárii nedojde ke strátě žádných dat (Tail-Log Backup) • Je možné provést obnovu databáze do konkrétního časové okamžiku Bulk Logged • Upravená verze Full režimu logující bulk operace jako celek nikoliv po řádcích • Menší transakční log, potenciálně vyšší výkon Dostupné typyzáloh Typ zálohy Popis Full Zálohuje všechny datové soubory a část transakčního logu Differential Zálohuje změněné datové stránky od poslední plné zálohy a nové záznamy v TL od poslední plné zálohy Transaction Log Zálohuje změny popsané v transakčním logu FullBackup • Provádí kompletní kopii databáze • Celý datový soubor • Aktivní část transakčního logu • Vhodné pro malé databáze • Je typicky prvním druhem zálohy, který je třeba obnovit po havárii Differential Backup • Předpokládá provádění plných záloh • Nahrazuje některé plné zálohy • Ukládá změněné stránky od poslední plné zálohy • Vhodná pokud se mění jen část databáze častěji než zbytek Transaction Log Backup • Předpokládá provádění plných záloh • Nahrazuje některé plné zálohy • Zálohují se změny prováděné v databázi uložené ve formě transakčního logu • Umožňuje databázi obnovit do libovolného časového okamžiku Kombinacezáloh • Zálohy je výhodné kombinovat • Např.: • 2x do týdne plná záloha • 1x denně diferenciální • Každých 15 minut záloha transakčního logu • Při plánování vycházejte z obsahu databáze • Kolik dat (za jaký časový úsek) je přípustné ztratit? Kamseukládají zálohy • Jsou zapisovány do zálohovacích zařízení • Soubor na pevném disku • Pásková jednotka • SQL Server si v databázi MSDB uchovává historii záloh každé databáze Oprávnění zálohovat • Právo zálohovat mají jen: • Sysadmin Members (Server Role) • Db_owner Members (Database Role) • Db_backupoperator (Database Role) Kontrola integritydatabáze • Provedení kontroly integrity databáze provedete příkazem: • V databázi může dojít k poškození uložení některých objektů, případně jejich špatné alokaci DBCC CHECKDB TailLog Backup aCopyOnly • Tail Log Backup • Zálohuje konec transakčního logu • Potřeba pokud nechceme ztratit žádná data • Není třeba když si vystačíme s již provedenými zálohami • NO_TRUNCATE, CONTINUE_AFTER_ERROR • Copy Only Backup • Nenaruší pořadí obnovování databáze • Záloha není uvedena v MSDB • Neořízne se transakční log Kompresezáloh • Novinka v SQL Serveru 2008 • Zmenší velikost zálohy • Zvýší výkon I/O ale zvýší zátěž na CPU • Podporováno v edicích • Enterprise • Standard • Business Intelligence http://msdn.microsoft.com/en-us/library/bb964719(v=sql.110).aspx Možnostipro zaručení integrityzáloh • Mirrored Media Sets • BackupVerification • Checksums • Zálohovat je sice hezké, ale důležité je, aby šla záloha obnovit http://msdn.microsoft.com/en-us/library/ms175053(SQL.100).aspx http://msdn.microsoft.com/en-us/library/ms189587(SQL.100).aspx Jak funguje obnovení 1.Fáze kopírování dat 2.Fáze zapsání již dokončených transakcí na disk (Redo Phase) 3.Pokud: a)WITH RECOVERY Fáze zrušení nepotvrzených transakcí a přepnutí databáze do použitelného stavu (Undo Phase) b)WITH NO RECOVERY Ponechání rozpracovaných transakcí aby mohla navázat další obnova zálohy Postup přiobnově zálohy 1. Podle situace proveďteTail-log backup 2. Ujasněte si, do jakého okamžiku chcete databázi obnovit 3. Ujasněte si postup obnovy 4. Zjistěte, které zálohy bude pro obnovu třeba 5. Začněte obnovu 1. Poslední Full Backup 2. Poslední Differential Backup 3. Všechny zálohy transakčního logu od poslední FB/DB po daný cílový okamžik Úkol 1:Zálohadatabáze 1. Přepněte databázi AdventrureWorks do Full Recovery režimu 2. Ověřte integritu této databáze 3. Vytvořte první plnou zálohu této databáze 4. Spusťte následující SQL dotaz: UPDATE Person.Contact SET FirstName = 'Carl' WHERE FirstName = 'John' Úkol 1:Zálohadatabáze(2) 5. Proveďte diferenciální zálohu této databáze 6. Proveďte zálohu transakčního logu této databáze 7. Porovnejte velikost diferenciální zálohy a zálohy transakčního logu 8. Proveďte 2. plnou zálohu do jiného souboru se zapnutou kompresí a určete v % kolik místa se ušetřilo Úkol 2:Obnova databáze 1. Zazálohujte databázi všemi 3 druhy záloh v pořadí Full, Differential aTransaction Log 2. Vytvořte ještě jednu zálohu transakčního logu 3. Obnovte databázi AdventureWorks do stavu 10s před provedením poslední zálohy transakčního logu Úkol 2:Obnova databáze 4. Obnovte databázi AdventureWorks do původní podoby 5. Zazálohujte databázi všemi 3 druhy záloh v pořadí Full, Differential aTransaction Log 6. Vytvořte ještě jednu zálohu transakčního logu 7. Proveďte operaci v MARKEDTRANSACTION 8. Obnovte databázi AdventureWorks do stavu před provedením označené transakce Databázový snapshot • Read-only pohled na databázi v konkrétním stavu • Typické použití: • Tvorba reportů nad databází v určitém okamžiku • Ochrana před chybou uživatele/správce • Rychlá obnova dat Jak funguje snapshot Zápis: Copy-on-write Čtení databázového snapshotu SQLServerAgent • Komponenta SQL Serveru sloužící jako časovač • Vytváříme Job, tvořen kroky (Job Step) • Umožňuje spustit: • T-SQL skript • PowerShell skript • Externí aplikace • SSIS balíček • Spouštění je řízeno časovým plánem SQLServerAgent • Umožňuje definovat Alerty a Operátory • Využití: • Když dojde k selhání některého kroku je o tom informován emailem operátor co má právě službu • Vyžaduje nakonfigurovaný Database Mail MaintenancePlans • Postaveny nad SSIS • Umožňují vizuální sestavení plánů údržby databází v dané instanci formou workflow • Jsou spouštěny SQL Agentem v daných časech Úkol 3:Automatizaceúdržby 1. Vytvořte Maintenance plán, který pokrývá kompletní údržbu databáze a obsahuje i upozornění pro operátory LAB OF SOFTWAREARCHITECTURES AND INFORMATION SYSTEMS FACULTY OF INFORMATICS MASARYK UNIVERSITY 4.Bezpečnostní model Důležitépojmy • Autentizace • Ověření identity uživatele • Autorizace • Ověření práv k operaci • Principal • Nadtyp uživatele – entita, co může mít práva k operaci • Securable • Nadtyp objektu který zabezpečujeme, jsou nad ním definovány operace • Permisssion Autentizačnímetody • Windows Autentication • Nezasílá se jméno a heslo při ověření • Proces/služba přistupující k SQL je ověřen automaticky operačním systémem • Doporučený postup • Mixed SQL andWindows Autentication • SQL ověřování kvůli starším aplikacím a scénářům, kde nelze využítWindows ověřování • Nevýhodou je vznik většího množství účtů Hierarchieoprávnění Uživatel / služba Ověření loginu V kontextu DB je login mapován na objekt user ExecutionContext • Je určen loginem připojeným k dané session • Autentizační token obsahuje informace o primární a sekundárních identitách • Určuje práva přístupu k securables objektům v daném spojení • Může se během spojení měnit! Serverové role Role Popis sysadmin Nejvyšší oprávnění dbcreator Vytváření a změny databází diskadmin Správa datových souborů serveradmin Konfigurace nastavení instance securityadmin Správa a audit loginů processadmin Správa procesů bulkadmin Právo pouštět BULK INSERT setupadmin Konfigurace replikace a propojených serverů DatabaseLevelPrincipals • Uživatelé / skupiny kterým lze na úrovni databáze přiřadit oprávnění • User • Uživatel mapovaný na login • Database Role • Skupina uživatelů se stejnými právy • Application Role • Virtuální uživatel do kterého se lze přepnout DatabaseRoles • Důležité: • db_owner • db_datareader • db_datawriter • http://msdn.microsoft.com/en- us/library/ms189121(v=sql.110).aspx • Přiřazujte minimální potřebná práva Speciálníuživatelé • DBO • Login SA a členové role sysadmin jsou namapováni na tohoto uživatele v každé databázi • Guest • Tento účet umožňuje přistoupit k databázi uživatelům bez účtu v té databázi Oprávnění na úrovni databáze • Přidělení oprávnění (Permission) k jednotlivým objektům v databázi (Securables) pro uživatele (Principal) • Grant/With Grant / Deny • Objekty mají svého vlastníka • Ownership Chain • Jiný přístup k vyhodnocování oprávnění pokud volá objekt jiný objekt Správa uživatelů 1. Vytvoříme login 2. Vytvoříme uživatele na úrovni databáze 3. Přiřadíme oprávnění uživateli Databáze nezávislá na instanci • V SQL Serveru 2012 podpora Partially Contained databází • Důležité změny v bezpečnosti: • Možnost přihlášení přímo k databázi, obejití instance • Správa uživatelů jen v DB • Řízení přístupu k DB je zcela v rukou DB_OWNER