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.
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.
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.
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
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
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
Again, choosing to keep the database or dump it when finished is entirely up to
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
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.
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.
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.
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.
|Ease of Use
||The product is extremely easy to use and flexible.
||DBGhost has all the features you need to fully manage the change lifecycle.
||Managing SQL Server code changes has always been an issue. DBGhost solves this issue, hands down.
||The support rep was not only one of the developers, he was also a DBA.
|Lack of Bugs
||The product works. Period.
||The DBGhost website has a ton of useful resources to help you get started.
||DBGhost seemed to work very fast. I did notice a slowdown when checking in a large database, but this was due to SourceSafe.
||Simple, painless Windows Installer MSI. Using the license manager is easy as well.
||You have to understand how DBGhost works, as well as the process it uses.
||I really like this product.
Web Site: http://www.innovartis.co.uk/Home.aspx
Developer: Innovartis Ltd.
Single Machine - US$350
5 Machine license - US$990
10 machine license - US$1,890