SQL Server 2014 In-Memory OLTP Provider - ASP.NET

Rating: No reviews yet
Downloads: 213
Released: Jul 9, 2014
Updated: Jul 10, 2014 by DerrickVMSFT
Dev status: Stable Help Icon

Downloads

Source Code Microsoft.Web.SessionState.SqlInMemory.1.0.1.zip
source code, 19K, uploaded Jul 10 - 213 downloads

Release Notes

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.

Get Started

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.

NuGet Installation

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"?>
<configuration>
  <system.web>
    <sessionState mode="Custom" customProvider="SqlInMemoryProvider">
      <providers>
        <add name="SqlInMemoryProvider"
             type="Microsoft.Web.SessionState.SqlInMemoryProvider"
             connectionString="data source=sqlserver;initial catalog=ASPStateInMemory;User ID=user;Password=password;" />
      </providers>
    </sessionState>
  </system.web>
</configuration>

• 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'
)
GO

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.

Reviews for this release

No reviews yet for this release.