Az SQL Server 2005 relcis motorjnak jdonsgai Ksz

Az SQL Server 2005 relációs motorjának újdonságai Kószó Károly rendszermérnök Microsoft Magyarország

Tech. Net események 2005 tavaszán 2005. március 23. A Windows Server idei újdonságai 2005. április 6. Átállás a legfrissebb technológiákra: Windows Server 2003, XP, Office 2003 2005. április 20. Az SQL Server 2005 relációs motorjának újdonságai 2005. május 4. Az SQL Server 2005 üzleti intelligencia szolgáltatásai 2005. május 18. Üzemeltetői konferencia

Napirend v Az SQL Server adatplatform v Az SQL Server 2005 áttekintése Ø Nagyvállalati adatkezelés – Biztonság – Rendelkezésre állás – Skálázhatóság Ø Programozhatóság – XML, CLR, T-SQL Ø (Üzleti intelligencia)

SQL Server Átfogó, integrált adatplatform Reporting Services Analysis Services (OLAP, DM) Notification Services Integration Services Replication Services Relációs adatbázis

Az SQL Server generációi 1. A kezdetek 2. A modern SQL Server 6. 0/6. 5 SQL Server 7. 0 • Windows integráció • DTC (elosztott tranzakciók) • Beépített tranzakcionális replikáció • Új relációs és tároló motor • Kiterjedt automatikus erőforrás kezelés • OLAP • ETL eszköz (DTS) Állandó célok SQL Server 2000 • Teljesítmény, skálázhatóság fókusz • XML támogatás • Továbbfejlesztett OLAP kiszolgáló • Adatbányászat • Notification Services • Reporting Services • Megbízhatóság, biztonság • Integrált üzleti intelligencia 3. A közeli jövő SQL Server 2005 • Rendelkezésre állás fókusz • Biztonság • Hatékony fejlesztés • Natív XML • Nagyvállalati ETL és adatbányászat • A legalacsonyabb TCO • Automatikus hangolás

Az SQL Server 2005 fejlesztés fő területei Nagyvállalati adatkezelés v v Programozhatóság v v v Üzleti intelligencia v v Magas rendelkezésre állás Biztonsági és teljesítmény jellemzők Menedzselhetőség Visual Studio és. NET CLR integráció Natív XML technológia az adatbázisban Web Service felületek (natív SOAP) Valósidejű döntéstámogatás: jelentéskészítés, OLAP, adatbányászat Intelligens kliensek; Share. Point megoldások Átfogó (és lényegesen gyorsabb) ETL szolgáltatás

Nagyvállalati adatkezelés v Elvárások Biztonság Ø Rendelkezésre állás Ø Skálázhatóság Ø Menedzselhetőség Ø v SQL Server 2005 Jogosultságkezelés Ø Adatbázis tükrözés Ø Gyors helyreállítás Ø Adatbázis pillanatfelvétel Ø Online index műveletek Ø Pillanatfelvétel izoláció Ø Partícionálás Ø

SQL Server 2005 biztonsági fejlesztések v Továbbfejlesztett biztonsági jellemzők Adattitkosítás, titkosítási kulcs kezelés Ø Auditálás, felhasználó azonosítás és hozzáférési jogosultság kezelés Ø v A felület minimalizálása Ø A legtöbb szolgáltatás telepítése opcionális – explicit választást igényel v Telepítés, javítócsomagok Ø Microsoft Update Services integráció v Common Criteria Ø Tervezett: EAL 4+ (Evaluation Assurance Level 4, vagy erősebb) v Biztonsági eszközök MBSA (Microsoft Baseline Security Analyzer) Ø SQL Server Best Practices Analyzer Ø

Redukált (támadási) felület

v Surface Area Configuration demó

Jelszó házirend v A helyi NT jelszó házirend alapján Ø Ø Ø Jelszó erősség, lejárat Fiók kizárás Alapértelmezett működés v Megvalósítás Új jelszó házirend ellenőrző API a Windows 2003 Server-től kezdve Ø Azonosításkor, jelszó megadáskor hívják Ø v Adminisztrátori opciók a login-ok kapcsán Házirend ellenőrzések, lejárat ki/be kapcsolása Ø Jelszócsere előírása az első bejelentkezéskor Ø Login-ok gyors letiltása Ø

A felhasználók és a sémák szétválasztása v A principálisok és a sémák szétválasztása Ø Principális – Az egyedek, akikkel szemben az objektumokat védjük – sys. database_principals nézet Ø Séma – Objektum konténer; a 4 részes név 3. darabja – sys. schemas nézet v A default séma Ø Ø Ø A felhasználók és a szerepkörök tulajdonsága A névfeloldáskor használja a kereső algoritmus sys. database_principals v Új DDL a felhasználókra és a sémákra Ø CREATE/ALTER/DROP - USER/ROLE/SCHEMA v A felhasználó megszüntetése nem igényli a séma átnevezését

Default Schema v Névfeloldáshoz, ha nincs megadva a sémanév Ø Az SQL 2000 -ben a „dbo” az alapértelmezett „séma” Ø A dbo séma bővítése potenciális biztonsági rés – A tulajdonosi lánc helytelen használata esetén Ø SQL 2005 -ben csak akkor a „dbo” az alapértelmezett séma, ha az adatbázis felhasználónál nincs default schema megadva – create/alter user … WITH DEFAULT_SCHEMA = sémanév

Sémák és felhasználók v Az adatbázis több sémát v v tartalmazhat Minden sémának van tulajdonosa – user vagy role Minden felhasználónak van alapértelmezett sémája, aminek nem feltétlenül ő a tulajdonosa Az adatbázis objektumok sémákban léteznek A tulajdonosi lánc változatlanul működik Adatbázis Has default schema Owns Schema 1 Approle 1 User 1 Role 1 Owns Schema 2 Schema 3 SP 1 Tab 1 Owns Fn 1

Végrehajtási környezet SQL Server 2000 Execute jog ellenőrzés User 3 -ra Select jog ellenőrzés User 3 -ra User 2. Proc 1 User 1. T 1 User 1. Proc 1 User 1. T 1 Execute jog ellenőrzés User 3 -ra Nincs jogosultság ellenőrzés User 3 -ra User 3 SQL Server ‘Execute AS ‘X’ ’ User 3 User 2. Proc 1 Execute jog ellenőrzés User 3 -ra User 1. T 1 Select jog ellenőrzés‘X’-re, Nem user 3 -ra

Modul végrehajtási környezet v Module Ø Tárolt eljárás, függvény, trigger v A tulajdonosi lánc helyett/mellett használható Ø A tulajdonosi lánc szabályok változatlanul érvényesek v A jogosultságokat a végrehajtási környezetre ellenőrizzük Ø Az adat definíciós utasításokra és a dinamikus SQL-re is v A modulok végrehajtási környezetét tartalmazó rendszer nézetek: Ø sys. sql_modules és a sys. assembly_modules

Modul végrehajtási környezet (2) v Execute AS CALLER A modul utasításai a modult közvetlenül hívó nevében (és jogaival) hajtódnak végre Ø Alapértelmezett viselkedés, hasonló az SQL Server 2000 -hez Ø v Execute AS ‘user’ Ø Ø A modul a megadott felhasználóval hajtódik végre Az execute as záradékot megadó felhasználónak IMPERSONATE jog kell a megadott ‘user’-re v Execute AS SELF Ø A végrehajtás a záradékot megadó felhasználó nevében történik v Execute AS OWNER Ø Ø Az utasítások a modul mindenkori tulajdonosa nevében futnak Impersonate jog kell az aktuális tulajdonosra (megadáskor) v Kiszolgáló hatókörű DDL triggerek esetén: Ø Execute AS ‘login’

Példa az Execute As használatára v Probléma: Ø Ø A TRUNCATE jog nem továbbadható Az ALTER jog a TRUNCATE-et tartalmazza, de az túl sok lenne v Megoldás: Execute As Tárolt eljárást készítünk a TRUNCATE-re Execute As egy olyan felhasználóval, akinek van ALTER joga Ø Grant execute jog a cél felhasználónak Ø Ø v Eredmény: Ø A TRUNCATE jogot „továbbadhatóvá” tettük

Új jogosultságok v A legtöbb objektumra vonatkoznak a következő jogosultságok CONTROL: tulajdonos-szerű jogok Ø ALTER: a tulajdonságok megváltoztatása Ø ALTER ANY ‘X’: ALTER jog minden X típusú objektumra Ø Take Ownership: a tulajdonjog átvételének joga Ø v Rendszer nézetek Ø sys. database_permissions, sys. server_permissions

Jogosultság alapú meta-adat biztonság v A meta-adatok alapértelmezésben védettek v A sysadmin szerep mindent láthat a v v v kiszolgálón Az adatbázis tulajdonos (dbo) mindent láthat az adatbázisban Az objektum tulajdonosa láthatja az adott objektum adatait A ”VIEW DEFINITON” engedély teszi lehetővé az objektum meta-adatainak olvasását

A rendelkezésre állás fokozása v Automatikus feladatátvétel Feladatátvevő fürtözés (Failover Clustering) Ø Adatbázis tükrözés Ø v Adatbázis karbantartás, helyreállítás Online index műveletek Ø Gyors helyreállítás (recovery) Ø Online, akár lap szintű visszatöltés Ø v Egyidejű adatelérés, blokkolás elkerülése Ø Pillanatfelvétel izoláció v Egyéb fejlesztések Ø Ø Adatbázis pillanatfelvételek Replikáció

Adatbázis tükrözés Magas rendelkezésre állás – alacsony költséggel Ügyfelek v Adatbázis feladatátvétel Tanú Napló rekordok Principális Tükörkép <3 mp Ø Automatikus/ manuális feladatátvétel Ø Automatikus kliens átirányítás Ø v Standard hardver eszközök

Az adatbázis tükrözés előnyei v Gyors, automatikus feladatátvétel v Adatbázis szinten teljes másolat Különálló gépek Ø Az adatok két példányban léteznek Ø A gépek szabványos hálózaton keresztül kommunikálnak Ø v Nem igényel speciális hardvert v Önellenőrző v Magas rendelkezésre állás – adatbázis szinten

Tükrözés - szerepkörök v Principális Ø Fogadja az ügyfél kapcsolatokat Ø Engedi az adatok módosítását v Tükörkép („azonnali” tartalék) Ø A principálisnál történő változásokat végrehajtja a tükör adatbázison Ø Az ügyfelek közvetlenül nem érik el a tükörképet Ø Szerepet cserélhet, principálissá válhat Ø A tükörkép pillanatfelvételei felhasználhatók jelentés készítésre

A tanú szerepe v Lehetővé teszi az automatikus feladatátvételt v Megakadályozza a „megosztott agy” problémát Ø „Miért nem látom a másik kiszolgálót? A hálózat szűnt meg, vagy a kiszolgáló nem működik? v Csak az a kiszolgáló lehet principális, amelyik legalább egy másik kiszolgálóval képes kommunikálni

A tanú jellemzői v Egy SQL Server 2005 példány v Egy tanú több tükrözést támogathat v Nagyon kevés erőforrást fogyaszt Ø Válaszol a „ping”-ekre Ø Közli, hogy a másik kiszolgáló él-e v Nem egyetlen meghibásodási pont Ø A tükröző partnerek egymással is meg tudnak állapodni Ø Amíg két szerver tud kommunikálni egymással, a tükrözés működőképes

A tükrözés folyamata - szinkron Nyugta Érvényesítés Nyugta Folyamatos előregörgetés Továbbítás a tükörhöz Napló írás nyugta Tranzakciónapló írás DB Log Tranzakciónapló írás Log DB

Adatbázis tükrözés - szinkron v A principális egyszerre írja a saját v v tranzakciónaplóját és küldi a hálózatra a módosításokat A tranzakció akkor érvényesítődik, ha a tükör is beírta a módosításokat a saját tranzakciónaplójába Feladatátvétel esetén nincs adatvesztés A tükörkép kész a feladatátvételre, ha megtörtént a tranzakciók előregörgetése – az esetleges visszagörgetésre nem kell várni Automatikus feladatátvétel lehetséges

Az adatbázis állapotai a tükrözési folyamat során v SYNCHRONIZED Ø A tükör is minden adatot tartalmaz v SYNCHRONIZING Ø A tükör le van maradva, de dolgozik v SUSPENDED Ø A tükrözés fel van függesztve v DISCONNECTED Ø A kiszolgáló nem tud kommunikálni a partnerrel v EXPOSED (veszélyeztetett) = Ø SYNCHRONIZING / SUSPENDED / DISCONNECTED

Szinkron és aszinkron tükrözés v A biztonsági szint beállítása szerint Ø SECURITY: FULL – csak akkor érvényesítünk a principálison, ha a módosítás naplózódott a tükörképen is – A tükrözés hatással van a principális áteresztőképességére – A feladatátvétel ideje szabályozható (REDO_QUEUE adatbázis opció) Ø SECURITY: OFF – a principális nem vár a tükörkép nyugtájára – A tükrözés hatása a principális áteresztőképességére minimális

Aszinkron tükrözés Nyugta 1 Érvényesítés 3 2 Folyamatos előregörgetés Továbbítás a tükörhöz 2 Napló írás megtörtént Tranzakciónapló írás DB Log Tranzakciónapló írás Log DB

Az tükrözés alkalmazásai v Magas rendelkezésre állás Biztonság: FULL Ø Automatikus feladatátvétel Ø Egy kiszolgáló elvesztése esetén az adatbázis változatlanul használható Ø v Magas védelem Ø Biztonság: FULL; nincs tanú manuális áttérés v Nagy teljesítmény Biztonság: OFF Ø A rendszergazda végzi a feladatátadást Ø Adatvesztés lehetséges Ø Egy fajta mentési mechanizmus Ø

v Adatbázis tükrözés demó

Gyors helyreállítás (recovery) v SQL Server 2000 Ø Az adatbázis a visszagörgetés után hozzáférhető Előregörgetés Visszagörgetés Használható Idő v SQL Server 2005 Ø Az adatbázis a visszagörgetés megkezdésekor hozzáférhető Előregörgetés Visszagörgetés Használható

Adatbázis pillanatfelvétel Mire jó? v Felhasználói hiba kivédése Ø Visszatérés a „múltba” Ø A hiba megtörténte előtt kell pillanatfelvételt készíteni v Erősen használt adatbázis adott állapotának elmentése Ø Például, adatbázis tükörkép esetén Ø Jelentéskészítés céljából

Adatbázis pillanatfelvétel v Az adatbázis állapota egy adott időpillanatban Ø Azonnal létrejön Ø Csak olvasható v Az eredeti adatbázis változását nem korlátozza v A pillanatfelvétel új (csak olvasható) adatbázisként jelenik meg v Hiba esetén visszanyúlhatunk a pillanatfelvételhez a régi adatokért

Pillanatfelvétel Technológia v Helykímélő v Csak a módosult adatlapokat tároljuk Ø Másolás íráskor (Copy-On-Write) Ø Kezdetben a kópia nem igényel helyet v A pillanatfelvétel létezése befolyásolja az eredeti adatbázis működési sebességét

Pillanatfelvétel Másolás íráskor Parancs Northwind_SS Create Northwind_SS Update Northwind Read Northwind_SS Eredmény: DD Tárolási igény 12. 5% 0% Value R D X B D H J L Y M

Pillanatfelvétel a tükörképen v Több pillanatfelvétel is készíthető Ø Minden pillanatfelvétel saját névvel rendelkezik Ø Az adatbázis állapotát eltérő időpontokban mutatják v A tükörképen létrehozott pillanatfelvételek befolyásolhatják a principális működését v Az egy időben létező pillanatfelvételek számát csak az erőforrások korlátozzák

Jelentéskészítés a tükörképen v Pillanatfelvételekkel Adatbázis tükrözés OLTP kliensek Principális Tanú Tükörkép Pillanatfelvételek Jelentéskészítő kliensek

Online index műveletek v Online index karbantartás Ø Create, Rebuild, Reorganize, Drop Ø Add / Drop Constraint v Parallel végrehajtás (MAXDOP opció) v Online/offline választható

Pillanatfelvétel tranzakció izoláció v Az adatok olvasásakor az utasítás, vagy a tranzakció megkezdésekor érvényes utolsó értékeket látjuk Nincs zárolás Ø Az olvasások nem blokkolják a módosításokat és a módosítások nem blokkolják az olvasásokat Ø v Javítja az adatok elérhetőségét, csökkenti a v v deadlock-ok kialakulását Az adatsorok verziózásán alapul (tempdb) Adatbázis szinten lehet engedélyezni

Pillanatfelvétel izoláció változatok v A Read Committed szint új változata Ø Zárolás nélküli Read Committed Ø Utasítás szintű pillanatfelvétel izoláció – Olvasáskor az utasítás kezdetekor érvényes utolsó értéket kapjuk v Új tranzakció izolációs szint Ø Snapshot – új izolációs szint Ø Tranzakció szintű – Olvasáskor a tranzakció kezdetekor érvényes utolsó értéket kapjuk

SQL Server 2005 Tranzakció izolációs szintek Lehetséges anomáliák Izolációs szintek Érvénytelen adat olvasás Nem megismételhető olvasás Fantomok UPDATE konfliktus Egyidejű elérés vezérlése igen nem nincs Read Committed - zárolással - pillanatfelvétel nem igen nem pesszimista optimista Repeatable Read nem igen nem pesszimista Snapshot nem nem igen optimista Serializable nem nem pesszimista Read Uncommitted

A pillanatfelvétel engedélyezése és az izolációs szint beállítása v Engedélyezés – adatbázis szinten alter database <adatbázis> set READ_COMMITTED_SNAPSHOT on Ø és/vagy: Ø alter database <adatbázis> set ALLOW_SNAPSHOT_ISOLATION on Ø v Izolációs szint beállítás – kapcsolat szinten set transaction isolation level read committed Ø set transaction isolation level snapshot Ø v A tranzakció működése az engedélyezett és a beállított értéktől is függ

Read Committed Snapshot v Adatbázis: READ_COMMITTED_SNAPSHOT on v Kapcsolat: set transaction isolation level read committed v A módosítások nem blokkolják az olvasásokat és fordítva v A módosítások blokkolják egymást! Ø A módosítások zárakkal történnek v Nem használható a master, az msdb és a tempdb adatbázisokkal

Snapshot v Adatbázis: ALLOW_SNAPSHOT_ISOLATION on v Kapcsolat: set transaction isolation level snapshot v A módosítások nem blokkolják az olvasásokat és fordítva v A módosítások konfliktusba kerülhetnek Elvesztett módosítás veszélye Ø Automatikus és kötelező konfliktus feloldás véd az „elvesztett módosítás” probléma ellen Ø v Alapértelmezett a master–en és az msdb-n

v Pillanatfelvétel izoláció demó

Replikáció újdonságok v Tranzakcionális replikáció Oracle-ből az SQL Server-be v HTTPS Merge szinkronizálás v AZ SQL Server 2005 új adattípusainak támogatása v Pont-pont replikáció v A replikált táblák szerkezete módosítható v Jobb biztonság, teljesítmény, adminisztráció, diagnosztika, …

Partícionált táblák és indexek v Értéktartomány (range) alapú partícionálás v A táblákat, indexeket több tárolási objektumra bontjuk egy adatoszlop értéke szerint v A relációs motor és az SQL programozó számára a tábla egyetlen egység marad v A tároló motor több objektumként kezeli a partíciókat Ø Max. 1000 partíció / tábla

Egy nagy monolitikus tábla Order History Order ID Példa: Az ORDER HISTORY tábla; egy nem fürtözött index a CUSTOMER ID-n Customer ID Order Date Nem partícionált: Amount … Filegroup DATA Order History Table Filegroup IDX Customer ID Index

Partícionálás és tárolás Order ID History Customer Order IDID Order Date Customer ID ORDER DATE szerint: Filegroup DATA_2002 Filegroup DATA_2003 Filegroup DATA_2004 Amount Order Date … Amount … Order History Table Filegroup IDX_2002 Filegroup IDX_2003 Filegroup IDX_2004 Customer ID Index Order Date < ‘ 2003 -01 -01’ Customer ID Index Order Date >= ‘ 2003 -01 -01’ and Order Date < ‘ 2004 -01 -01’ Customer ID Index Order Date >= ‘ 2004 -01 -01’

A partícionált táblák előnyei v Kezelhetőség Gyors adatbetöltés és törlés Ø Darabonkénti mentés/visszatöltés a „régi” adatokra Ø Partíciónkénti index kezelés Ø Nincs index töredezettség a régi adatokra Ø Eltérő tárolási mód lehetséges a régi adatokra Ø v Jobb lekérdezési sebesség a nagy táblák esetén Hatékony join, ha az illeszkedő táblák azonosan partícionáltak Ø Kisebb index fát és adatpartíciót kell bejárni Ø

A partícionálás elemei Objektumok: v Partíció függvény v Partíció séma Műveletek: v Megosztás - Split Partition v Összefésülés - Merge Partition v Csere - Switch Partition

A partíció függvény v A partícionáló oszlop adatait egész számokra képezi le v A határpontok által definiált v N pont N+1 partíció Boundary 1 Partition # 1 2 2 3 3 4 4 5

Partíció függvény példa CREATE PARTITION FUNCTION annual_range (DATETIME) as RANGE RIGHT /* a határpontokra eső adatok helye */ for values ( -- Partition 1 -- 2001 és korábbi '2002 -01 -01', -- Partition 2 -- 2002 '2003 -01 -01', -- Partition 3 -- 2003 '2004 -01 -01', -- Partition 4 -- 2004 '2005 -01 -01' -- Partition 5 -- 2005 és későbbi ) Partíció sorszám lekérdezése: select $partition. annual_range(‘ 2003 -03 -15’)

Partíció függvény példa (2) Boundary 1 2 2002 -01 -01 Partition # 1 dátum < 2002 -01 -01 3 4 2003 -01 -01 2004 -01 -01 2005 -01 -01 2 2002 -01 -01 <=dátum < 2003 -01 -01 3 4 5 2003 -01 -01 2004 -01 -01 2005 -01 -01 <=dátum < <=dátum 2004 -01 -01 2005 -01 -01

Partíció séma v Egy-egy fájlcsoportot rendel a partíciókhoz Ø Nem kötelező különböző fájlcsoportokat használni Ø Különböző fájlcsoportok használata hasznos lehet mentés/helyreállítás esetén

Partíció séma példák CREATE PARTITION SCHEME annual_scheme_1 as PARTITION annual_range to (annual_min, -- filegroup pre-2002 annual_2002, -- filegroup 2002 annual_2003, -- filegroup 2003 annual_2004, -- filegroup 2004 annual_2005) -- filegroup 2005 -től Create PARTITION SCHEME annual_scheme_2 as PARTITION annual_range ALL to ([PRIMARY])

Partícionált táblák és indexek v A partícionáló kulcs csak egyetlen oszlop lehet v A partícionált táblák és indexek partíció sémákon hozhatók létre (nem fájlcsoportokon) v Több tábla és index is használhatja ugyanazt a partíció függvényt és sémát Partíció függvény Partíció séma Tábla, index

Tábla és index létrehozása CREATE TABLE Order_History ( Order_ID bigint, Order_Date datetime, Customer_ID bigint … ) ON Annual_Scheme_1(Order_Date) CREATE INDEX Order_Cust_Idx ON Order_History(Order_ID) ON Annual_Scheme_1(Order_Date)

Index partícionálás v Ha egy index hasonló partíció függvényt használ és ugyanazt a partícionáló kulcsot használja, mint az alaptábla, akkor az index „illeszkedő” (aligned) Ø Ø 1 -1 megfelelés az adat és index partíciók között Hasonló: – Azonos adattípus – Azonos számú partíció – Azonos határpontok v Index létrehozásakor alapértelmezett az illeszkedő index v Lehetővé teszi a gyors partícióváltást (switch partition)

Partícionált táblák adatainak lekérdezése v Ha a partícionáló kulcs szerepel a WHERE záradékban, a végrehajtás kevesebb partíciót érint Select * from Order. History o where … o. date_key between ‘ 2002 -01 -01’ and ‘ 2002 -06 -30’ v Vigyázat: join-on keresztül nem tudjuk szűkíteni az érintett partícók számát Select * from Sales s INNER JOIN Region d. region_id = s. region_id where … d. Name = ‘Asia’

Műveletek partíciókkal v Megosztás - Split Partition v Összefésülés - Merge Partition v Csere - Switch Partition

Split v ALTER PARTITION FUNCTION … SPLIT RANGE … v Új határpont felvétele a partíció függvényben v Minden objektumra hat, amelyik a függvényt használja v Az új határpont egy partíciót kettéoszt v Az új partícióba eső adatokat a tároló motor átmozgatja a régi helyükről v Azonnal megtörténik, ha a partíció üres Ø Partícionált tábla végein célszerű mindig egy üres partíciót fenntartani

Merge v ALTER PARTITION FUNCTION … MERGE RANGE v Eltávolít egy határpontot a partíció függvényből v A határpont két oldalán levő partíciók adatai összemásolódnak Ø Sok írással, olvasással járó művelet Ø Gyors (azonnali) ha a két partíció üres

Switch v Felcseréli a forrás partíciót, vagy táblát egy másik tábla egy üres partíciójával, vagy egy üres táblával (cél) v Tisztán meta-adat művelet v Megkötések: A cél üres kell, hogy legyen A forrás és a cél azonos fájlcsoportban legyen A forráson legyen meg minden index, amit a cél megkövetel, illeszkedve, és a megfelelő fájlcsoporton Ø Ha a cél egy partíció, a forrás táblán legyen olyan check constraints, ill. a forrás partíció olyan legyen, ami beleillik a cél partíció tartományba Ø Ø Ø v A csere az összes kapcsolódó indexet is automatikusan magával viszi

Switch tippek v Ha táblát cserélünk partícióra, a tábla indexeinek tartalmazni kell a partícionáló kulcsot Ø CREATE INDEX, INCLUDE opció v A $partition. <partíció függvény> konstrukciót használjuk az abszolút partíció sorszámok helyett Ø A partíció sorszámok változhatnak

Csúszó időablak kezelés Új adatok betöltése 2002 -01 -01 2003 -01 -01 2004 -01 -01 2005 -01 -01 [üres] Partíció # 1 2001 & korábbi 2006 -01 -01 [üres] 2 3 4 5 [üres] 6 2002 adatok 2003 adatok 2004 adatok 2005 adatok & 2006 & későbbi v Előkészítő tábla létrehozása v Split az utolsó partíción v Bulk load és index az előkészítő táblán v Switch 2005 Staging Table

Csúszó időablak kezelés A legrégebbi adatok törlése 2002 -01 -01 [üres] Partíció # 1 2001 & korábbi 2003 -01 -01 2004 -01 -01 2005 -01 -01 2006 -01 -01 [üres] 12 [üres] 23 34 45 65 2002 & adatok 2003 adatok 2004 adatok 2005 adatok 2006 & korábbi későbbi v Üres tábla létrehozása az 2002 Unload Table eltávolítandó adatok számára v Switch v Merge

Mentés, visszatöltés v A partícionálással kihasználhatjuk az SQL Server 2005 új szolgáltatásait Ø A Read-Only fájlcsoportok a tranzakciónapló visszatöltése nélkül is helyreállíthatók v Nem változó, historikus adatok esetén meggyorsítja a mentést

Online visszatöltés v SQL Server 2000 Ø Visszatöltés közben az adatbázis offline, nem használható v SQL Server 2005 Ø Az adatbázis online marad – Csak a visszatöltött rész nem elérhető Ø Visszatöltés részekben – File / Filegroup visszatöltés – A megsérült adatlapok követése és lap szintű visszatöltés

Programozhatóság v Elvárások Hatékony fejlesztés Ø Üzleti logika a kiszolgálón Ø Kevéssé strukturált adatok kezelése Ø Együttműködés más rendszerekkel Ø v SQL Server 2005 Visual Studio integráció Ø CLR függvények, eljárások, triggerek Ø XML adattípus kezelés a kiszolgálóban Ø Natív http, SOAP Ø Transact-SQL továbbfejlesztések Ø

. NET és Visual Studio integráció v Programozási nyelvek választéka T-SQL a halmaz-orientált adatkezelésre Ø. NET nyelvek a procedurális algoritmusokra Ø v Az üzleti logika helye választható Adatbázis, középső réteg, ügyfél oldal Ø Ugyanaz a modul, ugyanaz az adatelérési felület (ADO. Net) Ø v Integrált hibakeresés Ø Különböző nyelveken keresztül – TSQL és. NET v Rugalmas, bővíthető kód Függvények, tárolt eljárások, triggerek – SQL-ben, vagy. Net nyelven Ø Felhasználói adattípusok, aggregátumok Ø XML adattípus Ø

Üzleti logika megvalósítása a kiszolgálón - CLR v Függvények, tárolt eljárások, triggerek - tetszőleges CLR nyelven Ø Számításigényes algoritmusokat procedurális nyelvekben könnyebb megírni és a kapott kód gyorsabban fut, mint ugyanaz Transact-SQL-ben v A Transact-SQL (T-SQL) beépített függvényeinek kiterjesztése v Célterület: Számításigényes, procedurális logika az adatbázis rétegben Ø Sok adatelérést végző logika a középső rétegben SQL Server Ø Nem (csak) SQL Server API-kat használó modulok Ø Külső erőforrások elérése Ø

A fejlesztés menete VB, C#, C++ VS. NET Project Build CLR runtime SQL Server (in-process) SQL Data Definition: create assembly … create function … create procedure … create trigger … create type … SQL Server SQL lekérdezés: select dbo. függvény(paraméter) Assembly: “My. Lib. dll”

Függvények v Skaláris v Tábla-értékű

Skaláris CLR függvények v Egy. NET Framework assembly egy osztályának metódusai v A visszatérési érték Ø Csak skaláris (Unicode) lehet Ø Nem lehet: rowversion, text, ntext, image Ø A visszaadott. Net adattípusnak illeszkednie kell egy SQL Server adattípushoz v Testre szabott attribútumok használhatók Ø [Sql. Function(attribútumok)]

v Skaláris CLR függvény készítése demó

A skaláris függvények attribútumai v Sql. Function[(függvény-attribútum [, . . . ])] Ø Is. Deterministic= {true | false} Ø Data. Access = { Data. Access. Kind. None | Data. Access. Kind. Read } – Felhasználói táblák elérése Ø System. Data. Access = { System. Data. Access. Kind. None | System. Data. Access. Kind. Readread } – Rendszer katalógusok, nézetek elérése Ø Is. Precise = { true | false }

A skaláris függvények attribútumai - példa [Sql. Function(Is. Deterministic = true, Is. Precise = true)] Sql. Function public static Sql. String Extract. Area. Code(string s) { Regex r 1 = new Regex("\((? <ac>[1 -9][0 -9]+)\)"); Match m = r 1. Match(s); if (m. Success) return m. Value. Substring(1, m. Length-2); else return Sql. String. Null; }

Számított oszlop CLR függvénnyel v A CLR skaláris függvényeket felhasználhatjuk számított oszlopok létrehozására Ø A PERSISTED kulcsszóval előírhatjuk a számított oszlop tárolását v CLR függvénnyel készített számított oszlopra akkor készíthető index, ha Ø determinisztikus, nem végez adathozzáférést Ø PERSISTED (tárolt)

Index számított oszlopon - példa create table Contacts ( Fname nvarchar(30), Lname nvarchar(30), Phone. No nvarchar(30), Area. Code AS dbo. Extract. Area. Code(Phone. No) PERSISTED) go create index Contact. Area. Code ON Contacts(Area. Code)

Tábla értékű függvények v A függvény értéke táblaként használható v A tábla értékű függvény adatainak elérése Ø T-SQL függvény esetén: belső munkatáblán keresztül (spooled) Ø CLR függvény esetén: adatfolyam (streamed) – Nem kell bevárni az egész adathalmaz elkészültét a feldolgozással v A függvénynek implementálni kell az ISql. Reader interfészt

v Tábla értékű CLR függvény készítése demó

CLR tárolt eljárások v A függvényekkel ellentétben: Ø Végezhetnek adat definíciós (DDL) és adat módosító (DML) műveleteket Ø Küldhetnek eredményeket a kliens oldalra v Felhasználási terület Ø A CLR előnyös, ha: – Sok számítást végzünk az adatokon – A korábbi verziókban használt kiterjesztett tárolt eljárások (Extended Stored Procedures) helyett Ø Halmaz műveletekre változatlanul az SQL a legjobb

Adatelérés CLR tárolt eljárásból v In-process adatelérésre optimalizált ADO. NET szolgáltató v System. Data. Sql. Server névtér v A programozási modell alapvetően megfelel az Sql. Client-nek Ø Sql. Connection, Sql. Command, Sql. Reader, Sql. Parameter v Bővítmények Ø Sql. Context – Sql. Pipe – eredmények küldése a kliens felé – Sql. Trigger. Contex – a triggerek számára

CLR adatmódosítás példa using System. Data. Sql. Server; … Sql. Connection cn = Sql. Context. Get. Connection(); Sql. Command cmd = cn. Create. Command(); cmd. Command. Text = “UPDATE Emp SET photo = @img” + “WHERE Emp. Id=10”; Sql. Parameter param 1 = cmd. Parameters. Add(“@img”, Sql. Db. Type. Var. Binary, Sql. Metadata. Max); param 1. Value = img; cmd. Execute. Non. Query();

Eredményhalmaz küldése v Sql. Pipe - Send metódus (overloaded): Ø void Send(string message) – T-SQL: print Ø void Send(Sql. Data. Reader reader) – Több soros eredményhalmaz Ø void Send(Sql. Data. Record record) – Egy soros eredményhalmaz v Adatbázis lekérdezés eredménye a kliensre: Ø Sql. Pipe. Execute(Sql. Command) Sql. Command cmd = Sql. Context. Get. Command(); cmd. Command. Text = "select @@version"; Sql. Context. Get. Pipe(). Execute(cmd);

Eredményhalmaz küldése (2) v Előzőleg végrehajtott lekérdezés eredményének elküldése public static void Send. Reader. To. Client() { Sql. Command cmd = Sql. Context. Get. Command(); cmd. Command. Text = "select @@version"; ISql. Reader rdr = cmd. Execute. Reader(); using (rdr) { Sql. Context. Get. Pipe(). Send(rdr); } }

Dinamikusan összeállított eredményhalmaz //oszlop meta-adatok létrehozása Sql. Meta. Data[] column. Schema = new Sql. Meta. Data[] { new Sql. Meta. Data("stringcol", Sql. Db. Type. NVar. Char, 128) }; //sor meta-adatok létrehozása Sql. Meta. Data table. Schema = new Sql. Meta. Data("row", Sql. Db. Type. Row, column. Schema); //rekord létrehozás ISql. Updatable. Record new. Record = Sql. Context. Get. Connection(). Create. Record(table. Schema); //rekord oszlopainak feltöltése new. Record. Set. Sql. String(0, "Hello World!"); //rekord elküldés Sql. Context. Get. Pipe(). Send. Results. Start(new. Record, true /*sending row*/); //a küldés befejezése Sql. Context. Get. Pipe(). Send. Results. End();

Triggerek v DML és DDL triggerek is készíthetők CLR- ben v A triggerek a Trigger. Context objektumon keresztül érik el a módosításra vonatkozó információkat

Trigger ADO. NET bővítmények v Sql. Context. Get. Trigger. Context() Ø bool [ ] Columns. Updated: – T-SQL: Columns. Updated() Ø Trigger. Action: a triggert elindító művelet tc =Sql. Context. Get. Trigger. Context(); if (tc. Trigger. Action == Trigger. Action. Update) Log. Direct. Update(…);

Felhasználási javaslatok v A CLR nem a Transact-SQL helyett van v A CLR modulok lehetővé teszik Komplex, procedurális üzleti logika megvalósítását Ø Külső adatok elérését Ø Az üzleti logika SQL Serverben történő megvalósítását Ø v Nem „kötelező” minden üzleti logikát kiszolgálóban megvalósítani Ø Skálázhatósági megfontolások

XML kezelés v XML támogatás az SQL Server 2000 -ben v XML támogatás az SQL Server 2005 -ben Ø Beépül az adatbázis-motorba – XML adattípus, XML manipuláció, XML indexek, XML sémák Ø XML adat-manipuláció a kiszolgálón

SQL Server 2000 – kiszolgáló XML be és XML ki 1 sp_xml_preparedocument XML data 2 XML DOM Parse … 5 select … for xml … … 3 Table … Open. XML name Table 4 insert into … … ID Phone

SQL Server 2000 – középső réteg XML nézetek v Annotált leképező sémák (XDR, XSD) v SQLXML 3. 0 SP 2 1 Bul k load 2 XML View FN LN Person Lekérdezés, updategram 3 Address Phone First Name Last Name Address Phone

SQL Server 2005 XML a relációs adatbázisban v XML adattípus Oszlop, változó, … Ø Típus nélküli, és/vagy séma gyűjteménnyel ellenőrzött Ø Ellenőrzések (constraint-ek) XML adatokon Ø Bináris, vagy szöveges tárolás (max. 2 GB) Ø v XML adatok lekérdezése és módosítása Ø XQuery (fejlődő W 3 C szabvány), XPath 2. 0 v Indexek XML oszlopokon Elsődleges XML index: B+ fa minden címkén, értéken és ösvényen Ø Másodlagos XML indexek (PATH, PROPERTY, VALUE) Ø

SQL Server 2005 A relációs és XML adatok integrációja v Egységes relációs és XML tároló A meglevő SQL Server tároló motor és lekérdezés optimalizáló felhasználásával Ø A relációs és az XML záradékok ugyanabba az optimalizált lekérdezési tervbe kerülnek Ø v Az integrált XML adattípus előnyei Kevéssé strukturált adatok modellezése Ø A táblákra bontás, ill. újra összekapcsolás költsége eltűnik Ø Az XML feldolgozást az SQL Server optimalizálja Ø A meglevő relációs táblákba beilleszthető XML oszlopok nem igénylik a működő alkalmazások módosítását Ø

Kiszolgáló oldali XML támogatás v Tárolás Ø XML adattípus, varchar(max), varbinary(max) v Táblákra bontás Ø open. XML() és Ø XML metódusok – query() – XML dokumentum egy részének előállítása – value() – skaláris érték kiolvasása – exist() – létezés vizsgálata – nodes() – csomópontok keresése az XML dokumentumban – modify() – XML dokumentum egy részének módosítása

Kiszolgáló oldali XML támogatás (2) v Indexelés Ø XML indexek Ø Full-text index az elemek tartalmára v Lekérdezés Ø for xml … [type] Ø XQuery, XPath v XSD támogatás Ø CREATE XML SCHEMA COLLECTION v CLR támogatás Ø System. Xml névtér eszközei

Eszközök az XML dokumentumok kezeléséhez v XML szerkesztő SQL Server Management Studio-ban v Integration Services Ø XML dokumentum táblákra bontása Ø XML dokumentum XML oszlopokban történő tárolása

Adatmodellezési javaslatok v Strukturált adatok, ismert séma relációs modell Ø Ø Bevált, ismert technológia Esetleg [n]varchar(max) oszlopok a tárolásra, ha nem akarjuk manipulálni a dokumentumot a kiszolgálón v XML adattípus Egyszerű adatkezelés kevéssé strukturált adatok esetén Ø Jobb teljesítmény, mintha felbontanánk és újra összeállítanánk a komplex XML dokumentumokat Ø v Egyszerű migráció text típusú oszlopról: Ø ALTER TABLE <tábla> ALTER COLUMN <oszlop> XML [XML schema collection]

XML kezelés összefoglalás v Gyengén strukturált adatok kezelése Ø Natív XML adattípus Ø Fejlett indexelés & lekérdezés optimalizálás Ø Modern programozási nyelv a kiszolgálón v Az XML támogatás beépül a relációs tárolóba és a lekérdezés optimalizálóba v Az SQL Server eszközei használhatók Ø hibakeresés, lekérdezési tervek, nyomkövetés – a meglevő eszközökkel

Transact-SQL újdonságok v Lekérdezések Ø Rekurzív lekérdezések Ø Rang függvények Ø Új relációs operátorok Ø Adatmódosítás – output záradékkel Ø Teljes-szöveges keresés v Új adattípusok v T-SQL procedurális bővítmények

Common Table Expression (CTE) és rekurzív lekérdezések v Rekurzív hierarchia bejárása egyetlen lekérdezéssel Felhasználás v Hierarchia egy táblában (főnökbeosztott, alkatrészek beépülése) Ø Keressük egy főnök összes beosztottját Ø Keressük egy termék beépülő alkatrészeit

Common Table Expression v SQL-99 v Szintaxis: WITH <CTE Név> ( <oszlop lista> ) AS ( <CTE definíció> ) SELECT … FROM … <CTE Név> v Lehet rekurzív és nem rekurzív v Nem rekurzív CTE: Ø Olvashatóbb lekérdezések

Rekurzív CTE v Önmagára hivatkozik v Rekurzív CTE definíció: <nem-rekurzív SELECT> inicializálás UNION ALL <SELECT a CTE felhasználásával> gyűjtés v A rekurzió megáll, ha a második SELECT nem ad eredményt

Rekurzív CTE példa WITH Emp. CTE(ID, Név, Mgr, Szint) AS ( SELECT Employee. ID, First. Name+' '+Last. Name, Reports. To, 1 FROM Employees WHERE Reports. To is null UNION ALL SELECT E. Employee. ID, E. First. Name+' '+ E. Last. Name, E. Reports. To, M. Szint+1 FROM Employees AS E JOIN Emp. CTE AS M ON E. Reports. To = M. ID ) SELECT * FROM Emp. CTE Northwind adatbázis

Rang és sorszám függvények v Az SQL-99 OLAP bővítmények szerint Ø RANK() Ø DENSE_RANK() Ø NTILE(<expression>) Ø ROW_NUMBER() v Szintaxis <rang_függvény> OVER( [PARTITION BY <oszlop>] ORDER BY <oszlop>)

Rang és sorszám használata v Adatelemzés (RANK, DENSE_RANK, NTILE) Ø Sorrendeket generálhatunk különböző kritériumok szerint, egyetlen lekérdezésben Ø A megjelenítés sorrendje eltérhet a rangoktól v Lapozás a ROW_NUMBER segítségével Ø Nagyobb eredményhalmaz megjelenítése

Rang példa A termékek rangjának lekérdezése eladott darabszám és egységár szerint select Product. Name, sum(Quantity), rank() over (order by sum( Quantity) desc), rank() over (order by max(od. Unit. Price)) from dbo. Products p join dbo. [Order Details] od on p. Product. ID=od. Product. ID join dbo. Orders o on od. Order. ID=o. Order. ID group by Product. Name order by Product. Name Northwind adatbázis

Sorszámozás példa select N=row_number() over (order by Customer. ID) , * from dbo. Orders Northwind adatbázis

TOP v SQL 2000: SELECT TOP <konstans> v SQL 2005: SELECT TOP (<kifejezés>) Ø INSERT, UPDATE, DELETE esetén is használható Ø A SET ROWCOUNT is használható – a SELECT utasítás esetén felülbírálja a TOP-ot – INSERT, UPDATE, DELETE esetén a TOP felülbírálja a SET ROWCOUNT-ot – A következő verzióban ( ) a SET ROWCOUNT nem fog hatni INSERT, UPDATE, DELETE esetén

Új relációs operátorok PIVOT, UNPIVOT, APPLY v PIVOT Ø Sorokból oszlopokat készít Ø Hasonló az Access TRANSFORM-hoz v UNPIVOT Ø A PIVOT ellentéte v APPLY Ø A külső tábla minden sorára kiértékel egy tábla értékű függvényt és a két táblát összekapcsolja

PIVOT példa, összegzés Make Honda Acura Honda Year 1990 1991 Sales 2000 500 3000 Acura 1991 600 SELECT * FROM T PIVOT(SUM(Sales) FOR Year IN ([1990], [1991])) t Make 1990 Honda 2000 1991 3000 Acura 500 600

PIVOT példa, név-érték párosítás Obj. ID 1 1 2 Prop. Name Cr. Date Name Prop. Val x. doc 12/3/2001 Sales. xls 2 Author Higgins SELECT * FROM table PIVOT(MIN(Prop. Val) FOR Prop. Name IN ([Name], [Author])) t Obj. ID 1 2 Name x. doc Sales. xls Author NULL Higgins

DML utasítás OUTPUT záradékkal v Az adatmódosító utasítás részeként sorokat tudunk visszaadni egy tábla változóba v Az Inserted és Deleted virtuális táblák tartalmazzák a régi és új értékeket Példa: UPDATE Orders SET status= 'processed' OUTPUT DELETED. *, INSERTED. * into @tab WHERE status='unprocessed'

Full-Text indexelés v Skálázhatóság, teljesítmény Ø Az index felépítése egy nagyságrenddel gyorsabb Ø A lekérdezések 30 -50%-kal gyorsabbak

Full-Text - újdonságok v Thesaurus támogatás v Több oszlopos lekérdezések Ø CONTAINS((col 1, col 2), ‘Yukon’) v Jobb nyelv támogatás v Konfigurálható ékezetfüggőség v Elosztott lekérdezések: Ø Fulltext lekérdezés kapcsolt kiszolgálóra v Indexelt nézetek támogatása

Full-Text - adminisztráció v Az adatbázis mentés a full-text katalógusokat is tartalmazza v Adatbázis detach/attach a full-text katalógusokat is mozgatja v Példány szintű erőforrás (nincsenek megosztott komponensek) v Az MS Search az SQL Server biztonsági kontextusában fut

Varchar(max) v A varchar, nvarchar, varbinary kiterjesztése 2 GB méretig create table T (Id int, Picvarbinary(max)) v A text/ntext/image alternatívája v Minden string függvény működik a varchar(max)-on v Az UPDATE támogatja a darabolást UPDATE T SET Picture. Write(@newchunk, @offset, @remove)

DDL triggerek v Adatszerkezet módosításra induló triggerek v Minden DDL eseményre Ø CREATE_TABLE, ALTER_PROCEDURE, DROP_LOGIN, stb. v Adatbázis és kiszolgáló szinten v Az esemény adatait a trigger az eventdata() függvényen keresztül éri el

A DDL triggerek felhasználása v Fejlesztési szabályok érvényesítése Ø CREATE/ALTER visszagörgetése v Véletlen objektum eldobás megakadályozása v Objektum checkin/checkout v Forrás verziózás v Az adatbázis menedzsment naplózása

Kivételkezelés v Az “if @@error” kód helyett v Szintaxis: BEGIN TRY <utasítások> END TRY BEGIN CATCH <kivételkezelés> END CATCH

Kivételkezelés - tranzakció v Tranzakció abortáló kivételek esetén a tranzakció „haldokló” állapotban marad egy explicit „rollback” kiadásáig Ø Haldokló tranzakcióban naplózott műveletek nem végezhetők Ø A kivételkezelés többnyire rollback-kel kezdődik v Új függvények a hibakód, hibaüzenet, súlyosság elérésére a CATCH blokkban v Újra lehet dobni az eredeti kivételt

Tranzakció abort példa CREATE PROCEDURE add_to_T 1 @a int, @b nvarchar(1000) AS declare @error_msg nvarchar(400), @error_num int, @error_severity int BEGIN TRY BEGIN TRAN --idegen kulcs megsértés hiba INSERT T 1 VALUES (@a, @b) COMMIT TRAN END TRY BEGIN CATCH ROLLBACK INSERT T 1_Log VALUES (@a, @b, GETDATE()) SELECT @error_msg=error_message(), @error_num=error_number(), @error_severity=error_severity() RAISERROR (@error_msg, @error_num, @error_severity) END CATCH

Utasítás szintű újrafordítás (recompile) v Nem kell az egész tárolt eljárást újrafordítani (mint az SQL 2000 -ben) Ø Az utasítás szintű tervek is a gyorsírótárba kerülnek a modul szintű tervekkel együtt v Hasznos, ha: Ø Ø Ø Változnak a tábla statisztikák Nagy méretű tárolt eljárás újrafordítása helyett Dinamikus SQL helyett

Összefoglalás v A Transact-SQL tovább él és virul v A Transact-SQL változatlanul a legjobb nyelv halmaz-orientált műveletekre v A. Net CLR kiváló procedurális lehetőségekkel bővíti az SQL Server fejlesztők eszköztárát

Címek, hivatkozások v SQL Server 2005 Ø http: //www. microsoft. com/hun/sql/default. mspx Ø http: //www. microsoft. com/sql/2005/ v SQL Server 2005 Webcasts Ø http: //www. microsoft. com/events/series/technet sqlserver 2005. mspx Ø http: //www. microsoft. com/events/series/msdnsq lserver 2005. mspx v MSDN Ø http: //msdn. microsoft. com/sql/