Migrating Microsoft Access to Azure SQL

With more people working remotely, it makes sense for databases to be available in a central location and accessible over the Web.  Traditionally, MS Access required an on-premise file system for storing the back-end Access database or a costly SQL Server that probably wasn’t accessible from the Web.  With the advent of Azure SQL, all of that has changed!  Access can now take advantage of high-performance, secure remote SQL Server databases hosted on the Web at a small cost!  Access databases can be migrated to Azure SQL, giving you the best of a remote, stable Web-accessible database and all the great features Access offers.  Here’s a run-down of what’s needed and how it works.

Sign Up for SQL Azure Database

First, set up an Azure SQL database here.  Plans start from just a few dollars a month.  For most Access databases with a small number of users and simple reports, this is all that’s needed.  If you’re not sure, you’ll want to get the opinion of a Microsoft Access consultant who can evaluate your database.

The initial database setup includes an admin user that has access to all databases and server-level operations on this SQL instance.  You’ll probably want to create a separate database login and user that has privileges separate from the server admin user.

Migrate Data and Update App Table References

Second, migrating the database to Azure SQL can be done using a variety of methods.  One common method is to use the SQL Server Migration Assistant.  This tool automates many of the tasks associated with migrating an application such as replacing local tables with links to the Azure SQL tables, data type conversions, data integrity checking, default value checking, schema creation and a lot more.  If you don’t use this tool, you’ll need to create the SQL schema and import the data by some other means.

This tool will also require the Access Runtime Redistributable or Office 365 Access Runtime.  These applications may be incompatible with other versions of Office installed on the same machine, so we recommend installing the Migration Assistant or Runtime on a PC or virtual machine without Office already installed.

New ODBC Driver required

Third, the Microsoft ODBC Driver 17 for SQL Server will be required for the linked tables to connect to the Azure SQL database.  This is likely not already installed on a standard Windows PC.  Earlier versions such as version 13 can work as well.  The connection string for the linked tables will look something like this:

ODBC;Driver={ODBC Driver 13 for SQL Server};Server=tcp:azureservername.database.windows.net,1433;Database=databasename;Uid=username;Pwd=password;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;

Azure SQL Firewall Setup

Because Azure SQL allows public connections, they could become easy targets for denial-of-service or brute force login attacks.  This concern is mitigated by the Azure SQL firewall that’s automatically activated with a new instance.  Firewall rules can be set for either the server level or the database level, and the rules can be set either in the Azure GUI or using stored procedures.

It may not be possible to know the IP addresses of all the Access application’s users due to dynamic IP updates, VPNs and working with varying remote locations.  Consider adding startup code to the Access database to identify the machine’s public IP address and execute either the server-level or database-level firewall rule stored procedure to add the current IP address to the allowed list.  The GRANT CONTROL permissions will be needed for the database-level users to make this update.

Performance Testing

Finally, you may find that some application operations are slower with an Azure SQL database.  This is partially due to ODBC queries that can’t be optimized.  Examples include SQL statements using VBA functions, subqueries, multiple table joins and joins between Azure SQL tables and local tables.  For these situations, consider rewriting the queries as SQL stored procedures or views then linking to those.

Conclusion

Azure SQL is a great way to make Access data accessible from Access applications remotely.  It’s an affordable solution that’s generally easy to set up.  Be sure to do plenty of performance testing on reports and complex queries as queries optimized for Jet databases could perform poorly in Azure SQL (or really any SQL Server) environment.

Need more help from a Microsoft Access Consultant? Give us a call!