Migrate Access Database to Azure SQL Database

Author: Prashant Pawar, Cloud Engineer.

In the digital era nowadays, a good database is crucial to any company or organization. This is because the database stores all the pertinent details about the company. Microsoft Access is a relational database. A relational database is a collection of data items organized as a set of tables. Access offers the functionality of a database and the programming capabilities to create easy to navigate screens (forms). It helps you analyze large amounts of information and manage data efficiently.

But there are certain limitations in Access database that’s why in this post, we are going to see how to migrate Microsoft Access database to Azure SQL database.

Why migrate from MS Access database to Azure SQL database?

There are few limitations of Access database:

1. MS Access is not available over the internet.
2. MS Access is not suitable for team use.
3. MS Access is suitable only for small databases.
4. SQL for MS Access is not as robust as MS SQL Server.
5. MS Access ties you to Microsoft Windows.

Advantages of Azure SQL database over Access database:

1. Enhanced scalability and performance improvement
2. Reliable and Highly Available
3. Security
4. Available over the internet.
5. Robust structured query language.

So now we know the need to migrate from MS Access database to Azure SQL database, we will see how to migrate it. For the migration process we need to install SSMA for Access, Access database engine and SQL server native client on our machine.

1. Create a new SSMA Project. After you create the project, you can set project options, including conversion options, migration options, and data type mappings.

2. Add Access database files to the project.

You can add individual files, including files that you find on the computer or network.

3. Connect to the target instance of SQL Azure.

4. To customize the mapping between one or more Access databases and SQL Azure schemas, map the source and target databases.

5. Optionally, you can create an assessment report to determine whether the Access database objects can be successfully converted to SQL Azure.

6. Convert Access database objects to SQL Azure object definitions.
7. Load the converted database objects into SQL Azure.

You can load the database objects into SQL Azure by using SSMA, or you can save Transact-SQL scripts.

8. Migrate Access data into SQL Server.

9. If you want your Access applications to use the data in SQL Server or SQL Azure, use link the Access tables to the SQL Server tables.

10. For linking database, you need to click on External Data and then click on from Azure database. To create a link you need to click on link to the data source option.

11. Create a file DSN to connect to a Azure SQL Database. Select SQL Native Client foe creating data source name.

12. Test the created DSN.

13. After creating DSN, you can connect Access DB with Azure SQL Database. Login into the SQL Server using Login ID and password.

14. Select databases which you want to link with Azure SQL.

15. Below is the database which is successfully linked using SQL Native Client.

So, by following this process we can migrate Access database to Azure SQL database.