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 and 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 indexes and constraints and then migrate the data.
Problem Number Two – Time
Second, the 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, 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.
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 then delete all the test data from your new DB and run the process, give it a quick test and set everyone back to work.