Database Deployment Practices

Question

I have deployed plenty of software to my clients. Mostly are Window Forms applications. Here is my current practice.

1. Manually install SQLExpress and SQL Management Studio to each client PC.
2. Then use ClickOne to install the code from the server.
3. When there is a changes in code, I will use ClickOne to deploy -(NO PROBLEM with this step)
4. But when there is a change in a database column, what do I do?

I have even tried writing a database update script. Each time the program starts, it will read through the .sql update file and run them if the database exists. This solves the problem of updating the database columns, but it does not help in my DEBUGGING work when my customer complain there is a wrong data. At that point, I have to personally go to their site to check it out.

I find it difficult to have the database installed on the client PC as it make my debugging work very very difficult. I am thinking about moving my client database to a host on an Online server. But that then comes with these constraints:

1. What if the internet is down?
2. What if my customer has no internet?

Could you help to advise me? Is this a common problem faced by developer? What is the common practice out there? Does Window Azure or SQL CE help?

Answer 1

Depending on the data I would recommend using SQL CE. If the data isn't too much, speed is not the primary goal (CE is slower than Express) and you don't need DB-Features not supported by CE (e.g. stored procedures) it is the better choice IMHO, because:

• The client does not need to install a full SQL server (easier installation/deployment)
• You do not have problems with multiple SQLExpress instances
• Your SW doesn't need to worry if there even is a SQL instance
• Less resources used on the client side

Additionally the clients could send you their SQL CE DB-File for inspection and you do not need to go to their site.

It is also relativly easy to implement an off site sync with SQL CE and MS Sync FW.