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.
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.