Session State Management Using SQL Server in ASP.NET

Web applications are by nature stateless. Statelessness is both an advantage and a disadvantage. When resources are not being consumed by maintaining connections and state, scalability is tremendously improved. But the lack of state reduces functionality severely. ECommerce applications require state to be maintained as the user navigates from page to page. ASP.NET’s Session object makes it easy for developers to maintain state in a Web application. State can be maintained in-process, in a session state server, or in SQL Server.

In-process state management is the ASP.NET default, and it offers the fastest response time, but does not work in a Web farm. Consequently, it is not practical in high capacity Web applications requiring the load to be spread over multiple servers. A dedicated session state server is shared by all servers in a Web farm, so it provides scalability of the Session objects across all Web servers. It cannot store state persistently. If a dedicated session state server goes down for any reason, all session state data is lost. SQL Server is another alternative for storing session state for all of the servers in a Web farm. Since SQL Server is a database, there is a popular misconception that ASP.NET session state maintained in SQL Server is stored persistently. By default, it is not. If the SQL Server is stopped, the session state data is lost. By making a few simple changes, state can be stored persistently. It is important to understand that persistent is not the same thing as permanent. ASP.NET places a time limit (timeout in web.config) on how long a session’s state is maintained. If the SQL Server is configured to store state persistently and it is down for longer than the ASP.NET session timeout interval, the session state data is lost.

Configuring ASP.NET Session State Management
Use the sessionState section of the web.config file to configure an ASP.NET Web application to use a SQL Server for session state management. The session state timeout interval is specified by using the timeout parameter.

By default ASP .NET uses cookies to identify which requests
belong to a particular session.
If cookies are not available, a session can be tracked by
adding a session identifier to the URL.
To disable cookies, set sessionState cookieless="true".
–>
_ mode="SQLServer"
_ stateConnectionString="tcpip=127.0.0.1:42424″
_ sqlConnectionString="data source=127.0.0.1; integrated security=true"
_ cookieless="false"
_ timeout="20″
/>

Configure the SQL Server to store Session objects by running a script to create the ASPState database. The .NET Framework provides a state database configuration script in %SYSTEMROOT%\Microsoft.NET\Framework\v1.0.3705\InstallSqlState.sql. If you open the file, you will see a statement to create a database called ASPState. This probably adds to the confusion about state being persistent. The ASPState database contains stored procedures that create tables in tempdb. The tables in tempdb are where session state is actually stored. Thus, when the SQL Server is shutdown, all session state is lost. This raises an important question: If the SQL Server is never shutdown, will tempdb eventually become 100 percent full and run out of space? Recall that ASP.NET connections automatically time out and their resources are freed up after the timeout duration is exceeded. The InstallSqlState.sql script creates a job called ASPState_Job_DeleteExpiredSessions to delete expired sessions from tempdb. Recall that ASP.NET does not keep session resources alive indefinitely. To support this feature when a SQL Server is used to maintain state, the SQL Server Agent must be running so that the expired session deletion job runs as needed. By default, the job is scheduled to run every minute. It deletes session state rows with an Expires value less than the current time. The account under which the SQL Server Agent runs must have the privilege to execute the DeleteExpiredSessions stored procedure.

ASPState database scripts come in pairs. InstallSqlState.sql creates the database and supporting objects. UninstallSqlState.sql drops the database and all supporting objects (e.g., the job to delete expired sessions). You cannot drop a database if it is in use, so if the UninstallSqlState.sql script fails with this error message:

Server: Msg 3702, Level 16, State 4, Line 4
Cannot drop the database ‘ASPState’ because it is currently in use.

Microsoft Knowledge Base article 311209 says to stop the Web server service to overcome this error. An “uninstallation” failure can still occur even if the Web server service is stopped. Additionally, you might not want to stop the Web server service because that will cause all Web applications on the server to stop. Instead, use the SQL Server Enterprise Manager. Find the processes accessing the ASPState database and delete them. If users are still accessing the application and causing new processes to be created faster than you can delete them, go to the IIS console and select the Properties for the Web application. On the Directory tab, click the Remove button. This will prevent access to the Web application and allow you to kill any remaining processes accessing the ASPState database. Once the processes are gone, uninstallation should completely successfully. Be sure to go back to the IIS console and click the Create button to restore the Web application to normal working order if you previously clicked the Remove button.

Version 1.0 of the .NET Framework does not provide a script for creating an ASPState database that maintains state persistently. However, Microsoft Knowledge Base article 311209 does provide a link for downloading InstallPersistentSqlState.sql and UninstallPersistentSqlState.sql. The InstallPersistentSqlState.sql script causes the session state data to be stored in permanent tables in ASPState instead of temporary tables in tempdb.

The .NET Framework provides both InstallPersistentSqlState.sql and InstallSqlState.sql. The Framework scripts are found in the %SYSTEMROOT%\Microsoft.NET\Framework\v1.1.4322 folder. Although the 1.0 and 1.1 versions of InstallPersistentSqlState.sql accomplish the same thing, they are different. For SQL Server 2000 and above, the 1.1 version creates the ASPState stored procedures using GETUTCDATE instead of GETDATE. The 1.0 version always uses GETDATE. You can use the Framework version 1.1 script to create a database for an application using the Framework version 1.0.

If you specify integrated security in the web.config file, you will have to create a server login for the ASPNET user and then make the login a user in the ASPState database. You will also have to grant permissions to the ASPNET user to use database objects. If you do not store state persistently, the ASPNET user must be granted permissions to use state management objects in tempdb. The prudent approach is to grant no more permissions than are absolutely necessary. Here are the permissions I granted after executing the Version 1.0 InstallSqlState.sql script:

USE masterGOEXECUTE sp_grantlogin [DBAZINE\ASPNET]GO USE ASPState GO EXECUTE sp_grantdbaccess [DBAZINE\ASPNET] GRANT EXECUTE on TempGetAppId to [DBAZINE\ASPNET] GRANT EXECUTE on TempGetStateItemExclusive to [DBAZINE\ASPNET] GRANT EXECUTE on TempInsertStateItemShort to [DBAZINE\ASPNET] GO USE tempdb — remove this if using persistent state GO — remove this if using persistent state EXECUTE sp_grantdbaccess [DBAZINE\ASPNET] — remove this if persistent state GRANT SELECT on ASPStateTempApplications to [DBAZINE\ASPNET] GRANT INSERT on ASPStateTempApplications to [DBAZINE\ASPNET] GRANT SELECT on ASPStateTempSessions to [DBAZINE\ASPNET] GRANT INSERT on ASPStateTempSessions to [DBAZINE\ASPNET] GRANT UPDATE on ASPStateTempSessions to [DBAZINE\ASPNET] GO

If you use the InstallPersistentSqlState.sql, remove the three lines as indicated above.

Consider the grants shown above as a starting point for creating your own script appropriate for your environment.

Conclusion
ASP.NET offers two simple solutions to session state management in a Web farm. Only SQL Server offers persistent state management. A dedicated session state server does not offer persistent state management, but does not require the creation of a database (one more thing for the DBA to administer). The value of persistent state has to be weighed carefully. Maintaining session state persistently is useful only if the SQL Server can be brought back up within the session state timeout specified in the web.config. For those situations where using a SQL Server as a state server makes sense, ASP.NET makes it easy.