| How to set Session State to use SQL Server | Back to How to index |
Background information/experience required:
This is an advanced Administrator function requiring experience with scripts and SQL Server.
This page shows how to implement session state in a SQL Server database instead of in memory (aka InProc). The reason to move to a SQL Server solution is to remove the RAM impact of storing user session variables in memory.
Please refer to the references at the end of this help page for detailed installation instructions.
Why is state management necessary?
HTTP is a stateless protocol. It works in a disconnected fashion with each web request serviced as it's received. After the request is processed, all of the data utilized is discarded. The server doesn't remember anything between calls. That is, it doesn't remember unless it has explicit instructions to do so.
Session variables
Session variables are utilized to remember various data throughout the user's entire online activities. Variables are stored and available throughout the user's session. The variables are discarded when the session ends.
ASP.NET state management
ASP.NET allows you to store session data in memory, via a state server, or in SQL Server. The determination of the storage location is the application's Web.config file. The sessionState element within the system.web element is where the state management option is configured. The following example shows SQL Server utilized:
(inside the system.web configuration element)
<sessionState
mode="SQLServer"
stateConnectionString="tcpip=127.0.0.1:42424"
sqlConnectionString="data source=127.0.0.1;user id=username;password=password"
cookieless="false"
timeout="20"
/>
IMPORTANT: The installation file, web.config, usually already has sessionState defined, so you need to modify the existing information, not simply insert the above. For example, here's typically what's inside web.config:
<sessionState mode="InProc" stateConnectionString="tcpip=127.0.0.1:42424" sqlConnectionString="data source=127.0.0.1;Trusted_Connection=yes" cookieless="false" timeout="40" />
So you would modify it to look like this:
<sessionState mode="SQLServer" stateConnectionString="tcpip=127.0.0.1:42424" sqlConnectionString="data source=127.0.0.1;userid=myuserid;password=mydbpassword" cookieless="false" timeout="40" />
or
<sessionState
mode="SQLServer"
stateConnectionString="tcpip=127.0.0.1:42424"
sqlConnectionString="data source=127.0.0.1;userid=myuserid;password=mydbpassword"
cookieless="false" timeout="40"
/>(note that "Trusted_Connection=yes" gets removed)
The element names and attributes are case-sensitive. The following are possible values for the mode attribute:
SQL Server setup
SQL Server requires a special database to handle state management. The .NET Framework installation includes the necessary files to get this up and running. The following scripts are installed:
These scripts may be run from Query Analyzer or via the isql.exe command-line utility. To set up state management, run InstallSqlState.sql. The result of the script is the creation of a database named ASPState. This handles the storage and maintaining of session variables.
Note on uninstalling the state management feature: Microsoft recommends stopping the World Wide Web Publishing service before executing the uninstall script. You can accomplish this with the net stop w3svc command from a command line. You can restart it with net start w3svc.
You can see the session management feature in action by examining the tempdb database on the SQL Server. It will contain two temporary tables used for session management: ASPStateTempApplications and ASPStateTempSessions.
A viable option
SQL Server provides an alternative if you worry about losing session state data due to Web server downtime. There is a performance hit since database interaction is involved, but it's the most reliable method available.
References:
http://www.ddj.com/windows/184416835