Een Access-database migreren naar SQL Server

Voordat u begint

De volgende secties bevatten achtergrondinformatie en andere informatie om u op weg te helpen.

Informatie over gesplitste databases

Alle Access-databaseobjecten kunnen zich in één databasebestand bevinden of in twee databasebestanden worden opgeslagen: een front-enddatabase en een back-enddatabase. Dit wordt het splitsen van de database genoemd en is ontworpen om delen in een netwerkomgeving te vergemakkelijken. Het back-enddatabasebestand mag alleen tabellen en relaties bevatten. Het front-endbestand mag alleen alle andere objecten bevatten, waaronder formulieren, rapporten, query's, macro's, VBA-modules en gekoppelde tabellen aan de back-enddatabase. Wanneer u een Access-database migreert, is deze vergelijkbaar met een gesplitste database in die SQL Server fungeert als een nieuwe back-end voor de gegevens die zich nu op een server bevinden.

Als gevolg hiervan kunt u de front-end Access-database nog steeds onderhouden met gekoppelde tabellen aan de SQL Server tabellen. In feite kunt u profiteren van de voordelen van snelle toepassingsontwikkeling die een Access-database biedt, samen met de schaalbaarheid van SQL Server.

SQL Server voordelen

Hebt u nog steeds overtuigende migratie naar SQL Server nodig? Hier volgen enkele extra voordelen om over na te denken:

Meer gelijktijdige gebruikers SQL Server kan veel meer gelijktijdige gebruikers verwerken dan Access en minimaliseert geheugenvereisten wanneer er meer gebruikers worden toegevoegd.
Verhoogde beschikbaarheid Met SQL Server kunt u dynamisch, incrementeel of volledig, een back-up maken van de database terwijl deze in gebruik is. U hoeft gebruikers dus niet te dwingen de database te verlaten om een back-up te maken van uw gegevens.
Hoge prestaties en schaalbaarheid De SQL Server-database presteert meestal beter dan een Access-database, met name met een grote database van terabyte-grootte. Bovendien verwerkt SQL Server query's veel sneller en efficiënt door query's parallel te verwerken, waarbij meerdere systeemeigen threads binnen één proces worden gebruikt om gebruikersaanvragen af te handelen.
Verbeterde beveiliging Met behulp van een vertrouwde verbinding integreert SQL Server met Windows-systeembeveiliging om één geïntegreerde toegang tot het netwerk en de database te bieden, waarbij het beste van beide beveiligingssystemen wordt gebruikt. Dit maakt het veel eenvoudiger om complexe beveiligingsschema's te beheren. SQL Server is de ideale opslag voor gevoelige informatie, zoals burgerservicenummers, creditcardgegevens en vertrouwelijke adressen.
Onmiddellijke herstelbaarheid Als het besturingssysteem vastloopt of de stroom uitvalt, kunt SQL Server de database binnen enkele minuten en zonder tussenkomst van de databasebeheerder automatisch herstellen naar een consistente status.
Gebruik van VPN Access en VPN (Virtual Private Networks) kunnen niet overweg. Maar met SQL Server kunnen externe gebruikers nog steeds de front-enddatabase van Access op een bureaublad gebruiken en de SQL Server back-end achter de VPN-firewall.
Azure SQL Server Naast de voordelen van SQL Server biedt dynamische schaalbaarheid zonder downtime, intelligente optimalisatie, wereldwijde schaalbaarheid en beschikbaarheid, eliminatie van hardwarekosten en minder beheer.

Kies de beste optie Azure SQL Server

Als u migreert naar Azure SQL Server, zijn er drie opties waaruit u kunt kiezen, elk met verschillende voordelen:

Individuele database/elastische pools Deze optie heeft een eigen set resources die wordt beheerd via een SQL Database server. Eén database is net als een ingesloten database in SQL Server. U kunt ook een elastische pool toevoegen, een verzameling databases met een gedeelde set resources die wordt beheerd via de SQL Database-server. De meest gebruikte SQL Server functies zijn beschikbaar met ingebouwde back-ups, patching en herstel. Maar er is geen gegarandeerde exacte onderhoudstijd en migratie van SQL Server kan moeilijk zijn.
Beheerd exemplaar Deze optie is een verzameling systeem- en gebruikersdatabases met een gedeelde set resources. Een beheerd exemplaar is net een exemplaar van de SQL Server database die zeer compatibel is met SQL Server on-premises. Een beheerd exemplaar heeft ingebouwde back-ups, patching, herstel en is eenvoudig te migreren vanaf SQL Server. Er is echter een klein aantal SQL Server functies die niet beschikbaar zijn en geen gegarandeerde exacte onderhoudstijd.
Azure Virtual Machine Met deze optie kunt u SQL Server uitvoeren op een virtuele machine in de Azure-cloud. U hebt volledige controle over de SQL Server engine en een eenvoudig migratiepad. Maar u moet uw back-ups, patches en herstel beheren.

Zie Uw databasemigratiepad naar Azure kiezen en Wat is Azure SQL? voor meer informatie.

Eerste stappen

Er zijn enkele problemen die u vooraf kunt oplossen die u kunnen helpen bij het stroomlijnen van het migratieproces voordat u SSMA uitvoert:

Tabelindexen en primaire sleutels toevoegen Zorg ervoor dat elke Access-tabel een index en een primaire sleutel heeft. SQL Server vereist dat alle tabellen ten minste één index hebben en dat een gekoppelde tabel een primaire sleutel heeft als de tabel kan worden bijgewerkt.
Primaire/refererende sleutelrelaties controleren Zorg ervoor dat deze relaties zijn gebaseerd op velden met consistente gegevenstypen en -grootten. SQL Server biedt geen ondersteuning voor gekoppelde kolommen met verschillende gegevenstypen en -grootten in beperkingen voor refererende sleutels.
De kolom Bijlage verwijderen SSMA migreert geen tabellen die de kolom Bijlage bevatten.

Voer de volgende eerste stappen uit voordat u SSMA uitvoert.

Sluit de Access-database.
Zorg ervoor dat de huidige gebruikers die zijn verbonden met de database ook de database sluiten.
Als de database .mdb bestandsindeling heeft, kunt u beveiliging op gebruikersniveau verwijderen.
Maak een back-up van uw database. Zie Uw gegevens beveiligen met back-up- en herstelprocessen voor meer informatie.

Tip Overweeg om Microsoft SQL Server Express-editie op uw bureaublad te installeren die maximaal 10 GB ondersteunt en een gratis en eenvoudigere manier is om uw migratie uit te voeren en te controleren. Wanneer u verbinding maakt, gebruikt u LocalDB als het database-exemplaar.

Tip Gebruik indien mogelijk een zelfstandige versie van Access. Als u alleen Microsoft 365 kunt gebruiken, gebruikt u de Access 2010-database-engine om uw Access-database te migreren wanneer u SSMA gebruikt. Zie Microsoft Access Database Engine 2010 Redistributable voor meer informatie.

SSMA uitvoeren

Microsoft biedt Microsoft SQL Server Migration Assistant (SSMA) om de migratie te vereenvoudigen. SSMA migreert voornamelijk tabellen en selecteer query's zonder parameters. Formulieren, rapporten, macro's en VBA-modules worden niet geconverteerd. In de SQL Server Metadata Explorer worden uw Access-databaseobjecten en SQL Server-objecten weergegeven, zodat u de huidige inhoud van beide databases kunt bekijken. Deze twee verbindingen worden opgeslagen in uw migratiebestand als u besluit om in de toekomst extra objecten over te dragen.

Opmerking Het migratieproces kan enige tijd duren, afhankelijk van de grootte van uw databaseobjecten en de hoeveelheid gegevens die moet worden overgedragen.

Als u een database wilt migreren met behulp van SSMA, downloadt en installeert u eerst de software door te dubbelklikken op het gedownloade MSI-bestand. Zorg ervoor dat u de juiste 32- of 64-bits versie voor uw computer installeert.
Nadat U SSMA hebt geïnstalleerd, opent u het op uw bureaublad, bij voorkeur vanaf de computer met het Access-databasebestand.

U kunt deze ook openen op een computer die toegang heeft tot de Access-database vanuit het netwerk in een gedeelde map.
Volg de begininstructies in SSMA om basisinformatie op te geven, zoals de SQL Server locatie, de Access-database en objecten die moeten worden gemigreerd, verbindingsgegevens en of u gekoppelde tabellen wilt maken.
Als u migreert naar SQL Server 2016 of hoger en een gekoppelde tabel wilt bijwerken, voegt u een kolom rowversion toe door Hulpmiddelen voor revisie te selecteren > Projectinstellingen > Algemeen.

Het veld rowversion helpt recordconflicten te voorkomen. Access gebruikt dit veld rowversion in een SQL Server gekoppelde tabel om te bepalen wanneer de record voor het laatst is bijgewerkt. Als u het veld rowversion toevoegt aan een query, wordt dit ook gebruikt om de rij opnieuw te selecteren na een updatebewerking. Dit verbetert de efficiëntie door schrijfconflictfouten en scenario's voor het verwijderen van records te voorkomen die kunnen optreden wanneer In Access verschillende resultaten van de oorspronkelijke indiening worden gedetecteerd, zoals kan optreden bij gegevenstypen met drijvende kommanummers en triggers die kolommen wijzigen. Vermijd echter het gebruik van het veld rowversion in formulieren, rapporten of VBA-code. Zie rowversion voor meer informatie.

Opmerking Vermijd verwarrende rijversie met tijdstempels. Hoewel de tijdstempel voor trefwoorden synoniem is voor rowversion in SQL Server, kunt u rowversion niet gebruiken als een manier om een gegevensinvoer te timestampen.
Als u nauwkeurige gegevenstypen wilt instellen, selecteert u Hulpmiddelen > Projectinstellingen > Typetoewijzing. Als u bijvoorbeeld alleen Engelse tekst opslaat, kunt u het gegevenstype varchar gebruiken in plaats van nvarchar .

Objecten converteren

SSMA converteert Access-objecten naar SQL Server-objecten, maar kopieert de objecten niet meteen. SSMA biedt een lijst met de volgende objecten die moeten worden gemigreerd, zodat u kunt bepalen of u deze naar SQL Server database wilt verplaatsen:

Tabellen en kolommen
Selecteer Query's zonder parameters.
Primaire en refererende sleutels
Indexen en standaardwaarden
Beperkingen controleren (kolomeigenschap met lengte nul toestaan, kolomvalidatieregel, tabelvalidatie)

Als aanbevolen procedure gebruikt u het SSMA-evaluatierapport, waarin de conversieresultaten worden weergegeven, inclusief fouten, waarschuwingen, informatieve berichten, tijdschattingen voor het uitvoeren van de migratie en afzonderlijke foutcorrectiestappen die moeten worden uitgevoerd voordat u de objecten daadwerkelijk verplaatst.

Bij het converteren van databaseobjecten worden de objectdefinities uit de Access-metagegevens gebruikt, geconverteerd naar een equivalente Transact-SQL-syntaxis (T-SQL) en wordt deze informatie vervolgens in het project geladen. Vervolgens kunt u de SQL Server- of SQL Azure-objecten en hun eigenschappen bekijken met behulp van SQL Server of SQL Azure Metadata Explorer.

Volg deze handleiding om objecten te converteren, te laden en te migreren naar SQL Server.

Tip Zodra u uw Access-database hebt gemigreerd, slaat u het projectbestand op voor later gebruik, zodat u uw gegevens opnieuw kunt migreren voor test- of definitieve migratie.

Tabellen koppelen

Overweeg de nieuwste versie van de SQL Server OLE DB- en ODBC-stuurprogramma's te installeren in plaats van de systeemeigen SQL Server stuurprogramma's te gebruiken die met Windows worden geleverd. Niet alleen zijn de nieuwere stuurprogramma's sneller, maar ze ondersteunen ook nieuwe functies in Azure SQL die de vorige stuurprogramma's niet hebben. U kunt de stuurprogramma's installeren op elke computer waarop de geconverteerde database wordt gebruikt. Zie Microsoft OLE DB-stuurprogramma 18 voor SQL Server en Microsoft ODBC-stuurprogramma 17 voor SQL Server voor meer informatie.

Nadat u de Access-tabellen hebt gemigreerd, kunt u een koppeling maken naar de tabellen in SQL Server die nu als host fungeert voor uw gegevens. Rechtstreeks koppelen vanuit Access biedt u ook een eenvoudigere manier om uw gegevens weer te geven in plaats van de complexere SQL Server beheerhulpprogramma's te gebruiken. U kunt gekoppelde gegevens opvragen en bewerken, afhankelijk van de machtigingen die zijn ingesteld door uw SQL Server databasebeheerder.

Opmerking Als u een ODBC-DSN maakt wanneer u tijdens het koppelingsproces een koppeling maakt naar uw SQL Server-database, maakt u dezelfde DSN op alle computers die de nieuwe toepassing gebruiken of gebruikt u programmatisch de verbindingsreeks die zijn opgeslagen in het DSN-bestand.

Zie Gegevens koppelen aan of importeren uit een Azure SQL Server-database en Gegevens in een SQL Server-database importeren of er een koppeling naar maken voor meer informatie.

Tip Vergeet niet om gekoppeld tabelbeheer in Access te gebruiken om tabellen gemakkelijk te vernieuwen en opnieuw te koppelen. Zie Gekoppelde tabellen beheren voor meer informatie.

Testen en herzien

In de volgende secties worden veelvoorkomende problemen beschreven die u tijdens de migratie kunt tegenkomen en hoe u hiermee omgaat.

Query's

Alleen selectiequery's worden geconverteerd; andere query's zijn dat niet, waaronder Query's selecteren die parameters gebruiken. Sommige query's worden mogelijk niet volledig geconverteerd en SSMA rapporteert queryfouten tijdens het conversieproces. U kunt objecten die niet worden geconverteerd handmatig bewerken met behulp van de T-SQL-syntaxis. Syntaxisfouten vereisen mogelijk ook handmatig het converteren van access-specifieke functies en gegevenstypen naar SQL Server. Zie voor meer informatie Access SQL vergelijken met SQL Server TSQL.

Gegevenstypen

Access en SQL Server vergelijkbare gegevenstypen hebben, maar houd rekening met de volgende mogelijke problemen.

Groot getal Het gegevenstype Groot getal slaat een niet-monetaire, numerieke waarde op en is compatibel met het gegevenstype SQL bigint. U kunt dit gegevenstype gebruiken om grote getallen efficiënt te berekenen, maar hiervoor moet u de bestandsindeling access 16 (16.0.7812 of hoger) van access gebruiken en presteert beter met de 64-bits versie van Access. Zie Het gegevenstype Groot getal gebruiken en Kiezen tussen de 64-bits of 32-bits versie van Office voor meer informatie.

Ja/Nee Standaard wordt een ja/nee-kolom van Access geconverteerd naar een SQL Server bitveld. Om recordvergrendeling te voorkomen, moet u ervoor zorgen dat het bitveld is ingesteld op het niet toestaan van NULL-waarden. In SSMA kunt u de bitkolom selecteren om de eigenschap Nulls toestaan in te stellen op NEE. Gebruik in TSQL de instructies CREATE TABLE of ALTER TABLE .

Datum en tijd Er zijn verschillende datum- en tijdoverwegingen:

Als het compatibiliteitsniveau van de database 130 (SQL Server 2016) of hoger is en een gekoppelde tabel een of meer datum/tijd2-kolommen bevat, kan de tabel het bericht retourneren #deleted in de resultaten. Zie Gekoppelde tabel openen naar SQL-Server database retourneert #deleted voor meer informatie.
Gebruik het gegevenstype Datum/tijd van access om toe te wijzen aan het gegevenstype datetime. Gebruik het uitgebreide gegevenstype Access Date/Time om toe te wijzen aan het gegevenstype datetime2 met een groter datum- en tijdbereik. Zie Het gegevenstype Datum/tijd uitgebreid gebruiken voor meer informatie.
Houd bij het opvragen van datums in SQL Server rekening met de tijd en de datum. Bijvoorbeeld:
- DateOrdered Tussen 1-1-19 en 31-1-19-19 zijn mogelijk niet alle bestellingen opgenomen.
- DateOrdered Tussen 1-1-19 00:00:00 en 31-1-19 11:59:59 PM omvat alle bestellingen.

Bijlage Het gegevenstype Bijlage slaat een bestand op in de Access-database. In SQL Server hebt u verschillende opties om te overwegen. U kunt de bestanden uit de Access-database extraheren en vervolgens koppelingen naar de bestanden in uw SQL Server-database opslaan. U kunt ook FILESTREAM, FileTables of Remote BLOB Store (RBS) gebruiken om bijlagen op te slaan in de SQL Server-database.

Hyperlink Access-tabellen hebben hyperlinkkolommen die SQL Server niet ondersteunt. Standaard worden deze kolommen geconverteerd naar nvarchar(max) kolommen in SQL Server, maar u kunt de toewijzing aanpassen om een kleiner gegevenstype te kiezen. In uw Access-oplossing kunt u nog steeds het hyperlinkgedrag in formulieren en rapporten gebruiken als u de eigenschap Hyperlink voor het besturingselement instelt op true.

Veld met meerdere waarden Het access-veld met meerdere waarden wordt geconverteerd naar SQL Server als een ntekstveld dat de gescheiden set waarden bevat. SQL Server biedt geen ondersteuning voor gegevenstypen met meerdere waarden die een veel-op-veel-relatie vormen. Mogelijk is er aanvullend ontwerp en conversie vereist.

Zie Gegevenstypen vergelijken voor meer informatie over het toewijzen van access- en SQL Server gegevenstypen.

Opmerking Velden met meerdere waarden worden niet geconverteerd.

Zie Datum- en tijdtypen, tekenreeks- en binaire typen en numerieke typen voor meer informatie.

Visual Basic

Hoewel VBA niet wordt ondersteund door SQL Server, moet u rekening houden met de volgende mogelijke problemen:

VBA-functies in query's Access-query's ondersteunen VBA-functies voor gegevens in een querykolom. Access-query's die gebruikmaken van VBA-functies kunnen echter niet worden uitgevoerd op SQL Server, zodat alle aangevraagde gegevens worden doorgegeven aan Microsoft Access voor verwerking. In de meeste gevallen moeten deze query's worden geconverteerd naar passthrough-query's.

Door de gebruiker gedefinieerde functies in query's Microsoft Access-query's ondersteunen het gebruik van functies die zijn gedefinieerd in VBA-modules om gegevens te verwerken die aan hen worden doorgegeven. Query's kunnen zelfstandige query's zijn, SQL-instructies in formulier-/rapportrecordbronnen, gegevensbronnen van keuzelijsten met invoervak en keuzelijst op formulieren, rapporten en tabelvelden, en standaard- of validatieregelexpressies. SQL Server kan deze door de gebruiker gedefinieerde functies niet uitvoeren. Mogelijk moet u deze functies handmatig opnieuw ontwerpen en converteren naar opgeslagen procedures op SQL Server.

Prestaties optimaliseren

Verreweg de belangrijkste manier om de prestaties te optimaliseren met uw nieuwe back-end SQL Server is om te bepalen wanneer u lokale of externe query's gebruikt. Wanneer u uw gegevens migreert naar SQL Server, gaat u ook over van een bestandsserver naar een client-serverdatabasemodel voor computing. Volg deze algemene richtlijnen:

Voer kleine, alleen-lezen query's uit op de client voor de snelste toegang.
Voer lange lees-/schrijfquery's uit op de server om te profiteren van de grotere verwerkingskracht.
Minimaliseer netwerkverkeer met filters en aggregatie om alleen de gegevens over te dragen die u nodig hebt.

Prestaties in het databasemodel van de clientserver optimaliseren

Zie Een passthrough-query maken voor meer informatie.

Hier volgen aanvullende, aanbevolen richtlijnen.

Logica op de server plaatsen Uw toepassing kan ook weergaven, door de gebruiker gedefinieerde functies, opgeslagen procedures, berekende velden en triggers gebruiken om toepassingslogica, bedrijfsregels en beleidsregels, complexe query's, gegevensvalidatie en referentiële integriteitscode op de server te centraliseren en te delen in plaats van op de client. Vraag uzelf af of deze query of taak beter en sneller kan worden uitgevoerd op de server? Ten slotte test u elke query om optimale prestaties te garanderen.

Weergaven gebruiken in formulieren en rapporten Ga als volgt te werk in Access:

Gebruik voor formulieren een SQL-weergave voor een alleen-lezen formulier en een geïndexeerde SQL-weergave voor een lees-/schrijfformulier als recordbron.
Gebruik voor rapporten een SQL-weergave als recordbron. Maak echter een afzonderlijke weergave voor elk rapport, zodat u een specifiek rapport gemakkelijker kunt bijwerken, zonder dat dit van invloed is op andere rapporten.

Het laden van gegevens in een formulier of rapport minimaliseren Geef geen gegevens weer totdat de gebruiker erom vraagt. Houd bijvoorbeeld de eigenschap recordbron leeg, laat gebruikers een filter selecteren in uw formulier en vul vervolgens de eigenschap recordsource in met uw filter. Of gebruik de where-component van DoCmd.OpenForm en DoCmd.OpenReport om de exacte records weer te geven die de gebruiker nodig heeft. Overweeg om recordnavigatie uit te schakelen.

Wees voorzichtig met heterogene query's Vermijd het uitvoeren van een query die een lokale Access-tabel combineert met SQL Server gekoppelde tabel, ook wel een hybride query genoemd. Voor dit type query is nog steeds Access vereist om alle SQL Server gegevens naar de lokale computer te downloaden en vervolgens de query uit te voeren. De query wordt niet uitgevoerd in SQL Server.

Wanneer lokale tabellen gebruiken Overweeg lokale tabellen te gebruiken voor gegevens die zelden worden gewijzigd, zoals de lijst met staten of provincies in een land of regio. Statische tabellen worden vaak gebruikt om te filteren en kunnen beter presteren op de Front-end van Access.

Zie Database Engine Tuning Advisor, De Performance Analyzer gebruiken om een Access-database te optimaliseren en Microsoft Office Access-toepassingen optimaliseren die zijn gekoppeld aan SQL Server voor meer informatie.