Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

DBGhost Review

By Jeremy Brown,

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.

Ease of Use 5 The product is extremely easy to use and flexible.
Feature Set 5 DBGhost has all the features you need to fully manage the change lifecycle.
Value 5 Managing SQL Server code changes has always been an issue. DBGhost solves this issue, hands down.
Technical Support 5 The support rep was not only one of the developers, he was also a DBA.
Lack of Bugs 5 The product works. Period.
Documentation 5 The DBGhost website has a ton of useful resources to help you get started.
Performance 5 DBGhost seemed to work very fast. I did notice a slowdown when checking in a large database, but this was due to SourceSafe.
Installation 5 Simple, painless Windows Installer MSI. Using the license manager is easy as well.
Learning Curve 4 You have to understand how DBGhost works, as well as the process it uses.
Overall 5 I really like this product.

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.

Total article views: 6344 | Views in the last 30 days: 6
 
Related Articles
FORUM

DBGhost

My company is currently in the process of becomgin SOX compliant. We are setting up a development,te...

FORUM

DBGhost Review

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/column...

SCRIPT

Script to change auto growth settings for all databases in SQL Server

Script to change auto growth settings for all databases in SQL Server.

ARTICLE

Databases for Change

Meaningful change often requires information collection, processing, retrieval and distribution. As ...

FORUM

Working with multiple Database change Release.

Latest database change script release should check for previous database change too

Tags
product reviews    
reviews    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones