1

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

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
UB.
  • 367
  • 5
  • 14

4 Answers4

3

We've been using DBGhost for a year or so now to keep our database under source control along with our codebase, and it makes this kind of thing dead easy. It's not just well thought through, but they've been using it to roll out their own code for years, so it's dead solid.

user130202
  • 51
  • 2
1

Do you insist on doing it yourself, or could you see yourself committing and investing in a tool?

I really like the idea of Red-Gate's SQL Packager, which will "diff" your two database versions, and then create a SQL script, a C# project, or a stand-alone executable to upgrade from version 1 to version 2.

Not 100% how you'd be able to upgrade from 1.0, 1.1, 1.2, 1.3 all to 2.0 - check out their website and see if they offer something for that scenario!

Otherwise, I guess it'll get quite thorny and messy......

Marc

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Thanks for the tip. We do have Sql Packager, but I am not sure if that will solve the problem. We need to send out upgrade (patches) just like Service Pack's from Microsoft. So the service pack must upgrade from any version to the latest version. Let me know if you need more info, _Ub – UB. Jul 22 '09 at 17:15
  • I understand your requirements. What you could do is this: have an upgrade for 1.0 -> 1.1, one for 1.1 -> 1.2, and so on. In your install script, find out which set of upgrades you need to run, and then run those in sequence. – marc_s Jul 22 '09 at 18:20
  • Or the other option would be to have an upgrade for 1.0 -> 2.0, another one for 1.1 -> 2.0, 1.2 -> 2.0 and so on, and then just pick the appropriate one and execute that. – marc_s Jul 22 '09 at 18:21
  • thanks for the suggestions. Looks like that's the right way to approach this problem. – UB. Aug 12 '09 at 22:31
1

Your problem is a pretty common one, and I've had to deal with this kind of problem at my last job. There is another tool aside from the RedGate tool that may help you do what you need to do. It's a tool called DB Ghost. They explicitly address the versioning problem, and have a packager as well. I would suggest doing a trial of the DB Ghost product because they have some interesting claims concerning multiple version upgrades. This was taken from their FAQ (http://www.innovartis.co.uk/faqs/faqs.aspx):

Q: Our problem is going to be managing data structure changes during upgrades. Our product line is Shrink-Wrapped, or downloadable from the website. So when a user downloads an upgrade, they can be upgrading from a very recent version, with few database structure changes, or the upgrade may be from a very old version with a multitude of structural changes. One upgrade needs to manage it all. The user would be offsite, so we can't hold their hand. We have users in Greece, Australia, Malaysia, Norway, etc. How would DB Ghost, if at all, handle updates in remote locations?

A: The DB Ghost Packager Plus product was design to specifically address this issue as it can dynamically handle the required updates to a target database seamlessly.

I'm just mentioning this because our company is trying to do something similar and I was doing research on this tool.

Thanks, Eric

Anon246
  • 1,821
  • 13
  • 16
  • Thanks for the feedback. Though our project has no funds to buy any new software, I'll check it out. See if it helps.. – UB. Aug 12 '09 at 22:46
  • Although a google search for 'DB Ghost' will show a sponsored link paid for by Red Gate, DB Ghost is actually a product of Innovartis [http://www.innovartis.co.uk/]. – Kenny Evitt Dec 18 '09 at 21:17
1

In the Rails world they are using a tool/method called Migrations.

Basically is boils down to creating a small sql script to upgrade and downgrade each little change to the database.

When you are testing the application you migrate your database to the version you want and on deployment the application can check what version it needs and migrate to that version.

There are free migration toolkits for most popular languages, they might be part of some MVC framework though.

A nice side effect of migrations is that you have database source code that is easily stored in you source control repository.

idstam
  • 2,848
  • 1
  • 21
  • 30
  • Thanks for the feedback. With all the info I am gathering from different sources, I am starting to see the solution. I just have to roll my sleeves up and get it to work. Thanks again. – UB. Aug 13 '09 at 14:48