Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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

share|improve this question
add comment

3 Answers

up vote 10 down vote accepted

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.

share|improve this answer
    
Plus 1 for take a full back up. –  Ian Apr 12 '12 at 19:05
    
Note that there are many tools that help you update a database (prepare the scripts, run them, perform sanity checks etc.). Some are mentioned in the other answers. The term to search for is "database migration" or "schema migration". –  sleske Jan 30 at 8:37
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.