Az SQL Server 2005 relációs motorjának újdonságai

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

2. TechNet 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

3. Napirend • Az SQL Server adatplatform • 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)

4. SQL ServerÁtfogó,integráltadatplatform Reporting Services Analysis Services (OLAP, DM) Notification Services Integration Services Replication Services Relációsadatbázis

5. Az SQL Server generációi 1. A kezdetek 2.A modern SQL Server 3.A közeli jövő SQL Server 6.0/6.5 SQL Server 7.0 SQL Server 2000 SQL Server 2005 • 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) • 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 • Rendelkezésre állásfókusz • Biztonság • Hatékony fejlesztés • Natív XML • Nagyvállalati ETL ésadatbányászat • Megbízhatóság,biztonság • Integrált üzleti intelligencia • A legalacsonyabb TCO • Automatikus hangolás Állandó célok

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

7. Elvárások Biztonság Rendelkezésre állás Skálázhatóság Menedzselhetőség 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 Nagyvállalatiadatkezelés

8. SQL Server 2005 biztonsági fejlesztések • 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 • A felület minimalizálása • A legtöbb szolgáltatás telepítése opcionális – explicit választást igényel • Telepítés, javítócsomagok • Microsoft Update Services integráció • Common Criteria • Tervezett: EAL4+ (Evaluation Assurance Level 4, vagy erősebb) • Biztonságieszközök • MBSA (Microsoft Baseline Security Analyzer) • SQL Server Best Practices Analyzer

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

10. demó • Surface Area Configuration

11. Jelszó házirend • 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 • Megvalósítás • Új jelszó házirend ellenőrző API aWindows 2003 Server-től kezdve • Azonosításkor, jelszó megadáskor hívják • 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

12. A felhasználók és a sémák szétválasztása • 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 • A default séma • A felhasználók és a szerepkörök tulajdonsága • A névfeloldáskor használja akereső algoritmus • sys.database_principals • Új DDL a felhasználókra és a sémákra • CREATE/ALTER/DROP - USER/ROLE/SCHEMA • A felhasználó megszüntetése nem igényli a séma átnevezését

13. Default Schema • Névfeloldáshoz, ha nincs megadva a sémanév • Az SQL2000-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 • SQL2005-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

14. Approle1 User1 Role1 Schema1 Schema2 SP1 Fn1 Tab1 Sémák és felhasználók • Az adatbázis több sémát 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 Hasdefaultschema Owns Owns Owns Schema3

15. User2.Proc1 User1.T1 User 3 User1.T1 User2.Proc1 Végrehajtási környezet SQL Server 2000 Execute jogellenőrzés User3-ra Select jogellenőrzés User3-ra User 3 User1.T1 User1.Proc1 Execute jogellenőrzés User3-ra Nincs jogosultság ellenőrzés User3-ra SQL Server ‘Execute AS ‘X’ ’ Execute jogellenőrzés User3-ra Select jogellenőrzés‘X’-re, Nem user3-ra

16. Modul végrehajtási környezet • Module • Tárolt eljárás, függvény, trigger • A tulajdonosi lánc helyett/mellett használható • A tulajdonosi lánc szabályok változatlanul érvényesek • 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 • A modulok végrehajtási környezetét tartalmazó rendszer nézetek: • sys.sql_modules és a sys. assembly_modules

17. Modul végrehajtási környezet (2) • Execute AS CALLER • A modul utasításai a modultközvetlenülhívó nevében (és jogaival) hajtódnak végre • Alapértelmezett viselkedés, hasonló az SQL Server 2000-hez • 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 • Execute AS SELF • Avégrehajtásazáradékot megadó felhasználó nevében történik • Execute AS OWNER • Az utasítások a modul mindenkori tulajdonosa nevében futnak • Impersonate jog kell az aktuális tulajdonosra (megadáskor) • Kiszolgáló hatókörű DDL triggerek esetén: • Execute AS ‘login’

18. Példa azExecute Ashasználatára • Probléma: • A TRUNCATE jog nem továbbadható • Az ALTER jog a TRUNCATE-et tartalmazza, de az túl sok lenne • 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 • Eredmény: • A TRUNCATE jogot „továbbadhatóvá” tettük

19. Új jogosultságok • 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 • Rendszer nézetek • sys.database_permissions, sys.server_permissions

20. Jogosultság alapú meta-adat biztonság • A meta-adatok alapértelmezésben védettek • A sysadmin szerep mindent láthat a 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

21. A rendelkezésre állás fokozása • Automatikus feladatátvétel • Feladatátvevő fürtözés (Failover Clustering) • Adatbázis tükrözés • 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 • Egyidejű adatelérés, blokkolás elkerülése • Pillanatfelvétel izoláció • Egyéb fejlesztések • Adatbázis pillanatfelvételek • Replikáció

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

23. Az adatbázis tükrözés előnyei • Gyors, automatikus feladatátvétel • 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 • Nem igényel speciális hardvert • Önellenőrző • Magas rendelkezésre állás – adatbázis szinten

24. Tükrözés - szerepkörök • Principális • Fogadja az ügyfél kapcsolatokat • Engedi az adatok módosítását • 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

25. A tanú szerepe • Lehetővé teszi az automatikus feladatátvételt • 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? • Csak az a kiszolgáló lehet principális, amelyik legalább egy másik kiszolgálóval képes kommunikálni

26. A tanú jellemzői • Egy SQL Server 2005 példány • Egy tanú több tükrözést támogathat • Nagyon kevés erőforrást fogyaszt • Válaszol a„ping”-ekre • Közli, hogy a másik kiszolgáló él-e • Nemegyetlen 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

27. 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 Tranzakció-naplóírás DB Log Log DB

28. Adatbázis tükrözés - szinkron • A principális egyszerre írja a saját 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

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

30. Szinkron és aszinkron tükrözés • 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

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

32. Az tükrözés alkalmazásai • 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ó • Magas védelem • Biztonság: FULL; nincs tanú  manuális áttérés • Nagy teljesítmény • Biztonság: OFF • A rendszergazda végzi a feladatátadást • Adatvesztés lehetséges • Egy fajta mentési mechanizmus

33. demó • Adatbázis tükrözés

34. Gyorshelyreállítás (recovery) • SQL Server 2000 • Az adatbázisa visszagörgetésután hozzáférhető • SQL Server 2005 • Az adatbázisa visszagörgetésmegkezdésekor hozzáférhető Visszagörgetés Előregörgetés Használható Idő Előregörgetés Visszagörgetés Használható

35. Adatbázis pillanatfelvételMire jó? • 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 • 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

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

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

38. PillanatfelvételMásolásíráskor Northwind Northwind_SS Parancs Create Northwind_SS Update Northwind Read Northwind_SS Eredmény: D D Tárolási igény 12.5% 0%

39. Pillanatfelvétel a tükörképen • 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 • A tükörképen létrehozott pillanatfelvételek befolyásolhatják a principális működését • Az egy időben létező pillanatfelvételek számát csak az erőforrások korlátozzák

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

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

42. Pillanatfelvétel tranzakció izoláció • 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 • Javítja az adatok elérhetőségét, csökkenti a deadlock-ok kialakulását • Az adatsorok verziózásán alapul (tempdb) • Adatbázis szinten lehet engedélyezni

43. Pillanatfelvétel izoláció változatok • 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 • Ú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

44. SQL Server 2005 Tranzakció izolációs szintek

45. A pillanatfelvétel engedélyezése és az izolációs szint beállítása • 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 • Izolációs szint beállítás – kapcsolat szinten • set transaction isolation level read committed • set transaction isolation level snapshot • A tranzakció működése az engedélyezett és a beállított értéktől is függ

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

47. Snapshot • Adatbázis: ALLOW_SNAPSHOT_ISOLATION on • Kapcsolat: set transaction isolation level snapshot • A módosítások nem blokkolják az olvasásokat és fordítva • 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 • Alapértelmezett a master–en és az msdb-n

48. demó • Pillanatfelvétel izoláció

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

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