Using SQL Server in

From the previous "classic" ASP to the current asp.net 4.5 Web Forms, many developers rely on ASP.net session state as an important means of temporarily saving data for each user. It is characterized by allowing the developer to store and read the user's data while the user accesses the Web application. Session data is automatically persisted and restored from storage and automatically expired.

Problem

Alternative scenarios that use session state these are beyond the scope of this article. There are also traps for applications that rely on session state, the most common being access to each user, the session base data for each request. This unique access is a way to maintain the consistency of the session state and is implemented through design. If you are interested in the design of such brutal details, they are explained here in the chapter titled "Lock Session State data". Session state is common in asp.net Web Forms applications, and ASP.net MVC uses tempdata (post data to get as an example) to a lesser extent.

Web applications use session state primarily to coordinate work between each other. In contrast, the heavy Web applications with more client script often have higher concurrent requests, in which case the session state access resource requires locking and unlocking the session, thus becoming a bottleneck for Web applications. Unrestricted-type Web applications will become another bottleneck because of the need for sufficient storage space to maintain the state of their sessions. There are three ways to optimize session state access so that some requests do not need to session or use read-only, but if the application scale continues to increase after loading, eventually there will still be bottlenecks.

The current situation

Based on these considerations the current ASP.net session state is still used very widely. In many areas I keep seeing many consumers using session state in Web applications with a large number of extensions. Internal use of ASP.net forms is more common for a large number of enterprise-level users. For these consumers, it is critical to choose the session state storage provider. These providers must serialize the contents of the session dictionary to a durable device and deserialize the data extracted from it (typically using a BLOB application). There are a number of vendors to choose from, including those provided by Microsoft and Third-party developers. Microsoft currently provides the following session storage tool, assuming ASP.NET applications are deployed within the enterprise:

Session Provider	Can be highly Available?	Can be Geo redundant?	Can is 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 patterns and data as persistent in the In-memory table

If your application requires session state high availability while supporting deployment across Web farm, the options you can offer from Microsoft are limited to SQL Server or AppFabric Caching.sql server with an added advantage, It provides geographic redundancy (geo-redundancy) across the data center. While AppFabric is limited to a single data center. Both of these solutions work well in practical applications. However, traditional SQL Server implementations often encounter bottlenecks, The reason for this is that there is competition on a single disk table. Competition leads to congestion, deadlock, or other unfriendly changes. This affects the time it stores and restores sessions. In addition, during the delete operation, the previous session data is cleared by the extension of the lock and the continuation of the competition, which can also be problematic.

New options for SQL Server 2014

To address the performance problems of older versions of SQL Server installation packages, the SQL Server team recently released a new installation package "Microsoft asp.net session state provider for SQL Sever in-memory" As a nugget package. There is a proof of this incredible performance boost in this case study. It uses session state in the ASP.net application to process 250,000 requests per second! This new implementation uses SQL Server 2014 Memory tuning table features called "Hekaton". This requires a 2014 version of this product. How does this installation package improve on the old version of SQL Server session state installation package?

1. The persistence of session storage is through the memory tuning table rather than the disk table. For heavy access models, such as storage session state, the memory tuning table is full-business, sustainable, and ideal. Such tables use unlocked data structures and optimized, multiple versions of concurrency control.

2. To further improve performance, you can use local compiled stored procedures to recover and store session data. Essentially, this is a new type of stored procedure that is compiled into local machine code.

These two SQL 2014 product features address major performance and competition issues that exist in the legacy of disk-based SQL Server installation packages. Installing and configuring this program is fairly straightforward. The NuGet package management Console can be installed in the following ways:

Install-package Microsoft.Web.SessionState.SqlInMemory.

In your application, the NuGet package will add a reference to Microsoft.Web.SessionState.SqlInMemory , as well as an additional name Aspstateinmemory.sql script file to 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 most likely review or modify:

1. The name of this database is aspstateinmemoryby default.

2. The main filegroup path of the database.

3. The Memory_optimized_data file group path for the database.

4. The size information of the Bucket_count, based on the expected size of the items in the session.

5. Make a table in a session durable or not durable (involving the need for session high availability)

The fifth section above requires some analysis of an existing SQL Server session database, which may resemble a datalength () that calculates a BLOB column in a traditional asp.net SQL Server session mode () As simple as that. For InProc or StateServer, it is more difficult to determine the average size of a session item, but it can be achieved by capturing the memory dump of the w3wp.exe or stateserver process. The number and size of items in the session dictionary are checked. 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 SQL Server 2014 Memory tuning table based on memory session is marked as non-persistent . This means that the data changes in these tables are transitional in consistency. These changes are not recorded in the log, which means that if SQL Server reboots, The server reboots or any form of failback occurs (FCI or AlwaysOn), and all session data is lost. Setting this default value is due to performance. To make these memory tuning tables sustainable, you need to be in the aspstateinmemory.sql A three-point change in the script. There are some comments in the script that explain why you need to make these changes.

1. Modify the Sessionitems table in the following manner.

   1. Modify with (Memory_optimized=on, durability=schema_only)

   2. For with (Memory_optimized=on, Durability=schema_and_data)

   3. Uncomment the statement (note the last comma): Id bigint IDENTITY,

   4. Uncomment the statement (note the last comma, modify 1000000 * 2 to real value as needed, read T-SQL comments before this statement to select a starting value): CONSTRAINT [pk_sessionitems_id] PRIMARY KEY Nonclustered HASH (Id) with (Bucket_count = 2000000),

2. Modify Session Table

   1. Modify with (Memory_optimized=on, Durability=schema_only

   2. For with (Memory_optimized=on, Durability=schema_and_data)

With these modifications, we can make the database part of the SQL Server AlwaysOn availability Group. When a failover occurs, the session data is preserved. Because of the increased retry logic, when an automatic or man-made failure is restored, Expired connections in the connection pool do not cause an exception to be thrown to the end user.

Note that even if we set the table to be not sustainable, put the session database into the SQL Server AlwaysOn availability Group, but the data in the session table is not available for replication (only schema is available). For the customer load, this "schema only" replication model, By using a non-sustainable memory tuning table, it is sufficient to ensure performance improvement.

This simplest, highly available topology is the most appropriate for SQL Server In-memory, and is similar to the following:

1. SQL Server 2014 Node 1 in Subnet (datacenter) a

2. SQL Server 2014 Node 2 located in Subnet (datacenter) b

3. File share in Subnet (datacenter) C

This topology provides geographic redundancy, automatic failover, and a complete lost connection of 1/3 of the data center. The dynamic nature of the Windows Server R2 makes it possible to automatically maintain lost connections for 2 data centers. (The final man's scene [last Mans standing scenario]).

asp.net configuration file

In the ASP.net Web application's configuration file Web.config, configure a new provider and edit it as follows.

Type= "Microsoft.Web.SessionState.SqlInMemoryProvider"
connectionstring= "Data source=agaspnet; Initial catalog=aspstateinmemory;integrated security=true; "/>

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

A quick example

Using the ASP.net Web Form 4.5 application and writing a simple timestamp string in the session, SQL Server 2014 generates the following data:

Note In sqlnode1-2014, the location of the Aspstateinmemory database. Next, we manually perform the recovery of the failover Availability Group.

On SQLNODE2-2014, the session is now available and does not interfere with the asp.net application. Simply tap the F5 of the Web application to get data from the session without throwing an exception to the client.

What happens to an expired session?

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

Overview

In the new session state, there are a lot of interesting details. I encourage you to delve into the code for yourself. You will find it a wonderful learning journey, in the midst of the performance and limitations of the SQL Server 2014 memory-based OLTP "Hekaton" feature. A particular attribute is contained in the Code, To simulate data in memory that stores blob types. Memory optimizations are not supported for BLOB types. What is the difference between a serialized session dictionary and a possible large BLOB data type? The preprocessor used (SPROCs) splits the serialized session into a 7000-byte block of data. To enhance the storage of large session item data.

Savvy readers may have discovered that in my screenshot, there is no data row in the [Sessionitems] table, but there is one row in the [Sessions] table. If my session content exceeds 7000 bytes, you should see "overflow" in the [Sessionitems] table ( Spill over) line. In this respect, there are many other potential applications outside of the ASP.net session store, and I probably dig deeper into this in the next article.

Local compilation of stored procedures is also worth looking at. There are some tricks to handle the limitations of compiling stored procedures locally, such as the lack of support for case statements. This limitation is because as long as the preprocessor (SPROC) is compiled into native code, it is not allowed to branch!

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

1. Memory tuning table is supported by memory! Does your SQL Server have enough memory to contain all session data during peak load?

2. The default table is not durable. Please carefully consider your high availability requirements. Whether it is durable or unsustainable, performance will exceed the session and mode of the traditional SQL Server database you are using.

3. Read the comments in the SQL file and adjust bucket_count to a nonclustered hash index. The following is a code fragment of the Sessionitems table DDL statement.
   
   

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

English Original: asp.net session state using SQL Server in-memory

Translated from: Http://www.oschina.net/translate/asp-net-session-state-using-sql-sever-in-memory