I depend on these tools also and they certainly allow you to get to the next level of development faster, great article.

Apologies for the long comment here but this article really got me thinking...

Building an automated and repeatable process for building and maintaining databases is really the holy grail of release management.

Tools like RedGate's line of products including Compare/Data Compare have gone a long way towards lowering the barrier to entry for doing this, and SQL Source Control is a logical addition to the product suite. I would go as far as to say it easily rivals the capabilities of the current Visual Studio-based database projects.

A bit of background on our shop. We have been utilising a deployment process built on RedGate's toolstack (including the excellent Compare API) and also TeamCity + MSBuild and the results have been great... for the most part. The part where it can get tricky is when we have a mix of auto-generated SQL Compare scripts and our own custom scripted migrations: sometimes we need to perform SQL operations not possible in RedGate tools like renaming a table or column, configuring Change Data Capture instances, or performing complex refactorings like splitting a table in two.

Even combining Compare and Data Compare has its challenges, especially when trying to create a new source controlled table and populate it with static data in one atomic transaction. Trying to include these kinds of migrations as separate steps in our build process has resulted in a bit of a "meatball sundae": two things nice on their own but don't mix terribly well!

We've also found that putting in place code review practices with the RedGate scripts has proven to be challenging given that the auto-generated scripts exist outside of source control as build artifacts (as they should be). It means that the DBA's need to look in two places to do code review, and they also need to review scripts for each environment (DEV, STG, QA, PROD etc).

We don't have a solution to these issues right now and the level of automation and dynamism these tools allows will keep us using RedGate for the moment, but I would definitely encourage readers to consider alternative approaches to source controlling and releasing databases prior investing in a tool, as we now are. Two notable contenders:

* Liquibase http://www.liquibase.org/ . A vendor-agnostic approach to database refactoring that allows targeting of multiple RBDMS platforms (i.e. MSSQL, MySQL, Oracle etc) using a special migration language. A highly structured way of applying database changes that is optimised for parallel development.
* DBDeploy http://dbdeploy.com/ . Migrations are hand-written and are executed in a simple predefined sequential order (i.e. Script 1, Script 2, Script 3). The target database keeps a record of which have been executed and never runs the same script twice. As simple as it gets really. Supports many RDBMS platforms.

Of course these tools don't offer the same wonderful intuitiveness of the RedGate products as Troy has eloquently pointed out here, but they do achieve the goal of enabling developers to autonomously work in their own sandbox by allowing the database to be fully reproducible from source control.

We're intending to implement support for custom migration scripts in the next versions of SQL Compare and SQL Source Control.

The aim is to make it easier to handle exactly the situations you describe: renaming a table or column, splitting a table, dealing with NULL to NOT NULL conversions, etc.

SQL Source Control will allow you to commit the custom migration script alongside changes to the database, and SQL Compare will be able to pick up this custom script and incorporate it into the sync script.

We're still in the early stages of implementing this and are running a survey (https://www.surveymk.com/s/migrations) to collect user feedback on how this feature might help you. If you could find the time to fill it in, that would be great.

Alice
Project Manager
Red Gate Software

Thanks Alice, great to hear you're already considering this!

I have lots of thoughts on the subject and very keen to see a workable solution in place so happy to share more details on your survey.

"And just before we go any further, it’s never ok to pull production data down into any other environment for the purpose of testing performance. There has to be a better way…"

I sympathize, but I'm not sure that I agree. I agree that sensitive data shouldn't be stored in a test environment-- As I read somewhere once, test environments are for failure, and security vulnerabilities are a kind of failure.

But for data that is "transactional" but not necessarily sensitive-- like a a database of movie titles or something like that-- I think that it's fine to have the live data in the test environment. (the company I work for deals with a combination of public-record data and more sensitive data; there's a clear line for us in how those two kinds of data are treated).

On the Continuous Integration front, I really like what you've laid out here, especially since the toolchain is a lot of stuff that we already use in-house.

That said, I really like SQL Compare Pro, but I'm wary of building a continuous integration process around it. If I refactor my schema and move a column from table A to related table B, won't I lose data? The column will be dropped in A, but the data won't move to table B. I've lost data when experimenting with SQL Compare before (not in production). But perhaps I wasn't making use of a feature that warns about potential data loss?

Is there a way to use SQL Compare for continuous integration, while ensuring that schema changes involving dropping columns are applied safely?