This post will run through some of the points that should be considered when migrating database server. For a change, this post will not have any code :(.
Since you are reading this, congratulations on getting approval for new database server from your management. May be you or your manager have gone through a tough time convincing folks to get it.
Below is the checklist I had checked when leading the database migration effort from Dev server to Prod server. We migrated to Oracle 11g from Oracle 10g. But the upgrade was seem less.
DBA had prepared the scripts to migrate data to new server. He locked the Dev database server on Fri eve and started the process of export-import. After approx 12 hours, he was done and the ball was in our court to make sure application reads from new database, perform sanity checks and test batch processes (already modified to read from new db).
Below is a handy checklist:
1. Cron jobs: Modify and test the shell scripts that connect to database (using sqlplus for Oracle) with new connection details.
2. Batch processes: Modify and test batch processes that run on a scheduled interval so that they perform all CRUD operations on new database.
3. Application configuration: Modify the configuration file (generally system.properties) of your web application to get connections from new database. Ideally this should be the only change in the App. But check for any hard-coded references to old database.
4. Database links: Test database links on new database server. Fully qualified path name of the server can lead to issues. Also, you can request a db link from old to new server and vice versa for getting more flexibility.
5. Other databases: If you are using other databases to do some work, modify the processes to point to new database instance.
6. Schedule jobs: Test Oracle scheduled jobs on new database instance. Think about whether you want the jobs to still run at old db instance.
7. Oracle packages: Test advanced Oracle packages you are using. Eg: utl_mail, utl_file, etc.
8. Oracle directories: Think about Oracle directories you are using. If you are migrating your database to a different Unix server, the directories will have to be recreated. Test processes that reference the directories.
9. Performance: The new database will most likely give better performance. If that’s not the case, notify the DBA about it. Keep an eye on the performance to see if it deteriorates.
10. Profile file: Modify your Unix profile file if you are loading any database related details.
11. Meta data: If you had access to Oracle dictionary tables in old database, make sure you can access those from new database.
12. Notification: Notify all users who are directly connecting to your database. Ideally you would want to give them a heads up before the migration to avoid any surprises/shocks :).
13. Back up plan: This is very important. Have a back up plan to roll back your code changes if database migration has some issues. You don’t want your application to be unavailable (may be for few days) until the issue is fixed.
Above is the check list I had to track database migration. Feel free to add some more you think are worth considering.
Thanks for reading this post.