SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Rant: Updating Application Databases the Right Way

Recently, a fellow DBA showed me a set of documentation on a commercially available product. This is a product people pay money for to license and use. It's not an in-house developed application or a community released free sample. The good news is the application is being updated. This update can occur as frequently as each quarter. And as you might expect, some updates require a database update. Fair enough. I can live with all of that. That's all normal, "cost of doing business" type of stuff. Where I stopped in shock was how the database updates were to be done.

  1. You are to obtain a new backup file from the vendor.
  2. You are to use a commercially available schema comparison tool to generate a change script (they've licensed said product so you can do so).
  3. You are supposed to take that script and apply it to your database.

I wish I was making this up, but I'm not. This approach is fraught with numerous concerns.

  • What happens if a dependency is missed?
  • What happens if a data change loses or destroys data?
  • What if the wrong options are selected when running the comparison and objects are missed?

You get the idea. I was stunned that a vendor is suggesting this as a method for keeping the database up to date for their commercial product! I've seen products that send out scripts on a CD/DVD and ask the DBA to run. This was the low-end approach, or so I thought. More typical is to have an installer package which performs all the updates properly and in a synchronized fashion.But that's not the case with this particular application. I wish I knew what to say to this vendor, but I don't think they'd accept it. Obviously, they think that their customers are fine doing the work for them. After all, the customer is generating the change script and executing it manually. Unreal.

Look, if you're a vendor, plan a professional upgrade path for your product, and this includes the database layer. Don't expect the customer to generate the change script for you. At the very least, do it yourself, test it, and then distribute it. Better yet, build a proper installer package that deploys the application updates and database updates at the same time. And if you're smart, you'll even prompt the user to backup the active database first!



K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.


Posted by Mark Horninger on 16 July 2009

Well said Brian!!!!

Posted by Anonymous on 16 July 2009

Pingback from  Professional Courtesy Is Not Full of Suck - SQLAgentMan

Posted by David McKinney on 17 July 2009

Sounds to me like they must have a reason for doing this.  Otherwise why don't the run the compare themselves, and send out the change script?

I'd guess that they're half-expecting your schema to be non-standard, so that a regular script wouldn't work.

A lot of 'off the shelf' packages are in fact heavily customised for each client, and I think their distribution strategy (is that too kind a word) has got something to do with that.


Posted by David.Poole on 17 July 2009

If the vendor can find customers who are prepared to do this for them then good luck to them!

More fool the purchaser who didn't check the upgrade method or didn't have the wit to involve the poor sods who had to support the product.

I used to support commercial software that was customised for each client.   We had each copy of the client's software configuration under source control so we could always be sure of what they [em]should[/em] have.

Posted by ALZDBA on 17 July 2009

Good rant Brian ;)

Another issue some vendors have is that they add foreign key stuff using "with nocheck" ....

Years afterwared someone (hm guess who) altered that in a new script to "with check" and all of a sudden many "legacy" errors show up.

Way to late to figure out what went wrong and why nobody noticed it (user side).

Posted by jparker2 on 17 July 2009

The last time our sys-admins ran an application set-up.exe pointed at our production SQL server it stopped and started the server in single user mode to create the initial application database. This was not a happy event since it occured on a Wendsday morning after thier on-site tech and support team told our CIO that the install would not interfere with our other applications.

Something called compliance testing (you might of heard of it) actually prefers this schema compare method as opposed to a regular install or setup.exe. We have to run all changes to any production application against a non production environment and have the users test. If it fails testing you are stuck with contacting the Vendor support channel.  I actually prefer the schema compare method, it alows me as a DBA with 10+ years experiance to look at the script and run it against a restore of production. I can then know exactly what to expect.

Posted by K. Brian Kelley on 17 July 2009

jparker2, I hear what you're saying, but I will still choose to disagree. You've cited an example of a poor installation package as a reason to say no to installation packages. That doesn't fit anyone's definition of a proper installation package.

With respect to the app in question, the database really shouldn't be touched as it's tied to the application. The vendor indicated that the reason for the compare was in case the customer built custom tables in the database... they wouldn't want to accidentally drop them. Which begs the question, "Why would you touch any objects other than the ones you know are changing?"

And I'm perfectly fine with a provided script solution. I'm not fine with a "script it yourself" solution. That offloads the work and the validation. And to your argument about having to contact the vendor support channel... if you find something's not right, wouldn't you be doing that anyway?

Posted by Todd McDermid on 17 July 2009

I completely agree with Brian.

It's the vendor's responsibility (and in its best interest) to provide a smooth, automated upgrade of the database to their customers.  Sure, you may want to provide an option to "script out what the upgrade is, but don't do it" - but that should be an additional feature to the installer.

If any vendor does what this one did - they're just asking for problems!  What happens when the customer screws up the upgrade?  The vendor can't very well say "sorry - you pooched it, you fix it".  It's their official upgrade method!  Now they have to support tens, hundreds, or thousands of customers (probably littered with low-skill part-time DBAs) to try and diagnose and repair customer data remotely.

I was forced to do exactly that before I smacked my boss around a little... it was insanity!

Posted by Scott Jacobson on 17 July 2009

So what you have a problem with is the vendor leaves this in the clients' hands?

I totally agree that's bogus.  We have pretty much the exact same update procedure but we take responsibility for ALL the work.  If there's a problem, we fix it.  Sure we coordinate with all interested parties on the client side but part of the reason people use our software is the service.  As far as I'm concerned this is a basic service deliverable.  The vendor is responsibly for handling this. not the client.

Posted by rudy komacsar on 17 July 2009

what a crock !!! sounds almost like a software 'Ponzi' scheme ...

maybe it's time to head for the hills ...

Posted by lisa.hale on 17 July 2009

I can see both sides of the argument. I will say that a packaged application that is not customized should have a standard upgrade executable that is TESTED in a NON production environment prior to implementing in production. With the advent of physical to virtual VDE and ESX environments there really should be no excuse for not testing unless the company doesn't have the funds for these environments.

With that being said, upgrades such as this compare should be performed by the vendor that has a signed SOW putting their butt on the line and they should have a copy of the binaries as well as of the data to test in their environment.

I try to remember that not all developers are equal, but the dba should be involved in the changes and understand them prior to any production changes.

Posted by NothingSurprisesMeAnymore on 20 July 2009

I work for a software company that releases database updates as incremental scripts that are downloaded from our web servers and applied only by our application. We do encourage our customers to perform the upgrades without our intervention, but we have documented the process thoroughly and our support department is immediately available for upgrading customers should they hit any problems. We also encourage our customers to use a test environment and we get nervous when we hear "we don't have a DBA/server admin to manage this application".

We've acquired other vendors in our market space who were using a hybrid compare methodology. They would use the compare tool to identify custom structures and manually create custom data placement script that plugged into their canned upgrade program. They even took it a few steps further by doing all the work directly on the production environment (no test environment) and often without any of the customers' staff directly involved. To top it off, the vendor techs doing the work barely (if at all) kept notes and change scripts of what they had done. When they did keep such records, they were scattered across the network and multiple laptops.

I'm not a fan of the "compare" methodology, but I can see its benefits, if done properly. NEVER should that method of upgrading be placed in the customers' hands. That's plain laziness.

Sure, it's possible to provide complete hand-holding to ease a customer's tech staff requirements, but you had better be prepared to invest the time, effort, and money in staffing, process documentation, process improvement, and any software you need to support that work. I would even suggest that the "service group" be another business unit within the vendor company that manages installs of the product in the same manner as the customers who do have IT personnel to handle that work

Posted by BrianK on 23 July 2009

As a development manager of a software company I may have a skewed view.  Since 1991 we have been selling commercial software (corporate enterprise stuff) starting with Clipper and DBF files back then, to Access MDB's, SQL 6.0... and all the way to SQL 2008.  Our systems are generally configurable via metadata and do not get a ton of client modification at the schema level, but sometimes they get additional procs, views or tables.

Since the beginning, we have had a policy that we will not make a breaking change to the systems that will force clients to lose data.  Consequently, we started using a compare update scheme way back then.  In order to make it work, we maintain in source control the build scripts for our schema and system data, and we build them into .NET resource DLL's which can be read by our DB updater program.  All of the database schema changes are versioned, and we use extended properties to document them in the databases.  Updates automatically back up the target DB, build a reference DB on the server, do comparisons (options, objects and mappings set by us), run necessary statements based on prior versioning, build a script (and save it in a predefined place), run the script (and log the output), run any additional scripts, clean up the reference DB and exit with a message to the client.  In the event of a trapped error, the whole process is rolled back to a backup copy.  

We have command line options to affect the process if we want to skip the backup, create a new DB instead of update existing, create scripts without running them, etc.

That said, it takes a ton of commitment to working within certain constraints to avoid changes that cannot be universally applied, and we are constantly learning about what strange things a client DBA can do to our schema to break our model and programming around them.  That is the big trade-off we make by allowing certain types of additions to our databases.  In our market space clients who buy onsite systems do it because they want the control of creating local integrations and reporting, and if we moved away from a compare method we couldn't provide that level of service.  

In the end, it all boils down to how helpful our support staff is when there is that inevitable unexpected case and a client needs assistance.  If we provide excellent service, I suspect the clients wouldn't care how we handled updates.

Leave a Comment

Please register or log in to leave a comment.