Using SQL server as state server in ASP.NET

Choosing the correct place store the session state for web application is a real challenge.
The way the session data is store and how much session data is going to be used are some of the questions rise when dealing with it.
Over the past few years developers thought about using SQL server as their session state manager under certain circumstances.
Some of them were because :
1. The web application has to be deployed in a server farm.
2. High number of users targeted to use the application and higher demand for large session data

Out of the box ASP.Net brings a session state management integrated with SQL server. Developers can easily configure a SQL server to act as the Session management server. This also enables developers to implement a failover cluster, so that they can make sure that application is available even if one SQL server is down. Also this enables the application to be deployed in a web farm since the session is managed by SQL server running on a different process rather than the IIS process itself.

How to configure :
First run the InstallPersistSqlState.sql or InstallSqlState.sql you find in SystemFolder\Microsoft.NET\Framework\VersionBoth of these files do the same thing.
But they differ from where they create the tables to hold session data. If you use InstallSqlState.sql it will create the tables in the TempDB causing the data to be removed when, the SQL server is restarted. But if you use the InstallPersistSqlState.sql it will create the tables in the ASPState database and data will not be deleted when, the SQL server is restarted.

Once that is done your SQL server is ready to act as a session management server. Then you can simply add the following entry in Web.config to use it.


So making SQL server as a State server is simple as that. But there is one more thing you should pay attention when storing custom objects in the session. ASP.Net stores objects in SQL server by serializing them to a byte stream. Therefore your custom objects should be serializable in order to work with SQL session state server.