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.