The best way to manage database changes

Question

What is the best way to manage database changes? I need to have a solutions regardless the database client's language. Also I'd like to be able to use specific database features in those changes such as stored procedures, triggers and so on.

Answer 1

Well, we are currently using Redgate's toolbelt which contains a Database Compare, Data Compare, etc.

You can also use any source control to track changes to your database objects.

Answer 2

Not sure what you are asking here, but if it's a good way to manage schema changes, and keeping them in synch between versions and deployments, it's hard to go wrong with Visual Studio Database Edition. It's sole purpose in life is to manage database schema changes, validate the schema, build and generate deployment scripts. If you have Visual Studio Developer Edition or Visual Studio Team Suite, you can get it for free.

Answer 3

First of all, make sure you have your entire Database Build scripted so you can rebuild the database if needed.

Each change should then be written as an Update script. That way you can run each change individually against the your databases.

Once the change has been commited to the codebase, merge the change script with the build process so it happens automatically...and then archive the change script in case any questions arise.

Answer 4

First and foremost, put all database changes in scripts and put them into the source control system.

Next remove any permissions to production that developers have. Exactly two people should have rights on production in a small to medium shop, the designated dba and his or her designated alternate. Once devs can't make changes to prod, you will find it easier to get them to actually write and use scripts.

Never run a script on prod that wasn't first loaded to QA or staging. If there are issues with the script, it should be found at this point.

Answer 5

Use idempotent change scripts (and maybe have a look at LiquiBase or dbdeploy).