Architecture – Differentiating Standalone and Client-Server Architectures

I would say that giving the sysadmin control over when the update is applied is the best way - this means that an admin can delay updating if he's working at a critical time (say, over christmas) when demand is likely to be high and staff availability low, and also the potential for a screwup to be really damaging is much higher than usual.

So an fully automated system would not be a benefit. However, that said, there are times when a non-automatic update system means the software will simply never get updated.

I would therefore offer an option - manual or automatic, and let the installer decide whether to check that auto option or not (which he will do depending on his circumstances).

the other thing that you do is the rollback mechanism - this is great. Jenkins does it, so if an update doesn't work well, you click a button and it all goes back to the previous state. I wish all applications worked like this. Once you have such a rollback system, then admins are much more likely to apply updates so you might find they use the automatic update option more often. In this case, what would also be a good thing is to make the fact that an update was recently applied to be more visible.

It all depends on the requirements of your system. If you have 1-5 users who will only use the application internally and there's a Windows domain, yes, a physically 2-tier application is a fine design. Logically you'd want to code for n-tier though, so that if those requirements do change you can more easily separate out the physical tiers. You could use AD Group membership, and connect to the database using Integrated Security. The map the AD group to a DB user, and grant permissions in your DB based on that role/group.

I had built such a system, and it worked well. Until half of the users had their offices moved to the location in another town. They were still logically part of the same network where the DB was, but the VPN tunnel was over a very slow T1 line. The app was unusable from their new location. Fortunately I had designed my n-tier application using the Csla.Net framework and was able to make some config file changes, setup an app server (IIS server), and my problem was solved. That made the network between the client application less chatty, as the business objects serialized themselves to the app server, did the chatty db work (and the DB and IIS servers were on the same network switch), then serialized back.

Of course you can do the same with WCF; you'd just have to build it in now and not worry about it, but there's more upfront cost to building the WCF service. However, you'd be prepared if the requirements change and the chattiness of the DB code becomes an issue. You can also use views and other DB objects as needed to help secure, although I've found SPs which are just thin wrappers around insert/update/delete statements to be more maintenance than they are worth.