How to Migrate a Busy Database

Maybe it is because your current database platform does not support the functionality you need for a big data project, or maybe you require features that are in a more recent database version. Whatever the reason, migrating a database is often a very valuable and necessary task that allows your business to both keep up with changing technology as well as lower the overall costs of running old systems.

However, if you maintain a very busy system with constant data movement, the thought of migrating to a new database version or a different database platform can seem especially daunting. With SymmetricDS, the process of migrating your busy system can be simplified as it allows you to move your data while still keeping it all in sync. This article will explain the basic steps of migrating your data while using SymmetricDS Pro. The steps for migration are as follows:

1. Stop Symmetric and get your data ready for a backup.

1. Stop your SymmetricDS application in order to stop data from replicating between your databases. To do this, click ‘Stop’ on your wizard.
2. Next, disable your SymmetricDS channel or channels that are used for your data. This includes the ‘default’ channel and any custom channels used by your nodes. To disable your data channels, update the sym_channel table and set enabled to 0. For example:

1. update sym_channel set enabled=0 where channel_id=’default’;

3. Once the channels have been disabled, truncate your sym_data, sym_data_event, and sym_outgoing_batch tables. To do this run:

1. Truncate sym_data;
2. truncate sym_data_event;
3. truncate sym_outgoing_batch;

At this point, you have stopped SymmetricDS in your original server, disabled the channels, and cleared out the sym_data, sym_data_event, and sym_outgoing_batch tables. The reason that these steps are necessary is to guarantee that there will be no pending changes captured before the backup is created. This is important because it will guarantee that your backup does not include any pending data and will therefore safeguard your new database from duplicate data when it is finally synced with the original database.

2. Start the Backup & Migration process

1. The next step from here is to create a backup of your database.The process for this differs between database platforms, so in order to get exact steps on how to do this for your platform, a DBA should be consulted.
2. Once your backup has concluded, the original SymmetricDS application should be started at the same time that you begin restoring your backup on a dummy server to ensure that your backup and change data capture occur at the same time.
3. After the data has been restored on a dummy server, start your data migration to the final destination production server. When finished migrating data, the new production database will be a consistent copy of the original database at the point in time in which the snapshot/backup was created.

At this point, you have your new database that you will eventually switch to from your old/original database. Your new database has all of the data from the original, minus the new changes that were captured at the point of starting up the Symmetric application. So, the next logical step from here is to sync up the old/original database to the new database.

3. Synchronize the the two production servers

1. The first step in syncing up your original database and your new database is to re-enable the channel(s) on the original server. By enabling the channels, the router service will start putting the changes that were collected when SymmetricDS was restarted into batches and routing that data out to the appropriate target nodes.
2. Once the target nodes are synced, configure SymmetricDS for the new database with triggers to sync back to the “old” database in order to create a bi-directional sync scenario. After configuration is done, run an initial load to get any changes from the old database over to the new database.

At this point, you have what is called “parallel production” which means that two production servers are running at the same time and staying in sync with each other and the target nodes.

Over time you can migrate your target applications to point to the new database server and once all applications are redirected to point to the new database, then you can finally uninstall SymmetricDS and shutdown the old database.