ASP.NET supports three different storage media for the application’s session state—the memory of the worker process, the memory of a tailor-made Windows NT service, and a table within a SQL Server database. Although the state management infrastructure works differently in the various cases, the high-level programming interface doesn’t change and the programmer always handles persistent information using the familiar Session object.
To use a SQL Server database as the session state storage medium, enter the following changes in the <sessionState> section of the web.config file:
<configuration>
<system.web>
<sessionState
mode="SQLServer"
sqlConnectionString="server=...;uid=...;pwd=...;" />
</system.web>
</configuration>
You set the mode attribute (which is case sensitive) to SQLServer and specify the connection string through the sqlConnectionString attribute. Notice that the sqlConnectionString attribute string must include user ID, password, and server name. It cannot contain, though, tokens such as Database and Initial Catalog. If you use integrated security, replace User ID and password information with the Integrated_Security token. But what is the database used to persist the session state? Who's in charge of creating it?
ASP.NET provides an installation script that you must run before you deploy the application. The script creates all necessary database objects including stored procedures and jobs. Only SQL Server 7.0 or newer is supported.
When Microsoft first shipped ASP.NET 1.0, only a couple of T-SQL scripts were available, one to install and one to uninstall the database. The scripts are named "InstallSqlState.sql and "UninstallSqlState.sql, and they create a database called "ASPState and several stored procedures. The session data, though, is not stored in regular, persistent tables but in a couple of tables within the TempDB database. In SQL Server, the TempDB database provides a storage area for temporary tables, temporary stored procedures, and other temporary working storage needs. Get the point? In ASP.NET 1.0, by default the session data is lost if the SQL Server machine is restarted.
ASP.NET 1.1 ships a second pair of T-SQL installation scripts, named "InstallPersistSqlState.sql and "UninstallPersistSqlState.sql. These scripts still create an ASPState database but, this time, the data tables are persistent because they're created within the same database. The tables that get created are named "ASPStateTempApplications and "ASPStateTempSessions. The ASPStateTempApplications table defines a record for each currently running ASP.NET application. The ASPStateTempSessions table stores the actual session data. The table contains one row for each active session. The scripts also create a job to delete expired sessions from the session-state database. The job is named "ASPState_Job_DeleteExpiredSessions and runs every minute. Note that the SQLServerAgent service needs to be running for the job to work.
All scripts are located in the following path:
%SystemRoot%\Microsoft.NET\Framework\v1.1.4322
If you install ASP.NET 1.1, the above path contains both scripts. If you run ASP.NET 1.0, you can download the persistent scripts through the Knowledge Base article Q311209.
Dino Esposito is Wintellect's ADO.NET and XML expert, and a trainer and consultant based in Rome, Italy. Dino is a contributing editor to Windows Developer Network and MSDN Magazine, and the author of several books for Microsoft Press including Building Web Solutions with ASP.NET and ADO.NET and Applied XML Programming for .NET. Contact Dino at [email protected].
