First published on MSDN on Nov 28, 2017
ASP.NET session state enables you to store and retrieve values for a user as the user navigates the different ASP.NET pages that make up a Web application. Currently, ASP.NET ships with three session state providers that provide the interface between Microsoft ASP.NET's session state module and session state data sources:
InProcSessionStateStore, which stores session state in memory in the ASP.NET worker process
OutOfProcSessionStateStore, which stores session state in memory in an external state server process
, which stores session state in Microsoft SQL Server database
This blog post focuses on the
provider and describes how you can configure it to use SQL Server In-Memory OLTP as the storage option for session data. You can either use the latest ASP.NET async version of the SQL Session State provider (which is the recommended approach), or configure an earlier version of the provider to work with In-Memory OLTP by downloading and running the In-Memory OLTP SQL scripts from our sql server samples github repo.
Use the latest ASP.NET async SQL Session State provider
The Microsoft ASP.NET team has released an async version of the session state provider that uses SQL Server as the data store and leverages async database operations to provide better scability. This version of the provider also includes built-in support for retry logic and works with both In-Memory and disk-bases tables.
Please follow the steps below to install the provider and configure it to use SQL Server In-Memory OLTP:
Open the project’s Web.config file and add the following attributes in the SqlSessionStateProviderAsync element to enable In-Memory OLTP and to adjust the retry times and retry interval (in ms).
Set the value to true for In-Memory OLTP or false for disk-based tables
The maximum number of retries. Set to 0 if you want to disable retries.
Time in ms for the retry interval.In addition to these three attributes, a
connection string section
needs to be added in the Web.config, where the name of the connection string should be the same as the value of
attribute of the SqlSessionStateProviderAsync provider. Below is a sample ASP.NET Web.config Session State section that uses SQL Server In-Memory OLTP with MaxRetryNumber=”5” and RetryInterval=”100ms”
attributes are not set, the provider will use regular disk-based SQL tables with the following default values for the RetryInterval and MaxRetryNumber:
SQL Server optionRetryInterval (ms)MaxRetryNumber
Disk based tables
Set the database isolation level to
by executing the following T-SQL command against the SQL database that you are using to store the session state. The
Transactions with Memory-Optimized Tables
article explains in detail why this is needed.
ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;
Use an earlier version of the ASP.NET Session State provider
In the case where you are not ready to upgrade to the latest async version of the SqlSessionStateStore provider, you can follow the steps below to configure an earlier version of the ASP.NET Session State provider to use In-Memory OLTP (with or without retry logic).
If durability of both schema and data is required:
Currently, the two memory-optimized tables: dbo.ASPStateTempApplications and dbo.ASPStateTempSessions in both scripts are created with DURABILITY = SCHEMA_ONLY meaning that if SQL Server restarts, the table schema persists, but data in the table is lost. If durability of both schema and data is required, the script needs to be altered and the two tables above need to be created with: DURABILITY=SCHEMA_AND_DATA. The
Defining Durability for Memory-Optimized Objects
article explains the two durability options for memory-optimized tables in detail.
Set the Target framework of your web project to
Configure the web app to use the sql session state provider by modifying the Web.config as follows:
• Set the mode attribute of the element to SQLServer to indicate that session state is stored in SQL Server.
• Set the sqlConnectionString attribute to specify the connection string for SQL Server.