Setting up ASP.NET session state with SQL In memory OLTP and AlwaysOn database

  • Article

Hello all,

 

Recently I had worked on a requirement of achieving high availability for asp.net sessions. This blog post focuses on using SQL Server as the session state mode and use In-memory OLTP and AlwaysOn technology as the storage option for session data and achieve high availability. In memory OLTP ensures increased performance and scalability whereas AlwaysOn ensures that the database hosting the sessions is highly available.

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. ASP.NET session modes available are:

  • InProc mode, which stores session state in memory on the Web server. This is the default.
  • StateServer mode, which stores session state in a separate process called the ASP.NET state service. This ensures that session state is preserved if the Web application is restarted and makes session state available to multiple Web servers in a Web farm.
  • SQLServer mode stores session state in a SQL Server database. This ensures that session state is preserved if the Web application is restarted and also makes session state available to multiple Web servers in a Web farm.

In a web farm scenario, the application which is running behind the load balancer, should maintain the same session state, and hence they need to point to same state server. If they point to different state server, then the state will be different for both application requests which results in erratic behavior. Now, because all the web servers point to the same state server, the solution is not highly available. If the state server hosting the asp session state crashes, then it’s very difficult to get the sessions back.

Lets dive into the steps used and the errors reported  during configuration of SQL Server as session mode and use In memory/AlwaysOn to achieve highly available solution to host sessions.

  1. Use the script in the github solution repository to setup the SQL server database. Execution of the script creates the below tables and stored procedures needed and it also enables In memory OLTP on dbo.ASPStaTetempSessions table which is used to store the sessions. Please note that the script uses DURABILITY = SCHEMA_ONLY on In memory OLTP table, hence when SQL Server is restarted/AlwaysOn  database is failed over, only the table schema persists, but data in the table is lost. Hence to preserve the sessions, use DURABILITY=SCHEMA_AND_DATA. 

https://github.com/Microsoft/sql-server-samples/blob/master/samples/applications/aspnet-session-state/aspstate\_sql2016\_no\_retry.sql

Tables created:  [dbo].[ASPStateTempSessions] and [dbo].[ASPStateTempApplications]

To incorporate retry on the transactions, use the below script:

https://github.com/Microsoft/sql-server-samples/blob/master/samples/applications/aspnet-session-state/aspstate_sql2016_with_retry.sql

 

 

 

Next step is to configure AlwaysOn on ASPState database to achieve high availability of the database.

 

 

For demonstration purpose, lets use buggybits site.

 

To change the session state from In process (Default session mode) to SQL Server, connect to Internet Information Services manager (inetmgr) and locate the site and session state module.

Select SQL Server state mode and click on create:

 

Provide the AlwaysOn listener name in the Server list and specify ASPSTATE database. Also, specify the credentials used to connect to the SQL Server.

 

 

Make sure that "Enable Custom database" setting is enabled. otheriwise, below error is reported:

 Server Error in '/' Application.
Configuration Error
Description:  An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately. 
Parser Error Message:  The sqlConnectionString attribute or the connection string it refers to cannot contain the connection options 'Database', 'Initial Catalog' or 'AttachDbFileName'. In order to allow this, allowCustomSqlDatabase attribute must be set to true and the application needs to be granted unrestricted SqlClientPermission. Please check with your administrator if the application does not have this permission.

 

Now access the application. When SQL Server is used as session state mode, dbo.ASPStatetempApplications table creates an entry, with the application ID details.

 

Review, the ASPStateTempsessions table, and the table will reflect all the sessions currently active.

Let us simulate a scenario of Availability Group fail over.

 

When the database is in middle of the failover, because the session states are not retrievable, below error is reported:

 
Cannot open database "ASPSTATE" requested by the login. The login failed.
Unable to access availability database 'ASPSTATE' because the database replica is not in the PRIMARY or SECONDARY role. Connections to an availability database is permitted only when the database replica is in the PRIMARY or SECONDARY role. Try the operation again later.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot open database "ASPSTATE" requested by the login. The login failed.
Unable to access availability database 'ASPSTATE' because the database replica is not in the PRIMARY or SECONDARY role. Connections to an availability database is permitted only when the database replica is in the PRIMARY or SECONDARY role. Try the operation again later.


 

Once the Availability Group  fail over is done, you would be able to access the application again.

 

Hope the steps mentioned above will help you in setting up SQL Server as session state mode to host ASP.NET sessions.

 

 

Please share your feedback, questions and/or suggestions.

Thanks,
Don Castelino | Premier Field Engineer | Microsoft

Disclaimer: All posts are provided AS IS with no warranties and confer no rights. Additionally, views expressed here are my own and not those of my employer, Microsoft.