How do I merge schema changes made to a production database into my migration-managed process?

Question

My team is evaluating dbdeploy for managing database migrations. As I understand it, using migrations requires a bit of process discipline, namely that a migration is written for every change, and that to reach production, it would have to be promoted from local to development to test to production.

Occasionally our production DBA team makes schema changes directly to the production environment. If we write a new migration to make the change against our current development version of the database, that migration will never be tested against a schema that already contains the change until the migration is being deployed to production. This concerns me.

The other option is to make the change directly to the baseline schema, then rebuild the database in all environments (local, development, test, stage). This approach concerns me, because the new schema could cause one or more migrations to break.

How are people currently handling this scenario?

Answer 1

It is understandable that DB changes on the production schema have to happen from time to time. It is very important though that these have to be documented and communicated ASAP back to the development team. Plain text with the sql executed together with comments on affected use cases/functionalities and possible bug tracking issues would do

Fetching the live DB back to the development every now and then and comparing it (it's schema) with what the developers have is a good idea as well.

Answer 2

I assume that the only thing DBA can change on production DB is to add an index here and there and tweak a few sprocs - all for the sake of performance. All other changes to the DB can, generally speaking, render DB schema to be incompatible with the application.

With this in mind, the only thing that actually should be versioned are sprocs, and it's the responsibility of a DBA to check them into source control. Indexes are much more volatile and may actually not be included in migration scripts.

Answer 3

We restore a copy of our production DB onto a test server overnight.

This then serves:

• As a reference copy (code and data)
• We can reset any changes we've made
• We can test against real data
• We can side by side new/old code preformance
• We can generate 100% safe change/rollback scripts (Red Gate)

We don't rebuild dev/test databases etc but some of our fellow projects do. However, I'm not sure of the benefit because a database is not schema and code: it's data too. It's different to a complied .net app.

In my shop, a production DBA making changes to a prod DB (any change at all) without approval would be fired. And it's happened.