SQL Server

1. SQL Server 11-15. előadás Barabás Péter

2. Témakörök • Függvények • Létrehozása • Determinisztikus és nem determinisztikus függvények • Tárolt eljárások • Létrehozása • Újrafordítása • Jogok és szerepkörök • Triggerek • Létrehozása • DDL triggerek • Rekurzív triggerek • Egymásba ágyazott triggerek

3. Függvények implementálása • Skalár függvények • Input paraméterek: 0 v. több • Visszatérési érték: egyszerű skaláris érték • Megszorítások: nem változtathatják meg az objektumok állapotát CREATE FUNCTION [ schema_name. ] function_name ([ { @parameter_name[ AS ][ type_schema_name. ] parameter_data_type [ = default ] }      [ ,...n ]   ] ) RETURNS return_data_type [ WITH <function_option> [ ,...n ] ]      [ AS ]      BEGIN                  function_body RETURN scalar_expression END [ ; ]

4. Függvények implementálása II. • Tábla értékű függvények • Hasonló a skaláris függvényekhez • Különbség: visszatérési értéke tábla • Használata: SELECT utasítás FROM részében CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name[ AS ] [ type_schema_name. ] parameter_data_type [ = default ] }      [ ,...n ]   ] ) RETURNS @return_variable TABLE < table_type_definition >      [ WITH <function_option> [ ,...n ] ]      [ AS ]      BEGIN                  function_body RETURN      END [ ; ]

5. Determinisztikus és nem determinisztikus függvények • Determinisztikus függvények • Ugyanazon bemenő paraméterekre mindig ugyanazt a visszatérési értéket szolgáltatja • Pl. sin(), cos(), … • Az eredmény indexelhető • Nem determinisztikus függvények • Hívásonként más visszatérési értéket szolgáltat • Pl. : GETDATE() • Hívhat nem determinisztikus függvényt vagy tárolt eljárást • Az eredmény nem indexelhető

6. Tárolt eljárások • Bármilyen SQL Server által futtatható parancsot tartalmazhat • Biztonsági funkció: • Futtatási jog a tárolt eljárásra • Az adatokra, amin dolgozik nem kell jogot adni • Elrejti az adatbázis szerkezeti megvalósítását a user elől

7. Tárolt eljárások II. CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]    [ { @parameter [ type_schema_name. ] data_type }          [ OUT | OUTPUT ]      ] [ ,...n ] [ WITH <procedure_option> [ ,...n ] ] [ FOR REPLICATION ] AS { <sql_statement> [;][ ...n ] | <method_specifier> } [;] <procedure_option> ::=      [ ENCRYPTION ]     [ RECOMPILE ]      [ EXECUTE_AS_Clause ] <sql_statement> ::= { [ BEGIN ] statements [ END ] } <method_specifier> ::= EXTERNAL NAME assembly_name.class_name.method_name

8. Tárolt eljárások III. • Jellemzők: • Egyedi nevük van • Tetszőleges számú input paraméterekkel rendelkezik • Kimeneti paraméterek definiálására is lehetőség van • ENCRYPTION: az eljárás titkosítva tárolódik • RECOMPILE: minden futtatáskor újrafordítódik • A törzs nem tartalmazhatja a következőket: • SET SHOWPLAN_TEXT • SET SHOWPLAN_ALL • USE <database>

9. Tárolt eljárások IV. • Használathoz futtatási jog szükséges • GRANT EXECUTE ON <stored procedure> TO <database principle> • A futtatási joggal automatikusan elérhetővé válnak a tartalmazott objektumok és parancsok • DE! Direktben nem érhetők el, csak az eljáráson keresztül

10. Triggerek • Típusai: • DML triggerek • AFTER • INSTEAD OF • DDL triggerek • Rekurzív triggerek • Nested triggerek

11. DML triggerek • Egy adott táblához tartozik • Esemény bekövetkezésének hatására fut le, nem lehet direkt meghívni • Események: INSERT, UPDATE, DELETE • Módok: • AFTER • INSTEAD OF • Törzsben nem használhatóak: • Create, alter, drop, backup, restore

12. DML triggerek II. • Speciális táblák: • INSERTED • DELETED CREATE TRIGGER [ schema_name. ] trigger_name ON { table | view } [ WITH <dml_trigger_option> [ ,...n ] ] { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME <methodspecifier [ ; ] > } <dml_trigger_option> ::= [ ENCRYPTION ] [ EXECUTE AS Clause ]

13. Rekurzív és nested triggerek • Rekurzív triggerek: • Trigger akciója kiválthatja újra önmagát • Egy mechanizmus kezeli a végtelenséget • RECURSIVE_TRIGGERS opció állításával • Nested triggerek: • Egy trigger akciója kiváltja egy másik trigger „tüzelését” • A másik trigger pedig kiváltja az előző lefutását • NESTED_TRIGGERS paraméter állításával szabályozható

14. DDL triggerek • Használata: • Korlátozhatja a DDL utasítások használatát • Szintaktika: CREATE TRIGGER trigger_name ON { ALL SERVER | DATABASE } [ WITH <ddl_trigger_option> [ ,...n ] ] { FOR | AFTER } { event_type| event_group} [ ,...n ] AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < methodspecifier > [ ; ] } <ddl_trigger_option> ::= [ ENCRYPTION ] [ EXECUTE AS Clause ] <method_specifier> ::= assembly_name.class_name.method_name

15. Adatbázisok mentése • Teljes mentés • Különbségi mentés • Tranzakció log mentése • File csoport mentés • Tükrözött mentés • Részleges mentés

16. Teljes mentés • Minden adat mentésre kerül, ami az adatbázisban található • Használható adatbázis újralétrehozására is • A visszaállítási modelltől függetlenül mindig alkalmazható. • A lehető leggyorsabban történik az elvégzése minimális erőforrás használat mellett. • A backup engine lapokat ír backup device-re, a sorrend figyelembe vétele nélkül • A sorrendi függetlenség miatt ezt a műveletet több szál közt osztja szét és így a gyorsaság csak az eszköz sebességgétől függ.

17. Teljes mentés II. • Logikai inkonzisztencia léphet fel • Userek bejelentkezett állapota és aktivitása miatt • SQL Server a következőképpen küszöböli ezt ki • lockolja azt adatbázist és blockolja az összes tranzakciót • Tesz egy jelölést a tranzakció logba • Felengedi az adatbázis lockolást • Menti az összes lapot • lockolja azt adatbázist és blockolja az összes tranzakciót • Tesz egy jelölést a tranzakció logba • Felengedi az adatbázis lockolást • A két log jelölés közötti tranzakciókat hozzáfűzi a backup-hoz

18. Teljes mentés III. • Parancs: • BACKUP DATABASE <database_name> TO DISK=‘<directory>\<filename>’ WITH INIT • TO rész: • A backup device-t lehet megadni • DISK,TAPE: explicit útvonalat lehet kijelölni • WITH rész: • Több, mint egy tucat paramétere lehet • INIT: mindent írjon felül a backup eszközön

19. Különbségi mentés • Az utolsó teljes mentés óta változott extenteket menti. • Előnye: a tranzakciós log mentések számát csökkenti. • Csak teljes mentés után használható • Visszaállítási modelltől függetlenül haszálható.

20. Különbségi mentés II. • Nem inkrementális backup • Inkrementális backup: az utolsó inkrementális backup óta eltelt változásokat menti • Az utolsó TELJES MENTÉS óta eltelt változásokat menti. • Pl. teljes mentés éjfélkor történt • 4 óránként van különbségi mentés • Minden mentés az éjfél óta eltelt változásokat tartalmazza

21. Különbségi mentés III. • Extent map • Egy másik adatlap az adatbázisban • Minden bit az oldalon egy extent-et reprezentál • Amikor az extent változik, az extent bitje 0-ról 1-re változik • Teljes mentésnél minden bit 0-ra állítódik. • Mivel az adatbázisok mérete korláltlan és az adatlapok mérete 8KB lehet, 8192 extentenként jön létre mapping oldal

22. Különbségi mentés IV. • Parancs: • BACKUP DATABASE <database_name> TO DISK=‘<directory>\<filename>’ WITH DIFFERENTIAL

23. Tranzakció log mentés • Teljes vagy bulk-loggedrecoverymodel esetén használható • Teljes mentés után használható • Adatok egy részhalmazát tartalmazza és szükséges egy teljes mentés a visszaállításhoz • Az aktív logot menti • Az előző log backup utáni Log SequenceNumber-rel (LSN) kezdi. • Mindaddig menti a tranzakciókat, amíg egy nyitott tranzakciót el nem ér • A mentett tranzakciók a logból eltávolíthatók

24. Tranzakció log mentés II. • Parancs: • BACKUP LOG <database_name> TO DISK=‘<directory>\<filename>’ WITH INIT

25. Filecsoport mentés • Alternatív mentési stratégia a teljes mentéshez • Az adatbázis mentése helyett az egyes filecsoportokat menti az adatbázisból • Kiindulásként szükséges egy mentés az össze filecsoportról • Teljes vagy Bulk-logged recovery modell szükséges

26. Filecsoport mentés II. • Parancs: • BACKUP DATABASE <database_name> FILEGROUP = ‘<filegroup_name>’ TO DISK=‘<directory>\<filename>’ • BACKUP DATABASE <database_name> FILEGROUP = ‘<filegroup_name>’ TO DISK=‘<directory>\<filename>’ WITH DIFFERENTIAL

27. Tükrözött mentés • Minden backup létrehoz egy egyszeri másolatot az adatokról egy eszközön. • Az adminisztrátor duplikálhatja ezt az esetleges eszközhibák miatt. • A duplikáció fárasztó és időigényes folyamat. • Létrehozható másolat a mentésről az SQL Serverben: tükrözött mentés

28. Tükrözött mentés II. • BACKUP parancs opcionális része: • [[MIRROR TO <backup_device> [,…n]][…next-mirror]] • 4 másolat létrehozása lehetséges, ebből 3 a MIRROR TO részben definiált • Korlátozások: • Az eszköznek az összes másolathoz ugyanolyan típusúnak kell lenni. • Mindegyiknek hasonló tulajdonságokkal kell rendelkeznie. • Pl. ha a backup disk-re történik, a másolatok is disk-re kell kerüljenek

29. Tükrözött mentés III. • Példa: BACKUP DATABASE PUBS TO DISK=‘C:\DEMO\BACKUP\PUBS1A.BAK’, DISK=‘C:\DEMO\BACKUP\PUBS1B.BAK’ MIRROR TO DISK=‘\\BAKSERVER1\BACKUP\PUBSMIRROR1A.BAK’, DISK=‘\\BAKSERVER1\BACKUP\PUBSMIRROR1B.BAK’ MIRROR TO DISK=‘\\BAKSERVER2\BACKUP\PUBSMIRROR2A.BAK’, DISK=‘\\BAKSERVER2\BACKUP\PUBSMIRROR2B.BAK’ MIRROR TO DISK=‘\\BAKSERVER3\BACKUP\PUBSMIRROR3A.BAK’, DISK=‘\\BAKSERVER3\BACKUP\PUBSMIRROR3B.BAK’ WITH FORMAT GO

30. Részleges mentés • Lehetőség van írható és csak olvasható filecsoportok kezelésére • Előző verziókban a backup a csak olvasható filecsoportokra is kiterjedt, ami ugyebár nem változhatott • Új paraméter: • READ_WRITE_FILEGROUPS

31. Részleges mentés II. • Jelentése: • A backup figyelmen kívül hagyja a csak olvasható filecsoportokat • Időt és helyet takaríthatunk meg vele • Példa: • BACKUP DATABASE PUBS READ_WRITE_FILEGROUPS TO DISK=‘C:\DEMO\BACKUP\PUBS1.BAK’

32. Részleges mentés II. • Jelentése: • A backup figyelmen kívül hagyja a csak olvasható filecsoportokat • Időt és helyet takaríthatunk meg vele • Példa: • BACKUP DATABASE PUBS READ_WRITE_FILEGROUPS TO DISK=‘C:\DEMO\BACKUP\PUBS1.BAK’

33. Gyakorlás • 1. Mentsük az adatbázist teljes, különbségi és tranzakció log mentéssel! • 2. Készítsünk filecsoport, filcsoport különbségi és tranzakció log mentéseket!

34. Adabázisok visszaállítása • Full backup visszaállítása • Differential backup visszaállítása • Transaction Log visszaállítása • Részleges visszaállítás • Korrupt oldal visszaállítás • Visszaállítás eszköz hibákkal • Visszaállítás validálása • Adatbázis mozgatása

35. Full backup visszaállítás • Legtöbb visszaállítás az adatbázis újralétrehozásával kezdődik egy adott időben • Aztán a következő backup-ok visszaállítása a cél ideig • Ez a folyamat a teljes mentésből történő visszaállítással kezdődik.

36. Full backup visszaállítás II. • A teljes mentés az teljes adatbázist tartalmazza • A visszaállítási műveletnek az oldalakat szekvenciális sorrendben kell visszatenni az adatbázisba • A folyamat végén egy teljesen koherens adatbázist kapunk.

37. Full backup visszaállítás III. • Példa: • RESTORE DATABASE PUBS FROM DISK=‘C:\DEMO\BACKUP\PUBSFULL.BAK’ WITH REPLACE, STANDBY = ‘C:\DEMO\BACKUP\PUBSSTANDBY.STN’ • REPLACE opció: • Írja felül a már létező ugyanolyan nevű adatbázist • STANDBY opció: • Az adatbázist visszaállítás állapotban hagyja: • Írás nem megengedett • De a userek kapcsolódhatnak az adatbázishoz és lekérdezéseket végezhetnek • WITH RECOVERY, WITH NORECOVERY opciók

38. Differential Backup visszaállítás • Kiindulópont: • Teljes backup visszaállítás szükséges • Példák: • RESTORE DATABASE PUBS FROM DISK=‘C:\DEMO\BACKUP\PUBSFULL.BAK’WITH NORECOVERY • RESTORE DATABASE PUBS FROM DISK=‘C:\DEMO\BACKUP\PUBSFULL.BAK’WITH RECOVERY

39. Differential Backup visszaállítás II. • Filecsoport visszaállítás példa: • RESTORE DATABASE AdventureWorks FILEGROUP=‘FG1’ FROM DISK=‘C:\TEST\AWFG1.BAK’ WITH NORECOVERY • RESTORE DATABASE AdventureWorks FILEGROUP=‘FG1’ FROM DISK=‘C:\TEST\AWFG1.BAK’ WITH RECOVERY

40. Transaction Log visszaállítás • Az adatbázis előregörgetése egy adott pontig • Teljes vagy különbségi backup visszaállítás után lehetséges. • A TL Backup tranzakció sorozatot tartalmaz az LSN-nel azonosítva • Lehetőség van egy bizonyos LSN-nél leállítani a recovery folyamatot (STOPAT)

41. Példák • Visszaállítási folyamat (full+diff+TR) • Full • RESTORE DATABASE AdventureWorks FILEGROUP=‘FG1’ FROM DISK=‘C:\TEST\AWFG1.BAK’ WITH NORECOVERY • Differential • RESTORE DATABASE AdventureWorks FROM DISK=‘C:\TEST\FG1DIFF1.BAK’ WITH NORECOVERY • Transaction log • RESTORE LOG AdventureWorks FROM DISK=‘C:\TEST\AW2.TRN’ WITH RECOVERY

42. Példák II. • Visszaállítási folyamat (full+multiple TR) • Full • RESTORE DATABASE AdventureWorks FILEGROUP=‘FG1’ FROM DISK=‘C:\TEST\AWFG1.BAK’ WITH NORECOVERY • TR log • RESTORE LOG AdventureWorks FROM DISK=‘C:\TEST\AW1.TRN’ WITH NORECOVERY • RESTORE LOG AdventureWorks FROM DISK=‘C:\TEST\AW2.TRN’ WITH RECOVERY

43. Részleges visszaállítás • Az adatbázis egy részének visszaállítása • Az adatbázis többi része elérhetővé válik a kérések számára • Amennyiben a kérésekhez nincs szükség a visszaállítandó részre, a usereknek semmi sem tűnik fel

44. Korrupt oldal visszaállítása • Egy vagy több oldal korrupttá válhat • Előző verziókban server hiba okozta és az adtabázis offline módba került • Javítás az oldal típusától függő volt: • Index oldal vált korrupttá: • Droppolás után újragenerálódott • Adat oldal vált korrupttá: • Visszaállítás backupból, • A backup alatt a DB offline

45. Korrupt oldal visszaállítása II. • PAGE_VERIFY CHECKSUM • Ezen verifikáció engedélyezése után bármely korrupttá vált oldal loggolódik és karanténba kerül • Verifikáció engedéleyezése: • ALTER DATABASE <database_name> SET PAGE_VERIFY CHECKSUM • Alaphelyzetben ki van kapcsolva • Korrupt lapok a suspect_pages táblába loggolódnak az msdb adatbázisban

46. Korrupt oldal visszaállítása III. • A log végének mentése • BACKUP LOG PUBS TO DISK=‘C:\HA\DEMO\BACKUP\PUBS1.TRN’ WITH INIT • GO • Korrupt lap visszaállítása • USE MASTER • GO • RESTORE DATABASE PUBS PAGE=‘1:88’ FROM DISK=‘C:\HA\DEMO\BACKUP\PUBSMIRROR1.BAK’ WITH RECOVERY • GO • Tranzakciók a TR logba • USE MASTER • GO • RESTORE LOG PUBS FROM DISK=‘C:\HA\DEMO\BACKUP\PUBS1.TRN’ WITH RECOVERY • GO

47. Visszaállítás eszköz hibákkal • a hiba ritkán detektálható a backup előtt • Visszaállításnál a létező adatbázis tartalma kisöprődik • A visszaállítás abortálódik a hiba miatt • Marad egy teljesen érvénytelen adatbázis

48. Visszaállítás eszköz hibákkal II. • A RESTORE utasításnak van egy opciója, melynek hatására a hibás szektorok átugrásra kerülnek és a visszaállítási folyamat ezáltal befejeződhet • WITH CONTINUE_AFTER_ERROR • Nincs rá garancia, hogy a visszaállítás során az adatbázis használható lesz • Hiba esetén az adatbázis emergency módba kerül • Kapcsolódhatunk az adatbázishoz • Select utasításokat kiadhatunk • Adatváltoztatás nem lehetséges

49. Backup validálása • Honnan tudható, hogy a backup használható? • Módja: • Adatok visszaállítása és verifikációja • Igen időigényes és ritkán praktikus • Helyette: • RESTORE VERIFYONLY FROM <backup_device> […,n] • Ellenőrzi a mediaheader-t • Verifikálja a backup checksum-ot • Olvassa a belső lap láncokat és újraszámítja a backup checksum-ot az összehasonlításhoz

50. Adatbázis mozgatása • Szükség lehet adatbázisok szerveren belüli v. szerverek közötti mozgatására • 3 mechanizmus létezik erre: • Backup és restore • Detach/attach • Copy Database Wizard