Migrating Access to SQL Server

By | 2020-02-06

Migration from Access to SQL server is not as easy as it would first seem.

There are migration assistants that claim to do the process for you.

The problem is that none of them do it well.

Problem Number One – Quality

The Migration needs to be done in two stages.

The first is to create the new schema (Data Structure in SQL Server)

The second is to migrate the data

Migration assistants don’t do a very good job of creating a nice clean schema, with nicely named constraints and indexes and well thought out data types.

Far better to create a nice clean schema in SQL Server with well thoughtout and named indexes and constraints and then migrate the data. And you probably have some issues you have been getting around to addressing. Like normalising data. Now is a good time.

Problem Number Two – Time

You need to have a migration process will have minimal down time.

Data migration is rarely smooth, with crappy data throwing the process off the rails. SQL Server is not as forgiving with some data types and data type conversion failures can occur. Access will handle bad dates. SQL Server won’t.

So the data migration process must be repeatable.

If this second stage isn’t repeatable then you are hoping that you can just click a button and the migration happens and everyone can go back to work.

In reality it just doesn’t work that way. You need to test, resolve issues and all the while people are still working with your current Access BE in the production environment, adding and changing data.

So you can go one of two ways, you can either

  • bite the bullet now and apply good constraints
    and indexes to prevent bad data transferring and fix the data issues now,
  • or you can ignore constraint issues and let your
    new Db inherit all the crap.

This really is the best time to evaluate and fix your data

Either way, the migration is going to take time and you will need to allow your current BE to be used while you sort the issues.

Come the day of reckoning, once you have overcome all the issues, you can simply delete all the test data from your new DB (or restore from the Empty database) and run the process, release the new Application version with updated table links, give it a quick test and set everyone back to work.