Accessiin tutustuminen SQL Serverissä

Liiketoiminnan jatkuvuuden edistäminen

On tärkeää, että Access-ratkaisu toimii mahdollisimman hyvin ilman keskeytyksiä. Access-taustatietokannan käyttäminen rajoittaa kuitenkin mahdollisuuksia. Access-tietokannan varmuuskopiointi on olennaisen tärkeää tietojen suojaamiseksi, mutta se edellyttää, että käyttäjät siirretään offline-tilaan. Lisäksi laitteiston ja ohjelmiston ylläpitopäivitykset, verkko- tai virtakatkokset, laitteistoviat, suojausrikkomukset tai jopa kyberhyökkäykset aiheuttavat suunnittelemattomia käyttökatkoja. Jos haluat minimoida käyttökatkojen määrän ja vaikutuksen liiketoimintaan, voit varmuuskopioida SQL Server -tietokannan sen ollessa käytössä. SQL Serverillä on lisäksi strategiat korkeaa käytettävyyttä (HA) ja järjestelmäpalautusta (DR) varten. Nämä teknologiat tunnetaan yhdessä lyhenteellä HADR. Lisätietoja on artikkeleissa Liiketoiminnan jatkuvuus ja tietokannan palauttaminen sekä Liiketoiminnan jatkuvuuden edistäminen SQL Serverin avulla (e-kirja).

Varmuuskopiointi tietokannan ollessa käytössä

SQL Server hyödyntää verkossa toimivaa varmuuskopiointipalvelua, jota voi käyttää tietokannan ollessa käytössä. Voit tehdä täydellisen varmuuskopioinnin, osittaisen varmuuskopioinnin tai tiedoston varmuuskopioinnin. Varmuuskopio kopioi tiedot ja tapahtumalokit, jotta palautustoiminto on täydellinen. Erityisesti paikallisessa ratkaisussa on huomioitava yksinkertaisten ja täydellisten palautusvaihtoehtojen väliset erot ja se, miten ne vaikuttavat tapahtumalokin kasvuun. Saat lisätietoja kohdasta Palautusmallit.

Useimmat varmuuskopiointitoiminnot suoritetaan heti. Poikkeuksia ovat tiedostojen hallinta ja tietokantaa kutistavat toiminnot. Jos yrität luoda tai poistaa tietokantatiedoston, kun varmuuskopiointitoiminto on käynnissä, toiminto epäonnistuu. Lisätietoja on artikkelissa Varmuuskopioinnin yleiskatsaus.

HADR

Kaksi yleisintä tekniikkaa, joilla saavutetaan korkea käytettävyys ja liiketoiminnan jatkuvuus, ovat peilaus ja klusterointi. SQL Server hyödyntää peilaus -ja klusterointitekniikoita Always On Failover Cluster Instances- ja Always On Availability Groups -konsepteissa.

Peilaus on tietokantatason jatkuvuusratkaisu, joka tukee nopeaa vikasietoisuutta ylläpitämällä valmiustietokantaa eli aktiivisen tietokannan täyttä kopiota tai peilausta erillisessä laitteistossa. Peilaus voi toimia synkronisessa (vahvan suojauksen) tilassa, jossa saapuva tapahtuma on sidottu kaikkiin palvelimiin samanaikaisesti, tai asynkronisessa (suorituskykyisessä) tilassa, jossa saapuva tapahtuma on sidottu aktiiviseen tietokantaan ja se kopioidaan peilattuun paikkaan jossain aiemmin määritetyssä pisteessä. Peilaus on tietokantatason ratkaisu ja toimii vain tietokannoissa, joissa käytetään täyttä palautusmallia.

Klusterointi on palvelintason ratkaisu, joka yhdistää palvelimia yhdeksi tietojen tallennustilan käyttäjäksi, joka näyttää käyttäjälle samalta kuin yksittäinen esiintymä. Käyttäjät muodostavat yhteyden esiintymään tarvitsematta tietoa siitä, mikä esiintymän palvelin on tällä hetkellä aktiivisena. Jos yksi palvelin kaatuu tai on siirrettävä offline-tilaan ylläpitoa varten, käyttökokemus ei muutu. Klusterin hallinta valvoo klusterin jokaista palvelinta sykkeen avulla, joten se havaitsee, kun klusterin aktiivinen palvelin siirtyy offline-tilaan, ja yrittää saumatonta siirtymistä klusterin seuraavaan palvelimeen. Siirtymään liittyy kuitenkin vaihteleva aikaviive.

Lisätietoja on artikkeleissa Always On Failover Cluster Instances ja Always On Availability Groups: korkean käytettävyyden ja palautuksen ratkaisu.

Sivun alkuun

SQL Serverin suojaus

Vaikka voit suojata Access-tietokannan käyttämällä valvontakeskusta ja salaamalla tietokannan, SQL Serverissä on edistyneempiä suojausominaisuuksia. Tutustutaanpa kolmeen ominaisuuteen, jotka erottuvat edukseen Access-käyttäjälle. Lisätietoja on artikkelissa SQL Serverin suojaaminen.

Tietokantatodennus

SQL Serverissä on neljä tietokannan todennusmenetelmää, jotka voit määrittää ODBC-yhteysmerkkijonossa. Lisätietoja on artikkelissa Azure SQL Server -tietokannan tietoihin linkittäminen tai tietojen tuominen Azure SQL Server -tietokannasta. Kullakin menetelmällä on etunsa.

Integroitu Windows-todennus    Käytä Windowsin käyttäjätietoja käyttäjän kelpoisuuden tarkistamiseen, käyttöoikeusrooleihin ja ominaisuuksien tai tietojen käytön rajoittamiseen. Voit hyödyntää toimialuetunnuksia ja hallita helposti käyttöoikeuksia sovelluksessa. Kirjoita halutessasi palvelun päänimet (SPN:t). Lisätietoja on artikkelissa Todennustavan valitseminen.

SQL Server -todennus    Käyttäjien täytyy muodostaa yhteys tietokantaan määritetyillä tunnistetiedoilla antamalla kirjautumistunnus ja salasana, kun he käyttävät tietokantaa ensimmäistä kertaa istunnon aikana. Lisätietoja on artikkelissa Todennustavan valitseminen.

Integroitu Azure Active Directory -todennus    Muodosta yhteys Azure SQL Server -tietokantaan käyttämällä Azure Active Directorya. Kun olet määrittänyt Azure Active Directory -todennuksen, muuta kirjautumistunnusta ja salasanaa ei tarvita. Lisätietoja on artikkelissa Yhteyden muodostaminen SQL-tietokantaan Azure Active Directory -todennuksen avulla.

Active Directory -salasanatodennus    Muodosta yhteys tunnistetiedoilla, jotka on määritetty Azure Active Directoryssa, kirjoittamalla kirjautumisnimi ja salasana. Lisätietoja on artikkelissa Yhteyden muodostaminen SQL-tietokantaan Azure Active Directory -todennuksen avulla.

Vihje    Jos haluat saada ilmoituksia epätavallisista tietokantatoiminnoista, jotka merkitsevät mahdollisia tietoturvauhkia Azure SQL Server -tietokantaan, voit käyttää uhkien tunnistusta. Lisätietoja on artikkelissa SQL-tietokannan uhkien tunnistus.

Sovellussuojaus

SQL Serverissä on kaksi sovellustason suojausominaisuutta, joita voit hyödyntää Accessissa.

Dynaaminen tietojen peittäminen    Piilota luottamukselliset tiedot peittämällä ne käyttäjiltä, joilla ei ole niihin käyttöoikeutta. Voit esimerkiksi peittää henkilötunnuksia joko osittain tai kokonaisuudessaan.

Osittainen tietopeite

Täysi tietopeite

Voit määrittää tietopeitteen useilla tavoilla ja käyttää peitteitä eri tietotyyppeihin. Tietojen peittämisellä tarkoitetaan käyttöoikeuksia, jotka on määritetty tietyille käyttäjille taulukoiden ja sarakkeiden tasolla ja jotka lisätään kyselyihin reaaliajassa. Lisätietoja on artikkelissa Dynaaminen tietojen peittäminen.

Rivitason suojaus    Voit hallita tietokannan tiettyjen rivien käyttöoikeuksia käyttäjän ominaisuuksiin perustuvien luottamuksellisten tietojen avulla käyttämällä rivitason suojausta. Tietokantajärjestelmä soveltaa näitä käyttörajoituksia, mikä tekee käyttöjärjestelmästä luotettavan ja vankan.

Suojauspredikaatteja on kahdenlaisia:

• Suodatinpredikaatti suodattaa kyselyn rivejä. Suodatin on läpinäkyvä, eikä suodattimen käyttö näy loppukäyttäjälle.

• Estopredikaatti estää luvattomat toimenpiteet ja lähettää poikkeuksen, jos toimintoa ei voi suorittaa.

Lisätietoja on artikkelissa Rivitason suojaus.

Tietojen suojaaminen salauksella

Suojaa tiedot levossa, siirrossa ja käytössä vaikuttamatta tietokannan tehokkuuteen. Lisätietoja on artikkelissa SQL Serverin salaaminen.

Salaus levossa    Jos haluat suojata henkilökohtaiset tietosi offline-mediahyökkäyksiltä fyysisessä tallennuskerroksessa, käytä salausta levossa, jota kutsutaan myös läpinäkyväksi tietojen salaamiseksi (TDE). Tämä tarkoittaa sitä, että tietosi suojataan, vaikka fyysinen media varastettaisiin tai poistettaisiin väärin. Toiminto salaa tiedot reaaliajassa ja purkaa salauksen tietokannoista, varmuuskopioista ja tapahtumalokeista ilman, että sinun tarvitsee tehdä muutoksia sovelluksiin.

Salaus siirrossa    Jos haluat suojautua vakoilulta ja mies välissä -hyökkäyksiltä, voit salata verkosta lähetetyt tiedot. SQL Server tukee TLS (Transport Layer Security) 1.2 -suojausta. Tämä on erittäin suojattua viestintää. Taulukkomuotoisen tietovirran (TDS) protokollaa käytetään myös tietoliikenteen suojaamiseksi ei-luotetuissa verkoissa.

Asiakasohjelman käytössä oleva salaus    Jos haluat suojata henkilökohtaisia tietoja tietokantaa käytettäessä, käytä Salaa aina -toimintoa. Asiakastietokoneen ohjain salaa ja purkaa henkilökohtaiset tietosi paljastamatta tietokantamoduulin salausavaimia. Näin salattuja tietoja voivat tarkastella vain tietojen käsittelystä vastaavat henkilöt, eikä muilla erityisen etuoikeutetuilla käyttäjillä ole käyttöoikeutta. Valitusta salaustyypistä riippuen Salaa aina -toiminto voi rajoittaa joitakin tietokannan ominaisuuksia, kuten hakua, ryhmittelyä tai salattujen sarakkeiden indeksointia.

Sivun alkuun

Tietosuojaongelmien käsittely

Tietosuojaongelmat ovat niin laajalle levinneitä, että Euroopan unioni on määrittänyt niitä koskevan tietosuoja-asetuksen (GDPR). SQL Server -taustatietokanta täyttää onneksi hyvin lain asettamat vaatimukset. Ajattele GDPR:n käyttöönotto kolmivaiheiseksi.

Vaihe 1: Yhteensopivuusriskien arvioiminen ja hallinta

GDPR edellyttää, että etsit ja inventoit taulukoissa ja tiedostoissa olevat henkilökohtaiset tiedot. Tämä tieto voi olla esimerkiksi nimi, valokuva, sähköpostiosoite, pankkitiedot, verkossa toimivassa yhteisöpalvelussa lähetetty viesti, lääketieteellisiä tietoja tai jopa IP-osoite.

SQL Server Management Studioon sisäänrakennettu uusi työkalu SQL-tietojen etsimistä ja luokittelua varten auttaa löytämään, luokittelemaan, merkitsemään ja raportoimaan luottamuksellisia tietoja lisäämällä sarakkeisiin kaksi metadatamääritettä:

• Otsikot    Määrittää tietojen luottamuksellisuuden.

• Tietotyypit    Antaa lisää rakeisuutta sarakkeeseen tallennettujen tietojen tyypeistä.

Toinen mahdollinen etsintämekanismi on tekstihaku, jossa käytetään SISÄLTÄÄ- ja VAPAATEKSTI-predikaatteja sekä rivijoukkoarvotettuja funktioita, kuten SISÄLTÄÄTAULUKON ja VAPAATEKSTITAULUKKO, joita käytetään VALITSE-lauseen kanssa. Tekstihaun avulla voit etsiä taulukoista sanoja, sanayhdistelmiä tai sanan muunnelmia, kuten synonyymejä tai taivutusmuotoja. Lisätietoja on artikkelissa Tekstihaku.

Vaihe 2: Henkilökohtaisten tietojen suojaaminen

GDPR edellyttää, että suojaat henkilökohtaiset tiedot ja rajoitat niiden käyttöoikeuksia. Sen lisäksi, että voit tavalliseen tapaan hallita verkon ja resurssien käyttöoikeuksia, kuten palomuurin asetuksia, voit käyttää SQL Serverin suojausominaisuuksia, jotka auttavat hallitsemaan tietojen käyttöä:

• SQL Server -todennus, joka hallitsee käyttäjätietoja ja ehkäisee luvatonta käyttöä.

• Rivitason suojaus, joka rajoittaa taulukon rivien käyttöoikeuksia käyttäjän ja tietojen välisen suhteen perusteella.

• Dynaaminen tietojen peittäminen, joka rajoittaa henkilötietojen näkymistä peittämällä ne käyttäjiltä, joilla ei ole tietojen käyttöoikeuksia.

• Salaus, joka varmistaa, että henkilötiedot suojataan lähetyksen ja varastoinnin aikana ja että ne suojataan vaarantumiselta myös palvelimen puolella.

Lisätietoja on artikkelissa SQL Serverin suojaus.

Vaihe 3: Tehokas vastaaminen pyyntöihin

GDPR edellyttää, että henkilötietojen käsittelytietueet säilytetään ja että nämä tietueet ovat pyydettäessä valvontaviranomaisten saatavilla. Jos ilmenee ongelmia, kuten tietojen tahatonta vapauttamista, suojaushallinnan avulla voit vastata nopeasti. Tietojen on oltava nopeasti saatavilla, kun tarvitaan raportointia. GDPR esimerkiksi edellyttää, että valvontaviranomaisille on ilmoitettava henkilötietojen tietoturvaloukkauksesta "72 tunnin kuluessa sen ilmitulosta."

SQL Server 2017 auttaa sinua raportoimaan useilla tavoilla:

• SQL Server -tarkistuksen avulla voit varmistaa, että tietokannan käytöstä ja käsittelytoiminnoista on olemassa pysyvät tietueet. Se suorittaa hienorakeisen tarkistuksen, joka seuraa tietokannan toimintaa ja auttaa ymmärtämään ja tunnistamaan mahdollisia uhkia, epäiltyjä väärinkäytöksiä tai tietoturvaloukkauksia. Voit tutkia tietojasi helposti.

• SQL Serverin väliaikaiset taulukot ovat järjestelmäversioituja käyttäjätaulukoita, jotka on suunniteltu pitämään kirjaa tietojen muutoksista. Voit käyttää niitä helposti raportteihin ja ajankohdan analysointiin.

• SQL- haavoittuvuuden arvioinnista on apua suojaus- ja käyttöoikeusongelmien havaitsemiseen. Kun ongelma havaitaan, voit myös perehtyä tietokannan tarkistusraportteihin ja etsiä näin ratkaisutoimia.

Lisätietoja on artikkeleissa Luottamuksen käyttöympäristön luominen (e-kirja) ja Matka GDPR-yhteensopivuuteen.

Sivun alkuun

Tietokannan tilannevedosten luominen

Tietokannan tilannevedos on SQL Server -tietokannan vain luku -muotoinen, staattinen näkymä tiettynä ajankohtana. Vaikka voit kopioida Access-tietokantatiedoston ja luoda tietokannan tilannevedoksen, Accessissa ei ole valmista menetelmää SQL Serverin tapaan. Voit käyttää tietokannan tilannevedosta raporttien kirjoittamiseen tilannevedoksen luomishetken tietojen perusteella. Voit käyttää tietokannan tilannevedosta myös aiempien tietojen säilyttämiseen, esimerkiksi tallentamalla tilannevedoksen kustakin vuosineljänneksestä jakson loppuraportteja varten. Suosittelemme seuraavia parhaita käytäntöjä:

• Tilannevedoksen nimeäminen    Kunkin tietokannan tilannevedoksen nimen on oltava yksilöllinen. Lisää nimeen käyttötarkoitus ja ajankohta tunnistamisen helpottamiseksi. Jos esimerkiksi haluat tallentaa AdventureWorks-tietokannan kolme kertaa päivässä 6 tunnin välein kello 6:n ja 18:n välillä 24 tunnin kellon mukaan, anna tilannevedoksille nimeksi AdventureWorks_tilannevedos_0600, AdventureWorks_tilannevedos_1200 ja AdventureWorks_tilannevedos_1800.

• Tilannevedosten määrän rajoittaminen    Jokainen tietokannan tilannevedos säilyy, kunnes se poistetaan tarkoituksenmukaisesti. Koska jokainen tilannevedos jatkaa kasvuaan, kannattaa säästää levytilaa poistamalla vanhempi tilannevedos uuden tilannevedoksen luomisen jälkeen. Jos esimerkiksi teet päivittäisiä raportteja, säilytä tietokannan tilannevedos 24 tunnin ajan, poista se sitten ja korvaa se uudella.

• Yhteyden muodostaminen oikeaan tilannevedokseen    Jos haluat käyttää tietokannan tilannevedosta, Access-edustan on tiedettävä oikea sijainti. Kun korvaat aiemman tilannevedoksen uudella, sinun täytyy uudelleenohjata Access uuteen tilannevedokseen. Lisää logiikka Accessin edustaan varmistaaksesi, että olet muodostamassa yhteyttä oikeaan tietokannan tilannekuvaan.

Näin luot tietokannan tilannevedoksen:

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;  

Lisätietoja on artikkelissa Tietokannan tilannevedokset (SQL Server).

Sivun alkuun

Samanaikaisuuden hallinta

Jos useat käyttäjät yrittävät muokata tietokannan tietoja samanaikaisesti, tarvitaan valvontajärjestelmä, jotta yhden henkilön tekemät muutokset eivät vaikuta toisen henkilön tekemiin muutoksiin. Tätä kutsutaan samanaikaisuuden hallintajärjestelmäksi. On olemassa kaksi peruslukitusstrategiaa: pessimistinen ja optimistinen. Lukitus voi estää käyttäjiä muokkaamasta tietoja tavalla, joka vaikuttaa muihin käyttäjiin. Lukitus varmistaa myös tietokannan eheyden, erityisesti kyselyjen, jotka muuten saattaisivat tuottaa odottamattomia tuloksia. Accessin ja SQL Serverin tavoissa toteuttaa samanaikaisuuden hallintastrategioita on merkittäviä eroja.

Accessin oletusarvoinen lukitusstrategia on optimistinen ja antaa lukituksen omistajuuden ensimmäiselle käyttäjälle, joka yrittää kirjoittaa tietueeseen. Access näyttää Kirjoitusristiriita-valintaikkunan muille henkilöille, jotka yrittävät kirjoittaa tietueeseen samanaikaisesti. Ratkaistakseen ristiriidan toinen henkilö voi tallentaa tietueen, kopioida sen leikepöydälle tai hylätä muutokset.

Voit myös käyttää Tietuelukitus-ominaisuutta samanaikaisuuden hallintastrategian muuttamiseen. Tämä ominaisuus vaikuttaa lomakkeisiin, raportteihin ja kyselyihin, ja siinä on kolme asetusta:

• Ei lukitusta    Lomakkeissa käyttäjät voivat muokata samaa tietuetta samanaikaisesti, mutta Kirjoitusristiriita-valintaikkuna saattaa tulla näkyviin. Tietueita ei lukita raportissa, kun raporttia esikatsellaan tai tulostetaan. Tietueita ei lukita kyselyssä, kun kyselyä suoritetaan. Tämä on Accessin tapa toteuttaa optimistista lukitusta.

• Kaikki tietueet    Pohjana olevan taulukon tai kyselyn kaikki tietueet lukitaan, kun lomake on avoinna lomakenäkymässä tai taulukkonäkymässä, raporttia esikatsellaan tai tulostetaan tai kyselyä suoritetaan. Käyttäjät voivat lukea tietueita lukituksen aikana.

• Muokattu tietue    Lomakkeissa ja kyselyissä tietuesivu lukitaan heti, kun jokin käyttäjä alkaa muokata tietueen jotakin kenttää. Se pysyy lukittuna siihen asti, kunnes toinen käyttäjä siirtyy toiseen tietueeseen. Sen vuoksi vain yksi käyttäjä kerrallaan voi muokata tietuetta. Tämä on Accessin tapa toteuttaa pessimististä lukitusta.

Lisätietoja on artikkeleissa Kirjoitusristiriita-valintaikkuna ja Tietuelukitus-ominaisuus.

SQL Serverissä samanaikaisuuden hallinta toimii tällä tavalla:

• Pessimistinen    Kun käyttäjä suorittaa lukituksen aiheuttavan toiminnon, muut käyttäjät eivät voi suorittaa lukituksen kanssa ristiriidassa olevia toimintoja, ennen kuin omistaja avaa lukituksen. Tätä samanaikaisuuden hallintaa käytetään pääasiassa ympäristöissä, joissa tietojen varausvirhe on korkea.

• Optimistinen    Kun käytetään optimistista samanaikaisuuden hallintaa, käyttäjät eivät lukitse tietoja lukiessaan niitä. Kun käyttäjä päivittää tietoja, järjestelmä tarkistaa, onko toinen käyttäjä muuttanut tietoja niiden lukemisen jälkeen. Jos toinen käyttäjä on päivittänyt tietoja, näyttöön tulee virhesanoma. Yleensä virhesanoman saaneen käyttäjän tapahtuma palautetaan ja aloitetaan alusta. Tätä samanaikaisuuden hallintaa käytetään pääasiassa ympäristöissä, joissa tietojen varausvirhe on matala.

Voit määrittää samanaikaisuuden hallinnan tyypin valitsemalla useita tapahtuman eristystasoja, jotka määrittävät tapahtuman suojaustason muiden tapahtumien MÄÄRITÄ TAPAHTUMA -lauseella tehtyjen muokkausten avulla:

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

Lisätietoja on artikkelissa Opas tapahtumien lukitukseen ja riviversioihin.

Sivun alkuun

Kyselyn suorituskyvyn parantaminen

Kun käytät Access-läpivientikyselyä, voit hyödyntää kehittyneitä tapoja, joilla SQL Serverin suorituskykyä voi tehostaa.

Toisin kuin Access-tietokanta, SQL Server tarjoaa rinnakkaisia kyselyitä kysely- ja indeksointitoimintojen optimoimiseksi tietokoneissa, joissa on enemmän kuin yksi suoritin (CPU). Koska SQL Server pystyy suorittamaan kysely- tai indeksointitoiminnon rinnakkain useiden järjestelmän työsäikeiden avulla, toiminto voidaan suorittaa nopeasti ja tehokkaasti.

Kyselyt ovat tärkeä osa tietokantaratkaisun yleisen suorituskyvyn parantamista. Huonot kyselyt jatkuvat loputtomasti, ne aikakatkaistaan tai ne käyttävät paljon resursseja, kuten suorittimia, muistia ja verkkokaistaa. Tämä heikentää tärkeiden yritystietojen saatavuutta. Jopa yksi virheellinen kysely voi aiheuttaa vakavia suorituskykyongelmia tietokannassasi.

Lisätietoja on artikkelissa Nopeampi kysely SQL Serverissä (e-kirja).

Kyselyjen optimointi

Voit analysoida ja parantaa kyselyjen suorituskykyä useiden työkalujen avulla: Kyselyn optimointitoiminnon, toimeenpanosuunnitelmien ja kyselysäilön.

Kyselyn optimointitoiminto

Kyselyn optimointi on yksi SQL Serverin tärkeimmistä osista. Kyselyn optimointitoiminnolla voit analysoida kyselyä ja määrittää tehokkaimman tavan päästä käsiksi tarvittaviin tietoihin. Kyselyn optimointitoiminnon syötteeseen kuuluvat kysely, tietokantarakenne (taulukoiden ja indeksien määritykset) sekä tietokannan tilastotiedot. Kyselyn optimoijan tuotos on toimeenpanosuunnitelma.

Lisätietoja on artikkelissa SQL Serverin kyselyn optimointitoiminto.

Toimeenpanosuunnitelma

Toimeenpanosuunnitelma on määritelmä, joka järjestää käytettävät lähdetaulukot ja menetelmät tietojen poimimiseen kustakin taulukosta. Optimointi on prosessi, jossa valitaan yksi toimeenpanosuunnitelma mahdollisista useista suunnitelmista. Kuhunkin mahdolliseen toimeenpanosuunnitelmaan liittyy laskentaresurssien aiheuttamia kuluja. Kyselyn optimointitoiminto valitsee suunnitelman, jonka kulut ovat pienimmät.

SQL Serverin on myös sopeuduttava dynaamisesti tietokannan muuttuviin ehtoihin. Taantumat kyselyjen toimeenpanosuunnitelmissa voivat vaikuttaa suorituskykyyn merkittävästi. Tietyt tietokannan muutokset voivat tehdä toimeenpanosuunnitelmasta tehottoman tai virheellisen tietokannan uuden tilan perusteella. SQL Server havaitsee toimeenpanosuunnitelman mitätöivät muutokset ja merkitsee suunnitelman epäkelvoksi.

Seuraavaa kyselyn suorittavaa yhteyttä varten on luotava uusi suunnitelma. Suunnitelman mitätöivät ehdot:

• Kyselyssä viitattuun taulukkoon tai näkymään tehdyt muutokset (MUUTA TAULUKKO ja MUUTA NÄKYMÄ).

• Toimeenpanosuunnitelman käyttämiin indekseihin tehdyt muutokset.

• Toimeenpanosuunnitelman käyttämiin tilastotietoihin tehdyt päivitykset, jotka on luotu joko eksplisiittisesti lauseella, kuten PÄIVITÄ TILASTOTIEDOT, tai automaattisesti.

Lisätietoja on artikkelissa Toimenpanosuunnitelmat.

Kyselysäilö

Kyselysäilössä on tietoa valitusta toimeenpanosuunnitelmasta ja suorituskyvystä. Se yksinkertaistaa suorituskyvyn vianmääritystä auttamalla nopeasti löytämään toimeenpanosuunnitelman muutoksista aiheutuneet suorituskykyerot. Kyselysäilö kerää telemetriatietoja, kuten tietoa aiemmista kyselyistä, suunnitelmista, kestoista ja odotusajoista. Ota kyselysäilö käyttöön käyttämällä MUUTA TIETOKANTAA -lausetta:

ALTER DATABASE AdventureWorks2012 SET QUERY_STORE = ON;

Lisätietoja on artikkelissa Suorituskyvyn valvonta kyselysäilön avulla.

ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 140;

Eri kyselytavat

SQL Serverissä on useita kyselytapoja, joista kussakin on omat etunsa. On tärkeää tietää, mitä ne ovat, jotta voit tehdä oikean valinnan Access-ratkaisuusi. Paras tapa luoda TSQL-kyselyitä on muokata ja testata niitä vuorovaikutteisesti käyttämällä SQL Server Management Studion (SSMS) Transact-SQL-editoria, joka auttaa valitsemaan oikeat avainsanat ja tarkistaa kyselyn syntaksivirheiden varalta.

Näkymät

SQL Serverissä näkymä on kuin virtuaalinen taulukko, jossa näkymän tiedot tulevat yhdestä tai useammasta taulukosta tai muusta näkymästä. Kyselyissä näkymiin viitataan kuitenkin samalla tavalla kuin taulukoihin. Näkymät voivat peittää kyselyjen monimutkaisuuden ja suojata tietoja rajoittamalla rivien ja sarakkeiden joukkoa. Seuraavassa on esimerkki yksinkertaisesta näkymästä:

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;

Jos haluat optimaalisen suorituskyvyn ja mahdollisuuden muokata näkymän tuloksia, luo indeksoitu näkymä, joka säilyy tietokannassa kuten taulukko, jolle on varattu tallennustilaa ja jota voidaan käyttää kyselyissä kuin mitä tahansa taulukkoa. Voit käyttää näkymää Accessissa linkittämällä näkymään samaan tapaan kuin linkittäisit taulukkoon. Seuraavassa on esimerkki indeksoidusta näkymästä:

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);  

Tähän on kuitenkin rajoituksia. Tietoja ei voi päivittää, jos päivitys vaikuttaa useampaan kuin yhteen perustaulukkoon tai jos näkymässä on koostefunktioita tai ERILLINEN-lause. Jos SQL Server palauttaa virhesanoman siitä, ettei tiedä, mikä tietue poistetaan, sinun on ehkä lisättävä näkymään poistokäynnistin. Et myöskään voi käyttää LAJITTELUPERUSTE-lausetta Access-kyselyn tapaan.

Lisätietoja on artikkeleissaNäkymät ja Indeksoitujen näkymien luominen.

Tallennetut toimintosarjat

Tallennettu toimintosarja on ryhmä, joka sisältää yhden tai useamman TSQL-lauseen, jotka ottavat syöttöparametreja, palauttavat tulosparametreja ja ilmaisevat onnistumista tai epäonnistumista tila-arvolla. Ne toimivat välittäjäkerroksena Accessin edustan ja SQL Serverin taustan välillä. Tallennetut toimintosarjat voivat olla yksinkertaisia, kuten VALITSE-lause, tai monimutkaisia, kuten jokin ohjelma. Esimerkki:

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;  

Kun käytät tallennettua toimintosarjaa Accessissa, se palauttaa yleensä tulosjoukon lomakkeeseen tai raporttiin. Se saattaa kuitenkin suorittaa muita toimintoja, jotka eivät palauta tuloksia, kuten DDL- tai DML-lauseita. Jos käytät läpivientikyselyä, varmista, että olet määrittänyt Palauta tietueet -ominaisuuden oikein.

Lisätietoja on artikkelissa Tallennetut toimintosarjat.

Yleiset taulukoiden lausekkeet

Yleinen taulukon lauseke (CTE) on kuin tilapäinen taulukko, joka luo nimetyn tulosjoukon. Se on olemassa vain yhden kyselyn tai DML-lauseen suorittamisen ajan. CTE on rakennettu samaan koodiriviin kuin sitä käyttävä VALITSE-lause tai DML-lause, kun taas tilapäisten taulukoiden tai näkymien luominen ja käyttäminen on yleensä kaksivaiheinen prosessi. Esimerkki:

-- 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:ssä on useita etuja, mukaan lukien seuraavat:

• Koska CTE:t eivät ole pysyviä, sinun ei tarvitse luoda niitä näkymän kaltaisina pysyvinä tietokantaobjekteina.

• Voit viitata samaan CTE-kohteeseen useammin kuin kerran kyselyssä tai DML-lauseessa, jolloin koodisi on helpommin hallittavissa.

• Voit määrittää kohdistimen CTE:een viittaavien kyselyjen avulla.

Lisätietoja on artikkelissa WITH yleinen_taulukon_lauseke.

Käyttäjän määrittämät funktiot

Käyttäjän määrittämä funktio (UDF) voi suorittaa kyselyjä ja laskutoimituksia sekä palauttaa joko skalaarisia arvoja tai tulosjoukkoja. Ne ovat kuin ohjelmointikielten funktiot, jotka hyväksyvät parametreja, suorittavat monimutkaisen laskutoimituksen kaltaisen toiminnon ja palauttavat toiminnon tuloksen arvona. Esimerkki:

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:ssä on tiettyjä rajoituksia. Käyttäjän määrittämät funktiot eivät esimerkiksi voi käyttää tiettyjä ei-deterministisiä järjestelmätoimintoja, suorittaa DML- tai DDL-lauseita tai tehdä dynaamisia SQL-kyselyjä.

Lisätietoja on artikkelissa Käyttäjän määrittämät funktiot.

Sivun alkuun

Näppäinten ja indeksien lisääminen

Riippumatta siitä, mikä tietokantajärjestelmä on käytössä, näppäimet ja indeksit kulkevat käsi kädessä.

Näppäimet

Varmista SQL Serverissä, että luot jokaiselle taulukolle perusavaimet ja jokaiselle yhdistetylle taulukolle viiteavaimet. Accessin AutoNumber-tietotyyppiä vastaava SQL Serverin ominaisuus on KÄYTTÄJÄTIEDOT-ominaisuus, jota voidaan käyttää avainten arvojen luomiseen. Kun lisäät tämän ominaisuuden mihin tahansa numeeriseen sarakkeeseen, sarakkeesta tulee vain luku -muotoinen ja tietokantajärjestelmän ylläpitämä. Kun lisäät tietueen taulukkoon, joka sisältää KÄYTTÄJÄTIEDOT-sarakkeen, järjestelmä suurentaa KÄYTTÄJÄTIEDOT-sarakkeen arvoa automaattisesti arvolla 1 ja alkaen numerosta 1, mutta voit hallita näitä arvoja argumenteilla.

Lisätietoja on artikkelissa LUO TAULUKKO, KÄYTTÄJÄTIEDOT (ominaisuus).

Indeksit

Kuten aina, indeksien valinta tasapainottaa kyselyjen nopeuden ja päivityskustannusten välillä. Accessissa on yksi indeksityyppi, mutta SQL Serverissä on kaksitoista. Voit käyttää kyselyn optimointityökalua, joka auttaa sinua luotettavasti valitsemaan tehokkaimman indeksin. Azure SQL:ssä voit lisäksi käyttää automaattista indeksointitoimintoa, säätötoimintoa, joka suosittelee indeksien lisäämistä tai poistamista puolestasi. Toisin kuin Accessissa, SQL Serverissä sinun on luotava omat indeksit viiteavaimille. Voit myös luoda indeksoidun näkymän indeksejä, jotka parantavat kyselyjen suorituskykyä. Indeksoidun näkymän kääntöpuoli on suurempi kuormitus, kun muokkaat tietoja näkymän perustaulukoissa, koska myös näkymää täytyy päivittää. Lisätietoja on artikkeleissa SQL Serverin indeksiarkkitehtuuri ja suunnitteluopas sekä Indeksit.

Sivun alkuun

Tapahtumien suorittaminen

Online-tapahtumaprosessin (OLTP) suorittaminen on vaikeaa Accessissa, mutta melko helppoa SQL Serverissä. Tapahtuma on yksittäinen työyksikkö, joka vahvistaa onnistuessaan kaikki tietomuutokset, mutta peruuttaa muutokset epäonnistuessaan. Tapahtumalla on oltava neljä ominaisuutta, jotka tunnetaan lyhenteellä ACID:

• Jakamattomuus    Tapahtuman on oltava jakamaton työyksikkö: joko kaikki sen sisältämät tietojen muutokset suoritetaan tai mitään niistä ei suoriteta.

• Yhdenmukaisuus    Kun tapahtuma on valmis, kaikkien tietojen on jäätävä yhdenmukaiseen tilaan. Tämä tarkoittaa sitä, että kaikkia tietojen eheyssääntöjä käytetään.

• Eristäminen    Samanaikaisten tapahtumien tekemät muutokset eristetään nykyisestä tapahtumasta.

• Kestävyys    Kun tapahtuma on valmis, muutokset säilyvät myös järjestelmävirheen sattuessa.

Tapahtuman avulla varmistat tietojen eheyden, kuten ATM-käteisnostot tai palkan automaattinen talletus. Voit tehdä eksplisiittisiä tapahtumia, implisiittisiä tapahtumia tai erätapahtumia. Tässä on kaksi TSQL-esimerkkiä:

-- 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;

Lisätietoja on artikkelissa Tapahtumat.

Sivun alkuun

Rajoitusten ja käynnistimien käyttö

Kaikissa tietokannoissa on keinoja säilyttää tietojen eheys.

Rajoitukset

Accessissa viite-eheys pakotetaan taulukon suhteisiin yhdistämällä viiteavaimia perusavaimiin, johdannaispäivityksillä ja -poistoilla sekä kelpoisuussäännöillä. Lisätietoja on artikkeleissa Taulukoiden yhteyksien opas ja Tietojen syöttämisen rajoittaminen kelpoisuussäännön avulla.

SQL Serverissä käytetään YKSILÖLLINEN- ja TARKISTA-rajoituksia, jotka ovat tietojen eheyttä SQL Server -taulukoissa valvovia tietokantaobjekteja. Jos haluat varmistaa, että arvo on voimassa toisessa taulukossa, käytä viiteavainrajoitusta. Jos haluat varmistaa, että sarakkeen arvo on sisältyy tietylle alueelle, käytä TARKISTA-rajoitusta. Nämä objektit ovat ensimmäinen puolustuslinja, ja ne on suunniteltu toimimaan tehokkaasti. Lisätietoja on artikkelissa YKSILÖLLINEN- ja TARKISTA-rajoitteet.

Käynnistimet

Accessissa ei ole tietokantakäynnistimiä. SQL Serverissä voit käyttää käynnistimiä käynnistämään monimutkaisia tietojen eheyssääntöjä ja suorittamaan liiketoiminnan logiikkaa palvelimessa. Tietokannan käynnistin on tallennettu toimintosarja, joka suoritetaan, kun tietokannassa esiintyy tiettyjä toimintoja. Käynnistin on tapahtuma, kuten tietueen lisääminen taulukkoon tai poistaminen siitä, joka käynnistää ja suorittaa tallennetun toimintosarjan. Vaikka Access-tietokanta voi varmistaa viite-eheyden, kun käyttäjä yrittää päivittää tai poistaa tietoja, SQL Serverissä on hienostunut joukko käynnistimiä. Voit esimerkiksi ohjelmoida käynnistimen poistamaan tietueet joukkona ja varmistamaan tietojen eheyden. Voit myös lisätä käynnistimiä taulukoihin ja näkymiin.

Lisätietoja on artikkeleissa Käynnistimet – DML, Käynnistimet – DDL ja T-SQL-käynnistimen suunnitteleminen.

Sivun alkuun

Laskettujen sarakkeiden käyttö

Accessissa luot lasketun sarakkeen lisäämällä sen kyselyyn ja luomalla lausekkeen, kuten:

Extended Price: [Quantity] * [Unit Price]

SQL Serverissä vastaava laskettu sarake on näennäinen sarake, jota ei tallenneta taulukkoon, ellei saraketta ole merkitty PYSYVÄKSI. Kuten Accessissa, laskettu sarake käyttää lausekkeessa tietoa muista sarakkeista. Luo laskettu sarake lisäämällä se taulukkoon. Esimerkki:

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

Lisätietoja on artikkelissa Laskettujen sarakkeiden määrittäminen taulukossa.

Sivun alkuun

Tietojen aikaleimaus

Saatat joskus lisätä taulukon kentän tallentaaksesi aikaleiman tietueen luonnin yhteydessä, jotta voit kirjata tietojen syöttämisen lokiin. Accessissa voit yksinkertaisesti luoda päivämääräsarakkeen, jonka oletusarvo on =Now(). Jos haluat tallentaa päivämäärän tai kellonajan SQL Serverissä, käytä päivämääräkellonaika2-tietotyyppiä, jonka oletusarvo on SYSDATETIME().

Huomautus    Vältä riviversion sekoittamista siihen, että lisäät tietoihin aikaleiman. Avainsana aikaleima on synonyymi SQL Serverin riviversiolle, mutta kannattaa käyttää avainsanaa riviversio. SQL Serverissä riviversio on tietotyyppi, joka paljastaa tietokannan automaattisesti luodut yksilölliset binaariluvut, ja sitä käytetään yleensä taulukon rivien versioleimauksen mekanismina. Riviversio-tietotyyppi on kuitenkin vain kasvava luku, joka ei säilytä päivämäärää tai kellonaikaa ja jota ei ole tarkoitettu rivin aikaleimaamiseen.

Lisätietoja on artikkelissa riviversio. Lisätietoja riviversion käytöstä tietueiden ristiriitojen minimointiin on artikkelissa Access-tietokannan siirtäminen SQL Serveriin.

Sivun alkuun

Suurten kohteiden hallinta

Accessissa hallitset rakenteettomia tietoja, kuten tiedostoja, valokuvia ja kuvia, käyttämällä Liite-tietotyyppiä. SQL Serverin termistössä jäsentelemättömien tietojen nimi on Blob (suuri binaarinen objekti), ja niitä voi käsitellä usealla eri tavalla:

TIEDOSTOVIRTA    Käyttää varbinary(max)-tietotyyppiä tallentamaan rakenteettomia tietoja tiedostojärjestelmään tietokannan sijasta. Lisätietoja on artikkelissa Access-TIEDOSTOVIRTA-tiedot Transact-SQL:n kanssa.

FileTable    Tallentaa Blob-tietoja FileTable-erikoistaulukoihin ja mahdollistaa yhteensopivuuden Windows-sovellusten kanssa, ikään kuin ne olisi tallennettu tiedostojärjestelmään, tekemättä muutoksia asiakassovelluksiin. FileTable edellyttää, että TIEDOSTOVIRTA on käytössä. Lisätietoja on artikkelissa FileTable-taulukot.

Blob-etäsäilö (RBS)    Varastoi suuria binaarisia objekteja (Blob-objekteja) tallennusratkaisuihin sen sijaan, että ne tallennettaisiin suoraan palvelimeen. Tämä säästää tilaa ja vähentää laitteiston resursseja. Lisätietoja on artikkelissa Suurten binaaristen objektien (Blob) tiedot.

Sivun alkuun

Hierarkkisten tietojen käyttäminen

Vaikka Accessin kaltaiset relaatiotietokannat ovat erittäin joustavia, hierarkkisten suhteiden käsitteleminen on poikkeus. Siihen tarvitaan usein monimutkaisia SQL-lauseita tai -koodeja. Hierarkkisia tietoja ovat esimerkiksi organisaatiorakenne, tiedostojärjestelmä, kielitermien luokittelu ja verkkosivujen välisten linkkien kuvaaja. SQL Serverissä on oma sisäänrakennettu Hierarkiatunnus-tietotyyppi ja joukko hierarkkisia funktioita, joiden avulla hierarkkisia tietoja voi helposti säilyttää, kysellä ja hallita.

Lisätietoja on artikkeleissa Hierarkkiset tiedot ja Ohje: Hierarkiatunnus-tietotyypin käyttäminen.

Sivun alkuun

JSON-tekstin muokkaaminen

JavaScript Object Notation (JSON) on www-palvelu, joka käyttää ihmisten luettavissa olevaa tekstiä tietojen välittämiseen attribuutti-arvopareina asynkronisessa selain-palvelinviestinnässä. Esimerkki:

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

Accessissa ei ole sisäisiä tapoja hallita JSON-tietoja, mutta SQL Serverissä voit helposti säilyttää, indeksoida, kysellä ja poimia JSON-tietoja. Voit muuntaa ja säilyttää JSON-tekstiä taulukossa tai muotoilla tietoja JSON-tekstinä. Saatat esimerkiksi haluta muotoilla kyselyn tulokset JSON-muotoon verkkosovellukselle tai lisätä JSON-tietorakenteita riveihin ja sarakkeisiin.

Huomautus    JSON-ominaisuutta ei tueta VBA:ssa. Vaihtoehtoisesti voit käyttää VBA:ssa XML:ää MSXML-kirjaston avulla.

Lisätietoja on artikkelissa JSON-tiedot SQL Serverissä.

Sivun alkuun

Resurssit

Nyt on hyvä hetki tutustua SQL Serveriin ja Transact SQL:ään (TSQL). Kuten näet, monet ominaisuuksista ovat samanlaisia kuin Accessissa, mutta lisäksi tarjolla on ominaisuuksia, joita Accessissa ei ole. Jos haluat tutustua aiheeseen syvemmin, voit hyödyntää seuraavia oppimateriaaleja:

Sivun alkuun