I have an opinion thats been forming over the last decade:
Database dependency injections in setup are evil.
Unfortunatly, I also don't know if they are avoidable since many people expect an application to just work after being installed.
Eitherway, what is the best of class solution? I've not seen one yet.
Back in the 90's I was responsible for putting together 500MB+ tarball packages for a massive Unix based server system. One of the modules in the package deployed a series of Informix databases either from scratch or upgrade from a previous release. Other parts of the system configured the operating system, nightly/weekly/monthly CRON jobs an many other tasks.
Does anyone want to guess which part of the install failed the most often?
Yup, you guessed it... the database updates. Think about it, with over 1300 stored procedures, each one of those sql files was basically an untrusted out of process script which could introduce fragility into the package. On top of that, the sql scripts to deploy and upgrade the databases had even more complexity with all sorts of dependencies that would make the install even more unstable.
The truth is we frequently extracted the package, manually turned off the database updates and then ran the install. After we'd call a DBA on duty and ask them to manually update the database.
Yes, that's a horrible release strategy. But that's what the release manager ordered to keep the schedule.
I recently read a post on in the InstallShield community forum where a user was asking why an install could connect to sql server using localhost but not (local). My thought was to test if other sql clients could connect as (local) and then check the sql configuration. The posters response was:
I don't want our customers to have use any special SQL Server configurations. This software should work out of the box.
Unfortunatly, my opinion is becoming that these statements are mutually exclusive when dealing with datalayer dependency injections. Unfortunatly most developers I come across seem to have this overly simplistic understanding of the challenges of database deployment and maintenance. They want to throw extremly how level requirements along the line of 'The install should just work.' and then throw extremly technical challenges with many possible configuration variations.
I'm sorry, but I just haven't seen it work that way yet.
So I'm very, very interested to know what my readers think on this subject. Have you had the same pain or have you found a better way to deal with the challenges? Also do you have the same problem that I have: People don't seem to understand that when an install breaks because of a sql dependency injection that it wasn't the installs fault. After all, it wasn't the setup engineer who made the decision to inject the custom action and it wasn't the setup engineer who was responsible for developing or testing it.
Along with a colleague I have done quite a lot with SQL database scripting and we think we have a good approach to deploying SQL scripts and databases that works really well.
ReplyDeleteFirst I would have to say that I don't believe you can properly deploy SQL with Windows Installer, my initial comment is always what does rollback mean? Do you really want to remove all traces of a database on uninstall, I don't think you do. If you followed the proper Windows Installer model you could protentially drop all you tables and data on a repair or upgrade! This sounds bad to me.
The system we have created is based on a scripting scheme that means when you run the script it will update any version of the database to the latest version no matter what version you start with and will not destroy any data. We script each element e.g. table, stored proc, view, user etc in to it's own file, this makes source control easy. Each script element must run and update its self without destroying data e.g. a table is created if it doesn't exist or updated if it does. We have programs for creating these scripts from an existing database and joining the scripts together to form a release script. The release script is one big script that contains all the script elements run in the correct order. We also support replaceable parameters so that you can easily deploy to different environments.
Over serveral years and many database deployments we have found this system very reliable. We can run the script on any database schema version and update it to the latest. It doesn't work for going back to old versions but this has never really been a concern for us.
I have just started integrating this scheme in to a Windows Installer and I simply shell out to sqlcmd to perform the update (see: http://neilsleightholm.blogspot.com/2008/08/executing-sqlcmd-from-wix.html). On uninstall or repair I do nothing, it feels safer to me to leave the database and data behind.
I believe in the sql robustness from an install, uninstall, reinstall and install, upgrade, upgrade, upgrade perspective.
ReplyDeleteUnfortunatly the DBA types I deal with don't see to want to support that level of engineering.
They also expect requirements like setting up replication and tearing down replication in installs and uninstalls.
They also expect the installer to manage database permissions by passing service account information into stored procs and again on uninstall.
All of these requirements are expected to abort the install/uninstall if they fail and they also expect the uninstall to never be blocked as a result.
Christopher,
ReplyDeleteI see similar issues at my present employer. People write SQL scripts, fail to test them all and then push them to production, where surprisingly (and if I don't test out every step), they fail.
I'm pretty sure that we're dealing with an organizational problem above all else.
Christopher, I don't believe the install scripts are DBA responsibility. In my experience they are build team or developer. Our scripting handles everything include security, I have never had to do replication but I would include that as well. In practice we setup the script so that the only prerequesite is a valid SQL instance to install on to, everything else is in the scripts.
ReplyDeleteChristopher,
ReplyDeleteI agree, trying to run SQL scripts from an installer opens up a potential point of failure that you would have little control over. I added my own take over here.