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

Question

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.

Solution was to use a PartitionResolver : System.Web.IPartitionResolver with a public method public string ResolvePartition(object key) that will return the appropriate connection string. <sessionState mode="SQLServer" timeout="525600" partitionResolverType="PartitionResolver" cookieless="false"/> — Sébastien Richer

Ed Harper Ed Harper · Accepted Answer · 2010-09-30T16:13:11

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)

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

2 Answers