Growth in your business or organization brings an increase in the amount of information you store and manage. As your database size approaches the 2GB mark, which is the limit for MS Access, migrating to another Database Management System (DBMS) should be your immediate thought.
A few great alternative DBM systems present themselves: Oracle, PostgreSQL, SQL Azure Database, and SQL Server, being the most popular options on the market. This DBMS offers a lot in terms of the capability to store and retrieve data safely and accurately. However, since both MS Access and SQL Server have the same roots as Microsoft products, the migration to the SQL Server will be much easier.
This article provides a simple, step-by-step guide on how to migrate from MS Access to SQL Server. We’ll also walk you through some of the advantages of SQL Server over MS Access, and share valuable thoughts from database migration experts.
Why Do Companies Migrate from Access to SQL Server?
When it comes to managing enterprise-level data, MS Access doesn’t cut it for many reasons, including the fact that it’s 30 years too old. Microsoft even announced the retirement of MS Access from its online productivity suite in November 2017, before quietly changing its mind. Updates continued to appear — the latest version of Access in Office 365 was released in September 2020.
Even though MS Access is easier to learn, provides an easier and simple way of creating relational databases, and provides the possibility to use VBA to create fully-fledged data-centric applications, many companies are still migrating from MS Access to SQL Server, and so, over the years.
This is because of the numerous benefits that SQL Server and related DB systems have over MS Access. Microsoft Access is falling behind like FoxPro and COBOL and SQLBase.
To see the big picture, companies migrate Access to SQL Server for the following reasons:
Improved security
SQL Server is more secure than MS Access. It uses 128-bit encryption to provide a more secure way to store sensitive data like Social Security numbers, credit card numbers, and addresses. It also uses the same encryption algorithms to store user and application data in a remote place.
Many IT pros and businesses simply won’t tolerate Access because of its vulnerability to corruption. This means it’ll be harder to expand your business and find new business partners.
Increased availability
With SQL Server, you can dynamically backup the database (incremental, or complete) while it’s in use. The benefit? You do not have to force users to exit the database for every backup session.
As you might suspect, this feature is simply unavailable for MS Access owners. That means the business must pay for downtimes caused by DB inaccessibility for hours, days, or even weeks.
Better performance and scalability
SQL Server is efficient because it performs queries in parallel, thus reducing memory use. The Server also allows multiple concurrent users and supports huge, terabyte-sized databases — compared to the 2GB limit for an Access database. Access is usually preferred for small, general tasks.
Superior database maintenance and integrity
With features like triggers, transaction logs, and repair processes, the SQL Server delivers better stability, integrity, and non-repudiation.
- The MS Access database can become corrupt, thus it’s important to keep it up to date to get the best results. To check the current version of Microsoft you’re using, check the official blog.
Server-based processing
The client/server approach utilized by SQL Server enhances server processing speed by lowering network traffic and dealing efficiently with huge data volumes.
Because Access is a file server, it lacks the capabilities of a database server.
Interoperability with Other DBMS
One problem with Microsoft Access is that it has its own file format that isn’t compatible with any other system. The .accdb format imposed by Microsoft is unique to MS Access alone. This is a huge block to integration and interaction with data types from other database systems.
SQL Server provides good interoperability with other database management systems, allowing developers to create safe and high-performance applications.
Is your issue rather linked to legacy systems built on MS Access? Learn how to integrate legacy systems efficiently, with our complete guide.
Getting Started: MS Access to SQL Server Migration
If you find the SQL Server benefits more attractive than MS Access, and the nature and capabilities of SQL meet your data processing needs, development priorities, and project specifics; it is high time you started upgrading a legacy database to the modern environment.
After that, you need to set up your migration environment. In this guide, we’ll use the SQL Server Migration Assistant for Access (SSMA for Access), a recommended tool from Microsoft, but prepare to conduct manual fixes.
Below is the basic structure of the MS Access application. As a desktop DBMS, MS Access stores all of the components of the database system application within a single file. The DBMS software runs on a desktop computer. Data Entry forms, reports, and queries interact with the database tables stored within the same MS Access Database file.
To begin:
- Develop a data migration strategy for efficient outcomes.
- Download and install SQL Server Migration Assistant for Access (SSMA for Access). SSMA for Access is installed using a Windows Installer-based wizard. We used the Windows installer 3.1 for this guide.
- To get the latest version of the SSMA service and other prerequisites for installation, please see the official download page.
- Get connectivity and sufficient permissions on the computer that hosts the target instance of your SQL server.
6 Steps to Migrate MS Access Database to SQL Server
We’ll follow 6 major steps to import the Access database to SQL Server, divided into two phases: the pre-migration and the post-migration phase.
Before taking on the first step, it is advisable to involve an expert to complete some preparatory steps to ensure a smooth transition. These preliminary operations include adding table indexes and primary keys because every table in SQL Server must have a minimum of one index.
Every linked table must feature a primary key — if any update operation is to ever occur in the table. Plus, it is essential to verify primary and foreign key relationships by checking that they are associated with table fields featuring congruous data types and sizes.
Finally, it may help to analyze the whole system and to eliminate attachment columns. This is essential considering that SSMA does not change tables with attachment columns.
Ready to get started with the Access to SQL Server migration? Let’s dive in!
Step 1: Assessment
Use SSMA to review your database objects and the database itself. To do this, open SSMA for Access and click on a file then new project. Then enter a project name and a location to save your project. Select an SQL Server migration target from the drop-down list, and click OK.
Right-click the database you want to assess in the Access Metadata Explorer and then select ‘ Create Report ‘. You are advised to review the report so as to identify any errors and understand the conversion statistics.
Check your database for vulnerabilities and redundant dependencies.
Step 2: Validate the data types
You’re required to change your data types or validate the default data types. To change your data types, select ‘ Project settings ‘ on the tools menu and then select the mapping table tab. Select the table in Access Metadata Explorer to change its mapping type.
Step 3: Convert
Converting database objects takes the object definitions from the Access metadata, converts them into equivalent Transact-SQL (T-SQL) syntax, and then loads this information into the project.
To accomplish this, select the ‘ Connect to SQL Server’ query and fill up the connection information. In your Access Metadata Explorer, right-click the database and choose ‘ Convert Schema’.
Compare and review the converted objects to the original objects when the conversion is complete to detect potential flaws and resolve them using the recommendations. Select ‘ Review results’ in the output window, and then review the mistakes in the Error List pane.
Finally, select ‘Save Project’ from the File menu. This step allows you to assess the source and target schemas offline and make any necessary changes before publishing the schema to SQL Server.
Step 4: Migrate
Finally, it’s time to move the Access database to SQL Server. The first thing to do here is to publish the schema we converted in the previous step to the SQL Server. In SQL Server Metadata Explorer, right-click the database and select ‘ Synchronise with Database ‘ to publish the schema.
Now, examine the relationship between your source project and your target. To migrate the data, perform a right-click on the database or object you want to migrate in Access Metadata Explorer and select ‘migrate data.’
Alternatively, you can go to the ‘ Data Migrate’ tab. Check the box next to the database name to migrate data for the entire database. To migrate some particular tables, Open the database, expand Tables, and then click on the check boxes next to the tables to transfer data from individual tables. Uncheck the boxes corresponding to tables you want to omit. Once the Access database migration to SQL Server is complete, do well to view the data migration report.
But we’re not quite done yet!
Now Connect to your SQL Server instance with SQL Server Management Studio, and verify the data and schema to ensure the Microsoft Access to SQL Server migration is successful.
Read the full article on ModLogix.
Originally published at https://modlogix.com on June 24, 2022.
