Setup SQL Server session state for a web farm

Cows in a field

Setup SQL Server session state for a web farm

Posted 23 Dec 2016

It takes a bit of digging around to get all the information needed to setup out-of-process session state for an ASP.NET web farm. There are a couple of decisions that you need to make and then you need to configure the database and the application. This post explains all this using a real-life project.

The situation

I'm currently working on a project for a large medical center. There is a strong obligation to the public to be always online, especially in case of a large scale emergency.
This leads to interesting choices in infrastructure for their website: everything is redundant and split across multiple locations across the campus.
The database is a SQL Server Availability Group.

Picking the right session state provider

In-Process
Really only suitable for small applications that run in a single server instance.
Session-state server
A TCP service that is hosted on a single server within the infrastructure.
Since this introduces a single point of failure, it's no good for this project.
SQL Server session state
Stores session state in the database, either persistent or in temporary storage.
This is a great pick for a web farm but dus incur additional load on your SQL Server installation
Redis session state
This is the new kid on the block for ASP.NET. Since the medical center is a Microsoft shop and has already invested a lot in top-notch SQL performance, this would only incur technical risk and additional costs for infra.

SQL Server session state, but what flavor?

Putting ASP.NET session state is supported very well, there's tooling to set it up for you but before we dive into that there's yet another consideration.
Where to put the session state:

Application database
This would add a couple of tables to the application database and a bunch of stored procedures. It could be a nice fit when hosting at a shared provider and the additional cost of an extra database is not desired.
Session state in TempDB
Session state data is transient by nature so TempDB, which gets cleared on a server restart and will not be replicated to other SQL Server instances seems like a good idea. You can choose to put the TempDB on a different drive from the application db which could help squeeze more IOPS out of your server. Not writing through to the rest of the cluster may also help improve write performance, but this will also cause loss of session state when the cluster needs to fail over, for example due to maintenance.
Session state in it's own database
This mode will store session state in permanent storage and replicate it across the cluster. That's a performance penalty but gives more guarantees for seemles failover when needed.
The fact that this database is separate from the application allows to easily make different decisions about IT management, for example about backups or even hosting the session state database on a different database instance.
This is the best match for this project.

Configuring session state

Once we decided on where to store the session state we had to roll out the configuration in our environments. These are the steps to follow:

Setup the session state database using the ASP.NET SQL Server Registration Tool

%Windows%\Microsoft.NET\Framework64\v4.0.30319\aspnet_regsql.exe -S MyCluster\Prod -U sa -P topsecret -ssadd -ssype p

Configure the connection string in web.config
I strongly reccommend including the application name in the connection string and keeping the connection time out low.


<connectionStrings>
  <add name="SessionConnectionString"
    connectionString="Data Source=MyCluster\Prod,1234;user=sa;pwd=topsecret;Connect Timeout=10;Application Name=Kentico;Current Language=English" />
</connectionStrings>

Setup the machine key in web.config
If you're running the site on multiple servers or in the cloud, this is a must.


<system.web>
   <machineKey xdt:Transform="Insert"
     validationKey="..."
     decryptionKey="..."
     validation="SHA1" decryption="AES" />
</system.web>

Configure session state in web.config

<system.web>
  <sessionState mode="SQLServer"
     sqlConnectionString="SessionConnectionString"
     compressionEnabled="true"
     cookieless="false"
     timeout="20"/>
</system.web>

Developer Notes