Pitfalls to Avoid When Using the New Microsoft SSMA Version 7.8
Microsoft has been updating their SQL Server Management Assistants on a regular basis, and they just updated the SSMA for Access. However you can’t see what’s new for 7.8 in their official documentation. The latest version of SQL Server Migration Assistant (SSMA) version 7.8 can be downloaded from here.
The 7.8 version is much easier than prior especially with handling 32/64 bits but there are quirks, which we’ll be looking at.
Which version should I download?
SSMA must be able to connect to Access and in order to do that, it must be the same number of bits as the installed Access. For that reason, if you have 32-bit Access, you should download and install the 32-bit SSMA. Note that 32-bit programs are also referred to as “x86”. Otherwise, you should install 64-bit SSMA to work with 64-bit Access.
I loved the fact that SSMA recognized from the very beginning that the server was on Azure SQL. Big plus, thumbs up!
When using Office365 you need to download Access Database Engine 2010
Not long ago I needed to install it on a client’s VM and, while doing it, I came across these errors / bugs.
When running Office 365, you will need to download the Microsoft Access Database Engine 2010 Redistributable so that SSMA can read your Access data. The Microsoft Access that comes with Office365 is in a sandbox environment and therefor not accessible to SSMA.
Additional problems you may encounter with SSMA
After installing Microsoft Access Database Engine 2010 Redistributable I had another error, also related to Office 365. This thread may help!
In order to resolve the issue I uninstalled Office 16 Click-To-Run Extensibility Component 64-bit Registration – see image below.
I couldn’t migrate all tables at the same time
After logging into SQL Server, I selected the tables which I wanted to synchronize and hit the button. The migration didn’t take place though for all tables, but only one! So I was only able to migrate one table at a time, which is terrible. Think about having to migrate 100+ tables and queries, that wasn’t my issue but still … a nightmare.
You will need to add foreign keys yourself
My local Access database didn’t have any foreign key constraints set up. When migrating to SQL, SSMA didn’t ask me to set foreign key constraints. Technically not an issue with SSMA tool itself, but something to be aware of and to check when migrating since, I guess, the original database didn’t have any constraints, so we need to make sure we enforce it. SSMA should do that for us.
What bugs or errors did you get when using SSMA? Where they crucial for your project? Let us know in the comments below.