Frequently Asked Questions

No, the options that deal with Visual SourceSafe are there as an extra help if you happen to use SourceSafe. If you use a different Source Management System then you can still enjoy all of the benefits of the DB Ghost Process - you just need to add the scripted files into your source control system manually.

DB Ghost works with any Source Management System as it deals with the one thing they all have in common - 'working folders'.

Whatever Source Management System you use (Perforce, Visual SourceSafe, PVCS, CVS, Synergy etc.) they all require you to have a working copy of the source files on disk. It is this set of files that DB Ghost uses as the input to it's build stage so it doesn't need to know explicitly where they came from, just that they are a true representation of the required database structure and data.

There is a good mathematical reason why DB Ghost actually performs the upgrade (and works every time) and why our competitor's tools leave you with a script that must be manually tweaked for dependency issues.

Producing a script without actually updating a target database is an exponential problem:

• You identify, say, 20 differences between the two databases - easy.
• Now you have to choose what order the updates will run in the delta script - very, very difficult.

This is where an exponential problem comes in.

The code must be clever enough to work out the interdependencies between the 20 changes to get the order right. After the first statement has run the database may be in a significantly different state so the second change might not work and so on and so on...

Consider this example:

• Table T has some new columns
• View V has been changed to reference the new columns in table T and the new parameters on F1
• Function F1 has some new parameters
• Function F2 (called by F1) also has some new parameters and references T
• There are some new columns in the Static Data Table ST that F2 references
• There are also some new rows in ST.

You can see from this very small set of changes that the code needs to have some quite complex, in depth knowledge of the possible interdependencies between SQL objects. Just try doing a matrix that shows all possible dependencies between all SQL objects to the maximum possible reference depth - it's an exponentially increasing task and one that, although conceptually possible, is practically impossible to code from a cost/benefit point of view. You would need years of coding and testing to get anywhere near it. No software company could survive if they took that approach because it is economically unfeasible to acheive perfection and still produce a product that will return the investment i.e. one that is affordable for the customer and makes money for the vendor.

So what are the viable options left?

What our competitors do is to say, 'sorry, we cannot guarantee that the delta script will work - you may need to do some manual tweaking'. Basically they have given up and pushed the problem on to their users instead robbing valuable time every time an upgrade is performed debugging the delta script. EVERY other tool on the market today has this problem because one person came up with the idea and everyone else followed. Also, their approach is also fragile as, if new dependencies are introduced by Microsoft into SQL Server (for example, do you remember when UDFs couldn't be referenced in a query?), then they will have to try and incorporate those changes in a patch or new release.

As far as that approach is concerned, we beg to differ.

We started out with the question 'How do we guarantee that the delta script will work every time for our customers?'. The only answer to this is to actually perform the upgrade steps on the target database whilst creating the delta script. This means that our users will NEVER have to manually tweak the script that DB Ghost produces and is why we can give a 100% guarantee that the script will work. It also means that DB Ghost will always be able to handle any new object dependencies that are introduced by Microsoft.

And the best part of all this?

The fact that DB Ghost updates the target database is totally irrelevant.

Why?

Even if you had produced a script manually, or used a diff tool, you would ALWAYS have to test the script on a replica of the target database, at least once, to make sure that it works.

What's worse is that, unless you are very lucky or the changes are very simple, the script will fail, leaving the target database in an unknown state. You would have to sort out the problem in the script and then restore and try again. Maybe you would have to go around this loop several times before the script was right and, all the while, you're wasting your most valuable commodity - time.

So, if we assume that you will always need a replica of the target database (whatever approach you use) then the way DB Ghost works is actually faster and more productive. Even better, as you can run DB Ghost from the command line, you can set up a scheduled task to automate the process every night on your build server.

• Restore a copy of your target database
• Run DB Ghost from the command line
• Check back in a fully functional, guaranteed to work, delta script.
• Arrive in the morning to an email telling you everything is Ok.
• Use the extra time you have gained to look at some *real* databases issues like performance tuning, data integrity and schema design.

Generally speaking, ‘Diff’ tools are only about comparing two databases and writing a script of the differences. They don’t give any real kind of audit trail apart from saving the scripts and using a proprietary ‘archive'. Neither do they provide you with a true database change management solution...DB Ghost is the only tool on the market that can guarantee to propagate your changes seamlessly with 100% accuracy and dependability. For an example of how good DB Ghost is, try comparing and upgrading from Northwind to Pubs with any other tool and then try DB Ghost - it’s an eye opening experience.

DB Ghost lets you use your actual schema creation scripts in your Source Management System as the baseline and this is what the developers edit. You simply point DB Ghost at a set of creation scripts and a target database and it does all the work required to make them match. i.e. it is easy and straightforward to use. The ‘Diff’ tools require a number of steps to achieve a compare and baseline, and it still doesn't tell you who changed what and when. These tools don’t provide any audit trail of changes…and developers still overwrite each others code…and upgrade/downgrade scripts still have to be manually produced and so on…

To answer this, you firstly have to review the requirements of a database change management solution. That solution must, at a minimum:

• Tell you exactly who made the changes
• Tell you exactly when those changes were made
• Be able to repeat the changes reliably

Therefore, having the ability to just make a change is not a ‘database change management solution’…it's much more than that.

DB Ghost can tell you who made the changes and when they were made…it does this by relating the actual source code within the Source Management System either explicitly or implicitly as part of the build process and as the Source Management System contains the truth of the database, can be interrogated to tell you who made the changes and when those changes were made with a complete history of the file/object in question.

Any changes are repeatable as the same process is followed to propagate any changes made: the source code is retrieved, a database is built using the source code and is then compared to a copy of the production system which is updated with the applicable SQL. The resultant SQL file is the accumulation of all the changes made and is flawless. No debugging of the script is required as the process has taken care of all dependency paths through a repeatable and reliable solution. The file can then be applied to the production systems resulting in a simple and powerful way to incorporate a true change management solution for SQL Server databases.

Databases can never tell you when or by whom changes were made 100% of the time - they are not the truth of the system, only your Source Management System has this ability and is actually built for this purpose. No product on the market utilises the Source Management System in this way, except for DB Ghost.

DB Ghost is the fastest tool on the market when you consider the full lifecycle from start to finish i.e. from your developers making schema changes in the Source Management System to them being incorporated into your existing databases. The savings made in time alone means that DB Ghost pays for itself in a very short period of time...and we’re talking weeks, not months or years.

1. At the start of a "new piece of development" or bug fix you restore the latest baseline DB from the build (hereafter referred to as 'localDB').

2. You want to change a sproc - easy. Check it out, make the required changes and recompile it against localDB.

3. You need to increase the size of a char field that has a foreign key on it. Check out the table creation script that contains the FK definition first to ensure no one else can modify it. Use whatever means you are most comfortable with to make the modification on localDB. Enterprise Manager generally makes changes like this easy.

4. You want to create a new table. Creates the table on localDB using, say, EM again. As soon as possible generate a script and add this to source control and immediately check it out again. This prevents other developers from creating a table with the same name, which would be unlikely but it's better to be safe. After that you can carry on modifying the table structure until you are happy with it.

5. The table is going to hold static data. Again, the same approach is used as for the new table except that, when it comes to generating a script, you would use the Data and Schema Scripter tool and just select the new table for scripting the data out into a SQL insert script.

6. You want to add a NOT NULL column to an existing table. You perform the same steps as in step 3 but also check out the 'before.sql' custom script as well (there is an opion in the DB Ghost UI to include custom scripts as part of the upgrade process). In this you hand code an ALTER to add the column as NULLable and populate it with the required data. Note: you do not need to alter the column to be NOT NULL as, assuming that you have made the change in the table creation script, DB Ghost will do that as part of it's normal processing.

7. You want to rename a column. This is broadly the same type of change as in the previous step except in the 'before.sql' script you hand code an 'sp_rename' to rename the column before the automatic upgrade. Basically the custom scripts are how you include the "human element" upgrade steps i.e. where an automated tool cannot possibly know how to make the change correctly.

8. Once all the changes have been made and unit tested/reviewed you look in source control at all of the scripts you have checked out. You then go to enterprise manager and perform a 'generate script' for all of the changed objects that overwrites the previosuly checked out DROP/CREATE scripts.

9. You check all the scripts in at once with an appropriate comment.

10. To double check you can make a backup of localDB and then use DB Ghost to perform a build and upgrade of localDB from the latest set of scripts in source control. You then rerun the unit tests to ensure that all is well. If not you can look at the DB Ghost upgrade log and/or the history in source control to determine what has changed and what remedial action needs to be taken.

11. You can now either elect to use localDB for further development or wait until the next baseline database is produced from the build.

Most of our large clients work in this way and have found it pays off many times over.

You can also use DB Ghost to do a comparison with a colleague's localDB if you are working closely on related pieces of functionality adding further value to the development area.

Even the test team can use DB Ghost to monitor the changes that inevitably occur on the test database versus the baseline in source control to give them an idea what is changing over time and what areas need more test attention.

The list goes on and on....

If you have ever written code such as VB that has been managed by a Source Management System then you have probably already experienced the mechanism by which DB Ghost ensures that changes are not overwritten. It is as simple as checking out the creation script, making your amendments and then checking back in. This ensures that only one person can make changes to a database object at any one time.

Without DB Ghost developers have to hand craft 'alter' scripts which they typically keep on their own machines until their functionality is finished and ready to go. What this means is that several developers can be making changes to exactly the same part of the schema without ever getting sight of the other developer's changes. This leads to a 'last one wins' situation whereby the last developers script to be run (that will not have the changes from the other developers present) will, effectively, back out the other developer's changes.

When DB Ghost works with a baseline of all your scripts from the Source Management System it will automatically ensure that a target database exactly matches the schema described by the scripts. As this process is totally automated, human error is eliminated, thereby completely solving the problems of manual script execution. At the end of the deployment you can be totally assured that the target database matches the source code in your Source Management System.

DB Ghost is also the only tool on the market that can guarantee to propagate your changes seamlessly with 100% accuracy and dependability. For an example of how good DB Ghost is, try comparing and upgrading from Northwind to Pubs with any other tool and then try DB Ghost - it's an eye opening experience.

With the DB Ghost Process your Source Management System provides you with a full audit trail as this is what the developers use to make their schema changes. For example, if a developer wants to add a column to a table, they check out the table create script, put the new column in and then check the create script back into the Source Management System. This means that a full audit trail is maintained right there in the Source Management System – this means that you can use the Source Management System for SQL in just the same way as you do for other code.

Once all the changes have been made you can apply a label or baseline across all of the scripts and then let DB Ghost handle the propagation of the changes to the target database. As the target database now matches a baseline in the Source Management System you can easily communicate what schema level your databases are at by merely quoting the baseline name.

We cannot stress enough how much DB Ghost helps you in the development/test cycle, guaranteeing, on a daily basis, that your schema hasn't been broken and that it can be deployed against a range of example databases. For example: without DB Ghost if a dev wants to drop a column on a table he codes up the 'alter' script for this and it could then be run successfully against a test environment database. Ticks in boxes all the way along but, when testing starts in earnest, everything is broken as several stored procedures that relied on that column being there are now falling over. If you had run DB Ghost daily (or more frequently) as an automated process you would have picked up the break just a short while after the dev checked the table change in, not several days down the line when the test team get to it. We all know that the earlier you can spot problems in the systems development lifecycle the cheaper it is to fix them.

By using DB Ghost throughout your development and testing phases you can ensure that the 'go live' process is effectively rehearsed every time you re-baseline your scripts and propagate those changes up to the test systems. When you are ready for the live upgrade there will be no nasty surprises as DB Ghost can produce a script of its upgrade actions giving you a robust, fully tested SQL file that can be executed in Query Analyser. Alternatively you can use DB Ghost in full auto mode and let it dynamically handle any subtle differences on the fly during the upgrade process.

Yes, once you have labelled baselines in your Source Management System you can point DB Ghost at any one of them and let it automatically revert the schema back to that baseline.

Yes, DB Ghost handles data comparison and upgrade as a core part of its functionality.

The update of your production databases is easy. You extract all the labelled scripts from your Source Management System (this label having been applied at the top level) and then run DB Ghost with your production database as a target and the scripts as the source. DB Ghost leaves your target databases' data intact; it will never throw away data unless you specifically ask it to. During the development-testing-fixing-testing cycle you may label your source and then run DB Ghost every day (or more often) against your test databases. So your labels would be, say, an incrementing number ('Build XXXX'). Between each label the developers may have only made a few updates or thousands, it doesn’t matter to DB Ghost, it will apply them all. Clearly, over time, this builds up to a large number of changes as bugs are fixed and re-tested.

This is one of the beautiful things about the DB Ghost Process, at the end of the cycle, once everything is tested and ready to go, you just extract the latest labelled source - it doesn't matter how many times you ran DB Ghost against your test databases, all you need is the result of all those changes which are the scripts in your Source Management System.

Absolutely. The process can be fully automated as it's possible to execute DB Ghost in command line mode passing in a file saved from a previous GUI session. In fact this automatic execution is more help during the dev/testing phase as you could set up a machine to automatically label VSS, extract the source and then run DB Ghost against your testing databases overnight.

The answer to this one is a little complicated.

DB Ghost reports on all errors into a report file that you specify on the last page of the wizard, these errors are enclosed in <ERROR> tags to make them easy to spot. You can run DB Ghost as a two stage process where stage 1 would be the automatic creation of a brand new comparison database that would verify all scripts work and stage 2 would be the upgrade to a target database.

As you are probably aware, given the myriad of possible changes that can be made to a databases schema, it is impossible to guarantee a rollback within a change propagation tool. Especially as DB Ghost lets you include custom, hand crafted scripts as part of the upgrade process which it would have no real knowledge of and therefore would be unable to perform a rollback for.

However, there are two other ways to achieve what you are looking for:

1. Backup your database first and then restore it if everything does not go as planned.
2. Take a copy of the target database and try the upgrade on this first. If all goes well do the real upgrade.

We recommend dedicating a server to performing a test build and upgrade on a daily basis, or even more often, and automating everything using scheduled tasks. In this way you can very quickly discover any database breakages that are checked into your Source Management System. We know one of our customer's build and upgrade process takes only 20 minutes as they have a server that extracts the latest files from their Source Management System and runs DB Ghost every 20 minutes and emails the DBA's if anything is wrong. Their DBA's now concentrate on *real* database issues and can review what the developers have checked in at a time that suits them. Previously the DBA’s were on the critical path and got interrupted by developers every five minutes with requests to review scripts and place them in the Source Management System.

Yes. DB Ghost will automatically generate scripts and make amendments to a target database to make it ‘the same’ as a source database.

Certainly. DB Ghost produces a script of the actions it takes to upgrade a database.

Yes, DB Ghost reports on all differences, including objects that are only in the target database and not in the Source Management System (DB Ghost works with any Source Management System).

Yes. In fact DB Ghost cannot check in code to the Source Management System itself, it just uses it as a read only source for the scripts. We recommend you use all the utilities that you are currently comfortable with.

The DB Ghost Packager Plus product was design to specifically address this issue as it can dynamically handle the required updates to a target database seamlessly.

DB Ghost will make any modifications to ensure that the schema and data of two databases match exactly. It can also produce a script (again, schema AND data) of the upgrade process that can simply be executed in Query Analyzer.

You do not need to keep track of what version your users are running. DB Ghost, unlike other tools, operates on target databases dynamically which means that it can make decisions on how to propagate the schema changes based upon the structure of the individual target database.

For example:

A table script has been modified in VSS to include two new columns.

Target database 'A' already has one of the columns (from a previous upgrade) whereas database 'B' has neither.

If you run DB Ghost against database ‘A’ and then against database ‘B’ no errors will occur and DB Ghost will seamlessly make ONLY the required changes thus bringing both databases into line with the source script.

Yes. Although this is not achieved via a UI option. Some of our customers have this requirement and they have saved the 'session file' (i.e. all the options you select via the UI) and then automated the update of their databases by calling DB Ghost multiple times, in command line mode, via a scheduled task. Please note that a license for the Automation Toolkit is required for the command line and API functionality.

You can trust that DB Ghost won't let you down, and you'll find it reassuring to know that some of the largest companies in the world rely on DB Ghost as a core part of their database change management solution.

Propagating changes from scripts to a database is a highly complex task and one that DB Ghost handles effortlessly. In terms of the diff and upgrade it is the only product on the market that really delivers a true upgrade. However, any automated database change management solution must also allow for the few situations that cannot possibly be handled by a tool alone. A good example of this would be if you needed to migrate data between tables before the schema was upgraded. For these situations DB Ghost provides a way to easily incorporate custom scripts to perform actions before and after the main schema upgrade. This gives you 100% flexibility and a guarantee that DB Ghost will always be able to propagate your schema and data upgrades.

Every type of database object is supported.

Your choice of Source Management System is entirely up to you. All DB Ghost requires is the complete set of scripts that describe your database available on disk.

No dongles or irritating licensing schemes that tie you to anything are involved with the DB Ghost key.

We would recommend a license for each of the following:

• Every developer that modifies database objects. This enables each developer to do a build of the database before check in that will ensure that nothing is broken.
• Every DBA.
• The "Daily Build" server.