1 / 147

SQL Server

SQL Server. 11-15. előadás Barabás Péter. 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.

mabyn
Télécharger la présentation

SQL Server

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related