Apache License, Version 2.0
This content contains the source code developed for ASP.NET to take advantage of SQL Server 2014 In-Memory OLTP as a session state store. The zip file contains source code for an optimized ASP.NET Provider for In-Memory OLTP. It also contains .sql scripts to create memory-optimized tables and natively compiled stored procedures in SQL Server for further performance gains.
The source code is provided for reference here. A downloadable package for deployment is also available via NuGet: https://www.nuget.org/packages/Microsoft.Web.SessionState.SqlInMemory
Setting up the provider requires two steps, installing the provider into the ASP.NET application and creating the In-Memory OLTP database and objects in Microsoft SQL Server 2014.
Download the ASP.NET Session State Provider for SQL Server In-Memory OLTP from the NuGet gallery by running the following command from the Visual Studio Package Manager Console:
PM> Install-Package Microsoft.Web.SessionState.SqlInMemory
Installing the package will do the following things:
• Add references to the ASP.NET Session State Provider assembly.
• Add to the web.config file a customProvider equals to "SqlInMemoryProvider", where the connectionString attribute needs to be updated.
<?xml version="1.0" encoding="utf-8"?>
<sessionState mode="Custom" customProvider="SqlInMemoryProvider">
connectionString="data source=sqlserver;initial catalog=ASPStateInMemory;User ID=user;Password=password;" />
• Adds an ASPStateInMemory.sql file that includes the script for creating the SQL Server database configured to support In-Memory OLTP.
Setting up In-Memory OLTP Database and objects
Open the T-SQL script file "ASPStateInMemory.sql" and update the 'CREATE DATABASE' statement to replace the 'FILENAME' attributes to specify a path that will exist in your SQL Server machine where the memory-optimized filegroup should exist. For further considerations on placement of this filegroup, see Books Online section Creating and Managing Storage for Memory-Optimized Objects
CREATE DATABASE [ASPStateInMemory]
ON PRIMARY (
NAME = ASPStateInMemory, FILENAME = 'D:\SQL\data\ASPStateInMemory_data.mdf'
FILEGROUP ASPStateInMemory_xtp_fg CONTAINS MEMORY_OPTIMIZED_DATA (
NAME = ASPStateInMemory_xtp:, FILENAME = 'D:\SQL\data\ASPStateInMemory_xtp'
After updating the 'FILENAME' attributes, run the entire script for creating the In-Memory tables and the natively compiled stored procedures.
Additionally, create a periodic task in SQL Server to run the stored procedure 'dbo.DeleteExpiredSessions'. This procedure removes the expired sessions and frees up the memory consumed.NOTE:
The memory-optimized tables are created with a durability of SCHEMA_ONLY to optimize for performance. If session data durability is required, then change the 'DURABILITY' attribute from 'SCHEMA_ONLY' to 'SCHEMA_AND_DATA'. More information can be found in Books Online sections Defining Durability for Memory-Optimized Objects
and Durability for Memory-Optimized Tables