Jon's Blog

.NET Development & More

Using SQL Session State with Multiple Environments

15. February 2013 Jon Blog (0)

I really like storing Session State out-of-process in a SQL Server database. However, I use custom code that switches the connection string based on the current environment my code is running in (test, stage, production, etc.). It was a little tricky to get this setup properly. But I am pretty happy with the following solution.

  • First, you have to setup the Session State database on the SQL machines/environments you are interested in storing your out-of-process session state in. You need to execute a command similar to the following on the command line to build out the database structure:
    aspnet_regsql -S localhost -E -ssadd -sstype p
    See this link for further information: http://msdn.microsoft.com/en-us/library/ms229862%28v=vs.100%29.aspx
  • Add the following line in your web.config under the <system.web> node. Make note of the partitionResolverType. We will discuss that further in the next step.
    <sessionState mode="SQLServer" partitionResolverType="MyApplication.Utility.SessionConnStringResolver" timeout="60" cookieless="false"/>
  • The partitionResolverType above can reference a cusom class that can be used to choose which connection string you want to use at run-time. For example you can create the following class for dynamically switching your connection string:
namespace MyApplication.Utility
{
    public class SessionConnStringResolver : System.Web.IPartitionResolver
    {
        public void Initialize() { }

        // Return the correct ASP.NET Session DB for the current running environment
        public String ResolvePartition(Object key)
        {
            // Custom code to get connection string based on current environment
            return MyConfigClass.GetConfigSettings.SessionDBConnString;
        }
    }
}

 

If you are using LINQ to SQL you should also set Serialization Mode to Unidirectional on the Object Relational Designer as discussed in this link. This allows you to serialize/deserialize your LINQ classes when saving to the database.

For a better explanation of the Partition Resolver feature of SQL Server Session State see the following links. Above is just the steps I followed to set everything up.