how to do database updates in each release

Question

Our application uses database (mostly Oracle), and database is at the core. Each customer has its own database, with its own copy of application. Now with each new release of our product, we also need to update the database schema. These changes are adding new tables, removing columns, manipulating data etc. How do the people handle this? Are there any standard processes for this?

EDIT:- The main issue is the databases are huge with many tables and more of huge amount of data. We provide the scripts and some utilities to manipulate the data. How to handle the failures and false negatives? More of looking for this kind articles. http://thedailywtf.com/Articles/Database-Changes-Done-Right.aspx

Answer 1

It depends on your data, but in general you want to ensure that all the data makes it through the upgrade intact.

You will probably want to prepare some DDL scripts that create/modify structures, maybe even one script per structure. You will also probably need some DML scripts that update/backup/transform data during this process. Again, how much of this you will need to do will depend on the nature of the upgrade.

For testing purposes, it might be a good idea to try and get some (or all, if possible) of the client's data before the scheduled upgrade and test it out on their data to see if there's any data in their systems that could break the upgrade process.

Finally, you will want to take a full back up of the client's database right before begining their upgrade, so you can restore if things get weird.

Some other points that might be good to think about:

• If tables/columns are being removed, be sure to remove them in the correct order to avoid integrity constraint violations.

• Are there any constraints or triggers that should be disabled/removed for all or part of the upgrade? Are there any constraints or triggers you could create just for the upgrade that might make it easier?

• If you are updating data for columns that have been indexed, you might want to include re-indexing/stats gathering as a part of the upgrade process.

Answer 2

I recommend you look into myBatis migrations. You can use this to wrap your modification (and rollback) scripts into a simple commmand than can be run as part of an installation/upgrade.

Answer 3

It depends of the actual language, framework, platform and other things. If it's Java (standalone or EE), the best solution could be Liquibase while there are other solutions also.