We deployed a new build on Framework v 4.7.2. We also deployed the async Asp.Net session state module and in memory OLTP provider as documented here
Our testing environment was fine, and the canary server tested fine. We then rolled the build out to the other servers in the farm, and suddenly starting seeing lots of these exceptions:
System.Data.SqlClient.SqlException (0x80131904): Memory optimized tables and natively compiled modules cannot be accessed or created when the session TRANSACTION ISOLATION LEVEL is set to SNAPSHOT
Our provider configuration is as follows:
<providers>
<add name="SqlSessionStateProviderAsync" connectionStringName="Session.ConnectionString"
type="Microsoft.AspNet.SessionState.SqlSessionStateProviderAsync,
Microsoft.AspNet.SessionState.SqlSessionStateProviderAsync,
Version=1.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
UseInMemoryTable="true" MaxRetryNumber="5" RetryInterval="100" />
</providers>
EDIT:
Removed reference to AllowConcurrentRequestsPerSession which is not configured here, but as an app setting
SNAPSHOTanywhere)? When connections are recycled in the connection pool, the transaction isolation level is not reset, so if even one spot in the code anywhere changes it from the default, it should be explicitly set everywhere (or, alternatively, code that explicitly changes the isolation level should reset things before disposing the connection, or, alternatively, stored procedures should be used, as these do so implicitly).READ_COMMITTED_SNAPSHOTandMEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT. You may want to check if these are the same across environments. However, I don't think an incorrect setting of either one will produce this particular error (they will produce others).