How to configure ASP.NET Session State to use In-Memory OLTP

    Microsoft has some great news for you if you are using SqlStateSessionStore to store your user's state information in your application. With the version ASP.Net 4.6.2, new ISessionStateModule interface lets you store session data in your own way by using async methods. You can create your own async Session State Module implementation. This version works with the regular file base tables and the In-Memory tables.

  To enable the In-Memory tables, first be sure that your application's target framework version is 4.6.2. If it's not, you need to upgrade it to the 4.6.2 or later version.

     Next you need to adjust the session tables in your SQL Server. There are two ways to do this, and easiest way is installing the SqlSessionStateProviderAsync nuget package.

  

    When Installation is completed. The package will add the following configuration into the web.config file.

    In this point, you are ready configure the new async SqlSesionState provider. There are three new attributes you need to add in the SqlSessionStateProviderAsync element to enable In-Memory tables.

• UseInMemoryTable : 
• true to use In-Memory tables, false to use file-based tables
• MaxRetryNumber:
•  maximum number of retries. 0 to disable retries. Default value is 10
• RetryInterval : 
• Time in ms for the retry interval. Default value for In-Memory tables is 1, for Disk-Based tables is 1000

   If any of these settings are not set, your application will continue to use the disk based tables rather than In-Memory state tables. Also don't forget to change the connectionStringName. This should be the connectionString to the database that contains the state tables.

   Now, the last step is to change the database isolation level to snapshot. Here is an article why you need to change the isolation level if you like to learn why.

ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;