What are the prevalent best practices for managing database of an application?

One option is to have an sql script to create the empty tables, and also to start with hardcoded options. (Say all locations, or profession types). During upgrade, you know whats in there and whats not, so on the server, run an sql script that adds columns and any new data.

Another is to do this programmatically, where everytime the server runs- it checks if the db exists, and if not, create it. And during addition of new columns- there would be code that would check if a certain column exists in this schema, and if not add it with a default value of my choice, and also any new data that should be needed.

My constraints are:

  • Should be blindly able to restore a backup if anything goes wrong.
  • Minimal manual intervention
  • I'm planning to run it 24/7 Under addition of tables and columns: By upgrading the SQL database first, and then the application code for corresponding fields. Under removal of tables/columns: By downgrading the application first, and the SQL database.
  • I'm not using an ORM.

Am I missing things here that I should add to the constraints? Please clarify your reasoning in relation to the answer.

Also, I hope that since I'm asking for a constrained answer along with sources and reasoning, this wouldn't count as a opinion based question.

EDIT: In Added constraint(3) in response to comment.

  • 1
    Are you planning on running this 24/7 or not? – Vérace Mar 26 '15 at 11:54

I'd have a few points built up over the years.

There is always risk involved in projects such as this - for every extra 9, you can add a 0!

What I mean but that is that in order to move from (say) 99% reliability to 99.9%, you'll have to multiply your costs by 10.

Minimise, to the greatest extent possible, your web-facing data. Always perform your upgrades at your least busy time.

DDL on a running server is difficult.

In order for upgrades to proceed smoothly - ALWAYS put field lists in your SQL, NEVER SELECT or INSERT more than you absolutely have to.

i.e. SELECT f1, f2, f3... FROM tab1.... (not SELECT * FROM tab1) and

INSERT INTO tab1 (f1, f2, f3) VALUES (not INSERT INTO tab1 VALUES(f1, f2...fn)

The best thing for your scenario IMHO is to have some sort of replication scenario. Get to know your RDBMS's HA capacties very well. Take a look at my answer here (it's for MySQL, but the general principals will hold for any system).

Normally, Master is replicated to Slave.

At cutover time, Halt Slave. Upgrade the Slave. Old Slave becomes new Master. Old Master becomes new Slave.

Test, test and test again!

Good luck.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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