Things to Consider When Migrating Access --> SQL Server

SQL Server Administration
1

Hi experts.
I will soon embark on the fun task of converting an Access db to SQL Server 2019.
I expect I'll use the Migration Assistant rather than Access' upsize wizard.

Can anyone give me some considerations, questions to ask the owner before I choose which Access db as my first one to migrate?
Thanks.

2

  1. How many Access databases are there to be migrated? Do they have similar schemas?

  2. How many tables in total? How much data in total? (Gigabytes or less)?

  3. What is the motivation for migrating to SQL Server?

  4. Assuming there's Forms, Reports, and some other Access-specific elements, how (or will) those be migrated to use SQL Server sources?

  5. Will these migrate to one SQL Server instance, or multiple instances? Why?

  6. Will multiple Access databases be consolidated into fewer SQL Server databases? Or will they migrate one-to-one? Why?

  7. What steps, if any, will be taken to utilize SQL Server features such as stored procedures, partitioning, better security? What about redundancy/availability features like clustering or availability groups?

  8. If there are external applications connecting to MS Access now, how will they connect to SQL Server?

  9. Are these SQL Server instances using existing licenses, or will require new ones?

I'm assuming this project is being approached as a simple "let's migrate it to SQL Server" and may not be considering longer-term aspects of a maintainable database architecture and overall production system.

For instance, "consolidating databases" is probably a good idea, especially if the schemas are mostly the same. Unless, you have specific client requirements that their data MUST be kept separate for security reasons. And when I say MUST, I mean there's a literal contract stating that, not just some verbal agreement or understanding. Otherwise, you could end up buying a new SQL license just to maintain an isolated database that can be kept secure/separate via other means (contained databases, for example, or separate schemas in a single database)

If no one has brought up SQL licensing costs in this discussion, that's a huge red flag. It might be above your pay grade, but you'll want an answer to this so that you're not gleefully spinning up new SQL instances and suddenly the CTO/CFO starts screaming about costs.

1 Like