Migrate SQL server data to new schema

Question

I have a database that was originally created by a couple of people for a small business unit (about 8 staff and 10 customers) in MS Access. The original 'developer' was a non-geek who had been sent on a two day ms access course by a previous employer. It was poorly designed but worked well enough, as the unit grew there was the need to have more than one person administrating the data. At this time I had joined the unit and suggested using SQL server as the back end and keeping the access front end allowing multiple users, three or four at that time.

It now has approx 40 users servicing 50+ business customers, and is essentially a complete mess. But we need this tool to keep track of our jobs queue, who is doing what, when it is due, who for and every thing. Without it we could not function.

So I now want to throw it away and rebuild from scratch and do it properly, and ditch the access front end in the process. While doing this I need to keep the current system running, develop the new one and some how keep the data in sync and run them in parallel for a while until the higher powers are satisfied it's not going to cause any problems.

Does any one have an suggestions or pointer on how to keep the data in sync given that I will be taking several giant tables and normalizing them into sensible tables in the new schema?

It is not a huge database, the main table listing all jobs grows by about 1500 records per month for example, and currently has about 100,000 rows.

My thoughts were to add a row version column to the tables, and then hack something together to monitor it and translate to the new schema.

Is there a better way?

Answer 1

Migrating the Data is a Job in itself Keeping Data in sync is a job is yet another job. Creating a new front end is yet another job.

there's lots of ways to tackle the problem.

there's a lot that can go wrong with data migrations, so if you've never done one before unless the business is yelling for it I'd move very slowly and carefully.

1st I'd replicate the Schema and just do a simple import into sql.

From there build out what you want your new schema to look like and build your scripts for moving data around into your new schema, make sure you're logging every single insert update delete etc, it's easiest if you log the command that you ran to move it, and the Command to undo the operation in a central table.

then build your new front end against that and test it thoroughly.

then test your scripts for exporting from access and importing into your new schema, then you can automate the Export import process until it runs perfectly every time I'd probably do at least 100 or so executions to test the process before the move.

I wouldn't worry about keeping it in sync until you've got buy in from the business with your new process and application then I'd just set a time for the Switch.

If you have to have 2 simultaneous systems In place you've got a whole different set of problems.

Which one becomes the source of truth? do you need to be able to export everything back out to access at any given time.

Just about any change in scope will add additional time to the Project.

Hit me up if you have any other questions. I've done a couple around 50 or so of these small scale data migrations from flat files to sql, from access to sql, from sql to access, oracle to MongoDB, from SQL to Mongodb, mysql to sql, mongodb to SQL and they take a lot longer than you'd think if you want to do it right.

Answer 2

A possibly simple way is replacing all MS-Access tables with links to views in your SQL Server with the exact same structure as the old Access tables. If the views are simple enough (e.g. a select statement from a single table with a primary key and unmodified columns -other than renaming them-) they'll be directly updatable, otherwise you can use updatable views.