SQL vizsgakérdések. 1. SQLServer verziók, jellemzőik

SQL vizsgakérdések 1. SQLServer verziók, jellemzőik Verziók: 6-os verzió (első generáció): - teljes adatbáziskezelő - új elemek o windows OS elemekkel integrálódik (levelezés, monitorozás, erőforráskezelés) o replikáció megvalósítása 7-es verzió (második generáció): − optimalizáló modul (erőforrások parametrizálása egy belső optimalizáló modulon keresztül történik − OLAP (Online Analitikus Elemzés): cégek vezetőinek szól, erre épül a BI (Business Intelligence). Itt használnak adattárházakat. − ETL (Extraction Transformation Loading): célja, hogy a forrás adatbázisokból átemeljük az adatokat az adattárházakba. Harmadik generáció (SQLServer 2000, 2005, 2008): − 2000: o adatbányászati funkciók o UDF (felhasználó által definiált függvények): SQL parancsban saját függvényeket lehet használni o saját adattípusok használata o alacsony szinten támogatja az XML adatok kezelését − 2005, 2008: o skálázhatóság növelése: finomat beállíthatók az igényelt erőforrások szintjei, sok kicsi gép van mögötte, kis gépek együttműködősére gridek, griden belül az erőforrások ki tudják egymást segíteni o CLR (Common Lagnuage Runtime): közös metanyelv támogatása, támogatja a .NET alapstruktúrákat o magas rendelkezésre állás o védelem Változatok: Enterprise Edition: − nincs memórialimit, de ez csak beállításon keresztül érhető el − nincs DB méret korlát − replikálható − particionálható: a táblát több különböző csomópontra szétosztva helyezzük le, előnye, hogy növelhető a teljesítmény és megvalósítható a terhelésmegosztó. − DB tükrözés lehetséges − failover clustering: rendelkezésre állást növelő cluster (grid) Standard Edition: − nincs particionálás 1

− CPU korlát (4) − Limitált failover clustering Workgroup Edition: − kis OLTP, OLAP − memórialimit: 3 Gb − nincs tükrözés, elemzőszerver − nincs failover clustering − rendelkezésre állás jelentősen csökkent − CPU korlát (2) Express Edition: − CPU korlát (1) − memórialimit: 1 Gb − DB-méret korlát: 3 Gb − nincs semmi támogatottság Developer Edition: − Teljes funkcionalitás, de csak fejlesztésre használható OS követelmények: − Enterprise Edition o Windows 2000 Server SP4 o Windows 2003 Enterprise Standard − Standard Edition o Windows Professional SP4 o Windows XP Professional SP2 − Workgroup Edition o Windows 2000 Professional − Express Edition o Windows XP Home SP2 o Windows Server 2003 Web Edition

2

2. SQLServer állomány architektúra Az SQLServer több adatbázis együttese. Szerver és adatbázisszintet különböztetünk meg. Napló: REDO LOG. Maga az adatbázis alapesetben két fájlból áll. Egy data fájlból, ami a tényleges adatokat tartalmazza (táblák adatai, view-k stb.), valamint egy log naplófájlból. Típusok: − MDF: Fő adatfájl − NDF: segédfájlok − LDF: naplófájl Adatállományok: − itt tárolódik minden adatbázis objektum − szerepe: tárolja az aktuális állapotokat − róla másolat, mentés készül Mentés: − állománymentés − naplómentés Napló: − REDO LOG − újra lejátszáshoz használható fel − célja: ha az adatfájl megsérül, akkor a napló alapján újraépíthető egy korábbi állapotból − problémája: az idő teltével növekszik a mérete, egyszer elér egy kapacitás méretet − megoldás: a napló régebbi részeit törli helytakarékoskodás miatt, a naplót ciklikusan írja, ha eléri a végét, az elejére ugrik − következmény: a napló csak egy időablakot fed le

3

3. SQLServer DBMS modulok Protocol: − kliens oldali API-val kommunikál − TDS: tabular data stream (táblázatos adatok átküldése) − shared memory (gépen belül megosztott memória) − named pipse (különböző gépek közötti nevesített csatornák) − tcp/ip Query Processor: − Parser − Optimizer − SQL Manager − Database Manager − Quoery Executor Storage Engine: − Transaction Services: ACID elvek teljesítése − Lock Manager: zárolás kezelő, zárolási módok (R,X,U, intent log (hierarchikus zárolás), default zárolási szint a rekord, létezés jelzőként funkcionál − File Manager (nézi, hogy betelt-e a fájl) − Access Methods (írás, olvasás) − Buffer Manager, paging − Index Manager: rekordok indexelése Laptípusok: − Data page (normál tábla rekordok) − Index page (index fa) − LOB page (kép, hang, nem szokványos adatok) − Page Free Space page (olyan lapok, melyek megadják, hol vannak szabad lapok) − Index Allocation Map (IAM) page − Global Allocation Map (GAM) page − Differential Changed Map (DCM) page SQL OS: − Resource Manager (fájlok, diszkek) − Memory Manager − Lock Manager − I/O Manager − Scheduling (ütemezés) − Deadlock Manager (holtponti várakozás, Timeout módszer, WFG /waitfor gráf/) − Monitors (paraméter, eseményfigyelő)

4

4. SQLServer memória modulok Memory Structure: − Buffer pool (temporális adatok) − Data cache (feldolgozás alatt álló adatokat tárolja) − REDO LOG cache (elvégzett műveletek listája) − UNDO LOG cache (adatok visszaállításához tárolja az induló értékeket) − Shared pool (közösen használt metaadatok: jogosultságok, definíciók NUMA architektúra: − multiprocesszoros környezetben használandó − a memória elérés ideje függ a processzor elhelyezésétől − ténylegesen mennyit tud az SQL verziójától, jellegétől függ Memória műveletek: − Lapozási techika: LRU-K módszer: o Utolsó K műveletet tartja nyilván a lapnál o Számontartja a lap típusát is − Lazzy writer thread: o Periodikusan feléled, ha kicsi a szabad page lista, akkor átpásztázza a memóriát, és kiszámolja az LRU_k jelzőket. A régi lapokat kiírja és átteszi a szabad listázva. Checkpoint: − Az adatbázis cache-ben lévő módosult lapok kiírása a lemezre, ez aszinkron és nem aktualizálja a szabad lapok listáját − Indítása: explicit, shutdown, időkorlát vagy napló betelt Ütemezési módok: − Cooperative scheduling (a user task időnként rákérdez a kernelre, hogy mehet-e, nem preemptive) − SQL Worker: feladatot ellátó taszk, thread (Idle time limiet, Memory limit) − SPID: user session ID

5

5. SQLServer szolgáltatási komponensek Processes, services: − DBMS Server Service − Agent Service (ütemezett feladatok megoldása) − Transformation Service − SQL MAIL (üzenet küldése a rendszergazdáknak, felhasználóknak) Kiegészítő szolgáltatások: − SQL OS: közös erőforrás menedzser − cache: itt tárolja a feldolgozás alatt álló adatokat − lock: zárolásokat figyel − buffer: általános tároló (naplók, metaadatok) − database engine: SQL parancs végrehajtó − SQL Mail: üzenet küldés a rendszergazdának, felhasználónak − SQL agent: ütemezett feladatok megoldását végzi − analysis services: OLAP motor − integraton services: ETL funkciók megvalósítása − reporting services: jelentéskészítő − replication: másolat készítésében segít − service broker: közvetítő elosztó − distributed OS: elosztott lekérdezés − http webservices: SQLServer alkalmas arra, hogy porton keresztül fogadja az SQL parancsokat − ADO.NET: adatbázis elérési interfész, platform független − full text search: szabadszövegű dokumentumokban való keresés Segédprogramok: − Management Studio: DB admin felület − Configuration Manager: beállítások − Profiler: leírós paraméter sor, jellemzői leírása (erőforrás korlát, naplózás) − Books Online: help − Database Engine Tuning Advisor: adatbázis motor optimalizálásának eszköze − System Monitor: működési paraméterek változásának figyelése

6

6. Installáció menete Menete: 1. Milyen típusú SQLServer-t szeretnénk telepíteni (Standard, Enterprise…) 2. Telepítés helyének kiválasztása: van egy gyökérkatalógus, amelyik egyetlen eszközhöz kötődik. Megbízhatóság és teljesítmény szempontjából érdemes 5 különböző diszkre szétosztani az alábbi koncepció alapján: a.) segédprogram kód (SYSTEM) b.) data file c.) index d.) rollback e.) backup Szétosztás logikája: futnia kell a szervernek -> parancs ellenőrzéskor a Systemben ellenőriz -> DATA, de ehhez kell az indexből az érték -> végrehajtás után naplózás -> mentés mindig különböző helyen 3. Services account: legyen különböző az Agent és a Server Lehet: local-system, domain vagy network service 4. Azonosítási mód: a.) OS: átveszi az operációsrendszertől a jelszót b.) Vegyes: be lehet jelentkezni OS-sel és szerver azonosítóval is 5. Karakter sorrendiség (collation): később nem változtatható, több 100 sorrendiség támogatott, sok részbe beépül, például az index sorrendiségre is hatással van. Adatbázisonként külön sorrendiség megadható. Az SQLServer installálható kötegelt módban (batch). Ilyenkor az installációs batch fájl tartalmazza a feltett kérdésekre a választ, ez a szövegfájl. Előny: gyorsabb, automatizálható a telepítés. SA: az a login, ami a DB rendszergazdát azonosítja.

7

7. Konfigurációs paraméterek A konfiguráció célja a testre szabás. Több, mint 100 paramétert lehet beállítani. A paraméterek nem fixek, változtathatók, gyakran egy session alatt is változhat. Következmény: − paraméterek beállítására vannak módszerek − a paramétereknek több verziója is lehet o konfigurált érték (eredeti érték) o aktuális érték o default érték o tartomány (min, max) Paraméterek kategóriái: − memória − ütemezés − diszk IO − SQL művelet végrehajtás − hálózati − védelmi Paraméterek megtekintése: − Server Management Studio − EXEC sp_configure − SELECT serverproperty(nev) − SELECT * FROM sys.configurations Memória paraméterek: − Min. Server Memory − Max. Server Memory − Index Memory − Lock Memory − AWE Enabled: Address Windowing Extension, alapesetben az SQLServer max 3 GB memóriát foglalhat le, az AWE opció esetén ez megnövelhető az elméleti határig, zárolja a kijelölt lapokat, nem lesz kilapozható az allokált terület − Query Wait (meddig várjon szabad memóriára, sec) − minimum Query memory: SQL parancshoz szükséges memória minimális száma A Serverben futó szálak: − minden hálózati kapcsolathoz egy-egy − háttér processzek (pl. chehcpoint) − felhasználói kérésekhez Processz paraméterek: − Max Worker Threads (hány különböző szálat indíthat a rendszer) − Priority Boost (deafault: 7, max: 31) − Affinity Mask (megadhatja, hogy melyik CPU-t használhatja) Kapcsolati paraméterek: − Mx concurrent user connections − Permit remote server connection − Remote query timeout − Remote login timeout

8

Adatbázis paraméterek: − Auto Create Statistic (CBO - Cost Base Optimation, költségszámító függvények vannak hozzá, melynek értéke a paraméterről függ. A statisztikák ezen paramétereket gyűjtik össze. Ha nem engedjük, akkor a CBO sem működik) − Auto Update Statistic − Auto Close (ha nincs aktív kapcsolat, leállítja a szervert, az erőforrást átengedi másnak. Csak akkor használjuk, ha kiszámítható a terhelés) − Auto Shrink (a nem használt területeket visszaadja az operációs rendszernek) − Cursor Threshold − ANSI mód (SQL szabványra illesztés)

9

8. Tartalmazott adatbázisok Adatbázisok: − Master: rendszer metaadatok, rajta keresztül érhető el a többi DB − Msdb: agent metaadatok (ütemezéshez) − Tempdb: ideiglenes munka adatok − Mssqlsystemresource: DBMS rendszerkód − Model: felhasználói DB-k üres mintája − Northwind: példa DB − Pubs: példa DB − felhasználói DB-k Adatbázis felépítése:

Adatbázisok fizikai jellemzői: − az adatfájlok filegroupokban vannak, a filegroup egy karbantartási egység − a fájlcsoport fájlokból áll − a fájlok extendekből épülnek fel − egy extend egy összefüggő lapsorozat (allokálási egység) − az extendek blokkokból épülnek fel, egy blokk a legkisebb IO egység − a naplófájlok szekvenciális szerkezetűek Adatbázisok szerkezete: − Séma: felhasználó tulajdonában álló objektumok együttese (egy embernek lehet több sémája is) Adatbázis állapotai: − ONLINE/OFFLINE: a tartalom elérhető-e − DEFUNCT: használhatatlan, üzemképtelen − RECOVERING: az adatállomány visszamásolása (újra konzisztens állapotba hozza) − RESTORING: a fájlt másolással hozzák helyre

10

9. Adatbázis létrehozása, módosítása Létrehozás: CREATE DATABASE xxx ON PRIMARY (file-mdf), (file1-ndf)…. LOG ONG (file-log) File leíró: − NAME = db_nev − FILENAME = os_név − SIZE = induló méret − MAXSIZE = max méret − FILEGROWTH = növekedési ráta ON PRIMARY megadja, hogy melyik fájlcsoporthoz tartozzon. Minden adatbázishoz van egy induló primary-nak nevezett fájlcsoport. Fájlcsoport szerepe: segítségével lehet az adatok tárolási helyét szabályozni. A táblát fájlcsoporthoz lehet majd rendelni. Egy-egy fájlcsoport egy-egy karbantartási egységet képvisel. Az állományok filegroupba rendelése: CREATE DATABASE xxx ON PRIMARY (file-mdf),… FILEGROUP f1 (file1-ndf)… FILEGROUP f2 (file2-ndf)… … LOG ON (file-log) Új filegroup hozzáadása az adatbázishoz: ALTER DATABASE xxx ADD FILEGROUP yy; ALTER DATABASE xxx ADD FILE (NAME=xx…) TO FILEGROUP yy; Egy filegroup default filegroup-pá tétele: ALTER DATABASE xxx MODIFY FILEGROUP yy DEFAULT; Egy filegroup csak módosíthatóságának beállítása: ALTER DATABASE xxx MODIFY FILEGROUP yy READONLY | READWRITE Egy tábla megadott filegroup-hoz rendelése: CREATE TABLE nev(mezok…) ON filegroup_nev | DEFAULT; Egy adatfile meszüntetése: ALTER DATABASE xxx REMOVE FILE yyy; Egy filegroup megszüntetése: ALTER DATABASE xxx REMOVE FILEGROUp yy; Egy naplófájl hozzáadása az adatbázishoz: ALTER DATABASE xxx ADD LOG FILE (NAME=….);

11

10. Adatbázis konzisztencia ellenőrzés Adatbázis ellenőrzés: konzisztencia, objektumok épsége DBCC: Database Console Command Parancsai: DBCC CHECKDB xxxx REPAIR_REBUILD | NOINDEX DBCC CHECKALLOC xxxx DBCC CHECKTABLE xxxx DBCC CHECKCATALOG xxxx Nemcsak ellenőriz, hanem helyreállít is. Paraméter beállítás funkciója is van.

11. Adatbázis működési paraméterek Nem adatbázist indítunk el, hanem adatbázis kezelő instance-t. Adatbázis hozzáférés jellege: − SINGLE USER (DBA=root, rendszergazda léphet be) − RESTRICTED USER (csak az engedélyezettek léphetnek be és használhatják) − MULTIUSER (több felhasználó, mindenki) Adatművelet: − READ WRITE − READ ONLY Adatbázis elérése: − ONLIN/OFFLINE − EMERGENCY (összeomláskor, helyreállításhoz) Kezelhető paraméterek: − CURSOR kezelés − AUTO_SHRINK − SQL dialektus − DB Recovery mód − DB mirroring mód − Service Broker − Snapshot kezelés

12

12. Filegroup fogalma, kezelése A filegroup adatfile-ok logikai egysége. Egy karbantartási egységet képvisel (paraméterek, mentés, státusz beállítás. Csak adatfile-okat tartalmaz. Segítségével lehet az adatok tárolási helyét szabályozni. A táblát fájlcsoportokhoz lehet allokálni. Adatokat fájlcsoport szinten lehet kivonni a forgalomból. A primary filegroup az mdf-t tartalmazza. Ezen kívül van még default filegroup és userdefined filegroup. Fájlcsoportok kezelése: 9. tételnél

13. Adatfile szerkezete Az adatállomány heterogén struktúra, különböző részeket tartalmaz, de közös a fizikai tartalom. A szegmensek azonos típusú objektumokat tárolnak, emiatt különböző típusú szegmensek vannak (indexre, adatokra, rekordokra). A szegmensek extendekből épülnek fel. Extend: kiterjesztés, allokálási egység, folytonos tárterület. Használata azért előnyös, mert kisebb költségű és gyorsabb a nyilvántartás. Egy blokkot az alábbi három rész alkotja: − fejrész − fenntartott terület − adatterület Fejrész: a fejrész tartalmazza, hogy melyik táblához tartozik, milyen tranzakciók érintettek benne, valamint ide tartozik még a foglaltsági térkép is. Fenntartott terület: egy rekord mérete szerint lehet fix hosszúságú vagy változó hosszúságú A rekord hossza menetközben megváltozhat, ezért extra hely kellhet neki. A helyfoglalás alapvetően az extend egységekben történik. Az extend tárolása kétfajta lehet az adatbázisokban: − adatbázisszintű tárolás: a foglaltsági térkép egy metaadat táblában van, előnye: könynyen olvasható, egységes a tábla, hátrány: lassú − fájlszintű, lokális tárolása: a fájl fejrészében van lementve a térkép, előnye: goyrsabb Az extend fix hosszúságú: 8 lap hosszúságú. Fajtái: − mixed: vegyes − uniform: homogén Induláskor az extend heterogén. Indoka: jobb helykihasználás Extendek foglaltsági térképei: A foglaltsági térképek bitmap térképek (értékei binárisak), előnye: tömörség, egy bithez egy objektum fog tartozni. Nyilván kell tartani: − melyik extendek tartoznak a fájlhoz − hol van szabad extend − az egyes objektumoknak hol vannak extendjei A rendszer a rekordnak egy szabad vegyes extended próbál keresni. Extend allokálási algoritmusa: − GAM: szabad extend listának tekinthető. Elsőként a rendszer 0-s bitű extendet keres. 0: már írtunk oda, 1: még nincs használva. − SGAM: a foglaltak közül oda írhat, ami nincs tele. A még szabad hellyel rendelkező vegyes extendeket jelöli. 1: még nincs tele, 0: tele van.

13

14. SQLServer védelmi rendszer jellemzése Szintek: − mit tudsz − mid van − ki vagy − authentikáció: egyediség ellenőrsé, személy ellenőrzés − authorizáció: jogosultság, hozzáférési, műveleti jog − audit: tevékenység napló Védelmi modellek: − DAC: decentralizált védelmi modell, a tulajdonos meg tudja határozni a jogokat, hozzáféris mátrix: szubjektív, objektum, művelet. Egységek: csoportok, szerepkörök − MAC: központi felügyelet, keretrendszer, centralizált Védelmi struktúra:

15. LOGIN objektum kezelése LOGIN: A logint hozzárendeljük a userekhez. A login hozzárendelődik az adatbázis felhasználóihoz. Nyilvántartási módok: − WINDOWS: előnyei: megbízható ellenőrzés, szakértőbb csoport kezelheti, csoportosíthatók, a jelszó nem megy át a hálón. Hátrány: kevésbé biztonságos. − MIXED: előnyei: rugalmasabb szereposztás, nem Windows kliens eléréséhez használható, belsőleg tárolja LOGIN létrehozása: − windowsos használata: CREATE LOGIN nev FROM WINDOWS; − saját login létrehozása: CREATE LOGIN nev WITH PASSWORD=pwd MUST_CHANGE=m CHECK_EXPIRATION = m CHECH_POLICY=m LOGIN blokkolása, engedélyezés: ALTER LOGIN login DISABEL | ENABLE LOGIN bejelentkezés kezelése: GRANT CONNECT TO login DENY CONNECT TO login LOGIN módosítás: ALTER LOGIN nev WITH PASSWORD… DROP LOGIN nev

14

16. USER objektum kezelése Adatbázis szintű felhasználó vagy account. Minden adatbázisnak megvan a saját userrendszere. A loginokat és a usereket össze kell rendelni. Különböző adatbázisokban különböző lehet a userrendszer. Default user: GUEST Létrehozás: CREATE USER unev FOR LOGIN nev ALTER USER unev… DROP USER unev Jogosultság megadása: GRANT jog TO user REVOKE jog FROM user DENY jog TO user

17. ROLE, CREDENTIAL, titkosítás ROLE Szerepkör, jogosultságok halmaza, ez lehet szerver szintű és adatbázis szintű. Létrehozás: CREATE ROLE nnn Jogosultság felvitele: GRANT jog TO szerepkör Felhasználó kijelölése: EXECUTE sp_addrolemember role, user Szerver szintű szerepkörök:

Adatbázisszintű szerepkörök:

Application role: Nem személyhez, hanem megadott alkalmazáshoz kötődik. Csak az alkalmazás ideje alatt él. Felvétele: sp_setapprole. Jelszó védi.

15

CREDENTIAL Egy külső szolgáltatás eléréséhez szükséges jogosultságok együttese. Login-hoz rendelhető. Létrehozás: CREATE CREDENTIAL sqlusers WITH IDENTITY = ’machinesqlusers’, SECRET =… go CREATE LOGIN mary WITH PASSWORD = ’mary’ go ALTER LOGIN mary WITH CREDENTIAL = sqlusers Proxy: az SQLAgent esetében használatos. Célja: alkalmazások összekötése a Credential-szal Titkosítás Adattitkosítási lehetőség: Service master key: szimmetrikus kulcs, automatiksan generált; server password, connection strings, account passwords, DB master keys DB master key: CREATE MASTER KEY ENCRYPTION BY pwd; DB passwords Adat kódolás: − szimmetrikus vagy aszimmetrikus kulcs − CREATE SYMMETRIC KEY kk WITH ALGORITHM = mod ENCRYPTION BY PASSWORD = pwd; − felhasználása: o EncryptByKey(Key_GUID(kk), szöveg) o DecryptByKey(szöveg)

18. Audit, DDL és login trigger Audit Jogosultságokhoz kötődő tevékenységek naplózása. Szintjei: − szerver és adatbázis − személy − tevékenység Az SQLServer támogatja a speciális védelmi triggereket. Nemcsak naplóz, hanem tetszőleges SQL parancssort indít el. Ilyen speciális triggerek: DDL és login trigger DDL CREATE TRIGGER DDL Trigger_CreateTable ON DATABASE FOR create_table AS CREATE TRIGGER DDL Trigger_PrecentTableChanges ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT ’Cannot drop or modify tables in this database’ ROLLBACK Legtöbb rendszerben a DDL parancsok tranzakción kívül futnak le, így nem lehet őket viszszavonni.

16

LOGIN CREATE TRIGGER validateLogonTrigger ON ALL SERVER WITH EXECUTE AS ’logonTrigger’ FOR LOGON AS BEGIN DECLARE @zim time(0) = getdate() IF ORIGINAL_LOGIN() = ’ReportUser’ AND @time BETWEEN ’23:00:00’ and ’23:30:00’ ROLLBACK END

19. Policy based management Legfontosabb feladatai: − objektumok menedzselése − a minimális igényelt jogosultsági rendszer beállítása − full recovery modell működtetése − beállítások menedzselése − művelet optimalizálás A Policy-based management egy speciális Active Directory az SQLServer felé. Célja a DB rendszer integritásának, védelmének felügyelete. Eszközrendszer arra, hogy a védelmi, működési paraméterek a megszabott értéken belül maradjanak. Komponensei: − TARGET: vizsgált objektum (aminek a paramétereit vizsgálni fogom) − FACETS: egységbe fogott paraméterek − CONDITIONS: paraméterekre megadott feltételek − MODE: azt mutatja, milyen módon történik a CONDITION ellenőrzésének a módja − Policy: target + condition + mode − Enterprise policy manager:szerver park adminisztrálása Működési módok: − On Change – Prevent: ha a műveletnél megsérülne a szabály, akkor visszavonja a műveletet. Előny: biztos (nem lehet hibás állapot) Hátrány: erőforrásigényes. Az ellenőrzés DDL triggeren kersztül történik − On Change – Log Only: egy DDL triggeren keresztül ellenőriz és csak naplózza a sértő műveleteket. Előzőhöz hasonló az erőforrásigénye. − On Schedule: az ellenőrzés időzítetten, ütemezettem fut le. Hátrány: maga az ellenőrzés hoszabb idejű − On Demand: kézi ellenőrzés, hátrány: elfelejthető

17

20. TABLE típusok, létrehozásuk Ideiglenes táblák: memóriában tárolódik, speciális szegmens tárolja − tranzakció szintű − session szintű − lokális − globális: ##nev A tábla neve előtt # szerepel. Fizikai tábla: fizikai, permanens tábla. Két fajtája: − heap: a rekordok folytonos egységben tárolódnak − klaszter: egységekre bontott a tábla, van egy hasító függvény, amely a kulcsa alapján megadja a tároló csoport helyét. Következmény: az azonos kulcsúak egy helyre kerülnek. A klaszter csoportok több különböző tábla rekordjait is tudja tárolni. Nézeti tábla: VIEW, Tábla típusú változó: memórián belüli tábla Táblák létrehozása: − permanens tábla: CREATE TABLE nev (…) ON filegroup − lokális ideiglenes tábla: CREATE TABLE #nev (…) − globális ideiglenes tábla: CREATE TABLE ##nev(…) − memóriabeli tábla (táblaváltozó): DECLARE @valtozo TABLE(…)

21. MERGE parancs DML parancs, összefűzés. Céltábla frissítése egy forrás tábla alapján. MERGE USING (SELECT <expression> FROM <source>) AS ON WHEN MATCHED THEN WHEN TARGET NOT MATCHED [AND ] THEN WHEN SOURCE NOT MATCHEND [AND ] THEN Például, ha van egy árukészletünk, akkor egy MERGE parancs az alábbiakat foglalja össze: − ha már van ilyen áru, akkor növeljük a készletet − ha nincs áru, új áruként hozzá adjuk − ha a régi áru nem szerepel az újak között -> törlés

18

21. AL-SELECT használata Korrelálatlan: − nincs hivatkozás a külső SELECT-be − példa: WHERE db > (SELECT avg(db) …); Korrelált: − van hivatkozás a külső SELECT mezőire − példa: SELECT fo.aru, (SELECT count(*) FROM aruk al WHERE al.db > fo.db) FROM aruk fo; Operátorok: EXIST, ANY, ALL, IN… Az AL-SELECT új funkciója, hogy nem kell VIEW tábla, ha aggregált táblára aggregáció van, hiszen az AL-SELECT táblaként is szerepelhet.

22. SELECT speciális elemei: CASE, WITH, IDENTITY CASE Feltételes kifejezés SELECT CASE kif WHEN e1 THEN k1... ELSE k END SELECT CASE WHEN kif1 THEN k1… ELSE k END … Első esetben a kifejezés értékét vizsgálja a meglévő értékekre, míg a második esetben nem fix értékkel hasonlítunk össze. Tetszőleges logikai kif az egyes ágak mögött. Az első igaz ágat fogja visszaadni. NULL érték kezelése: − IS NULL: ha az ’a’ argumentum üres, akkor a ’b’-t adja vissza. Ha nem üres, akkor önmagát. Például SELECT IS NULL(3,2) -> 3-t ad, SELECT IS NULL(null,2) -> 2-t ad. − NULL IF: akkor ad vissza null értéket, ha a két érték megegyezik. Például SELECT NULLIF(5,5) -> null, SLECT NULLIF(3,2) -> 3 WITH Ideiglenes változók létrehozására. SQL parancs hatáskörében jön létre. WITH tnev (mezők) AS (SELECT…) SELECT … FROM tnev… String mintára illesztés: LIKE, CONTAINS, FREETEXT Aliast rendel a táblához, ez felhasználható az utána lévő SELECT-ekben. IDENTITY Mező típus. Származtatott kulcs. Auto increment key. Szintaktika: IDENTITY(n,m); kod int identity(1,1) Ha explicite szerepel, akkor azt használja, ha nincs megadva, akkor ő fogja generálni. AS kifejezés (számított mező); oar AS (db*ear) nev AS (2010-elerkor)

19

24. TSQL blokk, eljárás és függvény felépítése, futtatás Parancs alkotás menete: − forrástáblák meghatározása − a megfelelő join típus kiválasztása − szelekciós feltételek meghatározása − igényelt eredménymezők meghatározása − al-SELECT-ek meghatározása − helyesség ellenőrzése − teljesítmény ellenőrzése − hangolás TSQL blokk: BEGIN…. END Lokális változók deklarálása: DECLARE @vnev tipus; A típus lehet egész(int, bigint…), tört(numeric, decimal, float), dátum(datetime, smalldatetime), szöveg (char, varchar, text), bináris (binary, image, varbinary) BEGIN DECLARE @x int; SET @x = 3; SELECT @x; END; Tárolt eljárás előnyei: − kisebb hálózati forgalom − gyorsabb SQL végrehajtás − centralizált kezelés − hatékonyabb karbantartás − védhető adatbázis objektum − klienst tehermentesíti − egymást hívhatják − ütemezhetők − lehet függvény is Eljárás felépítése: Nincs visszatérési értéke, de lehet kimeneti paramétere CREATE PROCEDURE nev(@e1 [=ert1] tip1 …) WITH ENCRYPTION | EXECUTE AS user AS BEGIN TRY … END; Futtatása: EXEC nev p1, p2; Függvény felépítése: CREATE FUNCTION fnev(@p1 tip1…) RETURNS tipus WITH ENCRYPTION | EXECUTE AS user AS BEGIN … RETURN ertek; END; Futtatása: SELECT * FROM nev(p1, p2)

20

25. Vezérlési eljárások, parancsok TSQL-ben Vezérlési elemek: − Értékadás: SET @v = kif; − Ciklus: WHILE feltétel BEGIN … END − Kilépés: BREAK − Átlépés: CONTINUE − Elágazás: IF feltétel BEGIN … END ELSE BEGIN … END − Többszörös elágazás CASE … WHEN … ELSE … END − Eljáráshívás: EXECUTE enev p1 p2…

26. Lekérdezés eredményének kezelése eljárásban 27. Kurzor kezelés Deklaráció: DECLARE knev CURSOR FOR SELECT… Nyitás: OPEn knev Lekérdezés: FETCH poz knev INTO @v1… Ciklus: WHILE @@FETXH_STATUS = 0 Lezárás: CLOSE knev Felszabadítás: DEALLOCATE knev Információ lekérdezés: @@CURSOR_ROWS Kurzor működési módok: − INSENSITIVE (önálló másolat) − SCROLL − READ ONLY FOR UPDATE FORWARD ONLY STATIC KEYSET DINAMIC OPTIMISTIC Példa: BEGIN DECLARE @v char(29) DECLARE c1 CURSOR FOR SELECT megnev FROM aruk; OPEN c1; FETCH NEXT FROM c1 INTO @v; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @v; FETCH NEXT FROM c1 INTO @v; END; DEALLOCATE c1; END;

21

28. Kivétel, hibakezelés TSQL-ben Szintaktika: BEGIN TRY … END TRY BEGIN CATCH ERROR_NUMBER() ERROR_MESSAGE() ERROR_LINE() ERROR_SEVERITY() END CATCH; RAISEERROR(szoveg, kod1, kod2) 29. Tábla változók kezelése

30. Tábla értékű függvény Komplett táblát ad vissza és táblaként használható. Szerepe olyan, mint a VIEW. Szintaktika: CREATE FUNCTION (param) RETURNS @ret TABLE(m1 tip1, …) AS BEGIN … INSERT INTO @ret VALUES(…); RETURN; END; Ha a függvény determinisztikus, akkor lehet indexet építeni az eredményhez.

31. Triggerek megadása Előnye: automatikusan meghívódik egy kiváltó eseményre. A végrehajtás sebessége gyorsabb, előre le van fordítva. Garantálja a kapcsolódó tevékenységet, nagyobb biztonság, megbízhatóság. Figyelni kell a triggerek közötti függőségre, melyik trigger mit hív. Insertnél lehet hívni saját magát a triggert. Kezelésre a triggerek egymás hívására: − hívási gráf (körútmentesség) − hívási mélység figyelés, erre korlát Létrehozása: CREATE TRIGGER tnev ON tábla [WITH ENCRYPTION | EXECUTE AS user] FOR UPDATE | INSTEAD OF | INSERT | UPDATE | DELETE AS BEGIN … END; Működési szabályok: − csak egy INSTEAD OF trigger egy eseményhez

22

− − − −

több AFTER trigger egy eseményhez a válaszban nem lehet DDL parancs AFTER trigger a kiváltó sikeres befejezése után fut le van DML és DDL trigger

DDL trigger: módosításra aktivizálódik CREATE TRIGGER tnev ON DATABASE | ALL SERVER WITH ENCRYPTION | EXECUTE AS user FOR ALTER_TABLE | DROP_TABLE AS BEGIN END

32. Információ lekérdezési források Az információ lekérdezéshez a források (jogosultsághoz kötöttek): − INFORMATION_SCHEMA séma objektumai − sys.* információs VIEW elemek − sys.sp_* tárolt eljárások − segédprogramok (esemény és teljesítmény figyelés) − SQL parancsok − OS rendszer monitorok − ALERT mechanizmus Szerepkörök lekérdezése: exec sys.sp_helrpole; Tagok lekérdezése: exec sys.sp_helprolemembers Adatfile státuszok: exec sys.sp_helpfile Filecsoport információk: exec sys.sp_helpfilegroup Server szerepkörök lekérdezése: esec sys.sp_helpsvrole Tagok lekérdezése: esex ses.sp_helpsrvrolemembers Index információk: exec sys.sp_helpindex ’tabla’ SQL naplói: széleskörű naplózást biztosít, több eszköz áll a rendelkezésre. SQL ServerLog: minden szerves indítás új naplófájlt indít, kezd el. A naplófájl ciklikusan felülírható. Activity Monitor: tevékenység monitor. Az aktuális állapotokat tartja nyilván: mik futnak? Milyen objektumokat használnak? Zárolások, lefoglalások.

23

33. Adatbázis paraméterek lekérdezése Adatbázis objektumok alapadatai: − SELECT * FROM sys.all_objects; − Típusok: − CLR_STORED_PROCEDURE − SYSTEM_TABLE − PRIMARY_KEY_CONSTRANT − USER_TABLE − VIEW − SERVICE_QUEUE − SQL_TRIGGER − INTERNAL_TABLE Adatbázis adatok lekérdezése: − sys.databases (name, parameters) − sys.database_files Felhasználók adatai: − sys.syslogins (name, id, roles) − (use db) sys.sysusers

34. Teljesítmény problémák forrásai, megoldási elvek Az adatbázis életében nem ritkák a kísérő hibák, teljesítmény problémák jelenléte. A probléma megoldáshoz használható útmutatók: − ismerni kell a lehetőségeket − nem szabad idő előtt feladni − elszántságból jön a siker − kérj segítséget, tanulj − lokalizáld a problémát és elemezd − figyeld a naplókat − tanulmányozd a könyveket − figyeld mások megoldásait Megoldás részlépései: 1. a rendszer működésének megértése 2. a kijelölt mennyiségek változásának figyelése 3. kapott eredmények elemzése 4. hipotézisek felállítása 5. megoldási alternatívák kidolgozása 6. implementáció 7. eredmények tesztelése 8. folyamatos dokumentálás Fel kell tárni a rendszer struktúráját modulokra, majd a modulokat önállóan tesztelni. A tesz annál jobb, minél izoláltabban, egyszerűbben tudom tesztelni. Probléma lehetséges forrásai: − szerver gép (HW, hálózat, ellenőrzés) − operációs rendszer (szerveren) − SQL Server DBMS (nem fut, rossz account, memória hiba) − adatbázis séma (nincs olyan állomány, jogosultság)

24

− kliens SQL-API program (SQL parancs végrehajtható-e vagy sem, szintaktikai hiba mellett nehezebb, nagyobb probléma a hatékonyság) − munkamódszer (SQL parancs adat interface kialakítás elve: különböző hatékonyságú végrehajtási módok vannak: műveletek kiválasztása, operátorok) A leggyakoribb hiba a kliens SQL oldalon van. A legköltségesebbek az adatbázis séma tervezésének hibái. Rendszerhiba szintjei: − lemez, hardver − adatbázis − szolgáltatások Fontosabb elemzési eszközök: − OS működése − esemény és hibanapló − rendszerkezelő panel (services) − SSMS − SQL Server Configuration Manager − Profiler − Transact-SQL

35. Adatbázis hibák típusai, forrásai, megoldási elvek Az adatbázis hibák naplóból követhetők. Hibaesemény paraméterei: − hibakód − üzenet − szint, komolyság (severity) − okjelző (state) − eljárás neve, soszám Hibaszintek: 10-24 − 10: információ − 11: nem létező objektum − 19: végzetes erőforrás kapacitás hiba − 21: minden taszkot érintő hiba, db sértetlen marad − 22: db objektumot károsító hiba − 24: eszköz, diszk hiba Az esemény a 19-es szinttől bekerül az EventLog-ba Tipikus hibaesemények: − A REDO LOG megtelik (9002-es hiba generálódik) o Okai: túl hosszú tranzakció, futó mentés, helyreállítás o Lépések: log file mentése, hely növelése, tranzakciók leállítása − Az adatállomány helye megtelik (1101-1105 közötti számú hiba generálódik) o Okai: túl sok adat o Lépések: hely növelése, ALTER DATABASE ADD FILE − Tempdb esetén az ideiglenes adatok miatt is megltekhet Memóriahibák: Leálláshoz vezetnek. Lépések: − új hardver eltávolítása

25

− új meghajtók eltávolítása − meglévő komponensek kiemelése egyenként − RAM teszt futtatása − alaplap csere SQL service hibák: − Okai: o új komponens installálás o jelszó érvénytelenség − Service panel főbb funkciói: o indítás/leállítás o indítási mód beállítás o user megadása o hibakezelés

36. System Monitor működése, használata A monitor célja a kiválasztott teljesítmény metrikák − figyelése, nyomon követése − összevetése a bázisértékekkel − problémák észlelése Fontosabb metrikák és használatuk: − Memóriaterhelés o pages/sec: kilapozások gyakorisága (<20) o available bytes: fizikai memória hely (>5MB) o SQL Server Buffer Cache Hit Ratio: DB cache találati arány (>90%) o disk reads/sec o disk writes/sec − Lemezterhelés o % disk time: lemez foglaltság (<55%) o average disk queue length: várakozósor hossza (<10) o % free space: szabad terület aránya (>15%) − Processzorterhelés o % processor time: futási idő (<80%) o processor queue length: várakozósor hossza (<2) − Hálózat terhelés o % bytes received/sec: fogadott byte-ok o % bytes send/sec: küldött byte-ok

26

37. PROFILER működése, használata SQL Server Profiler: − események nyomon követése − események adatainak rögzítése − az adatok későbbi kiértékelése SQLServer specifikus eseményekre reagál Indítása: All Programs -> Administrative Tools -> Performance -> System Monitor A naplózást meghatározó legfontosabb paraméterek: − EventClass: esemény jellege (Stored Procedures) − EventCategory: esemény kategória (RPC Completed) − DataColumn: értintett adatmezők (DUration, CPU) − Template: minta − Trace: naplózás − Filter: szűrő Előnye: a hálózatot is figyeli, nem csak lokális. Lementése táblába és fájlba is lehet.

38. Adatmentési stratégiák és jellemzésük A DBA egyik fő feladata az adatok épségének, megőrzésének biztosítása. Az adatokat redundánsan kell tárolni: − mentések (backup) − helyreállítás (restore, recover) Különböző mentési modellek: − COPY mentés − SQLServer mentés − mentés jellege − mentés tartalma Mentési típusok: − FULL o minden adatelem mentésre kerül o minden köztes napló is mentésre kerül − BULK LOGGED o a bulk copy parancs nem mentődik le, ekkor egy teljes mentésre is szükség lehet − SIMPLE o nincs napló mentés o bluk copy nem naplózott A mentésekben az adatok ömlesztve jönnek, a helyreállítás modul teszi őket használhatóvá.

27

39. Mentési módok, teljes mentés parancsai Elvégezhető mentési módok: − differential backup − complete backup − transaction backup − file/filegroup backup − file/filegroup differential A napló és filegroup mentések csak SIMPE módban használhatók. A mentés célhelyeiként BACKUP DEVICE hozható létre: permanens vagy ideiglenes. Teljes adatmentés: konzisztens mentést adhat, függetlenséget adhat, több célhely megadható egyszerre, célszerű időszakosan megismételni Lépései: − adatbázisok zárolása, minden tranzakció blokkolása − a naplóban megadni a kezd SLN-t − adatbázis zárolás felengedése − adatbázis tartalom mentése − adatbázis zárolás, minden tranzakció blokkolása − jelezni a naplóban a záró SLN-t − adatbázis tartalom felengedése − a köztes szakasz napló bejegyzéseinek mentése Parancsa: BACKUP DATABASE nev TO DISK | TO TAPE WITH INIT Inkrementális adatmentés − helyreállítás hatékonyságát növeli − csak a legutóbbi mentés után módosult adatokat menti − induló teljes mentést igényel − több célhely megadható egyszerre − célszerű időszakosan megismételni

28

40. Differenciális és napló mentés és parancsai Differenciális mentés − helyreállítás hatékonyságát növeli − a legutóbbi teljes mentés óta eltelt változásokat menti le − induló teljes mentést igényel − több célhely megadható egyszerre − célszerű időszakosan megismételni Parancs: BACKUP DATABASE nev TO DISK | TO TAPE WITH INIT WITH DIFFERENTIAL Naplómentés − helyreállítás hatékonyságát növeli − a napló bejegyzéseket menti más helyre − a mentés után csonkolódik a naplófájl − induló teljes mentést vagy részleges mentést igényel − több célhely megadható egyszerre − célszerű időszakosan megismételni A napló mentés point int time vagy point of failure helyreállításokat is lehetővé tesz. Filecsoport adatmentés − helyreállítás hatékonyságát növeli − szelektálható az objektumok köre − gyorsabb mentés végrehajtás − több célhely megadható egyszerre − naplómentést is igényel

41. Adathelyreállítás lépései RESTORE: az állományok adattartalmának helyreállítása RECOVER: az adatok integritásának helyreállítása Főbb lépések: − adatfájlok visszaállítása (restore) − naplók tartalmának újrajátszása (redo) − az integritás helyreállítása (recover) A helyreállítás előtt célszerű még egy naplómentést végezni. Ha több elemű a mentés, akkor a sorrend: − FULL mentés helyreállítás − DIFFERENTIAL mentések helyreállítása

29

42. Helyreállítási parancsok Helyreállítás SQL parancsa: RESTORE DATABASE dd FROM DISK=device WITH REPLACE Az integritás helyreállítás szabályozása: RESTORE … WITH RECOVER | WITH NORECOVERY NORECOVERY ha a db-t alap mentés után több differenciális mentéssel mentettük le. Speciális helyreállítási esetek: − RESTORE LABELONLY − RESTORE HEADERONLY − RESTORE FILELISTONLY

43. Adatbázisok lecsatolása Speciális opció az adatbázisok lecsatolása és visszacsatolása Lecsatolás: database –tasks – detach Visszacsatolás: database – attach

44. Particionálás funkciója, parancsai Feladata a tábla, index adatainak szétosztása több filegroup-ba Előnyök: − nagy méret hatékonyabb kezelése − részek önálló mentése, karbantartása − hatékonyabb, gyorsabb adathozzáférés − terhelés kiegyensúlyozás Hátrányok: − nagyobb adminisztráció − egységként kezelése Lépések: végrehajtandó parancsok: − particionáló függvény létrehozása − particionáló séma létrehozása a particionáló függvény alapján − tábla, index kötése a particionálási sémához: objektum létrehozásakor, vagy új cluster index magadásával − információs adatok lekérdezése Particionálási függvény Célja a rekord tartalma alapján a rekord helyének kijelölése. Ez lehet csak tartomány alapú (nincs hash) vagy egy kulcsintervallumhoz tartozik egy célhely CREATE PARTITION FUNCTION fnev(kulcs_tipus) AS RANGE LEFT | RIGHT FOR VALUES (v1, v2…) Particinálási séma: Szerepe: − a tartományok rekordjainak helyét adja meg − értéktartományok hozzárendelése filegroup-okhoz − particionálási függvényen alapszik CREATE PARTITION SCHEMA pnev AS PARTITION pfnev TO ([filegroup1]…) 30

Tábla particionálása Létrehozáskor meg kell adni a partíciós sémát, valamint ki kell jelölni az aktuális kulcsmezőt. CREATE TABLE dbo.sema.tabla (mezolista) ON particio_séma (aktuális_kulcs) Index particionálása CREATE INDEX db.sema.index ON (mezolista) ON partíció_séma (aktuális_kulcs) Egy új clustered index létrehozásával utólag is particionálható a tábla. Partíciók adatai: Információs források − partíciós függvény: SELECT … FROM SYS.PARTITION_RANGE_VALUES − partíciós séma: SYS.PARTITION_SCHEMES − partíciók: SYS.PARTITIONS Partíciós függvény meghívás: SELECT … $PARTITION.par_fugv(pp) Partíciós függvény módosítása: − Intervallum szétvágása: ALTER PARTITION FUNCTION fnev SPLIT (hatar) − Intervallum összevonása: ALTER PARTITION FUNCTION fnev MERGE (hatar) Partíciók átmozgatása: Egy tábla partíciói átcsatolhatók egy másik táblához. ALTER TABLE db.sema.tabla SWITCH [PARTITION pn] TO db.sema.tabla [PARTITION pn] A partíciók duplán láncolt listába szervezettek. Particionálás 3 fajtája: − Vertikális: oszlopok mezők, más-más partícióba kerülnek − Horizontális: rekordonkénti, különböző rekordok más-más táblába kerülnek − Vegyes

31

45. LOG SHIPPING jellemzése A napló állományt átvisszük egy másik szerverre, ott pedig végrehajtjuk. A másodlagos szerver csak passzív szerepet tölt be, nem lehet önálló élete. Szinkronizációt igényel. Szerepe: − adatbázis mentések szinkronizálása − nincs automatikus helyreállítás − a végrehajtott parancsok naplóit átküldi a tükörképhez − megismétli az operatív DB-beli műveleteket − agent felügyelete alatt fut − a monitor csak naplóz Szereplők: − Primary database − Primary server − Secondary database − Secondary server − Monitor server Előfeltételek: − egyazon szerver is játszhatja mindhárom szerepet − nem fut Express Edition alatt − SQL Server Gent távoli elérést biztosítani kell − primary database FULL Recovery módban − osztott katalógust igényel (primary: RW, secondary: R) Primary adatbázis beállítása: − SQL Server Management Studio − Database Properties Window − Transaction Log Shipping Page − Enable as primary − Backup mód beállításai o katalógus megadása o megőrzés időtartama o késedelem figyelése o job neve, ütemzése Secondary adatbázis beállítása: − Primary database panelen keresztül − Secondary database settings − Secondary DB mód beállításai: • kapcsolati paraméterek (távoli agenthez történő kapcsolódás) • adatbázis inicializálás módja • naplófájl átmásolás módja • naplófájl lejátszás módja (szinkronizáció) − inicializálási módok:
full backup
létező mentés használata
nincs szükség inicalizálásra − naplófájl másolási paraméterek
katalógus
megőrzés időtartama

32


job paraméterek − naplófájl lejátszás beállításai:
no recovery mód
standby mód Monitor szerver beállítása − A primary és a secondary szerver Agent adatait olvassa − Database properties window a primary adatbázisnál − Backup mód beállításai o kapcsolódási jelszavak o megőrzés időtartama o Job neve RESTORE működési módja: − RECOVERY: nem teljes tranzakciók adatai törlődnek, használható a DB − NO RECOVERY mód: nem törlődnek tranzakciós adatok, várja a folytatás, nem használható a DB − STANDBY mód: nem teljes tranzakciók adatai külön állományba lementődnek, későbbi helyreállításkor felhasználhatók, közben használható a DB.

46. Tükrözés működési modellje Cél: a rendelkezésre állás növelése egy rugalmas automatizált architektúrán keresztül. Résztvevők: − principal: o működő, aktív adatbázis o full recovery módban működik o a végrehajtott parancsok naplóit is átküldi a tükörképhez − mirror: o megismétli az operatív DB-beli műveleteket o recovering state módban van (nem normál mód, helyreállítási státusz) o közvetlenül nem fogad tranzakciókat − witness: o állapot felügyelő o high availability módban van o failover detektálás és kezelése o feladata a komponensek rendelkezésre állását felügyelje és szükség esetén beavatkozzon Tükrözés lépései: − forrás DB beálltása • áttérés full recovery módra • adatbázis teljes mentése − tükör adatbázis beállítása • mentés helyreállítása norecovery opcióval • egyéb rendszer adatok átvitele − adatbázisok kommunikáció beállításai • kapcsolat engedélyezése • endpoint védelmi paraméterek o protokoll: TCP o jelleg: partner, witness, all

33

o program jellege: DATABASE_MIRRORING o engedélyezett port: 5022 o authentikációs mód: NTLM o titkosítási algoritmus: RC4 − endpoint létrehozása: CREATE ENDPOINT név AS TCP (LISTENER_PORT = 5022) FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION = REQUIRED) Működési módok: High Availablity mód: Folyamatos, szinkron adatávitel a másolatra Lépések: − forrásnál napló kiírása − napló átküldése a másolatra − másolatnál művelet elvégzése − COMMIT kiadása a forrásnál − COMMIT elküldése a másolatra, végrehajtás − nyugtázás vissza a forrásra − COMMIT végrehajtás a forrásnál Helyreállítási lépések: − folyamatos ping a komponensek között − a fő DB?S leáll − a tükör észleli a leállást, s megkére a witnesst, hogy ő legyen az új fő DBMS − witness engedélyezi a funkciócserét − volt tükör DBMS normál módra átáll − ha a korábbi fő DBMS helyreáll, látja, hogy nem ő a fő DBMS − a witness őt teszi új tükör DBMS-é High Performance mód: − cél a gyorsaság növelése − nincs witness komponens − nincs automatikus helyreállítás − asszinkron adatátvitel − független tranzakciók − eltérés a két DB kép között High Protection mód: − folyamatos, szinkron adatátvitel a másolatra − nincs witness elem − nincs automatikus helyreállítás − két adatbázis szinkronban van

34

47. Replikáció működési modellje Alapfogalmak: − article: elemi objektum: tábla, view, stored procedure − publication: egységként átküldött objektumok − publisher: objektumok birtokosa − subscriber: objektumok fogadója − distributor: objektumok forgalmának irányítója − működési módok: pull vagy push Snapshot replikáció: az igényelt objektumról normál mentés készül. A másolatok átkerülnek a célhelyre és ott megtörténik a visszaállítás. Tranzakciós replikáció: csak a módosítások kerülnek át. LOG file alapú adatátvitel Merge replikáció: több szervernél is módosulhat az adat. Induláskor szinkronizálódnak az adatok, a különböző forrásbeli módosulások eredője kerül át a tagokhoz eredményként. Aszinkron adatátvitel. Replication Agents: − replikáció megvalósításáért felelősek − Snapshot Agent − Log reader Agent − Distribution Agent − Merge Agent − Queue reader − History, distribution… clean up − Check up Replikáci beállítási lépései: − Publisher kiválasztása − Distributor konfigurálása − Publikáció engedélyezése − Publikáció konfigurálás − Előfizetés konfigurláls − Publikáció elküldése Distributor paraméterezése: − SSMS/Database/Replication − Distributor panel − Database kijelölés − kapcsolódó publisher adatbázisok megadása EXEC sp_addistributiondb @database=… EXEC sp_addistpublisher @publisher=… @distribution_db=… Publication paraméterezése: − SSMS/Database/Replication − Database kijelölés − publikáció típusának megadása (snapshot, merge…) − atricle elemek kiválasztása − védelem, időzítés megadása EXEC sp_addpublication @publication=… EXEC sp_addarticle @publication=… @article=…

35

Subscription paraméterezése: − SSMS/Database/Replication − Publication panel − New subscription − Előfizetési paraméterek megadása: pull vagy push; agent kijelölés; ütemezés EXEC sp_addsubscription @publication=… @subscriber=… EXEC sp_addpushsubscription_agent… NOT FOR REPLICATION mód: − replikáció külön kezelése a subscriber oldalon − trigger esetén: nem fut le a replikáció keretében elvégzett változtatásokra − foreign key esetén: nem végez ellenőrzést a replikáció keretében végzett DML parancsokra − identity mező esetén: kulcs érték generálás ütközés elkerülés EXEC sp_adddistributiondb @database=… EXEC sp_addistpublisher @publisher=… @distributin_db =… Konfliktuskezelés paraméterezése (merge): − priority value − averaging − datetime latest − datetime earliest − maximum/minimum − join text − subscriber wins − priority column Védelem paraméterezése: − Server vagy database szinte konfigurálható − PAL: publication access list: add, remove

36

48. Szöveges állományok beolvasása bcp-vel Az adatbázisba sokszor létező adatokat emelünk át. Módjai: − saját program készítése (drága, nagy beruházási költség) − bcp segédprogram: SQLServertől különálló. Nagy tömegű adatot visz be a tranzakció kezelés leegyszerűsítésével − BULK COPY: SQL parancsa is van, nem csak egy bcp segédprogram. Kisebb funkcionalitás. − XML-DB segítségével − SSIS: SQL Server Integration Service (integrációs komplett termék). Célja: univerzális adatfolyam a különböző komponensek között. Nemcsak mozgat, közben transzformál is. Előnye: drag and drop módon lehet programozni. Komplett folyam építhető fel: adatfolyam, vezérlésfolyam, eseménykezelés. Ez az SQL Server fő erőssége. BCP: Bulk Copy Program − parancssori program − korlátozott adatkonverzió és ellenőrzés − igényelt jog: SELECT, INSERT, ALTER bcp db.tabla in | out textfile -t mezőterminátor -r rekordterminátor -F átugrott első rekordok darabszáma BULK INSERT: − SQL parancs − csak adatbetöltésre (bcp funkcionális) − igényelt jog: INSERT, ALTER − a LOGIN OS joggal olvassa be a fájlt BULK INSERT db.tabla FROM file WITH FIRSTROW = n ROWTERMINATOR=… FIELDTERMINATOR=…

37

49. XML adatok generálása XML: − szemi-struktúrált adatszerkezet − helyesen formált: o pontosan egy gyökérelem o teljesen a szülőbe foglaltak a gyerekek o egy vagy kéttagú elemek o elemjellemzők idézőjelek között − felhasználása: adatátvitel, elemi adattárolás, információ kódolás − adatok mellett a metaadatok is tárolásra kerülnek − fő célja: az adatok közvetítése a formátum segítségével Perzisztens tárolás: − nem hatékony a feldolgozása − nagy méretű − sok leíró van benne XML tárolása: − szövegmezőben: o DECLARE @vv nvarchar(555) o SET @vv = ’…’ o Általános, egységben kezeli a teljes dokumentumot − XML adattípusban: o DECLARE @vv AS XML o SET @vv = ’…’ o DBMS közeli, XML specifikus adatkezelés XML validálás: Validáció az XMLSchemára épül Lehetőségek: − adattípusok, névterek használata − saját elem és adattípusok, származtatás − gazdag megszorításkészlet Séma definiálása: CREATE XML SCHEMA COLLECTION nn AS ’xmlschema leírás…’ Séma hivatkozás: − DECLARE @vv AS XML(nn) − CREATE TABLE tt(nn XML(nn),…) XML fájl előállítása a relációs adatokból: − SELECT … FROM … FOR XML RAW (’tagnev’): nincs gyökér, egyszintű, minden rekord egy tag; minden mező egy attribútum − SELECT … FROM … FOR XML RAW, ELEMENTS: minden mező egy gyerekelem − SELECT … FROM … FOR XML AUTO: minden forrástáblához külön gyerekelem; minden mező attribútum − SELECT … FROM … FOR XML AUTO, ELEMENTS: minden mező gyerekelem − SELECT … FOR XML PATH(’tagnev’), ROOT (’nev’): részletes kontroll, egyedi kialakítás; minden mezőre külön XPath beállítás; gyökérelem is kijelölhető − SELECT … FOR XML EXPLICIT(’tagnev’), ROOT(’nev’): legnagyobb kontroll, egyedi kialakítás; mezőkre külön kiterjesztett XPath beállítás

38

XML fájl előállítása XML adattípusú adatokból: − query(): XQuery lekérdezés XML fregmentet ad vissza − value(): XQuery lekérdezés skalár értéket ad vissza − exist(): XPath kifejezés, csomóponti létezés ellenőrzése XML adattípus módosítása: − modify(): XQuery Update funkciók biztosítása

50. XML adatok beolvasása OPENXML XML külső forrást nyit meg és egy táblát képez. Megadható a konvertálás. xPath-szal tudjuk kiemelni a szükséges részeket. … FROM OPENXML(forrás, ’xPath_kif’, mód) WITH (séma)… Séma: elemek relációs adattípusra konvertálása nodes() OK: insert into tt3 values(1,’Tomi23’); NO: insert into tt3 values(1,’Tomi<e>23’); Bulk copy update tt3 set leiras.modify(’replace values of (/a/c)[1] with 55’) where kod = 2; OPENXML használata: declare @vv xml; set @vv = Laci34 Anna24’ declare @h int; exec sp_xml_preparedocument @h OUTPUT, @vv; select * from openxml(@h,’/f/a’,2) with (b char(20), c int) XML indexek: primary index: − minden csomópontot indexel − xPath keresésre optimalizált − CREATE PRIMARY XML_INDEX inev ON t(m) secondary index − másodlagos keresésre, primary indexet indexeli − típusai: PATH, VALUE, PROPERTY − CREATE XML_INDEX inev ON t(m) USING XML INDEX primary FOR PATH | VALUE | PROPERTY

39