Kalandozzon egyet az Access világában egy SQL-kiszolgálón keresztül

Az üzletmenet-folytonosság javítása

Az Access-megoldásnak minimális megszakításokkal kell üzemelnie, ám az Access back-end-adatbázisnál elérhető lehetőségek korlátozottak. Az Access-adatbázis biztonsági mentése elengedhetetlen az adatok védelme érdekében, ám a mentést csak úgy lehet elvégezni, ha a felhasználók kilépnek. Aztán ott vannak még a hardveres és szoftveres karbantartási frissítések, a hálózati és áramkimaradások, a hardverhiba, a biztonsági szabálysértések vagy akár a kibertámadások által okozott váratlan leállások. Ha szeretné minimalizálni a leállásokat és a vállalatra gyakorolt hatásokat, használat közben is készíthet biztonsági másolatot az SQL Server-adatbázisokról. Az SQL Server ezenkívül magas rendelkezésre állási (HA) és katasztrófa-helyreállítási (DR) stratégiákat is kínál. Ezt a két egyesített technológiát nevezzük HADR-nek. További információkért lásd a következő részeket: Üzletmenet-folytonosság és adatbázis-helyreállítás és Az üzletmenet-folytonosság javítása az SQL Serverrel (e-Book).

Biztonsági mentés a használat során

Az SQL Server olyan online biztonsági mentési eljárást alkalmaz, amely az adatbázis futása közben is végrehajtható. Teljes biztonsági mentésre, részleges biztonsági mentésre vagy egy-egy fájl biztonsági mentésére is lehetősége van. A biztonsági mentés során a rendszer olyan adatokról és tranzakciós naplókról készít másolatot, amelyekkel teljes körű helyreállítási művelet hajtható végre. Különösen helyszíni megoldás esetén ügyeljen az egyszerű és a teljes helyreállítási lehetőségek közötti különbségekre, továbbá figyeljen oda, hogy ezek a lehetőségek hogyan hatnak a tranzakciós napló méretére. További információkért lásd: Helyreállítási modellek.

A biztonságimentés-műveletek többsége azonnal végbemegy; ez alól a fájlkezelési és adatbázis-zsugorítási műveletek képeznek kivételt. Ha biztonságimentés-művelet közben próbál meg adatbázisfájlt létrehozni vagy törölni, a művelet nem fog végbemenni. További információkért lásd: A biztonsági mentés áttekintése.

HADR

A magas szintű rendelkezésre állás és üzletmenet-folytonosság érdekében alkalmazott két leggyakoribb módszer a tükrözés és a fürtözés. Az SQL Server a tükrözési és a fürtözési technológiát a „Mindig aktív feladatátvevőfürt-példányok” és a „Mindig aktív rendelkezésre állási csoportok” megoldással kombinálja.

A tükrözés egy olyan adatbázisszintű folyamatossági megoldás, amely az aktív adatbázis egy külön hardveren tárolt teljes másolatát vagy tükrözését használva szinte azonnali feladatátvételt tesz lehetővé. A funkció működhet szinkron módban (magas szintű biztonság), amikor a rendszer a bejövő tranzakciót egy időben hajtja végre az összes kiszolgálón, illetve aszinkron módban (nagy teljesítmény), ahol a rendszer bejövő tranzakciót az aktív adatbázison hajtja végre, majd később, egy előre meghatározott időpontban másolja át azt a tükrözött helyre. A tükrözés egy olyan adatbázisszintű megoldás, amely csak a teljes helyreállítási modellt használó adatbázisokkal működik.

A fürtözés egy olyan kiszolgálószintű megoldás, amely a kiszolgálókat egy olyan adattárolóba vonja össze, amelyet a felhasználó egyetlen példánynak érzékel. A felhasználók ehhez a példányhoz csatlakoznak, és nem kell tudniuk, hogy a példány melyik kiszolgálója aktív valójában. Ha az egyik kiszolgáló meghibásodik vagy karbantartás miatt ki kell kapcsolni, a felhasználó ebből semmit sem érzékel. A fürt egyes kiszolgálóit a fürtkezelő felügyeli egy szívverés funkció használatával, így észleli, ha a fürt aktív kiszolgálója offline állapotba lép, és megpróbál zökkenőmentesen átváltani a fürt következő kiszolgálójára – ez azonban némi késlekedést okozhat.

További információkért lásd a következő részeket: Mindig aktív feladatátvevőfürt-példányok és Mindig aktív rendelkezésre állási csoportok: magas rendelkezésre állású katasztrófa-helyreállítási megoldás.

Vissza a lap tetejére

Az SQL Server biztonsága

Habár az Access-adatbázist az Adatvédelmi központ használatával és az adatbázis titkosításával is megvédheti, az SQL Server fejlettebb biztonsági funkciókkal is rendelkezik. Nézzük meg az Access-felhasználóknak szánt három kiemelkedő funkciót. További információkért lásd: Az SQL Server biztonságossá tétele.

Adatbázis-hitelesítés

Az SQL Server négy adatbázis-hitelesítési módszert kínál, amelyek mindegyike egy ODBC-kapcsolati karakterláncban adható meg. További információkért lásd: Az Azure SQL Server-adatbázisból származó adatok csatolása vagy importálása. Valamennyi módszernek megvannak a maga előnyei.

Integrált Windows-hitelesítés    Windows-hitelesítő adatok használata a felhasználók ellenőrzéséhez, a biztonsági szerepkörökhöz és a felhasználók szolgáltatásokhoz és adatokhoz való hozzáférésének korlátozásához. Használhatja a tartományi hitelesítő adatokat, és egyszerűen kezelheti az alkalmazás felhasználói jogait. Emellett lehetőség van Egyszerű szolgáltatásnevek (SPN-ek) megadására. További információkért lásd: Hitelesítési mód választása.

SQL Server-hitelesítés    A felhasználóknak az adatbázisban megadott hitelesítőadatokkal kell csatlakozniuk. Ehhez az adott munkamenetben történő első adatbázis-eléréskor meg kell adniuk a bejelentkezési azonosítót és jelszót. További információkért lásd: Hitelesítési mód választása.

Azure Active Directory szolgáltatással integrált hitelesítés    Csatlakozás az Azure SQL Server-adatbázishoz az Azure Active Directory használatával. Ha az Azure Active Directoryt hitelesítésre konfigurálta, a továbbiakban nincs szükség bejelentkezési adatok vagy jelszó használatára. További információt a Csatlakozás SQL-adatbázishoz az Azure Active Directory hitelesítésének használatával című témakörben talál.

Active Directory-jelszóval történő hitelesítés    Csatlakozás az Azure Active Directoryban beállított hitelesítő adatokkal, a bejelentkezési azonosító és a jelszó megadásával. További információt a Csatlakozás SQL-adatbázishoz az Azure Active Directory hitelesítésének használatával című témakörben talál.

Tipp:    A Veszélyfelismerés funkció riasztja a felhasználót az olyan rendhagyó adatbázis-tevékenységekről, amelyek biztonsági kockázatot jelenthetnek az Azure Server-adatbázisra nézve. További információ: Az SQL-adatbázis veszélyfelismerése.

Alkalmazásbiztonság

Az SQL Server két alkalmazásszintű biztonsági szolgáltatást kínál, amelyek előnyei az Accessben is kihasználhatók.

Dinamikus adatmaszkolás    Elrejtheti a bizalmas információkat a megfelelő jogosultságokkal nem rendelkező felhasználók elől. Így például lehetőség van TB-számok részleges vagy teljes elrejtésére.

Részleges adatmaszk

Teljes adatmaszk

Az adatmaszkokat többféleképpen is megadhatja, és más adattípusokra is alkalmazhatja őket. Az adatmaszkolás használata a tábla és az oszlop szintjén, a felhasználók meghatározott halmaza számára házirenden alapul, és valós időben alkalmazható a lekérdezésre. További információ: Dinamikus adatmaszkolás.

Sorszintű biztonság    A sorszintű biztonság funkcióval lehetőség van a bizalmas információkat tartalmazó adatbázissorok felhasználói jellemzők alapján történő hozzáférés-szabályozására. Az adatbázisrendszer ezeket a hozzáférési korlátozásokat alkalmazza, így a biztonsági rendszer megbízhatóbb és még robosztusabb.

A biztonsági predikátumoknak két típusa létezik:

• A szűrési predikátum a lekérdezés sorait szűri. A szűrő átlátszó, és a végfelhasználó nem tud a szűrésről.

• A blokk predikátum megakadályozza a jogosulatlan műveleteket, és kivételt jelent, ha a művelet nem hajtható végre.

További információkért lásd: Sorszintű biztonság.

Adatok védelme titkosítással

Gondoskodjon az adatok biztonságáról nyugalmi helyzetben, átvitel közben és használat során – mindezt az adatbázis teljesítményének befolyásolása nélkül. További információ: SQL Server titkosítása.

Titkosítás a tárolás során    A személyes adatok offline, a fizikai tárolón végrehajtott adathordozó-támadások elleni védelme érdekében használjon nyugalmi titkosítást, más néven transzparens adattitkosítást (TDE). Ennek révén az adatok akkor is védve vannak, ha a fizikai adathordozót ellopják vagy nem megfelelően selejtezik le. A TDE valós idejű titkosítást és visszafejtést végez az adatbázisokon, a biztonsági mentéseken és a tranzakciós naplókon, mindezt anélkül, hogy ez az alkalmazások bárminemű módosításával járna.

Az átvitel titkosítása    A bepillantások és közbenső fél által végzett támadások elleni védelem érdekében titkosíthatja a hálózaton küldött adatokat. A rendkívül biztonságos kommunikáció érdekében az SQL Server a Transport Layer Security (TLS) 1.2-t is támogatja. A Tabular Data Stream (TDS) protokoll a nem megbízható hálózatokon keresztüli kommunikáció védelmére is szolgál.

Használat közbeni titkosítás az ügyfélgépen    Ha a személyes adatokat használat közben is védeni szeretné, válassza a „Mindig titkosított” lehetőséget. A személyes adatokat egy illesztőprogram titkosítja és dekódolja az ügyfélgépen anélkül, hogy felfedné a titkosítókódokat az adatbázismotor számára. Ennek következtében a titkosított adatok csak az adott adatok kezeléséért felelős személyek számára láthatók, más, magas jogosultsággal rendelkező felhasználók számára nem. Attól függően, hogy milyen típusú titkosítás van kijelölve, a „Mindig titkosított” opció korlátozhatja az adatbázis bizonyos funkcióit, például a titkosított oszlopok keresését, csoportosítását és indexelését.

Vissza a lap tetejére

Az adatvédelmi aggályok kezelése

Az adatvédelmi aggályok annyira széles körben elterjedtek, hogy az Európai Unió az általános adatvédelmi szabályozáson (GDPR) keresztül határozta meg a törvényes követelményeket. Szerencsére egy SQL Server alapú back-end alkalmas arra, hogy eleget tegyen ezen követelményeknek. Gondoljunk a GDPR alkalmazására egy három lépésből álló keretrendszerben.

1. lépés: A megfelelőségi kockázat értékelése és kezelése

A GDPR megköveteli a táblázatokban és fájlokban tárolt személyes adatok azonosítását és leltárba vételét. Ezek az adatok lehetnek nevek, fényképek, e-mail-címek, banki adatok, közösségi hálózaton végzett közzétételek, orvosi adatok vagy akár IP-címek is.

Az SQL Server Management Studio alkalmazásba épített új eszköz, az SQL Data Discovery and Classification két metaadat-tulajdonsággal egészíti ki az oszlopokat a bizalmas adatok felfedezésének, osztályozásának, felcímkézésének és jelentésének elősegítése érdekében:

• Címkék    Az adatok bizalmassági fokának meghatározására szolgál.

• Adattípusok    Akkor használja, ha további részletességgel szeretné ellátni az oszlopokban tárolt adattípusokat.

Egy másik használható felderítési mechanizmus a teljes szöveges keresés, amely magában foglalja a CONTAINS és a FREETEXT predikátumokat és az olyan sorhalmaz értékű függvényeket, mint a CONTAINSTABLE és a FREETEXTTABLE, amelyek a SELECT utasítással együtt használhatók. A teljes szöveges keresés használatával megtalálhatja a szavakat, a szókombinációkat és a szavak variációit (például szinonimákat vagy ragozott alakokat) a táblákban. További információ: Teljes szöveges keresés.

2. lépés: Személyes adatok védelme

A GDPR megköveteli a személyes adatok védelmét és az azokhoz való hozzáférés korlátozását. A hálózathoz és az információkhoz való hozzáférés kezelésének szokásos lépésein kívül (pl. tűzfalbeállítások) az SQL Server biztonsági funkcióival is segíthet az adatelérés szabályozásában:

• Az SQL Server hitelesítés funkcióval kezelhető a felhasználók identitása, és megelőzhető a jogosulatlan hozzáférés.

• A sorszintű biztonság funkcióval korlátozható egy tábla soraihoz való hozzáférés a felhasználó és az adott adatok közötti kapcsolat alapján.

• A dinamikus adatmaszkolás funkció korlátozza a személyes adatok expozícióját, mivel a megfelelő jogosultsággal nem rendelkező felhasználók elől elrejti azokat.

• A titkosítás funkció biztosítja, hogy a személyes adatok az átvitel és a tárolás során védve legyenek, és (akár a kiszolgálóoldalon is) védve legyenek a felfedéssel szemben.

További információ: Az SQL Server biztonsága.

3. lépés: Hatékony válasz a kérésekre

A GDPR megköveteli a személyes adatok feldolgozására vonatkozó feljegyzések megőrzését, és előírja ezen feljegyzések kérésre történő elérhetővé tételét a felügyeleti szervek számára. Ha probléma merül fel, például véletlen adatkiadás, a védelmi szabályozás lehetővé teszi a gyors reagálást. Az adatoknak gyorsan elérhetőnek kell lenniük, amikor jelentést kell készíteni. A GDPR például megköveteli, hogy „legkésőbb 72 órával az eset észlelését követően” jelentsék a személyes adatokra vonatkozó incidenseket a felügyeleti hatóságnak.

Az SQL Server 2017 többféleképpen segíti a jelentéskészítési feladatokat:

• SQL Server Audit segítségével biztosítható, hogy az adatbázis-hozzáférési és -feldolgozási műveletekről állandó feljegyzések készüljenek. Ez a minden részletre kiterjedő audioeszköz részletes ellenőrzéseket végez a lehetséges veszélyforrások, a gyanús visszaélések és a biztonsági szabálysértések megértésének és azonosításának érdekében. Az adatok elemzését egyszerűen elvégezheti.

• Az SQL Server időbeli táblái rendszerverzióval ellátott felhasználói táblák, amelyek az adatmódosítások teljes előzményeinek a tárolására szolgálnak. Ezek segítségével egyszerűbben elkészíthetők a jelentések, továbbá időpontalapú elemzéseket végezhet.

• SQL Vulnerability Assessment segítséget nyújt a biztonsági és jogosultsági problémák felismerésében. Probléma észlelése esetén az adatbázis-vizsgálati jelentéseket is megtekintheti a megoldás megtalálása érdekében.

További információ: Megbízhatósági platform létrehozása (e-Book) és A GDPR-megfelelőséghez vezető út.

Vissza a lap tetejére

Adatbázis-pillanatfelvételek létrehozása

Az adatbázis-pillanatfelvétel az SQL Server adatbázis egy adott időpontra vonatkozó, írásvédett, statikus nézete. Habár lényegében az Access-adatbázisfájl átmásolásával is egy adatbázis-pillanatfelvételt kap, az Accessben – az SQL Serverrel ellentétben – erre nem található beépített módszer. Az adatbázis-pillanatfelvétel segítségével jelentést készíthet a pillanatfelvétel pillanatában érvényes adatok alapján. Ezenkívül az adatbázis-pillanatfelvétel használatával megőrizheti a múltbeli adatokat, például minden pénzügyi negyedévről készíthet egy-egy pillanatfelvételt, amelyeket aztán felhasználhat az időszakvégi jelentések készítése során. A következő ajánlott eljárásokat javasoljuk:

• A pillanatfelvétel elnevezése    Az adatbázis-pillanatfelvételeknek egyedi adatbázisnévvel kell rendelkezniük. Az egyszerűbb azonosíthatóság érdekében a célt és az időkeretet is tüntesse fel a névben. Ha például az AdventureWorks-adatbázisról naponta háromszor, 6 óránként, reggel 6 és délután 6 között kíván pillanatfelvételeket készíteni 24 órás formátumot használva, akkor a következő elnevezéseket használja: AdventureWorks_snapshot_0600, AdventureWorks_snapshot_1200 és AdventureWorks_snapshot_1800.

• A pillanatfelvételek számának korlátozása    Az adatbázis-pillanatfelvételek egészen addig megmaradnak, amíg azokat szándékosan nem törli. Mivel a pillanatfelvételek száma folyamatosan nő, előfordulhat, hogy egy új pillanatfelvétel létrehozása után törölni szeretne egy régebbit a szabad tárhely felszabadítása érdekében. Ha például napi rendszerességgel készít jelentéseket, akkor 24 órán át őrizze meg az adatbázis-pillanatfelvételt, majd ezután törölje és hozzon létre egy újat.

• Csatlakozás a megfelelő pillanatfelvételhez    Az adatbázis-pillanatfelvétel használatához az Access front-endnek tudnia kell a helyes helyet. Amikor egy meglévő pillanatképet lecserél egy újra, át kell irányítani az Accesst az új pillanatképhez. Adjon hozzá logikát az Access front-endhez, és győződjön meg róla, hogy a megfelelő adatbázis-pillanatfelvételhez állítja-e be a csatlakozást.

Az adatbázis-pillanatfelvétel létrehozása az alábbi módon végezhető el:

CREATE DATABASE AdventureWorks_dbss1800 ON  
( NAME = AdventureWorks_Data, FILENAME =   
'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\AdventureWorks_snapshot_0600' )  
AS SNAPSHOT OF AdventureWorks;  

További információ: Adatbázis-pillanatfelvételek (SQL Server).

Vissza a lap tetejére

Az egyidejűség szabályozása

Amikor több felhasználó egyszerre próbálja meg módosítani az adatbázisban lévő adatokat, akkor szükség van rendszerszabályozásra, hogy az egyik személy által végzett módosítások ne legyenek káros hatással a másik személy által végzett módosításokra. Ezt egyidejűség-szabályozásának nevezzük, és két alapvető zárolási stratégia létezik: a pesszimista és az optimista. A zárolással megakadályozható, hogy a felhasználók a többi felhasználót érintő módon módosíthassák az adatokat. A zárolással az adatbázis sértetlensége is biztosítható, különösen azon lekérdezések esetében, amelyek egyébként nem várt eredményt adnának. Fontos eltérések vannak aközött, ahogy az Access és az SQL Server alkalmazza ezeket az egyidejűség-szabályozási stratégiákat.

Az Accessben az alapértelmezett zárolási stratégia optimista, és annak a személynek adja meg a zárolás tulajdonjogát, aki először próbál meg írni egy rekordba. Ha egy másik személy ekkor próbál meg ugyanabba a rekordba írni, akkor az Access megjeleníti számára az Írási ütközés párbeszédpanelt. Az ütközés feloldásához a másik személy elmentheti a rekordot, a vágólapra másolhatja azt, vagy elvetheti a módosításokat.

Az egyidejűség-szabályozási stratégia a RecordLocks tulajdonsággal is módosítható. Ez a tulajdonság az űrlapokat, jelentéseket és lekérdezéseket érinti, és három beállítással rendelkezik:

• Nincs zárolás    Űrlapok esetén a felhasználók megpróbálhatják párhuzamosan szerkeszteni ugyanazt a rekordot, ám ekkor megjelenik az Írási ütközés párbeszédpanel. Jelentések esetén: A jelentések megjelenítésekor vagy nyomtatása közben a rekordok nem lesznek zárolva. Lekérdezések esetén: A rekordok nem lesznek zárolva a lekérdezés futtatása közben. Ez az Access módszere az optimista zárolás alkalmazására.

• Összes rekord    Az űrlap űrlapnézetben vagy adatlapnézetben történő megnyitásakor, a jelentés megjelenítésekor vagy nyomtatása közben, illetve a lekérdezés futtatása közben az alapul szolgáló tábla vagy lekérdezés összes rekordja zárolva lesz. A felhasználók számára a zárolt rekordok olvashatók.

• Szerkesztett rekord    Űrlapok és lekérdezések esetén, amikor egy felhasználó elkezdi szerkeszteni bármelyik rekord bármelyik mezőjét, a rendszer zárolja az aktuális rekordlapot, amíg a felhasználó át nem lép egy másik rekordra. Ennek következtében egy rekordot egyszerre csak egy felhasználó fog tudni szerkeszteni. Ez az Access módszere a pesszimista zárolás alkalmazására.

További információ: Írási ütközés párbeszédpanel és RecordLocks tulajdonság.

Az SQL Serverben az egyidejűségek szabályozása az alábbiak szerint működik:

• Pesszimista    Ha egy felhasználó olyan műveletet végez, amelynek hatására a rendszer zárolja a rekordot, akkor a többi felhasználó addig nem tud a lezárással ütköző műveleteket végrehajtani, amíg a tulajdonos fel nem oldja a zárolást. Ez az egyidejűség-szabályozás elsősorban olyan környezetekben használatos, ahol az adatok elérése gyakori.

• Optimista    Optimista egyidejűség esetén a felhasználók nem zárolják az adatokat azok megtekintése során. Amikor egy felhasználó frissíti az adatokat, a rendszer ellenőrzi, hogy egy másik felhasználó módosította-e az adatokat a megtekintés után. Ha egy másik felhasználó frissítette az adatokat, hiba történik. A hibát kapó felhasználó jellemzően visszagörgeti a tranzakciót, és újrakezdi a műveletet. Ez a egyidejűség-szabályozás elsősorban olyan környezetekben használatos, ahol az adatok elérése nem gyakori.

A párhuzamossági szabályozás típusának megadásához több tranzakció-elkülönítési szintet kell kiválasztani a SET TRANSACTION utasítás használatával. Ezek határozzák meg, hogy a tranzakció milyen szintű védelemmel rendelkezik a többi tranzakció módosításaival szemben:

 SET TRANSACTION ISOLATION LEVEL
 { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ  
    | SNAPSHOT
    | SERIALIZABLE
 }

További információ: Tranzakciók zárolása és a sor verziószámozási útmutatója.

Vissza a lap tetejére

A lekérdezés teljesítményének növelése

Az Access továbbított lekérdezés alkalmazása esetén használja ki az SQL Server által kínált előnyöket, amelyekkel hatékonyabbá tehetők a műveletek.

Az Access-adatbázisokkal ellentétben az SQL Server párhuzamos lekérdezésekkel optimalizálja a lekérdezés végrehajtását és az indexelési műveleteket azokon a számítógépeken, amelyeknek több mikroprocesszora (CPU-ja) van. Mivel az SQL Server több rendszervégző szál használatával párhuzamosan végezhet lekérdezéseket vagy indexelési műveleteket, a műveletek gyorsan és hatékonyan végrehajthatók.

A lekérdezések az adatbázis-megoldás általános teljesítménye javításának kritikus tényezői. A hibás lekérdezések a végtelenségig futhatnak, időtúllépést okozhatnak, és lefoglalhatják a rendszer erőforrásait – a CPU-kat, a memóriát és hálózati sávszélességet. Ez akadályozza a fontos üzleti adatok rendelkezésre állását. Akár egy rossz lekérdezés is okozhat súlyos problémákat az adatbázisban.

További információ: Gyorsabb lekérdezés az SQL Server használatával (e-Book).

A lekérdezés optimalizálása

Több eszköz is együttműködik a lekérdezés teljesítményének elemzéséhez és javításához: A Query Optimizer, a végrehajtási tervek és a Query Store.

Query Optimizer

A Query Optimizer az SQL Server egyik legfontosabb összetevője. A Query Optimizer segítségével elemezheti a lekérdezéseket, illetve meghatározhatja, hogy melyik a leghatékonyabb módszer a szükséges adatokhoz való hozzáféréshez. A Query Optimizer a lekérdezéstől, az adatbázissémától (tábla- és indexdefiníciók) és az adatbázis-statisztikáktól kap inputot. A Query Optimizer outputja egy végrehajtási terv.

További információ: Az SQL Server Query Optimizer funkciója.

Végrehajtási terv

A végrehajtási terv egy olyan definíció, amely az adatok az egyes táblákból való kinyerése céljából sorba rendezi a szükséges forrástáblákat és a használt módszereket. Az optimalizálás folyamata abból áll, hogy kiválasztunk egy végrehajtási tervet a számos lehetséges terv közül. Minden lehetséges végrehajtási tervhez tartozik egy számításiköltség-igény, és a Query Optimizer ez alapján választja ki a legalacsonyabb becsült költségű megoldást.

Az SQL Servernek emellett dinamikusan korrigálnia kell magát az adatbázis változásaihoz. A lekérdezés-végrehajtási tervek regressziói jelentősen befolyásolhatják a teljesítményt. Az adatbázis bizonyos módosításai azt eredményezhetik, hogy az adatbázis új állapota miatt a végrehajtási terv hatástalan vagy érvénytelen lesz. Az SQL Server felismeri azokat a változtatásokat, amelyek érvénytelenné teszik a végrehajtási tervet, és érvénytelenként jelöli meg az adott tervet.

Ezután a következő kapcsolathoz, amely végrehajtja a lekérdezést, ismét össze kell állítani egy tervet. A terv érvénytelenítésének feltételei a következők:

• A lekérdezés által hivatkozott tábla vagy nézet módosításai (ALTER TABLE és ALTER VIEW).

• A végrehajtási terv által használt indexek módosításai.

• A végrehajtási terv által használt olyan statisztikai adatok frissítése, amelyek explicit módon egy utasításból (pl. UPDATE STATISTICS) vagy automatikusan lettek létrehozva.

További információ: Végrehajtási tervek.

Query Store

A Query Store betekintést nyújt a végrehajtási terv teljesítményébe, és megtudhatja, hogy milyen végrehajtási terv lett kiválasztva. Az alkalmazás leegyszerűsíti a teljesítménnyel kapcsolatos hibaelhárítást, mivel gyorsan megtalálhatja a végrehajtási terv módosításai által okozott teljesítményváltozásokat. A Query Store összegyűjti a telemetriai adatokat, így például a lekérdezések, a tervek, a futtatási statisztikák és a várakozási statisztikák előzményeit. A Query Store implementálásához használja az ALTER DATABASE utasítást:

ALTER DATABASE AdventureWorks2012 SET QUERY_STORE = ON;

További információ: Teljesítményfelügyelet a Query Store használatával.

ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 140;

A lekérdezés módszerei

Az SQL Serverben többféleképpen is lefuttathatók a lekérdezések, és mindegyik módszernek megvannak a maga előnyei. Ezekkel érdemes tisztában lenni, hogy az adott Access-megoldáshoz legjobban illőt válassza. A TSQL-lekérdezések létrehozásának legjobb módja az, ha az SQL Server Management Studio (SSMS) Transact-SQL szerkesztője használatával interaktívan szerkeszti és teszteli azokat. Ez az eszköz az IntelliSense funkcióval segít a megfelelő kulcsszavak kiválasztásában és a szintaktikai hibák megtalálásában.

Nézetek

Az SQL Serverben a nézet olyan, mint egy virtuális tábla, amelyben a megtekintési adatok egy vagy több táblából vagy egyéb nézetből származnak. A nézetek azonban ugyanúgy hivatkozva vannak, mint a táblák a lekérdezésekben. A nézetek segítségével elrejtheti a lekérdezések összetettségét, és a sorok és oszlopok korlátozásával elősegítheti az adatok védelmét. Íme egy példa egy egyszerű nézetre:

CREATE VIEW HumanResources.EmployeeHireDate AS  
SELECT p.FirstName, p.LastName, e.HireDate  
FROM HumanResources.Employee AS e JOIN Person.Person AS p  
ON e.BusinessEntityID = p.BusinessEntityID;

Az optimális teljesítmény érdekében, illetve a nézetek eredményeinek szerkesztéséhez hozzon létre egy indexelt nézetet, amely ugyanúgy létezik az adatbázisban, mint egy tábla, továbbá a többi táblához hasonlóan tartozik hozzá tárhely, illetve bármikor lekérdezhető. Ahhoz, hogy használni tudja az Accessben, csatolja a nézethez ugyanúgy, mintha csak egy táblához csatolná. Íme egy példa egy indexelt nézetre:

CREATE VIEW Sales.vOrders  
WITH SCHEMABINDING  
AS  
    SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,  
        OrderDate, ProductID, COUNT_BIG(*) AS COUNT  
    FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o  
    WHERE od.SalesOrderID = o.SalesOrderID  
    GROUP BY OrderDate, ProductID;  

CREATE UNIQUE CLUSTERED INDEX IDX_V1   
    ON Sales.vOrders (OrderDate, ProductID);  

Vannak azonban korlátozások. Nem frissítheti az adatokat, ha egynél több alaptábla érintett, vagy ha a nézet összesített függvényeket vagy DISTINCT záradékot tartalmaz. Ha az SQL Server egy hibaüzenetet jelenít meg, amely azt jelzi, hogy nem tudja, melyik rekordot kell törölni, akkor lehetséges, hogy hozzá kell adni egy törlés triggert a nézethez. Végül pedig az Access-lekérdezésekben nem használhatja az ORDER BY záradékot.

További információ: Nézetek és Indexelt nézetek létrehozása.

Tárolt eljárások

A tárolt eljárások egy vagy több olyan TSQL-utasítást tartalmazó csoport, amely bemeneti paramétereket vesz fel, kimeneti paramétereket ad vissza, továbbá egy állapotértékkel jelzi a sikert vagy a hibát. A tárolt eljárások közbenső rétegként funkcionálnak az Access front-endje és az SQL Server back-endje között. A tárolt eljárások lehetnek olyan egyszerűek, mint például a SELECT utasítás, vagy olyan összetettek, mint bármely program. Lássunk egy példát:

CREATE PROCEDURE HumanResources.uspGetEmployees   
    @LastName nvarchar(50),   
    @FirstName nvarchar(50)   
AS   
    SET NOCOUNT ON;  
    SELECT FirstName, LastName, Department  
    FROM HumanResources.vEmployeeDepartmentHistory  
    WHERE FirstName = @FirstName AND LastName = @LastName  
    AND EndDate IS NULL;  

Amikor tárolt eljárást használ az Accessben, az rendszerint egy eredményhalmazt ad vissza egy űrlapon vagy egy jelentésben. Olyan műveleteket is végezhet azonban, amelyek nem adnak vissza eredményt – ilyen lehet például egy DDL vagy DML utasítás. Továbbított lekérdezés alkalmazása esetén ügyeljen a Rekordokat ad vissza tulajdonság megfelelő beállítására.

További információ: Tárolt eljárások.

Gyakori táblakifejezések

A gyakori táblakifejezések (CTE) hasonlítanak az olyan ideiglenes táblákra, amelyek névvel ellátott eredményhalmazokat hoznak létre. Ezek csak egyetlen lekérdezés vagy DML-utasítás végrehajtása céljából léteznek. A CTE ugyanabba a kódsorba van beépítve, mint a SELECT utasítás és az azt használó DML-utasítás, ám egy ideiglenes tábla vagy nézet létrehozása és használata általában két lépésből áll. Lássunk egy példát:

-- Define the CTE expression name and column list.  
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)  
AS  
-- Define the CTE query.  
(  
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear  
    FROM Sales.SalesOrderHeader  
    WHERE SalesPersonID IS NOT NULL  
)  
-- Define the outer query referencing the CTE name.  
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear  
FROM Sales_CTE  
GROUP BY SalesYear, SalesPersonID  
ORDER BY SalesPersonID, SalesYear;

A CTE több előnnyel is rendelkezik, többek között az alábbiakkal:

• Mivel a CTE-k tranziensek, ezért ezeket nem kell állandó adatbázis-objektumokként létrehozni, mint a nézeteket.

• Ugyanarra a CTE-re többször is lehet hivatkozni a lekérdezésekben vagy a DML utasításokban, így a kód hatékonyabban kezelhető.

• A CTE-re hivatkozó lekérdezésekkel megadhatja a kurzort.

További információ: WITH common_table_expression.

Felhasználó által definiált függvények

A felhasználó által definiált függvények (UDF) lekérdezéseket és számításokat végezhetnek, és skaláris értékeket vagy adat-eredményhalmazokat adnak vissza. Olyanok, mint azok a programozási nyelvekben használt függvények, amelyek paramétereket fogadnak el, műveleteket hajtanak végre (például komplex számításokat) és érték formájában adják vissza a művelet eredményét. Lássunk egy példát:

CREATE FUNCTION dbo.ISOweek (@DATE datetime)  
RETURNS int WITH SCHEMABINDING -- Helps improve performance
WITH EXECUTE AS CALLER  
AS  
BEGIN  
     DECLARE @ISOweek int;  
     SET @ISOweek= DATEPART(wk,@DATE)+1  
          -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104');  
-- Special cases: Jan 1-3 may belong to the previous year  
     IF (@ISOweek=0)   
          SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1   
               AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1;  
-- Special case: Dec 29-31 may belong to the next year  
     IF ((DATEPART(mm,@DATE)=12) AND   
          ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))  
          SET @ISOweek=1;  
     RETURN(@ISOweek);  
END;  
GO  
SET DATEFIRST 1;  
SELECT dbo.ISOweek(CONVERT(DATETIME,'12/26/2004',101)) AS 'ISO Week';  

Az UDF-ek bizonyos korlátozásokkal rendelkeznek. Nem használhatnak például bizonyos nem determinisztikus rendszerfüggvényeket, nem hajthatnak végre DML- vagy DDL-utasításokat, valamint nem futtathatnak dinamikus SQL-lekérdezéseket.

További információ: Felhasználó által definiált függvények.

Vissza a lap tetejére

Kulcsok és indexek hozzáadása

Akármilyen adatbázisrendszert is használ, a kulcsok és az indexek kéz a kézben járnak.

Kulcsok

Ügyeljen rá, hogy az SQL Serverben minden egyes táblához létrehozzon elsődleges kulcsot, és minden egyes kapcsolódó táblához létrehozzon idegen kulcsokat. Az Access AutoNumber adattípus SQL Server-ekvivalens funkciója az IDENTITY tulajdonság, amely új kulcsértékek létrehozására szolgál. Ha ezt a tulajdonságot bármilyen numerikus oszlopra alkalmazza, az írásvédetté válik, és annak karbantartásáról a továbbiakban az adatbázisrendszer gondoskodik. Amikor egy IDENTITY oszlopot tartalmazó táblába beszúr egy rekordot, akkor a rendszer automatikusan 1-gyel növeli az IDENTITY oszlop értékét (1-től kezdve). Ezek az értékek azonban argumentumok segítségével szabályozhatók.

További információ: CREATE TABLE, IDENTITY (tulajdonság).

Indexek

Mint mindig, az indexek kiválasztásával lehet megteremteni az egyensúlyt a lekérdezési sebesség és a frissítés költsége között. Az Accessben egyféle indextípus érhető el, ám az SQL Serverben 12. Szerencsére a Query Optimizer segítségével megbízhatóan kiválaszthatja a leghatékonyabb indexelést. Az Azure SQL-ben pedig használhat automatikus indexkezelést az automatikus finomhangolás funkció részeként, amely ajánlást tesz az indexek hozzáadására és eltávolítására. Az Accesstől eltérően az SQL Serverben az idegen kulcsokhoz saját indexeket kell létrehozni. A lekérdezés teljesítményének javítása érdekében az indexált nézetben is létrehozhat indexeket. Az indexált nézet hátránya a megnövekedett többletterhelés, ami a nézet alaptábláiban végrehajtott adatmódosításkor jelentkezik, mivel ilyenkor a nézetet is frissíteni kell. További információ: Az SQL Server indexarchitektúra és -kialakítás útmutatója és Indexek.

Vissza a lap tetejére

Tranzakciók végrehajtása

Az online tranzakciós eljárások (OLTP) végrehajtása az Access esetében nehézkes, de az SQL Server használatával viszonylag könnyen elvégezhető. A tranzakció egy olyan munkaegység, amely sikeres végrehajtás esetén minden adatmódosítást végrehajt, ám sikertelenség esetén visszavonja a változtatásokat. A tranzakciónak négy tulajdonsággal kell rendelkeznie, amelyeket együttesen gyakran ACID-nak hívunk:

• Atomitás    A tranzakciónak egy oszthatatlan, „atomnyi” munkaegységnek kell lennie: vagy az összes adatmódosítást végrehajtja, vagy nem hajt végre egyet sem.

• Következetesség    A művelet befejeztével a tranzakciónak minden adatot konzisztens állapotban kell hagynia. Ez azt jelenti, hogy az összes adatsértetlenségi szabálynak érvényben kell lennie.

• Elkülönítés    A párhuzamos tranzakciók módosításai elkülönülnek az aktuális tranzakciótól.

• Tartósság    A tranzakció befejeztével a változások véglegesek – akár rendszerhiba esetén is.

A tranzakciókat az adatintegritás biztosítására használják, például bankautomatából történő pénzfelvétel vagy fizetés automatikus jóváírása esetén. A tranzakciók lehetnek explicitek, implicitek vagy köteg-hatókörűek. Íme két példa a TSQL-re:

-- Using an explicit transaction

BEGIN TRANSACTION;  
DELETE FROM HumanResources.JobCandidate  
    WHERE JobCandidateID = 13;  
COMMIT;  

-- the ROLLBACK statement rolls back the INSERT statement, but the created table still exists.

CREATE TABLE ValueTable (id int);  
BEGIN TRANSACTION;  
       INSERT INTO ValueTable VALUES(1);  
       INSERT INTO ValueTable VALUES(2);  
ROLLBACK;

További információ: Tranzakciók.

Vissza a lap tetejére

Korlátozások és triggerek használata

Minden adatbázis esetében megvannak az adatintegritás megőrzésének módszerei.

Megkötések

Az Accessben a hivatkozási integritást idegenkulcs-elsődleges kulcs párosításon, lépcsőzetes frissítéseken és törléseken, valamint érvényességi szabályokon keresztül kell érvényesíteni egy táblakapcsolatban. További információ: Útmutató a táblakapcsolatokhoz és Az adatbevitel korlátozása érvényességi szabályok használatával.

Az SQL Serverben UNIQUE és CHECK korlátozásokat kell alkalmazni, amelyek az SQL Server táblákban az adatintegritást érvényesítő adatbázis-objektumok. Ha ellenőrizni szeretné, hogy egy érték érvényes-e egy másik táblában, használjon idegen kulcsbeli megkötést. Ha ellenőrizni szeretné, hogy egy érték egy oszlopban adott tartományon belül van-e, használjon ellenőrző megkötést. Ezek az objektumok képezik a védelem első vonalát, és a kialakításuknak köszönhetően hatékonyan működnek. További információ: Egyedi megkötések és ellenőrző megkötések.

Triggerek

Az Access nem tartalmaz adatbázis-triggereket. Az SQL Serverben triggerek használatával összetett adatintegritási szabályokat alkalmazhat, valamint üzleti logikát futtathat a kiszolgálón. Az adatbázistrigger egy tárolt eljárás, amely akkor fut le, amikor bizonyos műveletek bekövetkeznek egy adatbázisban. A trigger egy meghatározott esemény – például rekord hozzáadása vagy törlése – esetén indul el és hajtja végre a tárolt eljárást. Amellett, hogy egy Access-adatbázis biztosítja a hivatkozások integritását, amikor egy felhasználó megpróbálja frissíteni vagy törölni az adatokat, az SQL Server összetett triggerekkel rendelkezik. Beállíthatja például, hogy a trigger egyszerre több rekordot töröljön, és közben biztosítsa az adatok sértetlenségét. Triggereket akár táblákhoz és nézetekhez is hozzáadhat.

További információ: Triggerek – DML, Triggerek – DDL és A T-SQL trigger megtervezése.

Vissza a lap tetejére

Számított oszlopok használata

Az Accessben egy számított oszlop létrehozásához fel kell venni az oszlopot egy lekérdezésbe, majd létre kell hozni egy kifejezést, például:

Extended Price: [Quantity] * [Unit Price]

Az SQL Serverben a számított oszlop egy olyan virtuális oszlop, amely fizikailag nem található meg a táblában, kivéve, ha az oszlop megjelölése PERSISTED. A számított oszlop az SQL Server esetében is egy kifejezés többi oszlopának adatait használja. Számított oszlop létrehozásához vegye fel azt egy táblába. Például:

CREATE TABLE dbo.Products   
(  
    ProductID int IDENTITY (1,1) NOT NULL  
  , QtyAvailable smallint  
  , UnitPrice money  
  , InventoryValue AS QtyAvailable * UnitPrice  
);  

További információ: Számított oszlopok megadása egy táblában.

Vissza a lap tetejére

Időbélyeg elhelyezése az adatokon

A rekord létrehozásakor időnként felveszünk egy táblamezőt az időbélyegző számára, hogy naplózni lehessen az adatbevitelt. Az Accessben egyszerűen létrehozható egy dátum oszlop az alapértelmezett értékkel: =Now(). Ha dátumot vagy időpontot szeretne rögzíteni az SQL Serverben, használja a datetime2 adattípus alapértelmezett értékét: SYSDATETIME().

Megjegyzés    Ügyeljen rá, hogy ne keverje össze a rowversion adattípust a timestamp adattípussal. Az SQL Serverben a timestamp kulcsszó a rowversion szinonimája, de a rowversion kulcsszót kell használni. Az SQL Serverben a rowversion egy olyan adattípus, amely felfedi az adatbázis automatikusan generált, egyedi bináris számait, és amely jellemzően a táblasorok verziószámozására használatos. A rowversion adattípus azonban csak egy növekvő szám, nem jelzi a dátumot vagy az időt, és nem a sorok időbélyegzésére szolgál.

További információ: rowversion. Ha szeretne többet megtudni arról, hogyan minimalizálható a rekordok ütközése a rowversion használata esetén, lásd: Access-adatbázis áttelepítése az SQL Serverbe.

Vissza a lap tetejére

Nagy méretű objektumok kezelése

Az Accessben a nem strukturált adatok (pl. fájlok, fényképek és képek) kezelése az Attachment adattípussal történik. Az SQL Server terminológiájában a strukturálatlan adatokat blobnak nevezzük (bináris nagy objektum), és többféle módon is dolgozhat velük:

FILESTREAM    A varbinary(max) adattípus használatával a fájlrendszeren tárolja a nem strukturált adatokat, nem pedig az adatbázisban. További információ: A FILESTREAM adatok elérése a Transact-SQL használatával.

FileTable    FileTable nevű speciális táblákban tárolja a blobokat, és kompatibilitást biztosít a Windows-alkalmazások számára, így olyan, mintha azok a fájlrendszerben lennének – anélkül, hogy az ügyfélalkalmazások módosulnának. A FileTable megköveteli a FILESTREAM használatát. További információ: FileTables.

Távoli BLOB-tár (RBS)    A bináris nagy objektumokat (blobokat) nem közvetlenül a kiszolgálón, hanem egy külön tárolón tárolja. Ezzel helyet takaríthat meg, és csökkentheti a hardvererőforrás-felhasználást. További információ: Bináris nagy objektum (blob) adatok.

Vissza a lap tetejére

Hierarchikus adatokkal végzett munka

Habár a relációs adatbázisok (például az Access) nagyon rugalmasak, a hierarchikus kapcsolatok használata kivételt jelent, és gyakran összetett SQL-utasításokat vagy -kódokat igényel. A hierarchikus adatok közé tartoznak például a következők: a szervezeti struktúrák, a fájlrendszerek, a nyelvi kifejezések rendszertana és a weblapok közötti hivatkozások grafikonjai. Az SQL Server egy beépített hierarchyid adattípust, valamint hierarchikus függvényeket tartalmaz, amelyekkel egyszerűen tárolhatók, lekérdezhetők és kezelhetők a hierarchikus adatok.

További információ: Hierarchikus adatok és Oktatóanyag: A hierarchyid adattípus használata.

Vissza a lap tetejére

A JSON-szöveg kezelése

A JavaScript-objektumok jelölése (JSON) egy olyan webszolgáltatás, amely az emberek számára olvasható szöveg formájában továbbítja az adatokat attribútum–érték párokként aszinkron böngésző–kiszolgáló kommunikáción keresztül. Például:

{
"firstName": "Mary",
"lastName": "Contrary",
"spouse": null,
"age": 27
}

Az Access nem tartalmaz beépített módszereket a JSON-adatok kezeléséhez, de az SQL Serverben zökkenőmentesen tárolhatja, indexelheti, lekérdezheti és ki is nyerheti a JSON-adatokat. A JSON-szöveget konvertálni és tárolni lehet egy táblába, illetve az adatokat JSON-szövegként formázhatja. Előfordulhat például, hogy a lekérdezés eredményét JSON-ként szeretné formázni egy webalkalmazáshoz, vagy JSON adatstruktúrákat szeretne felvenni sorokba és oszlopokba.

Megjegyzés    A JSON a VBA-ban nem támogatott. Másik lehetőségként használhat XML-t a VBA-ban az MSXML-tár használatával.

További információ: JSON-adatok az SQL Serverben.

Vissza a lap tetejére

Források

Jó alkalom ez arra, hogy többet megtudjon az SQL Serverről és a Transact SQL-ről (TSQL). Amint láttuk, számos olyan funkció van, amit az Access is ismer, de olyanokat is tud, amiket az Access nem. Ha a következő szintre szeretne lépni, tanulmányozza az alábbiakat:

Vissza a lap tetejére