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.
- You are to obtain a new backup file from the vendor.
- 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).
- 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!