Overføre en Access-database til SQL Server

Gjelder for

Access for Microsoft 365 Access 2024 Access 2021 Access 2019 Access 2016

Vi har alle begrensninger, og en Access-database er intet unntak. En Access-database har for eksempel en størrelsesgrense på 2 GB og kan ikke støtte mer enn 255 samtidige brukere. Så når det er på tide å ta Access-databasen din til neste nivå, kan du overføre til SQL Server. SQL Server (enten lokalt eller i Azure-skyen) støtter større mengder data, flere samtidige brukere, og har større kapasitet enn JET/ACE-databasemotoren. Denne veiledningen gir deg en problemfri start på SQL Server-reisen, bidrar til å bevare Access-frontend-løsninger du opprettet, og motiverer deg forhåpentligvis til å bruke Access til fremtidige databaseløsninger. Bruk Microsoft SQL Server Migration Assistant (SSMA) til å overføre. Følg disse trinnene.

Stadiene i databaseoverføring til SQL Server

Før du begynner

De følgende avsnittene gir bakgrunn og annen informasjon for å hjelpe deg med å komme i gang.

Delte databaser

Alle Access-databaseobjekter kan enten være i én databasefil, eller de kan lagres i to databasefiler: en frontdatabase og en bakdatabase. Dette kalles å dele opp databasen og er utformet for å legge til rette for deling i et nettverksmiljø. Bakdatabasefilen må bare inneholde tabeller og relasjoner. Frontfilen må bare inneholde alle andre objekter, inkludert skjemaer, rapporter, spørringer, makroer, VBA-moduler og koblede tabeller til bakdatabasen. Når du overfører en Access-database, ligner det på en delt database på den måten at SQL Server fungerer som en ny serverdel for dataene som nå er plassert på en server.

Som et resultat kan du fortsatt vedlikeholde frontdatabasen i Access med koblede tabeller til SQL Server-tabellene. Du kan effektivt utnytte fordelene av den raske programutviklingen som en Access-database gir, sammen med skalerbarheten til SQL Server.

Fordeler med SQL Server

Trenger du fortsatt litt overbevisning om å overføre til SQL Server? Her er noen andre fordeler å tenke på:

Flere samtidige brukere SQL Server kan håndtere mange flere samtidige brukere enn Access, og minimerer minnekravene når flere brukere legges til.
Økt tilgjengelighet Med SQL Server kan du dynamisk sikkerhetskopiere databasen mens den er i bruk, enten trinnvis eller komplett. Dermed trenger du ikke å tvinge brukere til å avslutte databasen for å ta sikkerhetskopi av data.
Høy ytelse og skalerbarhet SQL Server-databasen yter vanligvis bedre enn en Access-database, spesielt med en stor database på én terabyte. SQL Server behandler spørringer mye raskere og effektivt ved å behandle spørringer parallelt, ved hjelp av flere integrerte tråder i én enkelt prosess for å håndtere brukerforespørsler.
Forbedret sikkerhet Ved hjelp av en klarert tilkobling integreres SQL Server med Windows-systemsikkerhet for å gi én integrert tilgang til nettverket og databasen, ved å bruke det beste fra begge sikkerhetssystemene. Dette gjør det mye enklere å administrere komplekse sikkerhetsopplegg. SQL Server er den ideelle lagringsplassen for sensitiv informasjon, for eksempel personnumre, kredittkortdata og konfidensielle adresser.
Umiddelbar gjenoppretting Hvis operativsystemet krasjer eller strømmen går, kan SQL Server automatisk gjenopprette databasen til en konsekvent tilstand i løpet av få minutter og uten at databaseadministratoren trenger å gjøre noe.
Bruk av VPN Access og virtuelle private nettverk (VPN) kommer ikke overens. Men med SQL Server kan eksterne brukere fortsatt bruke Access-frontdatabasen på en stasjonær datamaskin og SQL Server-backend som ligger bak VPN-brannmuren.
Azure SQL Server I tillegg til fordelene med SQL Server, tilbyr dynamisk skalerbarhet uten nedetid, intelligent optimalisering, global skalerbarhet og tilgjengelighet, eliminering av maskinvarekostnader og redusert administrasjon.

Velg det beste alternativet for Azure SQL Server

Hvis du overfører til Azure SQL Server, finnes det tre alternativer å velge mellom, hvert med forskjellige fordeler:

Enkel database / elastiske utvalg Dette alternativet har et eget sett med ressurser som administreres via en SQL Database-server. Én enkelt database er som en innesluttet database i SQL Server. Du kan også legge til et elastisk utvalg, som er en samling av databaser med et delt sett med ressurser som administreres via SQL Database-serveren. De mest brukte SQL Server-funksjonene er tilgjengelige med innebygd sikkerhetskopiering, oppdatering og gjenoppretting. Men det er ingen garantert nøyaktig vedlikeholdstid, og overføring fra SQL Server kan være vanskelig.
Administrert forekomst Dette alternativet er en samling av system- og brukerdatabaser med et delt sett med ressurser. En administrert forekomst er som en forekomst av SQL Server-databasen som har høy kompatibilitet med SQL Server lokalt. En administrert forekomst har innebygde sikkerhetskopier, oppdatering og gjenoppretting, og det er enkelt å overføre fra SQL Server. Det finnes imidlertid et lite antall SQL Server-funksjoner som ikke er tilgjengelige, og ingen garantert nøyaktig vedlikeholdstid.
Virtuell Azure-maskin Med dette alternativet kan du kjøre SQL Server i en virtuell maskin i Azure-skyen. Du har full kontroll over SQL Server-motoren og en enkel overføringsbane. Men du må administrere sikkerhetskopier, oppdateringer og gjenoppretting.

Hvis du vil ha mer informasjon, kan du se Velge databaseoverføringsbane til Azure og Hva er Azure SQL?.

Første skritt

Det finnes noen problemer du kan løse på forhånd som kan bidra til å strømlinjeforme overføringsprosessen før du kjører SSMA:

Legge til tabellindekser og primærnøkler Kontroller at hver Access-tabell har en indeks og en primærnøkkel. SQL Server krever at alle tabeller har minst én indeks, og krever at en koblet tabell har en primærnøkkel hvis tabellen kan oppdateres.
Kontrollere primær- og sekundærnøkkelrelasjoner Kontroller at disse relasjonene er basert på felt med konsekvente datatyper og størrelser. SQL Server støtter ikke sammenføyde kolonner med forskjellige datatyper og størrelser i begrensninger for sekundærnøkkel.
Fjerne Vedlegg-kolonnen SSMA overfører ikke tabeller som inneholder Vedlegg-kolonnen.

Før du kjører SSMA, bør du utføre følgende trinn.

Lukk Access-databasen.
Kontroller at gjeldende brukere som er koblet til databasen, også lukker databasen.
Hvis databasen er i .mdb filformat, må du fjerne sikkerhet på brukernivå.
Sikkerhetskopier databasen. Hvis du vil ha mer informasjon, kan du se Beskytte dataene dine med sikkerhetskopiering og gjenoppretting.

Tips Vurder å installere Microsoft SQL Server Express Edition på den stasjonære datamaskinen, som støtter opptil 10 GB og er en gratis og enklere måte å gjennomføre og kontrollere overføringen på. Bruk LocalDB som databaseforekomst når du kobler til.

Tips Bruk om mulig en frittstående versjon av Access.

Kjør SSMA

Microsoft tilbyr Microsoft SQL Server Migration Assistant (SSMA) for å gjøre overføringen enklere. SSMA overfører hovedsakelig tabeller og velger spørringer uten parametere. Skjemaer, rapporter, makroer og VBA-moduler konverteres ikke. SQL Server Metadata Explorer viser Access-databaseobjektene og SQL Server-objekter, slik at du kan se gjennom gjeldende innhold i begge databasene. Disse to tilkoblingene lagres i overføringsfilen hvis du bestemmer deg for å overføre flere objekter i fremtiden.

Vær oppmerksom på Overføringen kan ta litt tid, avhengig av størrelsen på databaseobjektene og hvor mye data som må overføres.

Hvis du vil overføre en database ved hjelp av SSMA, må du først laste ned og installere programvaren ved å dobbeltklikke den nedlastede MSI-filen. Sørg for at du installerer riktig 32- eller 64-bitersversjon for datamaskinen din.
Når du har installert SSMA, åpner du det på skrivebordet, fortrinnsvis fra datamaskinen med Access-databasefilen.
Du kan også åpne den på en maskin som har tilgang til Access-databasen fra nettverket i en delt mappe.
Følg instruksjonene i SSMA for å gi grunnleggende informasjon, for eksempel SQL Server-plassering, Access-databasen og objektene som skal overføres, tilkoblingsinformasjon og om du vil opprette koblede tabeller.
Hvis du overfører til SQL Server 2016 eller nyere og ønsker å oppdatere en koblet tabell, legger du til en ROWVERSION-kolonne ved å velge Se gjennom verktøy>Prosjektinnstillinger>Generelt.
ROWVERSION-feltet bidrar til å unngå postkonflikter. Access bruker dette ROWVERSION-feltet i en SQL Server-koblet tabell til å fastslå når posten sist ble oppdatert. Hvis du legger til ROWVERSION-feltet i en spørring, bruker Access det til å merke raden på nytt etter en oppdateringsoperasjon. Dette forbedrer effektiviteten ved å bidra til å unngå skrivekonfliktfeil og postslettingsscenarier som kan oppstå når Access oppdager andre resultater enn den opprinnelige innsendingen, for eksempel med datatyper for flyttall og utløsere som endrer kolonner. Unngå imidlertid å bruke ROWVERSION-feltet i skjemaer, rapporter eller VBA-kode. Hvis du vil ha mer informasjon, kan du se ROWVERSION.
Vær oppmerksom på Unngå å blande sammen ROWVERSION med tidsstempler. Selv om tidsstempelet for nøkkelord er et synonym for ROWVERSION i SQL Server, kan du ikke bruke ROWVERSION som en måte å tidsstemple en dataregistrering på.
Hvis du vil angi nøyaktige datatyper, velger du Se gjennom-verktøy> Typetilordningav prosjektinnstillinger>. Hvis du for eksempel bare lagrer engelsk tekst, kan du bruke datatypen varchar i stedet for nvarchar .

Konvertere objekter

SSMA konverterer Access-objekter til SQL Server-objekter, men kopierer ikke objektene umiddelbart. SSMA inneholder en liste over følgende objekter som skal overføres, slik at du kan bestemme om du vil flytte dem til en SQL Server-database:

Tabeller og kolonner
Velg Spørringer uten parametere.
Primær- og sekundærnøkler
Indekser og standardverdier
Kontroller begrensninger (tillat egenskap for nullengdekolonne, kolonnevalideringsregel, tabellvalidering)

Den beste fremgangsmåten er å bruke SSMA-vurderingsrapporten, som viser konverteringsresultatene, inkludert feil, advarsler, informasjonsmeldinger, tidsestimater for å utføre overføringen og individuelle feilrettingstrinn du kan utføre før du faktisk flytter objektene.

Når databaseobjekter skal konverteres, tas objektdefinisjonene fra Access-metadataene, de konverteres til en tilsvarende Transact-SQL-syntaks (T-SQL) og lastes deretter inn denne informasjonen i prosjektet. Du kan deretter vise SQL Server- eller SQL Azure-objektene og egenskapene deres ved hjelp av SQL Server eller SQL Azure Metadata Explorer.

Følg denne veiledningen for å konvertere, laste inn og overføre objekter til SQL Server.

Tips Når du har overført Access-databasen, lagrer du prosjektfilen for senere bruk, slik at du kan overføre dataene på nytt for testing eller endelig overføring.

Koble tabeller

Vurder å installere den nyeste versjonen av SQL Server-, OLE DB- og ODBC-driverne i stedet for å bruke de opprinnelige SQL Server-driverne som leveres med Windows. Ikke bare er de nyere driverne raskere, men de støtter også nye funksjoner i Azure SQL som de tidligere driverne ikke gjør. Du kan installere driverne på hver datamaskin der den konverterte databasen brukes. Hvis du vil ha mer informasjon, kan du se Microsoft OLE DB-driver 18 for SQL Server og Microsoft ODBC-driver 17 for SQL Server.

Når du har overført Access-tabellene, kan du koble til tabellene i SQL Server, som nå er vert for dataene. Hvis du kobler direkte fra Access, blir det også enklere å vise dataene i stedet for å bruke de mer komplekse administrasjonsverktøyene for SQL Server. Du kan spørre og redigere koblede data avhengig av tillatelsene som er konfigurert av SQL Server-databaseadministratoren.

Vær oppmerksom på Hvis du oppretter en ODBC DSN når du kobler til SQL Server-databasen under koblingsprosessen, må du enten opprette den samme DSN-en på alle maskiner som bruker det nye programmet, eller programmatisk bruke tilkoblingsstrengen som er lagret i DSN-filen.

Hvis du vil ha mer informasjon, kan du se Koble til eller importere data fra en Azure SQL Server-database, og importere eller koble til data i en SQL Server-database.

Tips Ikke glem å bruke tabellkoblingsbehandlingen i Access for å enkelt oppdatere og koble tabeller til på nytt. Hvis du vil ha mer informasjon, kan du se Administrere koblede tabeller.

Teste og revidere

Avsnittene nedenfor beskriver vanlige problemer som kan oppstå under overføring, og hvordan du håndterer dem.

Spørringer

Bare utvalgte spørringer konverteres. andre spørringer er ikke det, inkludert utvalgsspørringer som bruker parametere. Noen spørringer konverteres kanskje ikke fullstendig, og SSMA rapporterer spørringsfeil under konverteringsprosessen. Du kan manuelt redigere objekter som ikke konverteres, ved å bruke T-SQL-syntaks. Syntaksfeil kan også kreve manuell konvertering av Access-spesifikke funksjoner og datatyper til SQL Server-funksjoner. Hvis du vil ha mer informasjon, kan du se Sammenligne Access SQL med SQL Server TSQL.

Datatyper

Access og SQL Server har lignende datatyper, men vær oppmerksom på følgende potensielle problemer.

Stort tall Datatypen Stort tall lagrer en ikke-monetær numerisk tallverdi og er kompatibel med datatypen SQL bigint. Du kan bruke denne datatypen til effektivt å beregne store tall, men det krever at du bruker Access 16 (16.0.7812 eller senere) .accdb-databasefilformat, og det fungerer bedre med 64-bitersversjonen av Access. Hvis du vil ha mer informasjon, kan du se Bruke datatypen Stort tall og Velge mellom 64-biters eller 32-biters versjon av Office.

Ja/Nei En Ja/nei-kolonne i Access konverteres som standard til et SQL Server-bitfelt. Hvis du vil unngå postlåsing, må du sørge for at bit-feltet er satt til å ikke tillate NULL-verdier. I SSMA kan du velge bitkolonnen for å angi egenskapen Allow Nulls til NEI. Bruk uttrykkene CREATE TABLE eller ALTER TABLE i TSQL.

Dato og klokkeslett Det finnes flere faktorer å ta hensyn til dato og klokkeslett:

Hvis kompatibilitetsnivået for databasen er 130 (SQL Server 2016) eller høyere, og en koblet tabell inneholder én eller flere datetime- eller datetime2-kolonner, kan tabellen returnere meldingen #deleted i resultatene. Hvis du vil ha mer informasjon, kan du se Access koblet tabell til SQL-Server database returnerer #deleted.
Bruk datatypen Access-dato / klokkeslett til å tilordne til datetime-datatypen. Bruk Access Date/Time Extended-datatypen til å tilordne til datetime2-datatypen som har et større dato- og klokkeslettintervall. Hvis du vil ha mer informasjon, kan du se Bruke datatypen Date/Time Extended.
Når du spør etter datoer i SQL Server, må du ta hensyn til klokkeslettet i tillegg til datoen. Eksempel:
- DateOrdered Between 1/1/19 and 1/31/19 omfatter kanskje ikke alle ordrer.
- DateOrdered Between 1/1/19 00:00:00 AM And 1/31/19 11:59:59 PM inkluderer alle bestillinger.

Vedlegg Datatypen Vedlegg lagrer en fil i en Access-database. I SQL Server har du flere alternativer å vurdere. Du kan pakke ut filene fra Access-databasen og deretter vurdere å lagre koblinger til filene i SQL Server-databasen. Du kan også bruke FILESTREAM, FileTables eller Remote BLOB store (RBS) til å lagre vedlegg i SQL Server-databasen.

Hyperkobling Access-tabeller har hyperkoblingskolonner som SQL Server ikke støtter. Som standard konverteres disse kolonnene til nvarchar(max)-kolonner i SQL Server, men du kan tilpasse tilordningen ved å velge en mindre datatype. I Access-løsningen kan du fortsatt bruke virkemåten til hyperkoblinger i skjemaer og rapporter hvis du angir egenskapen for hyperkobling for kontrollen til sann.

Felt med flere verdier Access-feltet med flere verdier konverteres til SQL Server som et ntekst-felt som inneholder det skilletegnssettet med verdier. Ettersom SQL Server ikke støtter en datatype med flere verdier som gjenspeiler en mange-til-mange-relasjon, kan ytterligere utforming og konvertering være nødvendig.

Hvis du vil ha mer informasjon om tilordning av datatyper i Access og SQL Server, kan du se Sammenligne datatyper.

Vær oppmerksom på Felt med flere verdier konverteres ikke.

Hvis du vil ha mer informasjon, kan du se Dato- og klokkesletttyper, Strengtyper og binære typer og Numeriske typer.

Visual Basic

Selv om VBA ikke støttes av SQL Server, bør du merke deg følgende mulige problemer:

VBA-funksjoner i spørringer Access-spørringer støtter VBA-funksjoner på data i en spørringskolonne. Access-spørringer som bruker VBA-funksjoner, kan imidlertid ikke kjøres på SQL Server, så alle forespurte data sendes til Microsoft Access for behandling. I de fleste tilfeller bør disse spørringene konverteres til direktespørringer.

Brukerdefinerte funksjoner i spørringer Microsoft Access-spørringer støtter bruken av funksjoner som er definert i VBA-moduler, for å behandle data som sendes til dem. Spørringer kan være frittstående spørringer, SQL-setninger i postkilder for skjemaer/rapporter, datakilder for kombinasjonsbokser og listebokser i skjemaer, rapporter og tabellfelt og standarduttrykk eller valideringsregler. SQL Server kan ikke kjøre disse brukerdefinerte funksjonene. Du må kanskje manuelt omforme disse funksjonene og konvertere dem til lagrede prosedyrer på SQL Server.

Optimaliser ytelsen

Den desidert viktigste måten å optimalisere ytelsen med den nye serverdelen av SQL Server på, er å bestemme når du skal bruke lokale eller eksterne spørringer. Når du overfører dataene til SQL Server, flytter du også databehandling fra en filserver til en klient-server-databasemodell. Følg disse generelle retningslinjene:

Kjør små, skrivebeskyttede spørringer på klienten for raskest mulig tilgang.
Kjør lange lese/skrive-spørringer på serveren for å dra nytte av den økte prosessorkraften.
Minimer nettverkstrafikken med filtre og aggregasjon for å overføre bare de dataene du trenger.

Optimaliser ytelsen i klientserverens databasemodell Hvis du vil ha mer informasjon, kan du se Opprette en direktespørring.

Nedenfor finner du ytterligere anbefalte retningslinjer.

Legge logikk på serveren Programmet kan også bruke visninger, brukerdefinerte funksjoner, lagrede prosedyrer, beregnede felt og utløsere til å sentralisere og dele programlogikk, forretningsregler og policyer, komplekse spørringer, datavalidering og referanseintegritetskode på serveren i stedet for på klienten. Spør deg selv: Kan denne spørringen eller oppgaven utføres på serveren bedre og raskere. Til slutt tester du hver spørring for å sikre optimal ytelse.

Bruke visninger i skjemaer og rapporter Gjør følgende i Access:

For skjemaer kan du bruke en SQL-visning for et skrivebeskyttet skjema og en SQL-indeksert visning for et lese/skrive-skjema som postkilde.
Bruk en SQL-visning som postkilde for rapporter. Du bør imidlertid opprette en egen visning for hver rapport, slik at du enklere kan oppdatere en bestemt rapport, uten å påvirke andre rapporter.

Minimere innlasting av data i et skjema eller en rapport Ikke vis data før brukeren ber om det. Du kan for eksempel la postkildeegenskapen være tom, få brukerne til å velge et filter på skjemaet, og deretter fylle ut postkildeegenskapen med filteret. Eller bruk WHERE-setningen for DoCmd.OpenForm og DoCmd.OpenReport til å vise nøyaktig de(n) nødvendige posten(e) for brukeren. Vurder å slå av postnavigasjon.

Vær forsiktig med heterogene spørringer Unngå å kjøre en spørring som kombinerer en lokal Access-tabell og en koblet SQL Server-tabell, noen ganger kalt en hybridspørring. Denne typen spørring krever fortsatt at Access laster ned alle SQL Server-dataene til den lokale maskinen og deretter kjører spørringen. Den kjører ikke spørringen i SQL Server.

Når du bør bruke lokale tabeller Vurder å bruke lokale tabeller for data som sjelden endres, for eksempel listen over delstater eller provinser i et land eller område. Statiske tabeller brukes ofte til filtrering, og kan yte bedre på Access-frontserveren.

Hvis du vil ha mer informasjon, kan du se Justeringsrådgiver for databasemotor, Bruke Ytelsesanalyse til å optimalisere en Access-database og Optimalisere Microsoft Office Access-programmer som er koblet til SQL Server.