Use SQL Server In-Memory to store session Status of ASP. NET

Use SQL Server In-Memory to store session Status of ASP. NET

From the previous "classic" ASP to the current ASP. NET 4.5 Web Forms, many developers rely on the ASP. NET session status as an important means to temporarily Save the data of each user. It allows developers to store and read user data when users access web applications. Session data is automatically stored and recovered from the storage, and automatically expired and deleted.

Problem

The content of Session State is beyond the scope of this article. Applications that depend on Session State also have traps. The most common practice is to access the basic Session data of each user and each request. This unique access is a way to maintain Session State consistency, and is implemented through design. If you are interested in the design of such brutal details, they will explain in the chapter titled "lock session status data. Session status is common in ASP. NET Web forms applications, while ASP. net mvc uses TempDataPOST data to GET to a small extent ).

Web applications use the Session status to coordinate their work. In contrast, heavyweight web applications with many client scripts usually have higher concurrent requests. In this case, you need to lock and unlock sessions when using Session state to access resources, this has become the bottleneck of Web applications. A Web application without restrictions will become another bottleneck because it requires sufficient storage space to maintain the state of their sessions. There are three ways to optimize the access to the Session state so that some requests do not need a session or use read-only access. However, if the application size continues to increase after loading, there will still be a bottleneck.

Current Status

Based on these considerations, the session Status of ASP. NET is still very common. In many fields, I have seen that many consumers use session states in a large number of extended Web applications. For a large number of enterprise users, the internal use of ASP. NET forms is more common. For these consumers, it is critical to choose a Session State storage provider. These providers must serialize the Session dictionary content and store it on a durable device and deserialize it to extract data normally using BLOB applications ). There are many providers to choose from, including tools provided by Microsoft and third-party developers. Microsoft currently provides the following Session storage tool, assuming that ASP. NET applications are deployed within the enterprise:

Session Provider	Can be Highly Available?	Can be Geo Redundant?	Can be used in Web Farms?	Performance?
In-Proc	No	No	No	Excellent
State Server	No	No	Yes	Good
SQL Server (Traditional)	Yes	Yes	Yes	Fair
AppFabric Caching	Yes	No	Yes	Good
SQL Server (In-Memory)	Yes *	Yes	Yes	Excellent

* You need to mark the mode and data as persistent in the in-memory table.

If your application requires high Session State availability and supports cross-web farm deployment, you can select from Microsoft, which is limited to SQL Server or AppFabric Caching. SQL Server has an increasing advantage in providing geographic redundancy across data centers (geo-redundancy ). appFabric is restricted by a single data center. in practice, both solutions work well. however, traditional SQL Server implementations often encounter bottlenecks because tables on a single disk are competing. competition leads to blocking, deadlocks, or other unfriendly changes. this affects the time for storing and recovering sessions. in addition, during the delete operation, the session data before the deletion is cleared due to the expansion of the lock and the continuation of competition, which also causes problems.

New SQL Server 2014 options

To solve the performance problem of the old version of SQL Server Installation Package, the SQL Server team recently released the new installation package "Microsoft ASP. NET Session State provider for SQL Sever In-Memory "as the NugGet package. in this case study, this installation package provides an incredible proof of performance improvement. it is stored in ASP. NET Applications Use Session State to process 250,000 requests per second! This new implementation uses the memory optimization table feature called "Hekaton" in SQL Server 2014. this requires version 2014 of this product. how can I upgrade the installation package on the SQL Server session Status in earlier versions?

These two SQL 2014 products solve major performance and competition problems. These problems exist in the old traditional SQL Server installation package based on disk implementation. installing and configuring this program is quite straightforward. on the NuGet console, you can install the package as follows:

Install-Package Microsoft. Web. SessionState. SqlInMemory.

In your application, the NuGet package will be addedMicrosoft. Web. SessionState. SqlInMemoryAnd addASPStateInMemory. SQLTo install the SQL Server 2014 Session State database. this file contains the required DDL to install the database. there are some items in the SQL script that you want to review or modify most likely:

The fifth part above requires some analysis on the existing SQL Server session database, which may be like computing traditional ASP. net SQL parser or StateServer process memory dump to check the number and size of items in the session dictionary. for InProc or StateServer, there is a performance count for the number of items in the session. the best advice is always to test and adjust.

Make memory-based sessions highly available

By default, the memory optimization table of SQL Server 2014 based on memory sessions is markedNon-persistent. This means that the data changes in these tables are transitional and consistent. these changes are not recorded in the log, which means that if the SQL Server is restarted, the Server is restarted, or any form of fault recovery occurs (FCI or AlwaysOn), all session data will be lost. this default value is set because of performance. to make these memory optimization tables sustainable, you needASPStateInMemory. SQLThere are some annotations in the script to explain why these changes need to be made.

Modify a session table

With these modifications, we can make the database part of the SQL Server AlwaysOn availability group. session data will be retained when the fault is restored. due to the added retry logic, when an automatic or manual fault is restored, the expired connection in the connection pool will not generate an exception and be thrown to the end user.

Please note that even if the table is not sustainable, the session database is put into the SQL Server AlwaysOn availability group, but the data in the session table is not available for replication (only schema is available. for customer load, this "schema only" replication model is sufficient to ensure performance improvement by using a non-sustainable memory optimization table.

This simple and highly available topology is the most suitable for SQL Server In-Memory. It is similar to the following:

This topology provides geographic redundancy, automatic fault recovery, and 1/3 of the data center's complete lost connections. the dynamic feature of Windows Server 2012 R2 makes it possible to automatically maintain lost connections between two data centers. (The final man scenario [last man standing scenario]).

ASP. NET configuration file

In the configuration file web. config of the ASP. NET web application, configure a new provider and edit it as follows.

<SessionState mode = "Custom" customProvider = "SqlInMemoryProvider">
<Providers>
<Add name = "SqlInMemoryProvider"
Type = "Microsoft. Web. SessionState. SqlInMemoryProvider"
ConnectionString = "Data Source = AGAspNet; Initial Catalog = ASPStateInMemory; Integrated Security = True;"/>
</Providers>
</SessionState>

In the code snippet above, 'agaspnet' is the name of the listener that is always available in SQL Server 2014.

A quick example

SQL Server 4.5 generates the following data using ASP. NET web page form 2014 and a simple string with a timestamp in the session:

Note the position of the AspStateInMemory database in the SQLNode1-2014. Next, we will perform the fault recovery availability group manually.

On the SQLNode2-2014, sessions are now available and do not interfere with ASP.. NET application. simply hit F5 of the web application to obtain data from the session without throwing an exception to the client.

 

What happens to an expired session?
 

In the old SQL Server session, an SQL Agent job is created to delete expired sessions. In the new version, a stored procedure that must be called by the job is provided.[Dbo]. [DeleteExpiredSessions]By default, the Session Timeout time is 20 minutes. Each session item is accessed and the timeout value is reset to keep the user session "alive ".

Overview

There are many interesting details in the new session status. I encourage you to study code for yourself. you will find it a wonderful learning journey, during which it is about the performance and limits of SQL Server 2014 memory-based OLTP "Hekaton" features. A special attribute is included in the Code to simulate storing BLOB data in the memory. memory optimization does not support BLOB type. what are the differences between the serialized session dictionary and the possible big BLOB data types? The pre-processing program (sprocs) is used to split serialized sessions into 7000 bytes of data blocks to enhance the storage of large session item data.

A savvy reader may have discovered that there are no data rows in the [SessionItems] Table on my screen, but there is a row of data in the [Sessions] table. if my session content exceeds 7000 bytes, you should see the "spill over" row in the [SessionItems] table. in this regard, in ASP. in addition to the NET session storage, there are many other potential applications, which I may find in the next article.

Locally compiled stored procedures are also worth looking. there are some skills to deal with the limitations of local compilation stored procedures, such as lack of support for CASE statements. this restriction is because branches are not allowed as long as the Preprocessing Program (sproc) is compiled into local code!

If you are considering using this new feature, consider the following key points and problems:

Good luck! Please share your experience with the new version in the comments!

ASP. NET Session State using SQL Server In-Memory

By http://www.oschina.net/translate/asp-net-session-state-using-sql-sever-in-memory