Lessons Learned: Migrating Access to SQL Server

Recently we’ve been involved with transitioning an internal business database from a split Access database (one Access file holding the UI, another Access file holding the data tables) to a Access front-end with an SQL Server back-end. The purpose of this is to eventually migrate away from Access to an ASP.NET web application, but still providing backwards compatibility and an easier migration path. In the course of doing this, we’ve learned a few things:

  1. If set up correctly, the SQL Server Migration Assistant for Access (SSMA) is your best friend and will save you hours. If set up incorrectly, it’s your worst nightmare. In particular, we had a lot of trouble migrating straight from Access 2013 to SQL Server 2016 (as the software is built for SQL Server 2014). Our workaround was to migrate to an Azure SQL database (which is supported) and then move the database to our local server. Note that there are some data security concerns doing this compared to a straight local migration (as your data briefly goes off-site), but in our situation it worked really well.
  2. SSMA also defaulted to using DateTime2 as the SQL Server data type to replace Access’ Date/Time type. Unfortunately, Access doesn’t recognise DateTime2 and treats it as a text field instead, meaning you have to rewrite a lot of queries and code if you stick with that. However, there is the option to use DateTime in the migration, which Access does recognise. DateTime2 does have some added accuracy compared to DateTime, but DateTime should be sufficient in 95% of cases.
  3. After migrating, our second new best friend was the SQL Server Profiler, which allowed us to see what Access was requesting from the server. What was it requesting? e-v-e-r-y-t-h-i-n-g. There were some badly written queries in the Access front-end where the query ended up pulling every row from every related table, one row at a time. Very slow even run on the server. Over the local network, it took literal hours to complete a reporting run. Which brings us to our next point…
  4. If you have a complex query in your front-end, rewrite it as an SQL Server View. Because Views are executed on the server, and only the result set is passed to the client, it can hugely speed up processing on large datasets. It also means you can write queries in standard T-SQL rather than Access SQL. The one downside to this is that if you have a form in Access that is backed by a query, you’ll need to rewrite parts of the form, as an Access form cannot (easily) be backed by a View.

One we realised and adopted these techniques, we were able to complete the migration with much less pain and a lot more fun. Bringing 1990’s technologies and business processes kicking and screaming into the 21st century gave us a lot of satisfaction, and laid the groundwork for the ASP.NET website to come.

Leave a Reply

Your email address will not be published. Required fields are marked *