title: How to set up SQL Server 2017 for async in-memory session from scratch
body: <p>I am migrating an application that currently uses in-memory session storage, and as part of that migration, its backing database is being upgraded to SQL Server 2017, so I am keen to replace the in-process session state with Microsoft's new SQL Server async in-memory table support (<a href="https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/11/28/asp-net-session-state-with-sql-server-in-memory-oltp/" rel="nofollow noreferrer">asp.net session state with sql-server in memory oltp</a>). However, since I am effectively starting with a new database, there seem to be one or two steps missing in the documentation, and all other articles I've seen about it are basically copy/pastes of the Microsoft article.</p>
  
  <p>So my question is this : starting from an empty database server, with no user databases, how do I get SQL Server 2017 set up properly to use in-memory tables for session state? I've tried running the following script to initialise the database, then run the app without any further changes to the DB:</p>
  
  <pre><code>USE [master]
  GO
  
  DECLARE @SQLDataFolder nvarchar(max) = cast(SERVERPROPERTY('InstanceDefaultDataPath') as nvarchar(max))
  DECLARE @SQLLogFolder nvarchar(max) = cast(SERVERPROPERTY('InstanceDefaultLogPath') as nvarchar(max))
  DECLARE @SQL nvarchar(max) = N'';
  
  SET @SQL = N'
  CREATE DATABASE [MySessionDb]
   CONTAINMENT = NONE
   ON  PRIMARY 
      (NAME = N''MySessionDb'', FILENAME = N''' + @SQLDataFolder + 'MySessionDb.mdf'' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ), 
   FILEGROUP [MySessionDb_mod] CONTAINS MEMORY_OPTIMIZED_DATA  DEFAULT
      (NAME = N''MySessionDb_mod'', FILENAME = N''' + @SQLLogFolder + 'MySessionDb_mod'' , MAXSIZE = UNLIMITED)
   LOG ON 
      (NAME = N''MySessionDb_log'', FILENAME = N''' + @SQLLogFolder + 'MySessionDb_log.ldf'' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB );
  
  ALTER DATABASE [MySessionDb] SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON;'
  
  EXECUTE (@SQL)
  GO
  </code></pre>
  
  <p>When I run the application, it seems to start up OK, and I can see the table <code>dbo.ASPStateTempSessions</code> has been created. However, navigating beyond the first page, I very quickly find myself in a sea of errors:</p>
  
  <pre><code>System.Data.SqlClient.SqlException (0x80131904): The current transaction attempted to update a record that has been updated since this transaction started. The transaction was aborted.
  Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
  The statement has been terminated.
     at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
     at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
     at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean&amp; dataReady)
     at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
     at System.Data.SqlClient.SqlDataReader.get_MetaData()
     at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
     at System.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader(Boolean isInternal, Boolean forDescribeParameterEncryption)
     at System.Data.SqlClient.SqlCommand.InternalEndExecuteReader(IAsyncResult asyncResult, String endMethod, Boolean isInternal)
     at System.Data.SqlClient.SqlCommand.EndExecuteReaderInternal(IAsyncResult asyncResult)
     at System.Data.SqlClient.SqlCommand.EndExecuteReaderAsync(IAsyncResult asyncResult)
     at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
  --- End of stack trace from previous location where exception was thrown ---
     at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
     at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
     at Microsoft.AspNet.SessionState.SqlSessionStateRepositoryUtil.&lt;SqlExecuteReaderWithRetryAsync&gt;d__12.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
     at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
     at Microsoft.AspNet.SessionState.SqlInMemoryTableSessionStateRepository.&lt;GetSessionStateItemAsync&gt;d__19.MoveNext()
  </code></pre>
  
  <p>Any idea what I am doing wrong?</p>
  
  <p><strong>Update</strong></p>
  
  <p>My configuration in <code>web.config\system.web</code> looks like this:</p>
  
  <pre><code>&lt;sessionState cookieless="false" regenerateExpiredSessionId="true" mode="Custom" customProvider="SqlSessionStateProviderAsync"&gt;
      &lt;providers&gt;
          &lt;add name="SqlSessionStateProviderAsync" 
               connectionStringName="MySessionStateConnectionString"
               type="Microsoft.AspNet.SessionState.SqlSessionStateProviderAsync, Microsoft.AspNet.SessionState.SqlSessionStateProviderAsync, Version=1.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
               UseInMemoryTable="true"
               MaxRetryNumber="5"
               RetryInterval="100" /&gt;
      &lt;/providers&gt;
  &lt;/sessionState&gt;
  </code></pre>
  
  <p>There is also the default configuration for adding the session state HTTP module (used for async session support, IIRC).</p>