Kui olete oma andmed Accessist SQL Serverisse migreerinud, on teil kliendi/serveri andmebaas, mis võib olla kohapealne või Azure’i hübriidpilve lahendus. Mõlemal juhul on Access nüüd esitluskiht ja SQL Server andmekiht. Nüüd on sobiv hetk uuesti läbi mõelda oma lahendused, eriti need, mis puudutavad päringujõudlust, turvet ja jätkusuutlikkust, nii et saaksite parandada ja mastaapida oma andmebaasilahendust.
Et Accessi kasutaja saaks esmalt tutvuda SQL Server ja Azure dokumentatsiooniga, võib see tunduda heidutav. Seetõttu on vaja ekskursiooni, mis juhatab teid läbi teile oluliste teemade. Kui olete selle ekskursiooni lõpetanud, olete valmis tutvuma andmebaasitehnoloogia arenguga ja võtma pikema teekonna.
Selle artikli teemad
Draivi jätkusuutlikkus
Accessi lahenduse puhul soovite, et selle töötamise jooksul esineks võimalikult vähe katkestusi, kuid Accessi tagaandmebaasi võimalused on piiratud. Andmete kaitsmiseks on oluline Accessi andmebaasi varundada, kuid selleks on vaja viia kasutajad võrgust välja. Esineb ka planeerimata seisakuaeg, mida põhjustavad riistvara/tarkvara hooldustäiendused, võrgu- või elektrikatkestused, riistvaratõrked, turbemurded või isegi küberrünnakud. Selleks et minimeerida seisakuaega ja selle mõju teie ärile, saate SQL Serveri andmebaasi selle kasutamise ajal varundada. Lisaks pakub SQL Server ka suure kättesaadavuse (HA) ja avariijärgse taaste (DR) strateegiaid. Neid kahte kombineeritud tehnoloogiat nimetatakse HADR-iks. Lisateavet leiate teemadest Järjepidevuse ja andmebaasitaaste ningDraivi järjepidevus koos SQL Server (e-raamat) abil.
Varundamine kasutamise ajal
SQL Server kasutab võrgus varundamise protsessi, mis võib esineda andmebaasi töötamise ajal. Saate teha täieliku varukoopia, osalise varukoopia või faili varukoopia. Selleks et tagada täielik taastetoiming, teeb varundamine koopiad andmetest ja tehingulogidest. Kohapealse lahenduse puhul tuleb eriti silmas pidada erinevusi lihtsate ja täielike taastesuvandite vahel ning kuidas need mõjutavad tehingulogi kasvu. Lisateavet leiate artiklist Taastemudelid.
Enamik varundustoiminguid toimub kohe, v.a failihalduse ja andmebaasi kahandamise toimingud. Kui proovite andmebaasifaili luua või kustutada varundustoimingu ajal, siis toiming nurjub. Lisateavet leiate teemast Varunduse ülevaade.
HADR
Kaks levinumat meetodit suure kättesaadavuse ja jätkusuutlikkuse saavutamiseks on peegeldamine ja klasterdamine. SQL Server integreerib peegeldamise ja klasterdamise tehnoloogiat funktsioonidega „Always On Failover Cluster Instances“ ja „Always On Availability Groups“.
Peegeldamine on andmebaasitasemel järjepidevuslahendus, mis toetab kohe rikkeümberlülitust, säilitades ooteandmebaasi, täielikku koopiat või aktiivset peegelandmebaasi eraldi riistvaral. See võib toimida sünkroonrežiimis (väga turvalises), kus sissetulev tehing on pühendunud samal ajal kõigile serveritele, või asünkroonrežiimis (suure jõudlusega), kus sissetulev tehing on pühendunud aktiivsele andmebaasile ning seejärel kopeeritakse see varem kindlaks määratud hetkel peegelandmebaasi. Peegeldamine on andmebaasitasemel lahendus ja see töötab ainult andmebaasides, mis kasutavad täielikku taastemudelit.
Klasterdamine on serveritasemel lahendus, mis ühendab servereid ühte andmete salvestusruumi, mis näib kasutajale üksikeksemplarina. Kasutajad saavad luua ühenduse eksemplariga ega pea teadma, milline server on selles eksemplaris parajasti aktiivne. Kui üks server nurjub või vajab hoolduseks ühenduseta režiimi kasutamist, siis kasutuskogemus ei muutu. Igat klastris olevat serverit jälgib talitlusimpulssi kasutades klastrihaldur, mis avastab, millal klastris oleva aktiivse serveriga ühendus katkeb, ja üritab sujuvalt vahetada järgmisele klastris olevale serverile. Vahetuse ajal esineb viivitus.
Lisateavet leiate teemadest Always On Failover Cluster Instances (Alati on tõrkesiirdeklastri eksemplarid ) ja Always On availability groups (Alati saadaval): suur kättesaadavus ja avariijärgse taaste lahendus.
SQL Serveri turve
Ehkki Accessi andmebaasi on võimalik kaitsta usalduskeskuse abil ja andmebaasi krüptimisega, on SQL Serveris rohkem täiustatud turvafunktsioone. Vaatame kolme accessi kasutaja jaoks silmapaistvat funktsiooni. Lisateavet leiate teemast SQL Server turvamine.
Andmebaasi autentimine
SQL Serveris on neli andmebaasi autentimise meetodit. Igat neist saab täpsustada ODBC-ühenduse stringis. Lisateavet leiate artiklist Azure’i SQL Serveri andmebaasist andmete importimine või nendega linkimine. Igal meetodil on oma eelised.
Integreeritud Windowsi autentimine Kasutage kasutaja valideerimiseks, turberollideks ja kasutajate piiramiseks funktsioonide ja andmetega Windowsi identimisteavet. Saate kasutada ära domeeni identimisteavet ja hõlpsasti hallata oma rakenduses kasutaja õigusi. Soovi korral sisestage teenusesubjektide nimed (SPN-id). Lisateavet leiate teemast Autentimisrežiimi valimine.
SQL Server autentimiskasutajad peavad andmebaasis häälestatud identimisteabega ühenduse looma, sisestades seansi ajal andmebaasi esmakordsel juurdepääsul sisselogimistunnuse ja parooli. Lisateavet leiate teemast Autentimisrežiimi valimine.
Azure Active Directory integreeritud autentimine Azure SQL Server andmebaasiga ühenduse loomiseks Azure Active Directory abil. Kui olete konfigureerinud Azure Active Directory autentimise, ei pea te enam täiendavalt sisse logima ega parooli kasutama. Lisateavet leiate teemast SQL-andmebaasiga ühenduse loomine Azure Active Directory autentimise abil.
Active Directory parooli autentimine Looge ühendus Azure Active Directorys häälestatud identimisteabega, sisestades sisselogimisnime ja parooli. Lisateavet leiate teemast SQL-andmebaasiga ühenduse loomine Azure Active Directory autentimise abil.
Otsa Kasutage ohutuvastust, et saada teatisi anomaalse andmebaasitegevuse kohta, mis tähistab Azure SQL Server andmebaasi võimalikke turbeohte. Lisateavet leiate artiklist SQL-andmebaasi ohutuvastus.
Rakenduse turve
SQL Serveris on kaks rakendusetasemel turbefunktsiooni, mida saab Accessis kasutada.
Andmete dünaamiline maskimine Varjake tundlikku teavet, maskes selle õigusteta kasutajate eest. Näiteks saate maskida nii osaliselt kui ka täielikult sotsiaalkindlustuse numbrit.
A partial data mask |
A full data mask |
|---|
Andmete maski määratlemiseks on mitu võimalust ja neid saab rakendada ka eri andmetüüpide puhul. Andmete maskimine on kindlaks määratud kasutajatele tabeli- ja veerutasemel poliitikapõhine ning seda kohaldatakse päringule reaalajas. Lisateavet leiate teemast Andmete dünaamiline maskimine.
Reataseme turve Row-Level Security abil saate reguleerida juurdepääsu kindlatele andmebaasiridadele, millel on kasutajatunnuste põhjal delikaatne teave. Andmebaasisüsteem rakendab juurdepääsupiiranguid, mis muudavad turbesüsteemi usaldusväärsemaks ja töökindlamaks.
. Turbepredikaate on kahte tüüpi.
- Filtripredikaat filtreerib ridasid päringust. Filter on läbipaistev ja lõppkasutaja pole ühestki filtreerimisest teadlik.
- Plokkpredikaat takistab volitamata toimingut ja kuvab erandi, kui toimingut ei saa sooritada.
Lisateavet leiate teemast Reataseme turve.
Andmete kaitsmine krüptimise abil
Andmeid saab jõudeolekus, edastamisel ja kasutamise ajal kaitsta ilma andmebaasi jõudlust mõjutamata. Lisateavet leiate teemast SQL Server krüptimine.
Krüptimine jõudeolekus Selleks et kaitsta isikuandmeid ühenduseta andmekandja rünnakute eest füüsilisel salvestuskihil, kasutage krüptimist puhkerežiimis, mida nimetatakse ka läbipaistvaks andmekrüptimiseks (TDE). See tähendab, et andmed on kaitstud isegi siis, kui füüsiline andmekandja varastatakse või see likviteeritakse valesti. TDE krüptib ja dekrüptib reaalajas andmebaase, varukoopiaid ja tehingulogisid, ilma et rakenduses oleks vaja teha muudatusi.
Krüptimine edastamisel Võrgu kaudu edastatavate andmete krüptimise ja nipsamise eest kaitsmiseks saate krüptida võrgus edastatavaid andmeid. Pakkumaks väga turvalist suhtlust, toetab SQL Server transpordikihi turvet (Transport Layer Security, TLS) 1.2. Ebausaldusväärsetes võrkudes suhtluse kaitsmiseks kasutatakse ka tabelina esitatud andmevoo (Tabular Data Stream, TDS) protokolli.
Krüptimine on kliendis kasutusel Kasutuse ajal isikuandmete kaitsmiseks Always Encrypted on see funktsioon, mida soovite kasutada. Draiver krüptib ja dekrüptib klientarvutis isikuandmeid, kuid ei avalda andmebaasimootorile krüptimisvõtmeid. Seetõttu on krüptitud andmed nähtavad ainult inimestele, kes vastutavad andmete haldamise eest, mitte teistele suure õigustega kasutajatele, kellel ei tohiks olla juurdepääsu. Olenevalt valitud krüptimistüübist võib funktsioon Always Encrypted mõnda andmebaasifunktsiooni, nt otsimine, rühmitamine ja krüptitud veergude indekseerimine, piirata.
Privaatsusega seotud probleemide lahendamine
Privaatsusega seotud probleemid on nii levinud, et Euroopa Liit on määratlenud isikuandmete kaitse üldmääruse (GDPR) kaudu õiguslikud nõuded. Õnneks vastab SQL Serveri tagasüsteem neile nõuetele. Isikuandmete kaitse üldmäärust rakendatakse kolme sammu abil.
1: nõuetele vastavuse riskide hindamine ja haldamine
Isikuandmete kaitse üldmäärus nõuab, et tuvastaksite ja inventeeriksite tabelites ja failides olevaid isikuandmeid. Need andmed võivad olla näiteks nimi, foto, meiliaadress, pangaandmed, postitused sotsiaalmeedias, terviseandmed ja isegi IP-aadress.
Uus SQL Server Management Studio sisseehitatud tööriist SQL-andmete tuvastamine ja liigitus aitab teil delikaatsed andmed tuvastada, liigitada, silte ja aruandeid luua, rakendades veergudele kaks metaandmete atribuuti.
- Sildid Andmete tundlikkuse määratlemine.
- Teabetüübid Veerus talletatavate andmete tüüpide kohta täiendava granulaarsuse pakkumiseks.
Võite kasutada ka täistekstiotsingut, mis sisaldab funktsioonide CONTAINS ja FREETEXT predikaatide kasutamist ning selliseid reakomplektipõhiseid funktsioone nagu CONTAINSTABLE ja FREETEXTTABLE, mida saab kasutada lausega SELECT. Täistekstiotsingu abil saate otsida tabelitest sõnu, sõnakombinatsioone või sõnavariatsioone, nagu näiteks sünonüümid ja muutelõppudega vormid. Lisateavet leiate teemast Täistekstiotsing.
2. samm: Isikuandmete kaitsmine
Isikuandmete kaitse üldmäärus nõuab, et kaitseksite isikuandmeid ja piiraksite juurdepääsu nendele. Peale tavaliste sammude, mida kasutate oma võrgule ja ressurssidele juurdepääsu haldamiseks (nt tulemüüri sätted), saate andmetele ligipääsu juhtimiseks kasutada ka järgmisi SQL Serveri turbefunktsioone:
- SQL Serveri autentimine, mis aitab hallata kasutaja identiteeti ja ennetada volitamata juurdepääsu.
- Reatasemel turve, mis aitab piirata juurdepääsu tabeli ridadele. See põhineb kasutaja ja andmete vahelisel seosel.
- Andmete dünaamiline maskimine, mis aitab piirata isikuandmete avaldamist, maskides need õigusteta kasutajate eest.
- Krüptimine, mis tagab, et isikuandmed on ülekande ja salvestamise ajal kaitstud ning kaitstud ka turvarikke (sh serveripoolse) eest.
Lisateavet leiate artiklist SQL Serveri turve.
3. samm: Päringutele tõhusalt reageerimine
Isikuandmete kaitse üldmäärus nõuab, et säilitaksite isikuandmete töötlemise kirjed ning teeksite kirjed järelevalveasutuse nõude korral neile ligipääsetavaks. Kui tekib tõrkeid, nt juhuslik andmete avaldamine, võimaldavad kaitsekontrollid kiirelt reageerida. Aruande edastamiseks peavad andmed olema kiiresti saadaval. Näiteks nõuab GDPR, et isikuandmetega seotud rikkumisest teatatakse järelevalveasutusele "hiljemalt 72 tundi pärast sellest teada saamist".
SQL Server 2017 aitab teid teatamise juures mitmel eri viisil:
- SQL Server Audit aitab tagada andmebaasile juurdepääsu ja töötlustegevuste püsivate kirjete olemasolu. See sooritab täpselt määratletud auditi, mis jälgib andmebaasitoiminguid ning aitab mõista ja määratleda potentsiaalseid ohte, kuritarvituse kahtlusi või turberikkumisi. Saate hõlpsasti sooritada andmeekspertiisi.
- SQL Serveri ajalised tabelid on süsteemi versioonitud kasutajatabelid, mille eesmärk on talletada kogu andmete muutmiste ajalugu. Saate neid kasutada lihtsaks aruandluseks ja ajapunktipõhiseks analüüsiks.
- SQL-i haavatavuse hindamine aitab tuvastada turbe- ja õigusteprobleeme. Kui tuvastatakse probleem, saate minna süvitsi andmebaasiskanni aruannetesse, et leida selle lahendamiseks võimalusi.
Lisateavet leiate teemadest Usaldusplatvormi (e-raamat) loomine ja GDPR-i nõuetele vastavuse teekonna loomine.
Andmebaasi hetktõmmiste loomine
Andmebaasi hetktõmmis on SQL Serveri andmebaasi kirjutuskaitstud staatiline vaade mingil kindlal ajahetkel. Kuigi andmebaasi hetktõmmise loomiseks on võimalik Accessi andmebaasifaili kopeerida, pole Accessil sellist integreeritud metoodikat nagu SQL Serveril. Andmebaasi hetktõmmist saab kasutada selleks, et kirjutada aruannet, mis põhineb andmebaasi hetktõmmise loomise ajal esinenud andmetel. Samuti saate andmebaasi hetktõmmist kasutada selleks, et säilitada ajaloolisi andmeid, näiteks iga finantskvartali jaoks ühte, mida kasutatakse perioodilõpu aruannete kokkuvõtteks. Soovitame järgmisi parimaid tavasid:
- Hetktõmmisele nime andmine Iga andmebaasi hetktõmmis nõuab kordumatut andmebaasinime. Hõlpsamaks tuvastamiseks lisage nimele eesmärk ja ajavahemik. Näiteks, kui soovite teha AdventureWorksi andmebaasist hetktõmmist kolm korda päevas vahemikus kella 6.00–18.00 iga kuue tunni järel, pange neile nimeks AdventureWorks_snapshot_0600, AdventureWorks_snapshot_1200 ja AdventureWorks_snapshot_1800.
- Hetktõmmiste arvu piiramine Iga andmebaasi hetktõmmis jääb alles seniks, kuni see konkreetselt kaob. Kuna iga hetktõmmis kasvab edasi, võib juhtuda, et soovite kettaruumi säästa, kustutades vanema hetktõmmise pärast uue hetktõmmise loomist. Näiteks kui teete aruandeid iga päev, hoidke andmebaasi hetktõmmist alles 24 tundi ning seejärel loobuge sellest ja asendage hetktõmmis uuega.
- Loo ühendus õige hetktõmmisega Andmebaasi hetktõmmise kasutamiseks peab Accessi eesserver teadma õiget asukohta. Kui asendate olemasoleva hetktõmmise uuega, peate Accessi ümber suunama uue hetktõmmise juurde. Kui soovite veenduda, et loote ühenduse õige andmebaasi hetktõmmisega, lisage Accessi tagaserverisse loogikat.
Andmebaasi hetktõmmise loomiseks tehke järgmist.
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;
Lisateavet leiate teemast Andmebaasi hetktõmmised (SQL Server)..
Samaaegsuse juhtimine
Kui mitu inimest proovib andmebaasis andmeid samal ajal muuta, on vaja kontrollisüsteemi, mis tagab, et ühe inimese tehtud muudatused ei mõjuta teise inimese muudatusi ebasoodsalt. Seda nimetatakse samaaegsuse juhtimiseks, mille puhul esineb kaks peamist lukustamise strateegiat: optimistlik ja pessimistlik. Lukustamise abil saab takistada kasutajaid muutmast andmeid nii, et see mõjutaks teisi kasutajaid. Peale selle aitab lukustamine tagada andmebaasi tervikluse, eriti nende päringute puhul, mis võivad muidu anda ootamatuid tulemeid. Selles, kuidas Access ja SQL Server samaaegsuse juhtimise strateegiaid rakendavad, on olulisi erinevusi.
Accessis on lukustamise strateegia vaikimisi optimistlik ja annab esimesele inimesele, kes proovib kirjesse kirjutada, lukustamise omandiõiguse. Access kuvab järgmisele inimesele, kes proovib samal ajal samasse kirjesse kirjutada, dialoogiboksi Write Conflict (Kirjutuskonflikt). Konflikti lahendamiseks võib teine inimene kirje salvestada, kopeerida selle lõikelauale või muudatustest loobuda.
Samaaegsuse juhtimise strateegiate muutmiseks saab kasutada ka atribuutiRecordLocks. See atribuut mõjutab vorme, aruandeid ja päringuid ning sellel on kolm sätet:
- Lukke pole Vormil saavad kasutajad proovida redigeerida sama kirjet samaaegselt, kuid võidakse kuvada dialoogiboks Kirjutamiskonflikt . Aruannetes olevaid kirjeid aruande eelvaate või printimise ajaks ei lukustata. Päringu kirjeid ei lukustata päringu käitamise ajaks. See on Accessi viis rakendada optimistlikku lukustamist.
- Kõik kirjed Kõik aluseks oleva tabeli või päringu kirjed lukustatakse, kui vorm on avatud vormivaates või andmelehevaates, aruande eelvaates või printimisel või päringu käitamise ajal. Kasutajad saavad lukustamise ajal andmeid lugeda.
- Redigeeritud kirje Vormide ja päringute puhul lukustatakse kirjeleht kohe, kui mõni kasutaja alustab kirje suvalise välja redigeerimist, ja jääb lukuks seniks, kuni kasutaja siirdub mõnele muule kirjele. Seega saab kirjet redigeerida korraga ainult üks kasutaja. See on Accessi moodus rakendada pessimistlikku lukustamist.
Lisateavet leiate teemadest Dialoogiboks Kirjutuskonflikt ja atribuut RecordLocks.
SQL Serveris töötab samaaegsuse juhtimine järgmiselt:
- Pessimistlik Kui kasutaja on sooritanud toimingu, mis põhjustab lukustuse rakendamise, ei saa teised kasutajad teha toiminguid, mis oleksid lukuga vastuolus, kuni omanik selle vabastab. Sellist samaaegsuse juhtimist kasutatakse peamiselt keskkondades, kus on suur andmekonfliktide arv.
- Optimistlik Optimistliku samaaegsuse juhtimise korral ei lukusta kasutajad andmeid, kui nad neid loevad. Kui kasutaja andmeid värskendab, siis süsteem kontrollib, kas teine kasutaja on pärast andmete lugemist neid muutnud. Kui teine kasutaja on andmeid värskendanud, tekib tõrge. Tavaliselt pöörab tõrke saanud kasutaja tehingu tagasi ja alustab uuesti. Sellist samaaegsuse juhtimist kasutatakse peamiselt keskkondades, kus on väike andmekonfliktide arv.
Saate määrata samaaegsuse juhtimise tüübi, valides mitu tehingu isoleerimistaset, mis kehtestavad tehingu kaitsetaseme. Selle määravad teiste tehingute tehtud muudatused, kasutades lauset SET TRANSACTION:
SET TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SNAPSHOT
| SERIALIZABLE
}
| Isoleerimistase | Kirjeldus |
|---|---|
| Lugemine kinnitamata | Tehinguid isoleeritakse ainult nii palju, et tagada, et füüsiliselt rikutud andmeid ei loeta. |
| Lugemine kinnitatult | Tehingud saavad lugeda andmeid, mida enne luges teine tehing, ega pea ootama kuni esimene tehing on lõpetatud. |
| Korratav lugemine | Lugemise ja kirjutamise lukud esinevad valitud andmete puhul kuni tehingu lõpuni, kuid esineda võib fantoomlugemist. |
| Hetktõmmis | Kasutab reaversiooni, et pakkuda tehingutasemel lugemisühtsust. |
| Sarjadesse jaotatav | Tehingud on üksteisest täielikult eraldatud. |
Lisateavet leiate teemast Tehingu lukustamise ja rea versioonimise juhend.
Päringu jõudluse parandamine
Pärast seda, kui teie Accessi läbiv päring on hakanud tööle, kasutage ära SQL Serveri keerukamaid võimalusi, mis parandavad efektiivsust.
Erinevalt Accessi andmebaasist pakub SQL Server paralleelpäringuid, mis aitavad arvutite puhul, millel on rohkem kui üks mikroprotsessor (CPU), optimeerida päringute täitmis- ja indekseerimistoiminguid. Kuna SQL Server saab mitut süsteemi töölõime kasutades sooritada päringu- või indekseerimistoiminguid paralleelselt, on võimalik toiming lõpetada kiirelt ja efektiivselt.
Päringud on olulised komponendid, mis aitavad parandada andmebaasilahenduse üldist jõudlust. Halvad päringud käitavad lõputult, aeguvad ning kasutavad ära ressursid, nagu näiteks protsessorid, mälu ja Network BANDIT-i. See takistab omakorda olulise äriteabe kättesaadavust. Isegi ainult üks halb päring võib andmebaasile tekitada tõsiseid jõudlusprobleeme.
Lisateavet leiate teemast Kiirem päringute esitamine SQL Server abil (e-raamat).
Päringu optimeerimine
Päringu jõudluse analüüsimiseks ja täiustamiseks töötavad koos mitu tööriista: päringu optimeerija, käivitamisplaanid ja päringusalv.
? Päringuoptimeerija
Päringuoptimeerija on SQL Serveri üks kõige olulisemaid komponente. Päringuoptimeerija abil saab päringut analüüsida ja määrata kindlaks kõige efektiivsem viis vajalikele andmetele ligipääsemiseks. Päringuoptimeerija sisend koosneb päringust, andmebaasi skeemist (tabeli- ja indeksimääratlus) ning andmebaasi statistikast. Päringuoptimeerija väljund on käivitamisplaan.
Lisateavet leiate teemast SQL Server päringu optimeerija.
Käivitamisplaan
Käivitamisplaan on määratlus, mis järjestab lähtetabelid, kuhu peab juurde pääsema, ning meetodid, mida kasutati igast tabelist andmete ekstraktimiseks. Optimeerimine on protsess, mille käigus valitakse mitme võimaliku plaani hulgast üks käivitamisplaan. Igal võimalikul käivitamisplaanil on seostatud kulu, mis oleneb kasutatud arvutusressurside hulgast, ning päringuoptimeerija valib selle, millel on kõige väiksem eeldatav kulu.
SQL Server peab ka dünaamiliselt kohanema andmebaasis olevate tingimuste muutmise järgi. Päringu käivitamisplaanis esinevad regressioonid võivad jõudlust tugevalt mõjutada. Olenevalt andmebaasi uuest olekust võivad andmebaasi teatud muudatused põhjustada seda, et käivitamisplaan muutub ebaefektiivseks või kehtetuks. SQL Server tuvastab muudatused, mis muudavad käivitamisplaani kehtetuks, ning märgib plaani kehtetuks.
Seejärel peab järgmise ühenduse tarvis, mis päringu käivitab, uuesti kompileerima uue plaani. Tingimused, mis muudavad plaani kehtetuks, on:
- Päringu viidatud muudatused, mis on tehtud tabelile või vaatele (ALTER TABLE ja ALTER VIEW).
- Muudatused indeksites, mida kasutab käivitamisplaan.
- Värskendused statistikas, mida kasutab käivitamisplaan. Need on loodud kas selgelt lausest, näiteks UPDATE STATISTICS, või automaatselt.
Lisateavet leiate teemast Käivitamisplaanid.
Päringusalv
Päringusalv annab ülevaate käivitamisplaani valikust ja jõudlusest. See lihtsustab jõudluse tõrkeotsingut, aidates kiiresti tuvastada jõudluserinevusi, mida põhjustavad käivitamisplaani muudatused. Päringusalv kogub telemeetriaandmeid, näiteks päringute ajalugu, plaane, käitusajastatistikat ja ooteajastatistikat. Päringusalve rakendamiseks kasutage lauset ALTER DATABASE:
ALTER DATABASE AdventureWorks2012 SET QUERY_STORE = ON;
Lisateavet leiate teemast Jõudluse jälgimine päringusalve abil.
Automaatne plaaniparandus
Võib-olla kõige lihtsam viis päringujõudluse parandamiseks on automaatne plaaniparandus, mis on Azure’i SQL-andmebaasi funktsioon. Saate selle lihtsalt sisse lülitada ja seejärel lasta sel töötada. See jälgib ja analüüsib pidevalt käivitamisplaani, tuvastab problemaatilised käivitamisplaanid ning lahendab automaatselt jõudlusprobleeme. Automaatne plaaniparandus kasutab taustal neljaetapilist strateegiat: õpi, kohandu, kinnita ja korda.
Lisateavet leiate teemast Automaatne häälestamine.
Päringu adaptiivne töötlemine
Kiiremaid päringuid saab ka siis, kui täiendate SQL Server 2017-le, millel on uus funktsioon nimega päringu adaptiivne töötlemine. Olenevalt käitusaja tunnustest kohandab SQL Server päringuplaani valikuid.
Kardinaalsushinnang väljendab ligikaudselt nende ridade arvu, mida käivitamisplaani igas etapis töödeldakse. Ebatäpsete hinnangute tulemus võib olla aeglane päringu reaktsiooniaeg, ebavajalik ressursi kasutamine (mälu, CPU ja IO) ning vähenenud läbilaskevõime ja samaaegsus. Rakenduse töökoormuse omaduste kohandamiseks kasutatakse kolme meetodit:
- Partiirežiimi mälu andmise tagasiside Kehvad kardinaalsushinnangud võivad põhjustada päringute "lekkimine kettale" või võtta liiga palju mälu. SQL Server 2017 kohandab mälujaotuseid, võttes aluseks täitmistagasiside, eemaldab kettale lekkimised ja parandab korduvate päringute puhul samaaegsust.
- Paketirežiimi adaptiivsed ühendamised Adaptiivsed ühendamised valivad käitusajal käitusajal dünaamiliselt parema sisemise ühendamise tüübi (pesastatud tsükli ühendamised, ühendamised või räsiühendused), võttes aluseks tegelikud sisendread. Järelikult saab plaani täitmise ajal dünaamiliselt lülituda paremale liitmisstrateegiale.
- Interleaved execution Mitmelauselisi tabelipõhiseid funktsioone käsitletakse päringutöötluse korral tavaliselt musta kastina. SQL Server 2017 oskab paremini hinnata ridade arvu, et täiustada allavoolu toiminguid.
Selleks et töökoormused oleksid adaptiivse päringutöötluse jaoks automaatselt kõlblikud, saate määrata andmebaasi ühilduvustasemeks 140:
ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 140;
Lisateavet leiate teemast Intelligentne päringutöötlus SQL-andmebaasides.
Päringuviisid
SQL Serveris saab päringuid teha mitmel viisil ja neil kõigil on oma eelised. Neid on kasulik teada, sest siis saate teha oma Accessi lahenduse jaoks õige valiku. Parim viis TSQL-päringute loomiseks on neid interaktiivselt redigeerida ja testida, kasutades SQL Server Management Studio (SSMS) Transact-SQL-i redaktorit, millel on intellisense, mis aitab teil õigeid märksõnu valida ja süntaksivigu otsida.
Vaated
SQL Serveris on vaade nagu virtuaaltabel, kus vaate andmed pärinevad ühest või mitmest tabelist või muudest vaadetest. Sellegipoolest viidatakse vaadetele päringutes samamoodi nagu tabelitele. Vaated võivad peita päringute keerukust ja kaitsta andmeid, piirates ridade ja veergude kogumit. Siin on näide lihtsast vaatest:
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;
Optimaalse jõudluse tagamiseks ja vaate tulemite redigeerimiseks looge indekseeritud vaade, mis püsib andmebaasis nagu tabel, millele on eraldatud salvestusruum ja mida saab päringusse kaasata nagu mis tahes tabelit. Kui soovite seda Accessis kasutada, siis linkige sellele samamoodi nagu lingite tabelile. Siin on näide indekseeritud vaatest:
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);
Siiski on olemas teatud piirangud. Andmeid ei saa värskendada, kui see mõjutab mitut põhitabelit või kui vaade sisaldab kokkuvõttefunktsioone või DISTINCT-klauslit. Kui SQL Server annab tõrketeate, mis ütleb, et ei tea, millist kirjet kustutada, siis võib juhtuda, et peate vaatesse lisama kustutuspäästiku. Samuti ei saa te kasutada ORDER BY-klauslit nagu Accessi päringu puhul.
Lisateavet leiate teemadest Vaated ja Indekseeritud vaadete loomine.
Salvestatud protseduurid
Salvestatud protseduur on rühm ühest või mitmest TSQL-i lausest, mis võtavad sisendparameetreid, tagastavad väljundparameetreid ja näitavad oleku väärtust kasutades edu või nurjumist. Need toimivad vahekihina Accessi eesserveri ja SQL Serveri tagaserveri vahel. Salvestatud protseduurid võivad olla sama lihtsad kui SELECT-lause või sama keerukad kui mis tahes programm. Üks näide on toodud siin:
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;
Kui kasutate salvestatud protseduuri Accessis, siis tagastatakse tavaliselt tulem, mis on vormile või aruandele tagasi seatud. Siiski võib see teha muid toiminguid, mis ei tagasta tulemeid (nt DDL- või DML-laused). Läbiva päringu kasutamisel veenduge, et määrate atribuudi Returns Records (Tagastab kirjed) sobivalt.
Lisateavet leiate teemast Salvestatud protseduurid.
Levinud tabeliavaldised
Levinud tabeliavaldised (Common Table Expressions, CTE) on justkui ajutine tabel, mis loob nimega tulemite kogumi. See on olemas ainult ühe päringu või DML-lause täitmiseks. CTE on integreeritud samale koodireale kui SELECT-lause või DML-lause, mis seda kasutab, samas kui ajutise tabeli või vaate loomine ja kasutamine on tavaliselt kaheetapiline protsess. Üks näide on toodud siin:
-- 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;
CTE-l on mitu eelist, sealhulgas:
- Kuna CTE-d on ajutised, ei pea te neid looma püsivate andmebaasiobjektidena nagu vaated.
- Samale CTE-le on võimalik päringus või DML-lauses viidata rohkem kui üks kord, mistõttu on kood lihtsamini hallatav.
- Kursori määratlemiseks saab kasutada päringuid, mis viitavad CTE-le.
Lisateavet leiate teemast WITH common_table_expression.
Kasutaja määratletud funktsioonid (UDF)
Kasutaja määratletud funktsioon (user-defined function, UDF) saab teha päringuid ja arvutusi ning tagastada kas skalaarväärtusi või andmetulemite kogumeid. Need on samasugused nagu funktsioonid programmeerimiskeeltes, mis aktsepteerivad parameetreid, sooritavad selliseid toiminguid nagu keerukad kalkulatsioonid ja tagastavad selle toimingu tulemi väärtusena. Üks näide on toodud siin:
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';
UDF-il on teatud piirangud. Näiteks ei saa nad kasutada teatud mittedeterministlikke süsteemifunktsioone, DML- või DDL-lauseid ega dünaamilisi SQL-päringuid.
Lisateavet leiate teemast Kasutaja määratletud funktsioonid.
Võtmete ja indeksite lisamine
Olenemata sellest, millist andmebaasisüsteemi kasutatakse, käivad võtmed ja indeksid käsikäes.
Klahvid
SQL Serveris tuleb veenduda, et iga tabeli jaoks luuakse primaarvõtmed ja iga seotud tabeli jaoks välisvõtmed. SQL Serveris on Accessi automaatnumbri andmetüübiga samaväärne funktsioon atribuut IDENTITY, mille abil saab luua võtmeväärtusi. Kui rakendate seda atribuuti mis tahes arvuveerule, muutub see kirjutuskaitstuks ja seda hakkab haldama andmebaasisüsteem. Kui lisate kirje tabelisse, mis sisaldab IDENTITY-veergu, suurendab süsteem IDENTITY-veeru väärtust automaatselt 1 võrra ja alates 1-st, kuid neid väärtusi on võimalik argumentidega reguleerida.
Lisateavet leiate teemast CREATE TABLE, IDENTITY (Atribuut).
Indeksid
Nagu tavaliselt on indeksivalik tasakaalustav toiming päringukiiruse ja värskenduskulu vahel. Accessis on ühte tüüpi indeks, kuid SQL Serveris on neid tüüpe kaksteist. Õnneks saab kasutada päringuoptimeerijat, mis aitab usaldusväärselt valida kõige tõhusama indeksi. Azure’i SQL-is on võimalik kasutada automaatset indeksihaldust, automaathäälestuse funktsiooni, mis soovitab, milliseid indekseid lisada või kustutada. Erinevalt Accessist tuleb SQL Serveris välisvõtmetele luua oma indeksid. Selleks et päringu jõudlust parandada, saab luua indekseid ka indekseeritud vaates. Indekseeritud vaate tagurpidi on vaate põhitabelites andmete muutmisel suurem üldkulu, kuna vaadet tuleb samuti värskendada. Lisateavet leiate teemast indeksiarhitektuuri ja kujundusjuhendi SQL Server jaindeksid.
Tehingute sooritamine
Veebipõhiste tehingute protsessi (Online Transaction Process, OLTP) sooritamine on Accessi kasutades keeruline, kuid SQL Serveris suhteliselt lihtne. Tehing on üks tööüksus, mis teeb õnnestumise puhul kõik andmemuudatused, kuid mis ebaõnnestumise korral pöörab muudatused tagasi. Tehingul peab olema neli atribuuti, mida tihti nimetatakse ACID-iks:
- Aatomiteet Tehing peab olema aatomtööüksus; kas kõik selle andmete muudatused tehakse või neid ei tehta.
- Järjepidevus Kui tehing on lõpule viidud, tuleb kõik andmed jätta ühtsesse olekusse. See tähendab, et kohaldatakse kõiki andmete tervikluse reegleid.
- Isolatsiooni Samaaegsete tehingute tehtud muudatused on praegusest tehingust isoleeritud.
- Vastupidavus Pärast tehingu lõpuleviimist on muudatused püsivad ka süsteemitõrke korral.
Tehingut kasutatakse selleks, et tagada garanteeritud andmete terviklus, näiteks pangaautomaadist raha väljavõtmise või palga automaatse deposiidi korral. Võimalik on teha otseseid, kaudseid ja partiipõhiseid tehinguid. Järgnevalt on toodud kaks TSQL-i näidet:
-- 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;
Lisateavet leiate teemast Tehingud.
Piirangute ja päästikprotsesside kasutamine
Kõigil andmebaasidel on võimalused andmete tervikluse säilitamiseks.
Piirangud
Accessis saate rakendada tabeliseostes viitamisterviklust, kasutades välisvõtme ja primaarvõtme sidumist, värskenduste ja kustutamiste virnastamist ning valideerimisreegleid. Lisateavet leiate artiklitest Tabeliseoste juhend ja Andmesisestuse piiramine valideerimisreeglite abil.
SQL Serveris kasutatakse piiranguid UNIQUE ja CHECK, andmebaasiobjekte, mis rakendavad SQL Serveri tabelites andmete terviklust. Selleks et kontrollida, kas väärtus kehtib ka teises tabelis, kasutatakse välisvõtme piirangut. Kontrollimaks seda, kas veerus olev väärtus on konkreetses vahemikus, saab kasutada piirangut CHECK. Need objektid on teie esimene kaitseliin ja on loodud tõhusalt töötama. Lisateavet leiate teemadest Kordumatud piirangud ja Kontrolli piiranguid.
Päästikprotsessid
Accessis pole andmebaasipäästikuid. SQL Serveris saab kasutada päästikprotsesse selleks, et rakendada kompleksandmete terviklusreegleid ja käitada seda äriloogikat serveris. Andmebaasipäästik on salvestatud protseduur, mis töötab, kui andmebaasis esinevad teatud toimingud. Päästik on sündmus, näiteks tabelis kirje lisamine või kustutamine, mis vallandab ja seejärel täidab salvestatud protseduuri. Kuigi Accessi andmebaas tagab viitamistervikluse siis, kui kasutaja proovib andmeid värskendada või kustutada, on SQL Serveril arenenum päästikprotsesside kogum. Näiteks on võimalik programmeerida päästikut nii, et see kustutaks kirjeid suurtes kogustes ja tagaks andmete tervikluse. Päästikuid on võimalik lisada isegi tabelitele ja vaadetele.
Lisateavet leiate teemadest Päästikud – DML, Päästikud – DDL ja T-SQL-i päästiku kujundamine.
Arvutatud veergude kasutamine
Accessis saab luua arvutatud veergu (calculated column), lisades selle päringule ja koostades avaldist, näiteks:
Extended Price: [Quantity] * [Unit Price]
SQL Serveris on samaväärne funktsioon computed column (arvutatud veerg), mis on virtuaalne veerg, mida tabelis füüsiliselt ei salvestata, v.a juhul kui veerg on märgitud kui PERSISTED (püsiveerg). Mõlemad arvutatud veerud (calculated ja computed) kasutavad andmeid, mis on pärit avaldise teistest veergudest. Arvutatud (computed) veeru loomiseks lisage see tabelisse. Näiteks:
CREATE TABLE dbo.Products
(
ProductID int IDENTITY (1,1) NOT NULL
, QtyAvailable smallint
, UnitPrice money
, InventoryValue AS QtyAvailable * UnitPrice
);
Lisateavet leiate teemast Arvutatud veergude määramine tabelis.
Andmetele ajatempli lisamine
Andmesisestuse logimiseks lisatakse mõnikord tabeliväli, mis salvestab kirje loomise ajatempli. Accessis saate lihtsalt luua kuupäevaveeru vaikeväärtusega =Now(). Kuupäeva või kellaaja salvestamiseks SQL Server kasutage andmetüüpi datetime2 vaikeväärtusega SYSDATETIME().
Märkus Vältige andmetele ajatempli lisamisega reavahetamist. Keyword timestamp on SQL Serveris rowversioni sünonüüm, kuid kasutama peaks keyword rowversionit. SQL Serveris on rowversion andmetüüp, mis paljastab andmebaasis olevad automaatselt loodud kordumatud kahendnumbrid, ja seda kasutatakse tavaliselt mehhanismina, mis varustab tabeliveerge versioonitempliga. Siiski on andmetüüp rowversion vaid suurenev arv, see ei salvesta kuupäeva ega kellaaega ning see pole loodud veeru ajatempliga varustamiseks.
Lisateavet leiate teemast Rowversion. Lisateavet selle kohta, kuidas kasutada kirjekonfliktide vähendamiseks rowversioni, leiate artiklist Accessi andmebaasi SQL Serverisse migreerimine.
Suurte objektide haldamine
Accessis saate struktureerimata andmeid (nt faile, fotosid ja pilte) hallata andmetüübi Manus abil. SQL Serveri terminoloogias nimetatakse struktureerimata andmeid bloobiks (Binary Large Object, suur kahendobjekt) ja nendega töötamiseks on mitu võimalust:
FILESTREAM Kasutab struktureerimata andmete talletamiseks andmebaasi asemel andmetüüpi varbinary(max). Lisateavet leiate teemast Access FILESTREAM Data with Transact-SQL.
FileTable Salvestab bloobid spetsiaalsetesse tabelitesse Nimega FileTables ja tagab ühilduvuse Windowsi rakendustega nii, nagu need oleksid salvestatud failisüsteemi ja ilma klientrakendusi muutmata. FileTable’i kasutamiseks on vaja FILESTREAM-i. Lisateavet leiate teemast FileTables.
Kaug-bloobisalv (RBS) Talletab suuri binaarseid objekte (BLOB-sid) otse serveri asemel kaubasalvestuslahendustes. See säästab ruumi ja vähendab riistvara ressursse. Lisateavet leiate teemast Binaarse suure objekti (bloobi) andmed.
Hierarhiliste andmetega töötamine
Kuigi relatsioonandmebaasid, nt Access, on väga paindlikud, on nendes hierarhiliste seostega töötamine erand ning tihti on nende puhul vaja SQL-i keerukaid lauseid või koodi. Hierarhiliste andmete hulka kuuluvad näiteks organisatsiooniline struktuur, failisüsteem, keeletingimuste taksonoomia ja veebilehtedevaheliste seoste graaf. SQL Serveris on integreeritud andmetüüp hierarchyid ja hierarhiliste funktsioonide kogum, mis aitavad hierarhilisi andmeid hõlpsasti talletada ja hallata ning teha neis päringuid.
Lisateavet leiate artiklist Hierarhilised andmed ja õppetükk: hierarhia andmetüübi kasutamine.
JSON-teksti töötlemine
JavaScripti objektiesitus (JSON) on veebiteenus, mis kasutab andmete edastamiseks inimese jaoks loetavat teksti kui atribuudi-väärtuse paare asünkroonses brauseri-serveri suhtluses. Näiteks:
{
"firstName": "Mary",
"lastName": "Contrary",
"spouse": null,
"age": 27
}
Accessis pole integreeritud viise JSON-i andmete haldamiseks, kuid SQL Serveris saab JSON-i andmeid sujuvalt talletada, indekseerida ja ekstraktida ning päringuid sooritada. JSON-i teksti saab tabeli- või vorminguandmetes teisendada ja talletada JSON-i tekstina. Näiteks võib teil tekkida soov vormindada päringutulemeid veebirakenduse jaoks JSON-ina või lisada JSON-i andmestruktuure ridadesse ja veergudesse.
Märkus JSON pole VBA-s toetatud. Alternatiivina saate VBA-s tarvitada XML-i, kasutades MSXML-i teeki.
Lisateavet leiate teemast JSON-i andmed SQL Server.
Ressursid
Nüüd on sobiv aeg SQL Serveri ja Transact-SQL-i (TSQL) kohta lisateabe saamiseks. Nagu näete, on mitmeid funktsioone nagu Access, kuid ka võimalusi, mida Access lihtsalt ei ole. Selleks et saaksite oma teadmisi täiendada, on allpool toodud mõni õppematerjal:
| Ressurss | Kirjeldus |
|---|---|
| Päringute esitamine Transact-SQL-iga | Videopõhine kursus |
| Andmebaasimootori õpetused | Õpetused SQL Server 2017 kohta |
| Microsoft Learn | Parktilised õpetused Azure’i kohta |
| SQL Server koolitus ja sertimine | Saage eksperdiks |
| SQL Server 2017 | Peamine sihtleht |
| SQL Server dokumentatsioon | Spikriteave |
| AZURE SQL-andmebaasi dokumentatsioon | Spikriteave |
| Pilveandmete oluline juhend (e-raamat) | Pilve ülevaade |
| SQL Server 2017 andmeleht | Uute funktsioonide visuaalne kokkuvõte |
| Microsofti SQL Server versiooni võrdlus | Funktsioonide kokkuvõte versioonide järgi |
| Microsofti SQL Server Express väljaanded | SQL Server Express 2017 allalaadimine |
| SQL-näidisandmebaasid | Näidisandmebaaside allalaadimine |