SQLServer mode stores session state in a SQL Server database. Using this mode ensures that session state is preserved if the Web application is restarted and also makes session state available to multiple Web servers in a Web farm.
 Note |
|---|
|
Objects stored in session state must be serializable if the mode is SQL Server. For information on serializable objects, see the SerializableAttribute class.
|
To use SQLServer mode, you must first be sure the ASP.NET session state database is installed on SQL Server. You can install the ASP.NET session state database using the Aspnet_regsql.exe tool, as described later in this topic.
To configure an ASP.NET application to use SQLServer mode, do the following in the application's Web.config file:
The following example shows a configuration setting for SQLServer mode where session state is stored on a SQL Server named "SampleSqlServer":
<configuration>
<system.web>
<sessionState mode="SQLServer"
sqlConnectionString="Integrated Security=SSPI;data
source=SampleSqlServer;" />
</system.web>
</configuration>
| Note |
|---|
|
If you specify a trusted connection to your SQL Server in the configuration file using the sessionState element's sqlConnectionString attribute, the SessionStateModule will connect to SQL Server using SQL Server integrated security. The connection will be made using the ASP.NET process identity or the user credentials supplied for the identity configuration element, if they exist. You can specify that the IIS impersonated identity be used instead by specifying <identity impersonate="true" /> and setting the useHostingIdentity attribute of the sessionState configuration element to false. For more information on the ASP.NET process identity, see Configuring ASP.NET Process Identity and ASP.NET Impersonation.
|
To configure SQLServer mode for a Web farm, in the configuration file for each Web server, set the sessionState element's sqlConnectionString attribute to point to the same SQL Server database. The path for the ASP.NET application in the IIS metabase must be identical on all Web servers that share session state in the SQL Server database. For information on steps to resolve the issue when application paths differ between servers, see article 325056, "PRB: Session State Is Lost in Web Farm If You Use SqlServer or StateServer Session Mode," in the Microsoft Knowledge Base at http://support.microsoft.com.
Installing the Session State Database Using the Aspnet_regsql.exe Tool
To install the session state database on SQL Server, run the Aspnet_regsql.exe tool located in the systemroot\Microsoft.NET\Framework\versionNumber folder on your Web server. Supply the following information with the command:
-
Thename of the SQL Server instance, using the -S option.
-
The logon credentials for an account that has permission to create a database on SQL Server. Use the -E option to use the currently logged-on user, or use the -U option to specify a user ID along with the -P option to specify a password.
-
The -ssadd command-line option to add the session state database.
By default, you cannot use the Aspnet_regsql.exe tool to install the session state database on SQL Server Express. In order to run the Aspnet_regsql.exe tool to install a SQL Server Express database, you must first enable the Agent XPs SQL Server option using Transact-SQL commands like the following:
EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXECUTE sp_configure 'Agent XPs', 1
RECONFIGURE WITH OVERRIDE
GO
EXECUTE sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO
You must run these Transact-SQL commands for any instance of SQL Server Express where the Agent XPs option is disabled.
By default, the Aspnet_regsql.exe tool will create a database named ASPState containing stored procedures that support SQLServer mode. Session data itself is stored in the tempdb database by default. You can optionally use the -sstype option to change the storage location of session data. The following table specifies the possible values for the -sstype option:
|
Option
|
Description
|
|---|
|
t
|
Stores session data in the SQL Server tempdb database. This is the default. If you store session data in the tempdb database, the session data is lost if SQL Server is restarted.
|
|
p
|
Stores session data in the ASPState database instead of in the tempdb database.
|
|
c
|
Stores session data in a custom database. If you specify the c option, you must also include the name of the custom database using the -d option.
|
For example, the following command creates a database named ASPState on a SQL Server instance named "SampleSqlServer" and specifies that session data is also stored in the ASPState database:
aspnet_regsql.exe -S SampleSqlServer -E -ssadd -sstype p
| Note |
|---|
|
If you are running ASP.NET 1.0 or ASP.NET 1.1, you cannot use the Aspnet_regsql.exe tool to configure ASP.NET to store session state in a persistent SQL Server database. However, you can obtain scripts to store session state in a persistent database. For details, see article 311209, "HOW TO: Configure ASP.NET for Persistent SQL Server Session State Management" in the Microsoft Knowledge Base at http://support.microsoft.com. As an alternative, Web servers running ASP.NET 1.0 or ASP.NET 1.1 can direct persistent session state to a SQL Server that has the ASP.NET 2.0 session state schema installed.
|
In SQLServer mode, you can configure several computers running SQL Server to work as a failover cluster, which is two or more identical computers running SQL Server that store data for a single database. If one computer running SQL Server fails, another server in the cluster can take over and serve requests without session-data loss. To configure SQL Server mode for a failover cluster, you must specify -sstype p when you execute the Aspnet_regsql.exe tool so that session state data is stored in the ASPState database instead of the tempdb database. Storing session state in the tempdb database is not supported for a SQL Server cluster. For more information about setting up SQL Server mode for a failover cluster, see article 323262, "How to use ASP.NET session state SQL Server Mode in a failover cluster" in the Microsoft Knowledge Base at http://support.microsoft.com.
