3. Persist Session Data to SQL Server
Maintaining the session state in an external
process certainly makes the whole ASP.NET application more stable.
Whatever happens to the worker process, the session state is still
there, ready for further use. If the service is paused, the data is
preserved and automatically retrieved when the service resumes.
Unfortunately, if the state provider service is stopped or if a failure occurs, the data is lost. If robustness is key for your application, drop the StateServer mode in favor of SQLServer.
Performance and Robustness
When ASP.NET works in SQLServer
mode, the session data is stored in a made-to-measure database table.
As a result, the session data survives even SQL Server crashes, but you
have to add higher overhead to the bill. SQLServer
mode allows you to store data on any connected machine, as long as the
machine runs SQL Server 7.0 or newer. Besides the different medium, the
storage mechanism is nearly identical to that described for remote
servers. In particular, the serialization and deserialization algorithm
is the same, only it’s a bit slower because of the characteristics of
storage. When storing data of basic types, the time required to set up
the page’s Session object is normally at least 25 percent higher than in an InProc scenario. Also in regard to this issue, the more complex types you use, the more time it will take to manage the session data.
Note
When you get to make a
decision between state server or SQL server storage, consider the fact
that SQL Server is cluster-aware, which makes a solution based on it
more robust and reliable than one based on a state server. |
Configuring Session State for SQL Server Support
To use SQL Server as the state provider, enter the following changes in the <sessionState> section of the web.config file:
<configuration>
<system.web>
<sessionState
mode="SQLServer"
sqlConnectionString="server=127.0.0.1;integrated security=SSPI;" />
</system.web>
</configuration>
In particular, you need to set the mode attribute (which is case sensitive) to SQLServer and specify the connection string through the sqlConnectionString attribute. Note that the sqlConnectionString attribute string must provide for a user ID, password, and server name. It cannot contain, though, tokens such as Database and Initial Catalog, unless a custom database is enabled using allowCustomSqlDatabase, as mentioned in Table 15-8. You can specify a SQL Server Initial Catalog database name or use the SQL Server Express attachDBFileName to point to an MDB file in the connection string only if the allowCustomSqlDatabase
configuration setting is enabled. If that is disabled, any attempts to
specify these settings in the connection string will result in an
exception.
Note
The connection string for an out-of-process session state implementation (both SQLServer and StateServer) can also be specified referring to a connection string defined in the <connectionStrings> section. The session state module first attempts to look up a connection string from the <connectionStrings> section with the name specified in the appropriate <sessionState> attribute; if it is not found, the session state attempts to use the specified string directly. |
As for credentials to access the database, you can either use User ID and passwords or resort to integrated security.
Note
Whatever account you use to access session state in SQL Server, make sure that it is granted the db_datareader and db_datawriter
permissions at the very least. Note also that to configure the SQL
Server environment for storing session state, administrative privileges
are required, as a new database and stored procedures need to be
created. |
Session state in SQL Server mode supports the
specification of a custom command timeout value (in seconds) to
accommodate slow-responding-server scenarios. You control it through the
sqlCommandTimeout attribute, as mentioned in Table 15-8.
Creating the SQL Server Data Store
ASP.NET provides two pairs of scripts to
configure the database environment by creating any needed tables, stored
procedures, triggers, and jobs. The scripts in the first pair are named
InstallSqlState.sql and UninstallSqlState.sql. They create a database called ASPState
and several stored procedures. The data, though, is stored in a couple
tables belonging to 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. This means that
the session data is lost if the SQL Server machine is restarted.
The second pair consists of the scripts InstallPersistSqlState.sql and UninstallPersistSqlState.sql. Also in this case, an ASPState
database is created, but the tables are persistent because they are
created within the same database. All scripts are located in the
following path:
%SystemRoot%\Microsoft.NET\Framework\[version]
Important
These script files are included for backward compatibility only. You should use aspnet_regsql.exe to install and uninstall a SQL session state. Among other things, the newest aspnet_regsql.exe supports more options, such as using a custom database table. |
The tables that get created are named ASPStateTempApplications and ASPStateTempSessions. Figure 3 shows a view of the session database in SQL Server.
The
ASPStateTempApplications table defines a record for each currently
running ASP.NET application. The table columns are listed in Table 3.
Table 3. The ASPStateTempApplications Table
| Column | Type | Description |
|---|
| AppId | Int | Indexed field. It represents a sort of auto-generated ID that identifies a running application using the SQLServer session mode. |
| AppName | char(280) | Indicates the application ID of the AppDomain running the application. It matches the contents of the AppDomainAppId property on the HttpRuntime object. |
The ASPStateTempSessions table stores the
actual session data. The table contains one row for each active session.
The structure of the table is outlined in Table 15-10.
Table 15-10. The ASPStateTempSessions Table
| Column | Type | Description |
|---|
| SessionId | Char(88) | Indexed field. It represents the session ID. |
| Created | DateTime | Indicates the time at which the session was created. It defaults to the current date. |
| Expires | DateTime | Indicates
the time at which the session will expire. This value is normally the
time at which the session state was created plus the number of minutes
specified in Timeout. Note that Created refers to the time at which the session started, whereas Expires adds minutes to the time in which the first item is added to the session state. |
| Flags | int | Indicates action flags—initialize items or none—from the SessionStateActions enum. Not available in ASP.NET 1.x. |
| LockCookie | int | Indicates the number of times the session was locked—that is, the number of accesses. |
| LockDate | DateTime | Indicates
the time at which the session was locked to add the last item. The
value is expressed as the current Universal Time Coordinate (UTC). |
| LockDateLocal | DateTime | Like the previous item, except that this one expresses the system’s local time. Not available in ASP.NET 1.x. |
| Locked | bit | Indicates whether the session is currently locked. |
| SessionItemLong | Image | Nullable field, represents the serialized version of a session longer than 7000 bytes. |
| SessionItemShort | VarBinary(7000) | Nullable
field. It represents the values in the specified session. The layout of
the bytes is identical to the layout discussed for StateServer providers. If more than 7000 bytes are needed to serialize the dictionary, the SessionItemLong field is used instead. |
| Timeout | int | Indicates the timeout of the session in minutes. |
The column SessionItemLong,
contains a long binary block of data. Although the user always works
with image data as if it is a single, long sequence of bytes, the data
is not stored in that format. The data is stored in a collection of 8-KB
pages that aren’t necessarily located next to each other.
When installing the SQL Server support for
sessions, a job is also created to delete expired sessions from the
session-state database. The job, which is shown in Figure 4, is named ASPState_Job_DeleteExpiredSessions,
and the default configuration makes it run every minute. You should
note that the SQLServerAgent service needs to be running for this to
work.
Reverting to the Hosting Identity
In
ASP.NET 1.x, credentials used to access the SQL Server stored session
state depend on the connection string. If explicitly provided, the user
name and password are used to access the database. Otherwise, if
integrated security is requested, the account of the currently logged-in
client is used. This approach clashes with the StateServer
state provider, which uses the ASP.NET identity to do its job. More
importantly, though, it poses some administrative issues for intranet
sites using client impersonation. In these cases, in fact, you have to
grant access to the database to every client account that might be
making calls.
In ASP.NET 2.0 and newer versions, the useHostingIdentity attribute (shown in Table 15-8) lets you decide about the identity to be effectively used. Breaking the ASP.NET 1.x behavior, when the SQLServer
state provider is used with integrated security the identity is that
impersonated by ASP.NET. It will typically be ASPNET or NETWORK SERVICE
or any other account impersonated by the ASP.NET worker process through
the <identity> section of the
configuration file. This simplifies the administrative experience for
intranet sites, requiring that only the ASP.NET account be granted
access to protected and critical resources. The useHostingIdentity attribute defaults to true, which enables you to revert to the ASP.NET identity before making calls to the SQLServer session state provider. This will also happen if a custom provider is used.
Note
If you’re using Windows
integrated authentication to access SQL Server, reverting to the host
identity is the most recommended option, for security reasons.
Otherwise, it is advisable that you create a specific account and grant
it only rights to execute session state stored procedures and access
related resources. |
ASP.NET applications designed to run in a Web
farm or Web garden hardware configuration cannot implement an in-process
session state. The InProc mode won’t
work on a Web farm because a distinct worker process will be running on
each connected machine, with each process maintaining its own session
state. It doesn’t even work on a Web garden because multiple worker
processes will be running on the same machine.
Keeping all states separate from worker
processes allows you to partition an application across multiple worker
processes even when they’re running on multiple computers. In both Web
farm and Web garden scenarios, there can be only one StateServer or SQLServer process to provide session-state management.
If you’re running a Web farm, make sure you have the same <machineKey> in all your Web servers.
In addition, for the session state to be
maintained across different servers in the Web farm, all applications
should have the same application path stored in the IIS metabase. This
value is set as the AppDomain application ID and identifies a running
application in the ASP.NET state database. (See Knowledge Base article
Q325056 for more details.)
In ASP.NET 2.0, partition resolvers
have been introduced to let a session state provider partition its data
onto multiple back-end nodes. This allows you to scale session state on
large Web farms, according to a custom, user-defined load-balancing
scheme. A partition provider is a component that supplies the connection
string (the actual string, not the pointer to a string in the web.config file) to the session state that is used to access data, overriding any other settings in the <sessionState> section.
