Experience with SQLExpress for a multi-user commercial application?

Question

I have inherited a VB6/Access application that we have developed and sold for many years. We're going to SQL Server 2005 Express Edition and .Net.

The application can be multi-user. Currently the setup is simple for the customer -- Navigate to the folder to create the database on first launch; second user browses to the same file.

If we go with SQLExpress I believe our application will require more involvement to configure SQLExpress on the server. But I think we will get better security, and (with no code changes) a SQL version for larger customers.

How can I create the best customer experience from an installation and tech support point of view? What issues have come up for you? What install procedures have worked?

Do you set up a separate install for server/client, or just provide good instructions? What kinds of things do customers get wrong on the first try?

Answer 1

A deployment project from Visual Studio allows you to install a SQL Server Express instance with ease.

We have the same kind of scenario for our applications and it means you do need separate installations for the client and server.

Our server installation deals with either installing a new SQL Server or upgrading the schema of an existing installation if necessary. The client installation simply packages up the files required by the client. You have to consider the scenario of upgrading the database schema and ensuring the clients have the updated client version which works against the new schema. We achieve this in a simple way by:

Storing a version id in the database e.g. 1.0.1 Updating the AssemblyInfo.cs of the client application and ensuring the assembly version matches the version stored in the database. If it doesn't it prompts the user to install the new version.

For the best possible user experience you would like to be able to install a new server version and for all the clients to auto update. We have a method for doing this and I can give you more details if required.

Answer 2

I would recommend going the SQL Express route and including it in the install package. The installer has a ton of command-line options, and you can use SQL scripts to do any post-install configuration to the database (i.e., enabling/disabling CLR integration, OpenRowset, other features).

In addition, it's much more stable than the old MSDE 2000 installs; I had nightmares supporting that. I've also found that 99 times out of 100, putting default DB install parameters makes people happy.

SQL Express Weblog

How to install SQL 2008 From the command prompt