The only true change management solution for SQL Server code

At last, you can take control of your SQL Server code!

It may be that you are currently struggling with a problem that we all, in the SQL Server development community, face at some point. You have a production database out there and you need to make amendments to it in a controlled, auditable and repeatable way due to new releases of software and/or bug fixes. You may have adopted one of the many well known solutions to this issue such as having a development database where the changes are made initially and unit tested and then, possibly, a clean testing database where someone else can verify that things are as they should be.

All too often, though, things don't go as smoothly as we would like.

Developers overwrite each others changes and the deployment of the code often yields a schema that is subtly different to what has been developed. Worse still, these problems are only picked up during the test phase and so they are more expensive to fix than if they were resolved during development. If this strikes a chord with you then read on, as one of our developers describes a revolutionary new approach to this that can transform your SQL coding and deployment process. When you're finished reading you'll probably wonder why someone hasn't thought of it before...

The problem

"Throughout my career as a developer I have worked for many large corporations on big software development projects that involve a lot of developers, DBAs, testers etc. The one common problem with all of them was the team development and deployment of SQL code. All the C#, HTML, ASP etc. stuff was easy, we just used the source control system to check the code out, make our amendments, and then check it back in again. But SQL was different, just checking out a table creation script, adding a column to it and then checking it back in wasn't enough - you had to hand code an upgrade script and then tell the deployment guys or DBAs about it. Then, after it had been executed against the target environment, if a further issue was found then another mod script needed to be coded and so on and so. We very often ended up with lots of incremental scripts just for one new piece of functionality. When you multiplied that out by many developers and many new pieces of functionality the scripts could run into the hundreds.

Database comparison and synchronization tools were useful for producing the upgrade/synchronization scripts automatically but they still didn't really cover the problem as they dealt an existing development database that had lots of unwanted dev-time objects. Our DBAs still had a mundane, laborious process of selecting which objects to promote whilst talking with the developers to work out should, and should not, be promoted. Also, after many dev-test cycles the delta scripts still mounted up.

Basically, the real issues like the whole team working together seamlessly, making deployments easy and having a full audit trail weren't addressed at all."

The solution - the DB Ghost Process™ - simple, efficient and automated

What you do, as a one-off process, is to script out your databases entire schema and static data into individual scripts and add them into your source control system using the DB Ghost Data and Schema Scripter. Then just instruct the development team to use them in the same manner that they would C#, C++, VB, ASP, Java code etc. Basically, the set of object creation scripts under source control are now the "development database".

For example, if you want to edit a table, you check it out first, add, say, a column, execute it against your dev database, do your unit testing/bug fixing and then simply check it back in when you're finished. This applies equally to every type of database object. What you end up with is a set of base schema creation scripts that succinctly reflect the desired database schema - in other words your desired "source database" as a set of scripts.

But what good is a set of base scripts when you have an existing database to upgrade? How are the changes deployed without losing the data in the target database?

This is where the DB Ghost tools come in - they enable the whole process

You point the DB Ghost Change Manager at your target database and at the scripts that have been extracted from source control and click "Process" (or run from the command line / API using the Automation Toolkit). The Change Manager will then verify that all the scripts compile, with no dependency errors, by building a brand new database. If the database has build breaks then you can automatically email the development team, but, if it has built successfully then it is used as the source for a compare and synchronization of the target database, making all necessary changes to the target database to ensure that it reflects the scripts EXACTLY.

This now means that your existing investment in your source control system is fully realised for SQL code. In fact, if you label the source code at regular intervals then you have a named, point-in-time view of your entire database. You also have a target database that PRECISELY matches a labelled/baselined set of scripts under source control which enables you to audit every single change that is made to the database code using the history and diff functions of the SCS. Think about it - all you have to do now is label, extract and run the Change Manager to update your target databases. If you want to know what has gone on in between baselines your SCS can tell you, down to the last character, what, when and why changes were made and who made them. i.e. everything you currently enjoy for all your other code assets.

This same process can be used for all databases in your enterprise ensuring a consistent, auditable and reliable database. When you're ready to release to the live system just use the Change Manager to produce a single script that will apply the changes to your production database.

Straightforward, easy and fast.

That's the DB Ghost Process and something which we believe is a ground breaking way of dealing with SQL development and deployment issues - a uniquely powerful one.

Can you afford not to have DB Ghost?

You don't need any more hardware or software. You have it all already except for the last piece of the puzzle - DB Ghost™.