Share via

Configure a SQL Server to Maintain Session State (IIS 7)

  • Article

Applies To: Windows 7, Windows Server 2008, Windows Server 2008 R2, Windows Vista

Out-of-process session state mode preserves session state data by running in a worker process outside the worker processes where ASP.NET applications are running. One type of out-of-process session state uses a SQL server to store session state data. The advantage of this configuration is that session state is preserved despite recycling of the application's worker process, or if either the Windows state service or the Web server goes down.

When a SQL server runs on the same Web server that has the applications for which it maintains state, it supports a Web garden configuration, which increases the Web server's scalability. When the SQL server runs on another server, it supports a Web farm configuration, which greatly increases scalability across a group of servers.

Important

The Windows state service (Aspnet_state.exe) must be running for out-of-process session state to take effect. By default, this service is installed when Windows Server® 2008 is installed and is configured for manual start. You must change the start behavior to Automatic.

Important

Before you configure a SQL server for session state, you must run the InstallSqlState.sql script on the server. By default, this script is stored in %systemroot%\Microsoft.NET\Framework\V2.0.50727.

Prerequisites

For information about the levels at which you can perform this procedure, and the modules, handlers, and permissions that are required to perform this procedure, see Session State Feature Requirements (IIS 7).

Exceptions to feature requirements

  • None

To configure a SQL server to maintain session state

You can perform this procedure by using the user interface (UI), by running Appcmd.exe commands in a command-line window, by editing configuration files directly, or by writing WMI scripts.

User Interface

To Use the UI

  1. Open IIS Manager and navigate to the level you want to manage. For information about opening IIS Manager, see Open IIS Manager (IIS 7). For information about navigating to locations in the UI, see Navigation in IIS Manager (IIS 7).

  2. In Features View, double-click Session State.

  3. On the Session State page, in the Session State Mode Settings area, click SQL Server.

  4. Type a connection string in the Connection string text box, or click Create to create a connection string. If the SQL server is on the server you are managing, select LocalSqlServer in the Connection string text box.

  5. Type a time-out value in the Time-out text box.

  6. (Optional) Check the Enable custom database check box to use a custom database for storing session state data.

  7. (Optional) Configure cookie settings in the Cookie Settings area on the Session State page.

  8. (Optional) Check the Use hosting identity for impersonation check box to use Windows authentication and the host process identity (either ASP.NET or a Windows service identity) for remote connections to the SQL database.

  9. Click Apply in the Actions pane.

Command-line

To configure a state server to maintain session state, use the following syntax:

**appcmd set config /commit:WEBROOT /section:sessionState /mode:SqlServer /sqlConnectionString:**string /sqlCommandTimeout: timeSpan /useHostingIdentity:True|False

The variable mode:SqlServer sets the session state mode to store session data in a SQL server database. By default, the variable string defines the connection string that the SQL server uses. The default setting is LocalSqlServer. The variable timeSpan sets the time, in seconds, that the connection to the SQL server is maintained. The default is 30 seconds. The variable useHostingIdentity:True|False enables or disables the use of Windows authentication and the host process identity (either ASP.NET or a Windows service identity) for connections to the database. The default value is True.

Note

When you use Appcmd.exe to configure the <sessionState> element at the global level in IIS 7, you must specify /commit:WEBROOT in the command so that configuration changes are made to the root Web.config file instead of ApplicationHost.config.

For more information about Appcmd.exe, see Appcmd.exe (IIS 7).

Configuration

The procedure in this topic affects the following configuration elements:

  • <sessionState>

  • <sessionPageState>

For more information about IIS 7 configuration, see IIS 7.0: IIS Settings Schema on MSDN.

WMI

Use the following WMI classes, methods, or properties to perform this procedure:

  • SessionStateSection.Mode property "SQLServer" flag

For more information about WMI and IIS, see Windows Management Instrumentation (WMI) in IIS 7. For more information about the classes, methods, or properties associated with this procedure, see the IIS WMI Provider Reference on the MSDN site.

See Also

Concepts

Configuring Out-of-Process Session State
Configuring Session State in IIS 7