Just out of Curiosity i wanted to know if there is a best way of upgrading clients version.

We are a small software developing company and Our software is a WinForm front end using LLBL "then Great " on SQL 2005 and goes through lots of changes and improvements almost every day with more than 4-6 people making changes and many times without one person knowing other person's changes...because of people working in different areas of the software.

We use Visual Source Safe so we have One "Master" solution. We dont have SQL on VSS.

Whats the best way of doing the upgrade, so that clients can do the upgrade on thier own which will insure all the data from SQL is transfered from old to new version and the applicatoin works fine with the new version. (often time a big table may have be modified into two or three smaller table... we have to make sure the data gets splits correctly)

Currently we do our clients upgrade ourself and have to go through testing everytime we do the upgrade...the only problem is we have to upgrade every moth because of the nature of the industry and its starting to consume lot of time.

Any thoughts would be appriciated!!

What's your high-level architecture? If you have a middle-tier in between the client and the database, you can control access and you're happy. If this is the case you can set up an auto-download of your updates from the the middle tier. The middle tier can then block service requests from your clients while it performs the upgrade. You'll want to create an update package that the middle tier understands, which should include a Sql script (check out DB Ghost by Innovartis or Red Gate's tools) and all of the assemblies for the middle tier and the clients. Once the back- and middle-tiers have been updates, the middle tier "unlocks", then requires clients to upgrade and streams the assemblies directly to the clients.

If you do not have a middle tier, you'll want have your clients auto-update directly. The first client to receive the update sets a flag on the database that all clients must test for. This will be a soft lock that you'll depend on your clients honoring. The client updates the database and itself, then sets a minimum version level in the database which the other clients, upon next connect, read in. If they're out of date, it triggers a dl of the new update.

Jeff

Ypu could create SQL Upgrade Scripts using RedGate SQL Tools. You could even write a program in you client app that pulls down the latest binaries and the latest SQL Scripts and runs them on the client.

I have found that RedGate SQL Tools are very handy for scripting schema and data changes between database versions.

Look at a tool called DbGhost, it allows for putting your database under source control and managing change. It also will generate change/update scripts if you want.

They even have an API available to deploy a full db update engine if needed.

BOb