ASP.NET Session State with SQL Server In-Memory OLTP

  • [アーティクル]

ASP.NET session state enables you to store and retrieve values for a user as the user navigates the different ASP.NET pages that make up a Web application. Currently, ASP.NET ships with three session state providers that provide the interface between Microsoft ASP.NET's session state module and session state data sources:

  • InProcSessionStateStore, which stores session state in memory in the ASP.NET worker process
  • OutOfProcSessionStateStore, which stores session state in memory in an external state server process
  • SqlSessionStateStore, which stores session state in Microsoft SQL Server database

This blog post focuses on the SqlSessionStateStore provider and describes how you can configure it to use SQL Server In-Memory OLTP as the storage option for session data. You can either use the latest ASP.NET async version of the SQL Session State provider (which is the recommended approach), or configure an earlier version of the provider to work with In-Memory OLTP by downloading and running the In-Memory OLTP SQL scripts from our sql server samples github repo.

Option1: Use the latest ASP.NET async SQL Session State provider

The Microsoft ASP.NET team has released an async version of the session state provider that uses SQL Server as the data store and leverages async database operations to provide better scability. This version of the provider also includes built-in support for retry logic and works with both In-Memory and disk-bases tables.

Please follow the steps below to install the provider and configure it to use SQL Server In-Memory OLTP:

  • Step 1: Install the latest Microsoft ASP.NET Async SqlSessionState Provider from Nuget. Note that the target framework of your web project need to be 4.6.2 (or above).

  • Step 2: Open the project’s Web.config file and add the following attributes in the SqlSessionStateProviderAsync element to enable In-Memory OLTP and to adjust the retry times and retry interval (in ms).
    • UseInMemoryTable: Set the value to true for In-Memory OLTP or false for disk-based tables
    • MaxRetryNumber: The maximum number of retries. Set to 0 if you want to disable retries.
    • RetryInterval: Time in ms for the retry interval.In addition to these three attributes, a connection string section needs to be added in the Web.config, where the name of the connection string should be the same as the value of connectionStringName attribute of the SqlSessionStateProviderAsync provider. Below is a sample ASP.NET Web.config Session State section that uses SQL Server In-Memory OLTP with MaxRetryNumber=”5” and RetryInterval=”100ms”

    Note: If the UseInMemoryTable, RetryInterval, and MaxRetryNumber attributes are not set, the provider will use regular disk-based SQL tables with the following default values for the RetryInterval and MaxRetryNumber:

    SQL Server option RetryInterval (ms) MaxRetryNumber
    In-Memory OLTP 1 10
    Disk based tables 1000 10

  • Step 3: Set the database isolation level to snapshot by executing the following T-SQL command against the SQL database that you are using to store the session state. The Transactions with Memory-Optimized Tables article explains in detail why this is needed.

    ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

Option2: Use an earlier version of the ASP.NET Session State provider

In the case where you are not ready to upgrade to the latest async version of the SqlSessionStateStore provider, you can follow the steps below to configure an earlier version of the ASP.NET Session State provider to use In-Memory OLTP (with or without retry logic).

  • Step 1: Download and run (on the target SQL Server to be used to store session state data) one of the following SQL Server In-Memory OLTP scripts: aspstate_sql2016 (no retry logic) or aspstate_sql2016 (with retry logic) . These scripts are based on work from early adopters that modified their SQL Server objects to take advantage of In-Memory OLTP for ASP.NET session state, with great success. To learn more, read the bwin.party case study Gaming site can scale to 250,000 requests per second and improve player experience.

    Based on your workload characteristics and the way your application handles session state you should decide:

    • If retry logic is needed or not: Choose the aspstate_sql2016 (with retry logic) if you need retry logic or the aspstate_sql2016 (no retry logic) if not. These scripts are required as the earlier versions of the ASP.NET Session State provider do not include retry logic or built-in support for In-Memory OLTP. You can read the Transactions with Memory-Optimized Tables article that explains the logic used to detect conflict and implement retry logic in the above T-SQL scripts.
    • If durability of both schema and data is required: Currently, the two memory-optimized tables: dbo.ASPStateTempApplications and dbo.ASPStateTempSessions in both scripts are created with DURABILITY = SCHEMA_ONLY meaning that if SQL Server restarts, the table schema persists, but data in the table is lost. If durability of both schema and data is required, the script needs to be altered and the two tables above need to be created with: DURABILITY=SCHEMA_AND_DATA. The Defining Durability for Memory-Optimized Objects article explains the two durability options for memory-optimized tables in detail.

  • Step 2: Set the Target framework of your web project to .net 4.5 (or above).

  • Step 3: Configure the web app to use the sql session state provider by modifying the Web.config as follows:
    • Set the mode attribute of the element to SQLServer to indicate that session state is stored in SQL Server.
    • Set the sqlConnectionString attribute to specify the connection string for SQL Server.

Further Reading

• Microsoft ASP.NET Universal Providers
• In-Memory OLTP (In-Memory Optimization)
• Session State Provider
• Implementing a Session-State Store Provider

Comments

  • Anonymous
    December 01, 2017
    The comment has been removed
    • Anonymous
      December 01, 2017
      It works fine if I remove these 3 fields but then it is not storing it In-Memory which will be much slower. UseInMemoryTable="true" MaxRetryNumber="5" RetryInterval="100"
      • Anonymous
        December 01, 2017
        Thanks Kevin for trying out the new Session State provider with In-Memory OLTP! Could you please set the database option MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT to ON by executing the following T-SQL command against the SQL database that you are using, and see if that resolves the issue.ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;This article: https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/transactions-with-memory-optimized-tables explains in detail why this is needed. We'll edit this blog post to include this.Thanks again,Perry
        • Anonymous
          December 02, 2017
          Perry, you are awesome! That worked! Thanks! Now I will load test it to compare with the sync version.
          • Anonymous
            December 04, 2017
            Great - Thanks Kevin! Please let me know how the test goes :)
  • Anonymous
    December 11, 2017
    The comment has been removed
    • Anonymous
      December 11, 2017
      Hi @Kevin - I do not think that MARS is required. I would remove the “MultipleActiveResultSets=true;” from the connection string and see if the error goes away. BTW, if you have multiple transactions updating the same session state at the same time, you'll need to use retries (by setting the MaxRetryNumber to a value greater than 0 and the RetryInterval according to your logic as there is no locking and latching in In-Memory OLTP) if not already doing so. You can read more regarding transaction with Memory-Optimized tables here: https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/transactions-with-memory-optimized-tables Please let me know if that mitigates the issue.Thanks, Perry
      • Anonymous
        December 11, 2017
        The comment has been removed
        • Anonymous
          December 12, 2017
          The comment has been removed
      • Anonymous
        December 15, 2017
        The comment has been removed
        • Anonymous
          December 18, 2017
          Hi Kevin -What is the exact error code that you are getting (E.g 41302, 41301)? I would experiment by increasing the number of retries and/or decreasing the retry interval to see if that makes a difference. The retry interval really depends on the typical duration of the conficting transactions. What are the current values that you are using for these settings?Take a quick look at the guidelines for Retry Logic for Transactions on Memory-Optimized Tables:https://msdn.microsoft.com/en-us/library/dn169141.aspx
  • Anonymous
    August 24, 2018
    Hi,I am trying to implement ASP.NET async SQL Session State provider in our application.We have created a sample application to implement this feature. But we are facing some issues with 'Session.Abandon()'.In the sample application attached, when we click on 'Login' button, the page will be redirected to 'Default.aspx' page. On 'Default.aspx' page, when we click on 'Session Abandon' button page will be redirected to 'Login.aspx' page.While clicking on 'Session Abandon' button, 'Session.Abandon()' is fired.When 'sessionState' mode is set to 'Custom',On 'Session_Start(object sender, EventArgs e)' event in 'Global.asax' page, the "HttpContext.Current" becomes NULL.In this case, if we are using 'Session.Clear()' instead of 'Session.Abandon()' its working fine.Could you please provide the reason for why "HttpContext.Current" becomes NULL when 'Session.Abandon()' is used. Also please provide a solution to overcome this issue?When 'sessionState' mode is set to 'SQLServer',"HttpContext.Current" is not null.Please find the sample application from the following linkhttps://drive.google.com/open?id=1oThUUiigcGR9NK6Y65JFnoociiVVjzxTCan anyone suggest a solution for this?Thanks in advance.