Een Access-database migreren naar SQL Server

Van toepassing op

Access voor Microsoft 365 Access 2024 Access 2021 Access 2019 Access 2016

We hebben allemaal wel eens beperkingen, en een Access-database vormt daarop geen uitzondering. Een Access-database heeft bijvoorbeeld een maximale grootte van 2 GB en kan niet meer dan 255 gelijktijdige gebruikers ondersteunen. Dus wanneer het tijd is om de Access-database naar een hoger niveau te tillen, kunt u migreren naar SQL Server. SQL Server (on-premises of in de Azure-cloud) ondersteunt grotere hoeveelheden gegevens, meer gelijktijdige gebruikers en heeft een grotere capaciteit dan de JET/ACE-database-engine. Deze handleiding biedt u een soepele start van uw SQL Server-reis, helpt u bij het behouden van de front-endoplossingen van Access die u hebt gemaakt en hopelijk motiveert u om Access te gebruiken voor toekomstige databaseoplossingen. Gebruik voor een succesvolle migratie de Microsoft SQL Server Migration Assistant (SSMA). Volg deze stappen.

De fasen van de databasemigratie naar SQL Server

Voordat u begint

In de volgende secties vindt u achtergrondinformatie en andere informatie om u op weg te helpen.

Over gesplitste databases

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

Hierdoor kunt u de front-enddatabase van Access nog steeds onderhouden met tabellen die zijn gekoppeld aan de SQL Server-tabellen. Zo profiteert u van de snelle ontwikkeling van toepassingen die een Access-database biedt, naast de schaalbaarheid van SQL Server.

Voordelen van SQL Server

Nog wat overtuigingskracht nodig om te migreren naar SQL Server? Hier zijn enkele extra voordelen om rekening mee te houden:

Meer gelijktijdige gebruikers In SQL Server kunnen veel meer gelijktijdige gebruikers worden verwerkt dan in Access en is er minder geheugen nodig als er meer gebruikers worden toegevoegd.
Verbeterde beschikbaarheid Met SQL Server kunt u dynamische back-ups maken (incrementeel of volledig) van de database terwijl deze wordt gebruikt. 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, vooral bij een grote database van een terabyte. Bovendien worden query's in SQL Server veel sneller en efficiënter verwerkt door query's parallel te verwerken, waarbij meerdere native threads in één proces worden gebruikt om gebruikersaanvragen te verwerken.
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 gemakkelijker om complexe beveiligingsschema's te beheren. SQL Server is de ideale opslag voor gevoelige informatie zoals burgerservicenummers, creditcardgegevens en adressen die vertrouwelijk zijn.
Onmiddellijke herstelbaarheid Als het besturingssysteem crasht of de stroom uitvalt, kan SQL Server de database binnen enkele minuten automatisch herstellen naar een consistente status zonder tussenkomst van de databasebeheerder.
Gebruik van VPN Toegang en VPN (Virtual Private Networks) kunnen niet met elkaar overweg. Maar met SQL Server kunnen externe gebruikers nog steeds de Access-front-enddatabase op een desktop gebruiken en de back-end van SQL Server achter de VPN-firewall.
Azure SQL Server Naast de voordelen van SQL Server, biedt het dynamische schaalbaarheid zonder downtime, intelligente optimalisatie, globale schaalbaarheid en beschikbaarheid, eliminatie van hardwarekosten en minder beheer.

Kies de beste optie voor Azure SQL Server

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

Enkele database/elastische pools Deze optie heeft een eigen set bronnen die worden beheerd via een SQL Database-server. Eén database is als een database in SQL Server. U kunt ook een elastische pool toevoegen: een verzameling databases met een gedeelde set bronnen die worden beheerd via de SQL Database-server. De meest gebruikte SQL Server-functies zijn beschikbaar met ingebouwde back-ups, patching en herstel. Maar de exacte onderhoudstijd kan niet exact worden gegarandeerd en de migratie van SQL Server kan lastig zijn.
Beheerd exemplaar Deze optie is een verzameling systeem- en gebruikersdatabases met een gedeelde set bronnen. Een beheerd exemplaar is vergelijkbaar met een exemplaar van de SQL Server-database dat zeer compatibel is met de on-premises versie van SQL Server. Een beheerd exemplaar heeft ingebouwde back-ups, patching en herstel en is eenvoudig te migreren vanuit SQL Server. Er is echter een klein aantal SQL Server-functies die niet beschikbaar zijn en er is geen gegarandeerde exacte onderhoudstijd.
Azure Virtual Machine Met deze optie kunt u SQL Server uitvoeren in 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 een paar problemen die u vooraf kunt aanpakken en die kunnen helpen het migratieproces te stroomlijnen voordat u SSMA uitvoert:

Tabelindexen en primaire sleutels toevoegen Zorg ervoor dat elke Access-tabel een index en een primaire sleutel heeft. Voor SQL Server moeten alle tabellen ten minste één index hebben en moet een gekoppelde tabel een primaire sleutel hebben als de tabel kan worden bijgewerkt.
Relaties tussen primaire/refererende sleutels 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 refererende-sleutelbeperkingen.
De bijlagekolom verwijderen SSMA migreert geen tabellen die de kolom Bijlage bevatten.

Voordat u SSMA uitvoert, moet u de volgende eerste stappen uitvoeren.

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

Tip Overweeg om Microsoft SQL Server Express Edition op uw desktop te installeren, wat maximaal 10 GB ondersteunt. Dit is een gratis en eenvoudigere manier om de migratie uit te voeren en te controleren. Wanneer u verbinding maakt, gebruikt u LocalDB als exemplaar.

Tip Gebruik indien mogelijk een zelfstandige versie van Access.

Voer SSMA uit

Microsoft biedt Microsoft SQL Server Migration Assistant (SSMA) om de migratie te vereenvoudigen. SSMA migreert voornamelijk tabellen en selectiequery's zonder parameters. Formulieren, rapporten, macro's en VBA-modules worden niet geconverteerd. In de SQL Server Metagegevensverkenner 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 het migratiebestand voor het geval u besluit in de toekomst meer objecten over te brengen.

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

Om een database te migreren met behulp van SSMA, moet u eerst de software downloaden en installeren door te dubbelklikken op het gedownloade MSI-bestand. Zorg ervoor dat u de juiste 32- of 64-bits versie voor uw computer installeert.
Na de installatie van SSMA opent u het op uw bureaublad, bij voorkeur vanaf de computer met het Access-databasebestand.
U kunt het bestand ook openen op een computer die toegang heeft tot de Access-database van het netwerk in een gedeelde map.
Volg de eerste instructies in SSMA om basisinformatie te verstrekken, zoals de locatie van de SQL Server, de Access-database en de 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 rowversion-kolom toe door Hulpmiddelen > voor nakijkente selecteren Projectinstellingen>algemeen.
Het rowversion-veld helpt recordconflicten te voorkomen. Dit rowversion-veld wordt gebruikt in een gekoppelde SQL Server-tabel om te bepalen wanneer de record voor het laatst is bijgewerkt. Als u het rowversion-veld toevoegt aan een query, wordt dit bovendien gebruikt om de rij na een updatebewerking opnieuw te selecteren. Dit verbetert de efficiëntie doordat schrijfconflictfouten en scenario's voor recordverwijdering worden voorkomen, die kunnen optreden wanneer in Access andere resultaten dan de oorspronkelijke indiening worden gedetecteerd, zoals kan optreden bij drijvendekommanumerieke gegevenstypen en triggers die kolommen wijzigen. Vermijd echter het gebruik van het veld rowversion in formulieren, rapporten of VBA-code. Zie rowversion voor meer informatie.
Notitie Vermijd verwarrende rowversion met tijdstempels. Hoewel het trefwoord tijdstempel een synoniem is voor rowversion in SQL Server, kunt u rowversion niet gebruiken als een manier om een tijdstempel aan een gegevensinvoer toe te voegen.
Als u exacte gegevenstypen wilt instellen, selecteert u Hulpmiddelen voor Controle>Projectinstellingen>Type Toewijzing. 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 om te migreren, zodat u kunt beslissen of u ze wilt verplaatsen naar de SQL Server-database:

Tabellen en kolommen
Selecteer query's zonder parameters.
Primaire en refererende sleutels
Indexen en standaardwaarden
Controleer beperkingen (kolomeigenschap lengte nul toestaan, kolomvalidatieregel, tabelvalidatie)

Gebruik als best practice het SSMA-beoordelingsrapport, dat de conversieresultaten laat zien, inclusief fouten, waarschuwingen, informatieve berichten, tijdschattingen voor het uitvoeren van de migratie en individuele foutcorrectiestappen die moeten worden genomen voordat u de objecten daadwerkelijk verplaatst.

Bij het converteren van databaseobjecten worden de objectdefinities uit de Access-metagegevens omgezet in een equivalente Transact-SQL-syntaxis (T-SQL) en deze informatie vervolgens in het project geladen. U kunt vervolgens de SQL Server- of SQL Azure-objecten en hun eigenschappen bekijken met behulp van SQL Server of de metagegevensverkenner van SQL Azure.

Volg deze handleiding als u objecten wilt converteren, laden en migreren naar SQL Server.

Tip Nadat u de Access-database hebt gemigreerd, slaat u het projectbestand op voor later gebruik, zodat u de gegevens opnieuw kunt migreren om ze te testen of de uiteindelijke migratie uit te voeren.

Tabellen koppelen

Overweeg de meest recente versie van de OLE DB- en ODBC-stuurprogramma's voor SQL Server Server te installeren in plaats van de systeemeigen SQL Server-stuurprogramma's die bij Windows worden geleverd. De nieuwere stuurprogramma's zijn niet alleen sneller, maar ondersteunen ook nieuwe functies in Azure SQL die de vorige stuurprogramma's niet ondersteunen. 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 met de tabellen in SQL Server, waarin uw gegevens nu worden gehost. Door uw gegevens rechtstreeks vanuit Access te koppelen, kunt u uw gegevens ook eenvoudiger weergeven dan met de complexere SQL Server beheerprogramma's. Het is mogelijk om gekoppelde gegevens op te vragen en te bewerken, afhankelijk van de machtigingen die zijn ingesteld door de databasebeheerder van uw SQL Server.

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

Zie Gegevens koppelen of importeren vanuit een Azure SQL Server-database en Gegevens importeren of koppelen in een SQL Server-database voor meer informatie.

Tip Vergeet niet om in Access Koppelingsbeheer 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 tijdens de migratie kunnen optreden en hoe u hiermee omgaat.

Query's

Alleen selectiequery's worden geconverteerd; Andere query's niet, waaronder selectiequery's 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 converteren van Access-specifieke functies en gegevenstypen naar SQL Server-functies. Zie voor meer informatie Access SQL vergelijken met SQL Server TSQL.

Gegevenstypen

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

Groot getal Met het gegevenstype Groot getal wordt een niet-monetaire, numerieke waarde opgeslagen en het gegevenstype is compatibel met het gegevenstype SQL bigint. U kunt dit gegevenstype gebruiken om efficiënt grote getallen te berekenen, maar hiervoor is het gebruik van de Access 16-databasebestandsindeling (16.0.7812 of hoger) vereist, en het werkt beter met de 64 bitsversie van Access. Zie Het gegevenstype Groot getal gebruiken en Kiezen tussen de 64-bits- en 32-bits versie van Office voor meer informatie.

Ja/Nee Een Ja/Nee-kolom in Access wordt standaard geconverteerd naar een SQL Server-bitveld. Om recordvergrendeling te voorkomen, moet u ervoor zorgen dat het bitveld zo is ingesteld dat NULL-waarden niet worden toegestaan. IN SSMA kun je de bitkolom selecteren om de eigenschap Allow Nulls in te stellen op NO. Gebruik in TSQL de instructie 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 kolommen datetime of datetime2 bevat, kan het bericht #deleted worden geretourneerd in de resultaten. Zie een gekoppelde tabel in Access voor meer informatie over SQL-Server database retourneert #deleted.
Gebruik het gegevenstype Datum/tijd van Access om het gegevenstype datetime toe te wijzen. Gebruik het uitgebreide datum/tijd-gegevenstype van Access voor toewijzing aan het gegevenstype datetime2 , dat een groter datum- en tijdbereik heeft. Zie Het uitgebreide datum/tijd-gegevenstype gebruiken voor meer informatie.
Wanneer u datums zoekt in SQL Server, houdt u rekening met zowel de tijd als de datum. Bijvoorbeeld:
- DateOrdered Between 1/1/19 and 1/31/19 mag niet alle orders bevatten.
- DateOrdered Between 1/1/19 00:00:00 AM And 1/31/19 11:59:59 PM omvat alle bestellingen.

Bijlage Met het gegevenstype Bijlage wordt een bestand opgeslagen in een Access-database. In SQL Server kunt u verschillende opties overwegen. U kunt de bestanden uit de Access-database ophalen en daarna overwegen om koppelingen naar de bestanden op te slaan in uw SQL Server database. U kunt ook FILESTREAM, FileTables of Remote BLOB store (RBS) gebruiken om bijlagen in de database van SQL Server op te slaan.

Hyperlink Access-tabellen hebben hyperlinkkolommen die niet door SQL Server worden ondersteund. 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 het gedrag van hyperlinks nog steeds gebruiken in formulieren en rapporten als u de eigenschap Hyperlink voor het besturingselement instelt op waar.

Veld met meerdere waarden Het veld met meerdere waarden van Access wordt naar SQL Server geconverteerd als een tekstveld dat de set met scheidingstekens 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 gegevenstypen in Access en SQL Server.

Notitie 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 in SQL Server, zodat alle aangevraagde gegevens ter verwerking worden doorgegeven aan Microsoft Access. In de meeste gevallen moeten deze query's worden geconverteerd naar Pass Through-query's.

Door gebruiker gedefinieerde functies in query's Microsoft Access-query's ondersteunen het gebruik van functies die zijn gedefinieerd in VBA-modules voor het verwerken van gegevens die aan de query's worden doorgegeven. Query's kunnen zelfstandige query's zijn, SQL-instructies in recordbronnen voor formulieren/rapporten, gegevensbronnen van keuzelijsten met invoervak en keuzelijsten in formulieren, rapporten en tabelvelden, en expressies voor standaard- of validatieregels. SQL Server kunnen deze door de gebruiker gedefinieerde functies niet uitvoeren. Mogelijk moet u het ontwerp van deze functies handmatig wijzigen en converteren naar opgeslagen procedures in SQL Server.

Optimaliseer de prestaties

Verreweg de belangrijkste manier om de prestaties van uw nieuwe back-end SQL Server te optimaliseren, is te bepalen wanneer u lokale of externe query's wilt gebruiken. Wanneer u uw gegevens naar SQL Server migreert, stapt u ook over van een bestandsserver naar een client-serverdatabasemodel voor computergebruik. 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.
Beperk het netwerkverkeer met filters en aggregatie om alleen de gegevens over te brengen die u nodig hebt.

De prestaties van het client-serverdatabasemodel optimaliseren Zie Een Pass Through-query maken voor meer informatie.

Hieronder volgen aanvullende, aanbevolen richtlijnen.

Logica op de server zetten Uw toepassing kan ook weergaven, door de gebruiker gedefinieerde functies, opgeslagen procedures, berekende velden en triggers gebruiken om toepassingslogica, bedrijfsregels en -beleid, complexe query's, gegevensvalidatie en referentiële integriteitscode op de server in plaats van op de client te centraliseren en te delen. Vraag uzelf af, kan deze query of taak beter en sneller worden uitgevoerd op de server? Test ten slotte elke query om er zeker van te zijn dat de query optimaal presteert.

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 SQL-geïndexeerde weergave voor een lezen/schrijven formulier als recordbron.
Gebruik voor rapporten een SQL-weergave als de recordbron. Maak echter een afzonderlijke weergave voor elk rapport, zodat u een specifiek rapport eenvoudiger kunt bijwerken zonder dat dit van invloed is op andere rapporten.

Het laden van gegevens in een formulier of rapport minimaliseren Geef gegevens pas weer als de gebruiker erom vraagt. Zorg er bijvoorbeeld voor dat de eigenschap Recordsource leeg blijft, laat gebruikers een filter selecteren in uw formulier en vul vervolgens uw filter in de eigenschap Recordsource. 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 Voer geen query uit die een combinatie is van een lokale Access-tabel en SQL Server gekoppelde tabel, ook wel een hybride query genoemd. Voor dit type query is het nog steeds vereist dat alle SQL Server gegevens worden gedownload naar de lokale computer en vervolgens de query worden uitgevoerd. De query wordt niet uitgevoerd in SQL Server.

Wanneer gebruikt u lokale tabellen? U kunt het beste lokale tabellen gebruiken voor gegevens die zelden veranderen, zoals de lijst met provincies in een land of regio. Statische tabellen worden vaak gebruikt voor filters en kunnen beter presteren in de Access-front-end.

Zie voor meer informatie Database Engine Tuning Advisor, Use the Performance Analyzer to optimize an Access database, and Optimizing Microsoft Office Access applications linked to SQL Server.