Automatic SQL Server State Management


ASP.NET Developer's JumpStart
By Paul D. Sheriff, Ken Getz
Table of Contents
Chapter 23.  State Management in ASP.NET

If you have an installation of SQL Server available to you, you might want to consider moving your session state management to SQL Server, especially if session state is of critical importance to your application and you can't afford to lose the session state for a user. When you send persistent session data to a SQL Server database, the data not only will survive the restarting of Web services but also a restart of the whole machine.

Follow these steps to use SQL Server to manage your state:

  1. Open the Web.Config file in the Visual Studio .NET editor.

  2. Locate the <sessionState> XML element.

  3. Change the Mode attribute to SQLServer.

  4. Make sure the Cookieless attribute is set to True.

  5. Change the sqlConnectionString attribute so the data source expression refers to your server. Add valid user ID and password values as well. You do not need to specify the name of a database because the tables that manage state are located in Tempdb. Here's an example:

     <sessionState     mode="SQLServer"     stateConnectionString="tcpip="     sqlConnectionString="data source=(local);user id=sa;password="     cookieless="True"     timeout="20" /> 

After setting these attributes, you need to create the ASPState database with some stored procedures that the .NET Framework will use to manage state. Follow these steps to complete the installation:

  1. Find the file named InstallSqlState.sql located in your <systemdrive>\Windows\Microsoft.NET\Framework\<Version> folder.

  2. Load the InstallSqlState.sql file into SQL Query Analyzer and execute the statements. This creates the ASPState database and all the appropriate stored procedures.

After you have done these tasks, try running your code that creates a session variable. You can stop and start the IIS and Web Publishing Services, and once again, your state will remain. If you open SQL Server Enterprise Manager and navigate to the Tempdb database, you will find a table named ASPStateTempSessions. Open this table, and you will find a record with your session ID, the time this session was created, and when this session will expire. You will also see several binary fields. These fields contain the data for the session. You won't be able to look at this data, but then you don't really need to because the .NET Framework takes care of all this for you automatically.

Issues with Automatic SQL Server State Management

Although using SQL Server to store your session state relieves you of many difficult development issues, you'll still need to consider some important limitations:

  • You're limited to SQL Server. This technique can only use SQL Server; no other server database will work. If you do not have a SQL Server installation available, you will be unable to use this solution.

  • Performance may suffer. Like any of the state management techniques, using SQL Server to manage your application's state can cause your performance to degrade a little. Because it takes a little bit of time to make a connection and read and write state information in the database, there's no avoiding a small bit of overhead.


    ASP. NET Developer's JumpStart
    ASP.NET Developers JumpStart
    ISBN: 0672323575
    EAN: 2147483647
    Year: 2002
    Pages: 234 © 2008-2017.
    If you may any questions please contact us: