Migrating an app from one database to another

November 27, 2013 at 12:21 am Leave a comment

We have a bunch of apps that run against a database, dating back about 10 years.  These apps have grown over the years.  The original database was done in MSSQL.  The apps that access this are mostly done in PHP.  We’ve recently needed to add a few fields to the database, but instead of trying to get them added to the MSSQL database, I took the approach of building a class to access the data.  This class actually reads some fields from the original MSSQL database, and others from a MySQL database.  The update logic is built into the class, so it doesn’t matter which database contains the field, it will perform the update to the appropriate DB.

So, this has all been working well now for the last 5 or 6 months, and now we want to take the next step.  We want to move all the fields that are still relevant to the MySQL database, and just stop using the MSSQL database altogether.  This is made more complicated by the fact that numerous apps access the MSSQL database directly.  In particular, there is one old VB.NET app that we want to eliminate.  I’ve already created a web page that does the majority of the things this old VB.NET app does, and it uses my new class.  There’s still a little work to do to get everything, but it’s close.

I’ve been trying to think of how to best handle the DB migration.  I see a couple of options:

1.  Duplicate the fields exactly (column name included) from the MSSQL DB into the MySQL DB, and have the class stop using the MSSQL DB.  This would require all the apps that access the MSSQL DB directly to be switched to MySQL.  That’s a lot of work, and will likely take a few days, perhaps even a week or so (especially given this time of year and the vacation days people take).  During that coding time, some apps are using the records from MySQL and others are using the MSSQL DB.  Since these apps manage the configuration of hundreds of routers and thousands of switches in about 700 remote sites, we really don’t want anything going wrong, as it could be a major pain to fix.

2. Create new fields in the MySQL DB to match the required fields from the MSSQL DB, but name them differently.  This gives us the ability to search easily through source code that accesses various field names (the MSSQL versions) to make the MySQL changeover.  This would be nice, but I believe this would also require us to code the class so that the class would synchronize the “duplicate” field in the MySQL DB with the appropriate field in the MSSQL DB.  If a record is updated using the MySQL field name, it would need to also update the corresponding MSSQL field with the same value.  Similarly, if the update used the MSSQL field name, it would need to update the MySQL field.  Finally, upon each load of data into the class, we’d need the class to compare the  corresponding fields, and if the values weren’t equal, make a decision on which one “wins”, and is then copied into the “loser” field.  The start with, we’d probably want the MSSQL side to win.  When we are certain we were done with all the code changes across a few servers, then it would be time to drop out the MSSQL code altogether.

I’ll probably give this a good bit more thought, and look through the source code a while before I come to a 100% answer, but I’m leaning toward option #2 at this point.  Once the coding is done to the class to keep everything in sync, this should allow us more time to finish the other coding changes.


Entry filed under: General, Networking, PHP, Programming General.

Thanksgiving Credit Card Float

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


November 2013
« Oct   Dec »

Most Recent Posts

%d bloggers like this: