SQL Server: Mitigating schema changes/upgrades

Question

I haven't spent a ton of time researching this yet, mostly looking for best practices on upgrading/changing DB schemas.

We're actively developing a new product and as such we often have additions or changes to our DB schema. We also have many copies of the DB -- one for the test environment, one for the prod environment, dev environments, you name it. We don't really want to have to blow away test data every time we want to make a change to the DB.

Are there good ways of automating this or handling this? None of us have really ever had to deal with this so...

Answer 1

Normalize, Normalize, Normalize

Then do it again.

This means that you can just slip new tables / views and other tasty goodness in without disrupting other tables.

I have seen databases that claim to be normalized, but are not. Try and look ahead when thinking about separating things out.

You may pay a bit with joins, but query views not tables and adopt a good caching strategy and you will be good to go. Some NoSQL databases offer better flexibility, but are a bit like the schizophrenic nephew at the moment in terms of maturity.

What we have is an SQL-independent table description which gets translated into SQL and updates and an ORM/ActiveRecord/Mapper that uses nothing but data from the SQL database schema itself to work out what is going on ... this means you app adjusts to changes too. We also use stored procedures heavily for inserts and mainly read from views.