Want Input on VB.NET Exe and SQL DB Setup (and Updates_

We have a VB.NET exe and SQL DB we want to deploy to customers. The DB would reside on their own server, with multiple users accessing it. (So Customer A and Customer B each have their own servers with a copy of our DB. Customer A has users A1, A2, and A3, while Customer B has users B1 and B2, each with their own machines.)

As we update the exe and the SQL DB structure, what's the best/recommended setup practices?

To avoid possible security issues with .NET exes on servers, we thought the exe (and support files) should reside locally on each user's machine. And we'll create the msi for this in VB.NET in a setup project. Therefore, each user will need to get updated files locally when we create a new msi.

So what we envision, is when we have an update, we put the msi on their server. When a user runs the main program, it determines if there's a newer msi on their server. If so, it runs it. The first person to run it will also run scripts against the SQL DB to update it's structure. (Which means others need to be logged off, and it would somehow need to prevent others from logging in.)

Does all this sound feasible? If we're on the right track, is there a recommended practice to having SQL scripts stored and ran against a SQL DB to update it's structure? Is there a way to kick users out of a SQL DB, and prevent others from logging into it?

Otherwise, if this is overly complicated, we're all ears regarding alternatives. Thanks.
Who is Participating?
Vadim RappCommented:
I think, the following is shaky: "The first person to run it will also run scripts against the SQL DB to update it's structure. (Which means others need to be logged off, and it would somehow need to prevent others from logging in.)". Plus looks like security breach. The users aren't supposed to have permissions in the database that would allow them to upgrade it. If they do, one day the smartest of them will "upgrade" it in the way nobody could have imagined.

I would do this:

1. the administrator runs the installation (preferably in the middle of the night :-) and creates administrative image. It's during this administrative installation the updating script runs and updates the database.

2. the application is assigned to the users by group policy, or published. When the users log on, or when they launch the application from an advertised shortcut, the new version will be installed automatically, upgrading the old one. Can be other upgrade scenarios as well, but the point is, the database must be upgraded first, and not by the users.

3. As a extra precaution, the database has a special table that says what is the minimum version number of the application that is allowed to work with it; and part of the database upgrade increments that version. When the application starts, it checks if its version is not lower than what the database wants, and if it's lower, then it says "Sorry, this is outdated version; please use "add new programs" to install the current version". We have this implemented in our applications, and it proved to work well and not frustrate the users too much.
Everything you have planned for your front-end sounds fine.

For the SQL part though, how often do you anticipate stucture changes/additions?

I would assume it is not a daily thing, and if that is the case, I would pass off the project of updating the structure to your client's DB Admin. If they don't have one, then you could create the script to do all of the updates/changes, but let them decide when to run it. This eliminates you having to account for logging out everyone, keeping them out, etc. In this manner, your client can simply stay late one day, wait for everyone to log off, take the server offline, and then run the script.


- Anthony
For application deployment, you can use ClickOnce and publish the app to each customer's server.

For DB deployment, you can make an exe which runs the required scripts to make the changes and then the admin can run this exe on the server.
jjsatherAuthor Commented:

Thanks for the feedback, and I'll gladly take more. One issue is the range of customer types we have, which maybe I should have mentioned. Meaning, some customers are larger, with 10+ users, who will have a DB admin to deal with the server (and maybe the DB script separately as has been suggested.)

However, we also have customers with only 1 user. They'll have SQL Express on their 1 local computer (no server) and very limited computer knowledge. In such cases, the simpler we can make the process, the better. Hence, we like the idea of the user running the program like normal, and letting the program compare versions and update the program files (and SQL DB script changes) as needed.

So it'd be nice to have a single process for all customer types, but maybe that's not realistic. Thoughts?
Vadim RappCommented:
During the installation, the installation checks (1) if the user is administrator (2) if the database has been upgraded. If 1=yes and 2=no, then it gives the option to upgrade the database.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.