G'day

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").

ConnectionManager.ShowAvailableConnectionStrings() is a convenient way of maintaining the connection strings, and the "Select" capability does what we want, except for the fact that the user performing the task could have the wrong connection selected at the time (our users are not renowned for their clarity at describing what is on the screen in front of them at such times, or any other times for that matter). We would use it for configuring the system while on-site installing. And switching servers is an installation test we always perform, to ensure that we have the settings right.

What I would find useful would be

public function SetApplicationActiveConnectionString(ByVal ApplicationKey as String, _
                                                                        ByVal DataSourceKey As String, _
                                                                        ByVal ApplicationDefaultTitle As String) _
                                                                        As Boolean

which would do just as pressing the "Select"  in ShowAvailableConnectionStrings does returning true if sucessful false otherwise (i.e. there is no connection string for the given combination).

Any chance of something like this?

I have investigated storing Connection Strings in each App's .config, but then I have a problem with ensuring that they are all kept in sync, and I don't really want to reinvent the wheel.

Peter

You can already do this manually via the ConnectionManager class and SetConnections(). We do this in our medical app by storing off the valid connection strings at install time and them choosing them as appropriate programatically when the program launches.

Dustin,

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

We only have one DataSource for all users for all programs in the system, which runs on a single server through Terminal services sessions. We are not looking to selectively change bits and pieces, we want to change everything in a single hit. It is not a choice a user can make that they want to use a particular datasource, the whole system must be using the same datasource, and it is only ever changed by a supervisor under our instruction when the server running the database fails.

In a test program I’ve populated a textbox as follows

TextBox1.Text = ConnectionManager.GetApplicationActiveConnectionString("TMS", "")

What this shows me is the following Connection String regardless of the value of ApplicationDefaultDescription whether it is "TMS Database Connection", "TMS AppServer Database Connection", "TMS DataServer Database Connection", or "Mary had a little lamb".

Data Source=HAHNDORF;Initial Catalog=TMS201;Integrated Security=True;Persist Security Info=False;Asynchronous Processing=True

If I then run ConnectionManager.ShowAvailableConnectionStrings() again and select the other Connection as below:

Then I get the following Connection string for each of the values of ApplicationDefaultDescription described above.

Data Source=PGD-VISTA;Initial Catalog=TMS201;Integrated Security=True;Persist Security Info=False;Asynchronous Processing=True

This is pretty much what I want to happen (except that it gives the person performing the task too many choices) and I thought it was what the ShowAvailableConnectionStrings was for. 

I know if I changed the ApplicationKey I would get a different Connection String, but that would mean storing that value somewhere central accessible by all the programs in the system, which I might have to do, but it would be far easier if I could press that “Select” button in code.

I hope this expands on what I'm trying to achieve to make it a bit more understandable. Am I misunderstanding something fundamental?

Peter

Really Peter, you will probably not want to use the connection manager at all in this case but rather create a solution on your side to just manually add the data sources.  This is actually what we do within our medical software (along with a number of other things).  We actually prevent our end-users from ever entering a connection string on the client side altogether as we have a server service that we distribute with our medical software which "tells" all of the clients what their connection string will be.  Now you don't have to take this approach, but you will still want to manually create your data source connections (which ultimately may save some installation steps anyway for your end-user).

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

Have you thought about having using a SQL cluster?



http://www.sql-server-performance.com/articles/clustering/clustering_intro_p1.aspx



Then you don't need to worry about managing connections. Just another perspective...

G'day

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

Hi 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.

Edhy Rijo

Edhy,

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