ASP.NET Session State Management Using SQL Server

Version 2
    Share This:

    by John Paul Cook


    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="
       _ sqlConnectionString="data source=; integrated security=true"
       _ cookieless="false"
       _ timeout="20"

    Configure the SQL Server to store Session objects by running a script  to create the ASPState database. Version 1.0 of 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.


    Version 1.1 of the .NET Framework provides both InstallPersistentSqlState.sql and InstallSqlState.sql. The Framework Version 1.1  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

    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]

    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]

    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.


    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.


    John Paul Cook is a database and .NET consultant. He  also teaches .NET, XML, SQL Server, and Oracle courses at Southern  Methodist University's location in Houston, Texas.