How to change sqlConnectionString for sessionState in SQLServer mode at runtime?

Question

Hello,

We're trying to find a way to change the sessionState's sqlConnectionString (in SQLServer mode) at runtime. We're implementing a fail over for our SQL server and we want to catch when the sessionState fails to have access to my SQL server and tell it to fail over to my secondary server and initialize the site-wide fail over at the same time.

If I'm not clear please feel free to ask me more details. (BTW we're using C#)

Edit: Just to be sure, I'm talking about sessionState and not regular sql server connections see http://msdn.microsoft.com/en-us/library/h6bb9cz9(vs.71).aspx.

My current web.config is built like this :

<configuration>
    [...]   
<system.web>
    [...]
    <sessionState mode="SQLServer" timeout="525600" sqlConnectionString="Data Source=localhost\SQLEXPRESS;User ID=myUser;password=myPassword" cookieless="false"/>
</system.web>
[...]
</configuration>

Hope this helps.

Answer 1

Depending on how you're managing your connection, you can specify a mirrored server's failover partners in a SQL server connection string:

Data Source=myServerAddress;
Failover Partner=myMirrorServerAddress;
Initial Catalog=myDataBase;
Integrated Security=True;

(from here)