Anyone who’s tried going through lists of tables and columns within tables trying to work out which version of the schema is in use on a particular copy of the database will know that it’s a quick route to a major headache. And even if you’re really careful, there’s always the nagging worry that you might have missed something small but significant. If you have to provide an audit trail, showing how databases have been created and what their definitions were, the situation is even trickier. There are ‘difference tools’ on the market that will compare two databases and create a script showing the differences, but that may not keep the people requiring the audit happy. Add in problems such as trying to return to a previous version of a schema, and you’ve got real problems.
So how do you manage situations where you want to make changes to databases in a consistent, repeatable and auditable fashion? Life gets particularly difficult if you have several database developers working on a large database project. Chances are they’ll overwrite each other’s changes, or at least produce scripts that don’t work because of changes that someone else has already made.
A helping hand
DB Ghost is a product designed to let you manage your database structures, and making changes to those structures, in an auditable, simple to manage fashion. Essentially, it lets you create a repository of SQL scripts that describe your database schemas. If you want to create a new database with a particular structure – on a new server, for example – you just run the scripts. More importantly, you can use DB Ghost to take scripts and apply the changes from those scripts to an existing database structure. For example, a developer could make changes to a stored procedure, and you then use DB Ghost to test whether the changes make sense. If they appear to work you can then make the changes to the definition of the stored procedure within the database.
The first task in DB Ghost is to script existing database structures
You can also use DB Ghost to compare one version of a database with another by using the definitions in the repository to see what the differences are between the two. You can make two databases the same, and validate any changes that would be involved in making one database identical in structure to another before the changes are made.
Previous generations of the schema can be safely archived within the repository, so you can revert to an earlier version of the schema if necessary. There’s a provable audit trail so that you can show what changes were made, when, and who authorised the changes. You get the choice of creating a repository using SourceSafe, or you can simply store the details in a set of folders which you can manually add to a source code system such as CVS, PVCS, ClearCase, Synergy, etc. Whichever method you choose, when you first create the scripts – from an existing database, say – you get a directory or project structure containing all the details about your database – logins, static data, stored procedures, tables, users and roles, and views are all held in separate folders.
Wizard and scripting
DB Ghost is very easy to use, with a choice of interfaces to suit most tastes. There’s a wizard that guides you through all tasks in a point and click manner. Alternatively, you can run the whole thing from the Command line.
Tasks in DB Ghost are managed via a wizard
When you start the main application, you get several choices to use when you’ve created or modified some SQL source code. Your choices are to build a database, identify changes against an existing database, or to apply changes to an existing database. You can also compare one database against another, or synchronise two databases. One of the nicer options is to use the choices you made in a previous session – you can save multiple session details to save you time. For new sessions, you then connect to a database server and database by choosing from the drop-down lists provided. It’s all very simple, the prompts are written in clear and understandable English, and it all works.
You’re shown the database objects you’ve chosen to work with
Perhaps the most useful element of DB Ghost is that when a database developer chooses to pass SQL code such as a stored procedure to a database via DB Ghost, the first thing that happens is that the code is verified against the existing database structure and tested for syntactical correctness. Only if the code checks out without errors is it passed any further. DB Ghost will also check for dependency errors. For example, if a database developer asks to change a table definition in such a way that a dependent view, stored procedure or user defined function no longer works, the changes will be blocked and a report generated showing the problem so the developer can go away and modify their changes.
The important fact about this method of working is that you use your actual schema creation scripts in the source code management system, and this is what the developers work on. Before any changed scripts are applied to the database, they are checked for syntax and dependency errors, and if you use the repository within Visual SourceSafe, you know exactly where changes have been made and who made them.
In addition to keeping track of scripts, you can use DB Ghost to manage your static data. You’re provided with two methods for this – the DB Ghost data and schema scripter, or a stored procedure that is available for download from the Innovartis site. The scripter is easy to use, but the files that are created are in Unicode format. This has the advantage that they can accommodate any type of data, but Visual SourceSafe sees these as binary files, so you can’t check for differences between versions within SourceSafe. For this reason, the script (which creates a stored procedure within the master database) is the preferred method to use.
Given the friendliness of the wizard, you might wonder whether the command line interface is there only for the perverse. It will undoubtedly appeal to the non-GUI enthusiasts, but the real purpose behind it is to give you a way to automate a daily build process using Windows scripting. The only thing that the command line interface needs is a valid selections file created within the standard DB Ghost interface. You can also run multiple instances of DB Ghost at the same time so you can process as many databases as necessary without waiting for one to finish before moving on to the next.
You can choose to create reports showing all the changes that have been made, and any errors that occurred. You can also choose to use the command line interface to verify every SQL script on a regular basis and have problems reported back to you automatically.
DB Ghost is comprehensive about what it does do, and Innovartis is even honest enough to warn you about what it doesn’t do – which is refreshing and saves a lot of time. If you need to rename a column, you’ll need to create a SQL script to do it and run the script from within DB Ghost’s wizard. Similarly, if you need to add data to a new column, you’ll need to create a script that will run after you’ve made the changes to the target database using the advanced scripts facility within DB Ghost. You can get free support via email, and all upgrades both minor and major are free for a year from the day you buy DB Ghost.
If time and resource is an issue, Innovartis will send one of their engineers in to install DB Ghost for you – and if you’re not convinced that it’ll transform the way in which you manage changes to your databases, they’ll uninstall the software and won’t charge you a bean.
DB Ghost is a nice product. It works well, is easy to use, and goes further than any rival software. However, you don’t have to take my word for it – you can download a time-limited version for free from the Innovartis website and try it for yourself.
Comments