Access Migrations
Let's talk about migrations in Access.
Classifying Migrations
In my time as an Access developer, I have worked with four primary types of migrations:
- "Upsizing" an .mdb/.accdb back-end data file to SQL Server
- Migrating from one SQL Server instance to another
- Migrating data from an existing system
- Migrating data to a new system
Numbers 3 and 4 go together, but they are distinct operations. If you are developing a system for a new client, you will usually be responsible for number 4. If a client is moving away from a system you developed to one that you did not, you will be responsible for number 3. If you built both the old system and the new system, you will be responsible for numbers 3 and 4.
I've been involved in each type of migration multiple times.
Common Activities
Certain techniques apply to every type of migration. Let's cover those first.
Document the process
You want to have a system for tracking what steps you take during the migration. I've never taken too many notes. If anything, I've gone back and regretted that I hadn't been more thorough with documenting the process. There are three main benefits to documenting the process:
- It forces you to think through what you are doing ("rubber ducking")
- It provides insights into why you made certain decisions when you look back on the migration
- It provides a blueprint for future migrations
Script everything
The key to a successful migration is to be able to iterate through the process, continually making small improvements. To do that, your process must be repeatable. And no manual process is reliably repeatable.
Oftentimes, a design decision you make early in the process will cause major problems down the line. If you've scripted the process, you simply go back and tweak the earlier step. If you're doing everything manually, that becomes nearly impossible.
Test the process with "dry runs"
Before you even think about making irreversible changes to live production data, you need to thoroughly test the process with backup copies of the data. Of course, a dry run that's not repeatable doesn't really do you much good, now, does it?
Schedule the final migration around user needs
Usually this means running the migration during off hours: nights or weekends, typically. Not every situation has regular downtime. The important thing is that you coordinate with the client and/or users in advance to minimize the impact of the migration.
Prevent post-migration data loss
Once you've performed the final data migration, you want to be sure that users are not updating data in the old location. I've found that you can't rely on application changes (e.g., relinking tables to the new location) to enforce this. Inevitably, some user will fail to get the required program update and spend several hours updating the wrong back-end data.
That's why my first step of the final migration is to either take the old data off-line completely or at least make it read-only. Which approach I take varies on a case-by-case basis, but the end goal is the same: don't let users make changes to data that's already been migrated. That's a mess you don't want to have to deal with. Trust me.
Unique Considerations
Now that we've covered what each migration has in common, let's discuss some differences and unique challenges.
SQL Server Upsizing
This is a huge topic that I can't fit into a couple of paragraphs. Without getting into specifics, the important thing to remember when upsizing to SQL Server is that it will require changes to your application design. A common example is the requirement to include the dbSeeChanges option when performing certain DAO Recordset operations.
There are also performance implications. One of the biggest benefits of upsizing to SQL Server is the ability to off-load some of the data processing load from the client machine to the database server. However, that often requires redesigning queries or even creating views or stored procedures on SQL Server itself.
SQL Server Migration
There are two main challenges that I've found when migrating between SQL Server instances: 1) relinking tables and 2) migrating security.
Relinking tables. The challenge of relinking tables also applies to the SQL Server Upsing migration I discussed above. The challenge is not so much how to relink the tables, but when. How do you synchronize the data migration with the required application changes? You can:
- Have two different versions of your application, one that links to the old location and one that links to the new one
- Have your application check for the old location at startup and automatically switch to the new location when the old one is no longer available
- Give your users an easy way to manually switch between the old and new locations
- Provide table linking information in a network file and check that file at startup
I've personally used the first three approaches at various times. I tend to favor approach #2 because it allows me to "pre-deploy" an application update independent of the actual data migration. There is a lot going on during the data migration. Not having to worry about trying to synchronize deployment of an application update (a la #1 above) is a welcome relief.
Migrating security. Migrating security in SQL Server is a topic that is well-covered elsewhere. What I've found eases the process, though, is to provide all of the granular security to my SQL Server databases via SQL Server roles (CREATE ROLE...). Then it's a simple matter of assigning SQL Server users or Active Directory logins as members of the appropriate role.
Migrating From an Old System
The key to migrating from an old system is understanding the purpose of each table and column of data. Whether you will be importing the data into the new system or just providing the old data to a third party, it's imperative that you understand what all the existing data represent.
The following items and tools will help with this transition:
- A database diagram showing relationships (foreign keys) among tables
- A description of each table's purpose and the sort of data it contains
- A description of each field's purpose and the sort of data it contains
- Explanations for any numeric or single-letter codes (e.g., "A" for Active, "I" for Inactive, etc.)
- A count of each value for fields that only contain a handful of unique values
Another important consideration is what format to use for the exported data. This requires coordination with whomever will be importing data into the new system. There are a variety of available options, including CSV, fixed length text files, pipe (or other character)-delimited text files, XML, JSON, SQL Server backup files, etc. Each format has its own strengths and weaknesses.
If you export data in CSV format, be sure that whomever you are sending the data to understands exactly what that means. There is no "standard" CSV format. Quoting of string fields, especially when it comes to escaping quotes within the data, varies wildly. I tend to avoid the format because of these challenges.
Migrating To a New System
Importing data from an old system into a new system is probably the most challenging migration of all. Here's one approach to this type of migration:
- Understand the existing data from the old system
- Map fields from the old system to fields in the new system
- Deal with orphaned data from the old system with no home in the new one
- Deal with required fields in the new system that have no match in the old one
- Script the import using test data from the old system
- Test the new system using the imported data
- Tweak the import as needed and repeat step 5 if you changed the import
- Perform the final data import
- OPTIONAL: Run the new system in parallel with the old system
Summary
Data migrations are high-risk operations. With the right approach you can maximize your chances for success. Document the process. Script the details. Test the migration. Schedule to minimize impact.
And then prepare for everything to go wrong anyway.