Apply upgrades (application related) to database.

[topub]

Guys,

Since I've not done this before I am not sure if the way I am planning to do this is okay or is there a better way. Like using Windows Installer or Install Shield or Windows Installer XML (WiX) toolset. Any help would be great, as I have no clue.

We have a product and we ship new version every few months. So far we've only been rolling out complete versions i.e. Either Version 1.0, or Version 1.5, but no upgrade from 1.0 to 1.2 to 1.3 to .... you get the picture, right! So any customer that get version 1.0 cannot upgrade to version 1.2 or 1.3 or even the latest. They'll have to uninstall old version and install the latest version. This is not right, but thats what we could do until now. But we'd like to change it.

My plan is to have a install file with (Sql Scripts) for each upgrade path. Check the table in database that stores the version info and depending on it run different script to upgrade database.

My concern is that this method may not be scalable, once we have more than 5 or 6 different versions.

If you could point to any articles or books on this topic, that would help a lot too.

Also, could we use Windows Installer or Install Shield for this?

thanks,
_UB

 

[mrdenny]

Pretty much the only way to do this is to ship with the installer a ton of database scripts.

We have the exact same problem, and here's our solution.

When the customer gets the server installer within the zip file is a folder with all the database script changes that have happened since our baseline database was setup (this is the version that we restore to setup a new install). The database script changes are 1 per change and names "05.01.0000.00-Description.sql" When the installer runs it looks for the database to see if it exists, if it does it then launches an app that we wrote for the installer which crawls through the folder of T/SQL scripts. As it gets to each script it checks the database to see if that file has been applied. If not it applies it (in order) one at a time until it gets through all the files.

When we get to a major version (v6 is next) the installer for v6 will include all the v5 files and all the v6 files. Eventually we'll get to the point of not including the v5 files for size reasons, but I've got no idea when that'll happen.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog