Introduction

DBGhost is a powerful set of change management tools for SQL database source code. 

Utilizing best practices, DBGhost simplifies the challenge of managing base object and change scripts. DBGhost features both a wizard-based and ActiveX script interface, which allows manual and scheduled code promotion.

DBGhost allows developers to write database code the "old-fashioned" way. When developers create or modify new application class / object definitions, the code that creates the object or class is modified. Once modified, the developer will re-compile the binary to make sure the changes did not break existing code for the application.

DBGhost mimics this approach by allowing developers to promote changes by changing just the base object scripts.

Environment

DBGhost performs well on most workstation - class computers. My workstation is a Pentium 4 Extreme Edition (Hyper Threading) 3.0 GHz with 1 GB of RAM, 80 GB HDD, and Windows XP SP2. I have also installed it on my 2.8 GHz laptop with no problems or noticeable performance differences.

DBGhost works with both SQL Server 2000 and SQL Server 2005.  It also works

with Visual Source Safe 6.0 as well as Visual Source Safe 2005.

Installation

The installation process is a simple windows installer MSI package. The DBGhost Change Management tool comes with a 30 day trial license. The complete version requires the installation of a PGP encrypted key, which is emailed upon purchase of the product.

If you do not have the Visual Source Safe client installed, DBGhost will not display

the VSS features.  However it can still write and read scripts from the local

hard disk or network share.

Using DBGhost

The idea behind each tool is centered around managing base object scripts, and generating

change scripts.  The base object scripts are arranged into local disk folders,

as well as Visual Source Safe project folders.  As mentioned before, having

VSS installed is not a requirement; however to get the most out of what this tool

can offer, I

recommend using VSS or some other version control software.  DBGhost

has native support for VSS, and Innovartis claims it is considering support for

other version management tools.

By creating a set of "baseline" scripts for a given database, you are able to get

a complete "version" of an entire database structure.  DBGhost provides a free

tool that allows you to create these baselines and check them in to VSS automatically. 

The Data and Schema Scripter tool creates base object scripts contained in a set

folders in a specified disk location.  The root folder indicates the database

name, and the subfolders indicate each object type scripted from the database (tables,

views, stored procedures, etc.)  Additionally the tool will add the base object

scripts to VSS using the same folder structure.

The first step during the scripting process allows you to select the database to script.

As you can see, you can type in the name of the SQL Server instance and database. You can also simply identify the instance, and click the "connect" button to populate a drop down list of all non-system databases on a particular instance. To complete your selection, click "Next".

During the scripting process, you can identify all static data you wish to script. Scripting static data is important because typically static lookup data is used to define key Entity Relationships (ER) between the data in the database.

The tool generates a set of files using the table name as an identifier. These SQL files contain simple insert statements to insert the data into the table. In order for the tool to detect changes in static data, a unique constraint must be applied to at least one column in the static data table.

The last part of the scripting process allows you to specify the folders on your local hard disk, as well as the SourceSafe project folders for the scripts. Checking the scripts into SourceSafe allows you to create a version or a "baseline" for coding.

Once the scripts are checked into SourceSafe, you can apply a SourceSafe "label" to the root database project folder. It’s important to label this baseline version because from this label, you will always be able to roll back to a starting point. Additionally, every time a new "version" or build rolls to QA or production, it’s

important to label the entire build of the database. For more information on SourceSafe labeling and versioning, see the Visual SourceSafe documentation.

When the Data and Schema Scripter tool completes, developers can modify database object code by simply adding or checking out base object code in VSS.  Making

changes by simply modifying the base object script allows much greater flexibility

in code development.  The DBGhost Change Manager generates the “change and

alter” scripts, as well as validates the source code.  I'll briefly step

through the process of making a change, validating the change, and applying the

change using the DBGhost Change Manager.

The first screen in the Change Manager wizard allows you to select the task you

wish to perform.  In this case, I have just completed changing a few database

objects, so I want to quickly rebuild the entire database from the scripts in VSS

to see if there are any broken object dependencies.

The first option allows me to build a database directly from the base object scripts. 

Building the database allows me to see any objects that will not compile due to

a dependency issue.

The build process simply uses an existing database as a template, and creates the shell database based on that template. A copy of the production database can serve as a model database, or you can set up your own model databases for build testing.

If you have production databases using different file groups, I recommend writing basic "create database" model scripts and checking them into SourceSafe. This makes it easy for developers to create local model databases for build testing and frees you from having to supply production database backups to developers.

Next, choose what you want to do with the test build database.

Keeping the build database or destroying it allows for greater flexibility during the development process. For example, perhaps you wish to build a "shell" database locally and do some analysis before going through the process of checking out and modifying code. Keeping the build database allows you to formulate a battle plan and choose exactly which objects that will need to be modified prior to beginning development.  In this case, I'll leave the option to keep the database un-checked. 

At the end of the process, DBGhost will prompt me again to let me decide to keep

the database or get rid of it.

Now, we tell DBGhost where to find the script files.

At this point, you may be asking "Wait a minute?  What if someone else scripted

and checked in the database?  I won't have this folder structure locally." 

There are two ways around this.  First, if you have access to the production

or model database that contains all objects, you can use the Data and Schema scripter

tool to create the folders.  Or you can simply recursively "get" the project

using the VSS client, and allow VSS to create the folder structure for you. 

Either way will accomplish the same end result.

Additionally, you can select to get the files from SourceSafe as indicated by the

checkbox at the bottom of the screen.  We'll select that and go on to the next

step.

At this point in the process, we map the VSS project folders to our disk locations. 

DBGhost will support any location to store scripts, including UNC paths.  Simply

enter the VSS login credentials, and double click each entry on the left to navigate

to the appropriate VSS project folder.

Next, we tell DBGhost how to handle the script files from SourceSafe.

You can tell DBGhost to get the latest version of the database object scripts, or

you can specify a specific version by defining the SourceSafe label.  Additionally,

if you have scripts on disk that are not in VSS, you can indicate how to handle

them.

The final step allows us to save the choices we have made up to this point, as well

as some other interesting options.

Here we can define settings that can be used to automate the build process later

if we choose.  DBGhost saves all settings for a particular session in an XML

file.  This XML file can be easily modified to change any setting we have selected

to this point.  Additionally, this XML can be specified on the very first screen

under the option "Use settings from a previous session".  This XML file can

also be defined in the Automation Toolkit (ActiveX script interface) which allows

you to run the process as part of an automated batch script.  If you are automating

your build process, you may want to create a report by selecting the "Create Report"

option.  The report is a simple text file that shows the result of the build

process, as well as any errors encountered along the way.  Another nice feature

is the "Capture Build SQL to file" option.  This option creates the entire

SQL script generated during the build process for troubleshooting purposes.

Once we make all of our choices, DBGhost allows us to quickly validate the session

by displaying a summary of the choices made.

Once we have validated all the settings, we click the process button.  DBGhost

fires the process off and reports any errors encountered.

It looks like there were no errors encountered.  Now that we have a successful

build, let’s try applying the changes to a target database.

The process of applying change is very elegant. DBGhost creates a temporary database on the same instance of SQL Server (to resolve any login conflicts), and uses a simple compare process to determine which objects have changed between the source (temporary) and the target (QA). From this compare process, it generates an update script to modify changed objects indicated. The process is flexible, allowing you to specify objects and changes you wish to promote. In addition to the updated objects, DBGhost allows you to include additional scripts that you write to handle exceptions or data conversion issues.

We fire up the DBGhost Change Manager again, this time selecting the option to build

a database and apply changes to a target database.  The next step allows us

to define the target for the database changes.

Here we simply define the instance and the target database name.  Click Next.

Here, as before, we tell DBGhost what to do with the database it builds from the

object scripts.

Again, choosing to keep the database or dump it when finished is entirely up to

you.

Now, let’s define the checks that will trigger DBGhost to detect a change between

the database built from object scripts, and the target we are applying the changes

to.

I believe this is the real power of this tool.  As you can see, DBGhost allows

us to make specific decisions about the types of changes we want to apply. 

You can also have DBGhost report on any foreign key or indexes that are duplicates. 

Even collation, ordinal positioning of columns and object extended property changes

can be compared and applied directly from the scripts in source control.

Once we define the change conditions, we tell DBGhost how to handle the scripts.

Just as before, we indicate the folders that contain the object scripts.  A

particularly nice feature here is that the object folder entries automatically populate

once the root directory is defined.  Additionally, here we can define custom

scripts we want to use in the upgrade process.  You may have to write custom

scripts to handle data conversion issues or other DML tasks.

Now, we map the VSS project folders to our disk folders.

Again, DBGhost provides a consistent, clean interface throughout the entire process. 

Just as before, double clicking on the left brings up the folder browser so we can

select the VSS project folders.

Now, we define script handling characteristics.

No surprises here.

Finally, we decide to save or keep our settings, SQL, and report.

Additionally, we can tell DBGhost how to apply the changes to the target database. 

I highly recommend capturing all the changes to a SQL file.  The SQL file is

the actual SQL update statements used to upgrade the target database.  This

script is typically used for implementation in the production environment. 

Although you can use DBGhost to upgrade a production database, I don't recommend

it.  If you generate the upgrade script by using a test database, you can review

the changes before applying them to a production database.

Finally, we click next, review our choices, and fire off the process.  DBGhost

reports any errors and prompts you for each change according to our setting above.

In addition to managing and applying database code changes, the tool includes features that allow you to identify and apply changes from a single source database to a target database even on separate SQL Server instances. It actually handles this process more efficiently than SQL Server database replication; although I have to admit that I haven't tried to use the tool for this purpose. The real purpose for this feature is to quickly bring another database up to a particular known version by way of some other database.

The tool doesn't solve the obvious problems, such as adding a non-null column to

an existing table without defining a default value. Also, the tool requires you to create at least one unique column for all static data (lookup) tables. However, this is a design best practice for any lookup changes to data result in drastic entity relationship changes. Being able to uniquely identify a single row of data in a lookup table allows you to target and identify changes over the lifecycle of a database.

Support

DBGhost is simple to use, once you understand the concepts behind the process. I only had one minor issue with VSS integration, which was quickly resolved by installing the full client locally. DBGhost is supported directly by the developers in Great Britain, so the chances of getting knowledgeable support are very high. The support rep even gave me his Skype account so I wouldn't have to waste money on an international phone call. Of course, email support is always available. I found the support rep to be extremely knowledgeable and quick to respond.

In addition to the knowledgeable staff, the Innovartis website has very good information on the DBGhost process. The website also features a set of videos showing the entire change management process in action.

Conclusions

The Microsoft Operational Framework for SQL Server recommends using a tool that performs the functions DBGhost provides to manage database code. However, taking on this task is somewhat daunting for the average DBA. For example, writing SQL-DMO code for SQL Server 2000 is no picnic. Fortunately the folks at Innovartis have done this for you with an easy to use tool in DBGhost.

Once you understand the DBGhost process, you can easily manage and monitor database changes throughout the development lifecycle.

I absolutely love this tool.  In my work environment, database changes are

very fluid and fast-paced.  DBGhost makes this change envionment easier to

manage, which allows more time to actually develop code the old - fashioned way.

Ratings

I will rate each of the following using a scale from 1 to 5. 5 being the best and 1 being the worst. Comments are in the last column.

Product Information

Web Site: http://www.innovartis.co.uk/Home.aspx

Developer: Innovartis Ltd.

Pricing:

Single Machine - US$350

5 Machine license - US$990

10 machine license - US$1,890
More Pricing.