We have a suite of programs that will run on a single dedicated server via numerous terminal server sessions, with every program in every session for every user accessing the same database which is usually on a second dedicated server. When the second server fails (which it will), we need to be able to be able to have the user quickly swap the connection to the database on the Application server (which is transactionally replicated from the database server). The users we deal with are shop floor supervisors in a very low tech industry (Leather Tanning), and this is a task that we may have to direct them to do after having been woken up at 4am in the morning by a phone call from the other side of the country, and that has to be done quickly because production will have stopped with potentially 50 workers standing around waiting, so simplicity is essential. I want to provide a form with a single button, that upon pressing the button will swap the connection for them and confirm that it has been done (with appropriate safeguards to ensure it isn't done accidently, i.e. limit the access to a single user specific to the task e.g. user id "Swap").

I've had a look at the SetConnections() method, and by itself I can't see how it helps me do what I want.

I’ve used ConnectionManager.ShowAvailableConnectionStrings() to configure two alternative connection strings for our system using the ApplicationKey “TMS”.

All the programs that make up our system have the following code in AppMain.vb

To manually setup a connection string within the AppMain.vb, just comment out the SetConnections line...this is what forces the Connection Manager to do its thing.

Instead, manually add a connection like this:

That is how you manually provide a connection string.  Now you can get more sophisticated as to how the end-user to obtain this connection.  As previously mentioned, we have a server service that all of the clients connection to first via an http request (you can also use a TCP socket).  If the server cannot be found, a UDP broadcast is sent out to which the server will listen and then respond with it's own IP so the client knows how to connection to it...once the client knows the IP of the server, it connects on a particular port, and then the server gives it the connection string that needs to be used.

This is probably not the approach that you need, but it might get some wheels turing as to how you may need to connect.  One other thought is that you can have a dialog that pops up with a combo asking which location a user is at or which database to which they want to connection which is populated froma network shared file.  Once selected then you would know how to connect...this is just another approach and idea.

Hope this helps

Our system is critical to production in the the Wet-blue (first stage leather processing)Tanneries where it is installed. Such Tanneries are generally in small to medium sized towns in country areas, usually with no IT staff on-site and limited availability of external IT specialists. These Tanneries usually have variable profitability based on factors they can't control, and therefore are reticent to spend any more than they have to on IT. As a result we have put a lot of time into ensuring our system, hardware and software, is as reliable as possible, can be maintained with the facilities the Tanneries have available to them, and that is within their budget. We have considered SQL clusters, but don't believe that it provides an appropriate solution for our customers.

Our end users will NEVER install our system, it will always be done by us on-site. We will be the only ones who will set up connection strings, of which there will only be two the primary one using the database on the DataServer, and the secondary using the AppServer. Every program in the system must be using the same connection string at the same time.

All the Programs that make up our system will use the same ApplicationKey, every BO will use the same DataSourceKey, the programs will only be run on one Computer, the AppServer (either directly or via terminal services).

We use two identical servers as our AppServer and DataServer each with hot swappable RAID disks. We run SQL Server on each and have transactional replication running to ensure that the TMS DataBase on AppServer is identical with that on DataServer. We use group policy to lock down the desktops of all users (except Administrator) to the extreme so that they can only use the programs that are started for them automatically when they log on.

If DataServer fails, the production will stop, we will be called, and we will instruct a production supervisor, how to reconfigure the system so that every program will now look at the database on AppServer, so that then after a reboot production can recommence. If AppServer fails, production will stop, we will be called, we will instruct the supervisor to powerdown both servers, transfer all the hot swap disks from one AppServer to DataServer, restart the server with AppServer's disks and then proceed as for DataServer having failed. This disk swapping process is one we have been through over a dozen times!

Once "DataServer" has been repaired, we wait for an extended break in production, either overnight or weekend, copy the database from AppServer to DataServer, re-establish replication and reconfigure the system to look at the database on DataServer.

The task of reconfiguring the system to look at the database that will be the Production database is what I'm trying to accomplish now. We will have a program for which this is the only task. This program will only be available to a special user "Swap" for which this the only function available. Normal users will never get anywhere near it.

My testing indicates to me that ConnectionManager.ShowAvailableConnectionStrings() will perform the task I want swapping between pre-configured connection strings. Given that the programs are only ever run on AppServer, this will make the change for every user. My only problem is still that I would prefer not to have the user "Swap" presented with so many options. Reverting to the database on DataServer is fine as we would be doing it, but in the first instance the change will be made at a time when the pressure is on as production is stopped, and the supervisor performing the change is unlikely to be computer literate.

This is the way I'll proceed, for now anyway.

Peter

Would you consider this idea:

• Create a flag file with special name on a shared folder to all workstation
• Your application will look for this file, if found, you will programatically do the swap of the connection
• After a day or two (checking the file date),  all workstations should have done the change, then delete the file, or you have a Workstation Version XML file in the same share where all workstation should write down which connection string they are using or a known value that will tell you which connection have been used, then email this to you.

As you can see, there are many things you can automatically do to keep in control of your workstations.

Hope this give you some useful ideas.

Thanks for the idea, but I don't have any workstations that have to be kept in sync, the programs are only executed on AppServer.

Peter