Using SQL Server for session state

原创 2004年09月19日 10:01:00

Before using any sample code published here please read the disclaimer.

What is session state?

A session is defined as the period of time that a unique user interacts with a Web application. Session state is a collection of objects, tied to a session are stored on a server.

Why use SQL?

Once you start running multiple web servers for the same web site, the default session state, InProc, is no longer useful, as you cannot guarantee that each page request goes to the same server. It becomes necessary to have a central state store that every web server accesses.

SQL Server offers you centralized storage of a session state in a Web farm. It also offers the transactional capabilities that provide reliability to most relational database systems. You can use SQL Server to save a session. This process is not as efficient as InProc and StateServer mode, because you must store the information in a different process or on a different server. However, this option may be more efficient than using the aspnet_state service, depending on the actual workload and the database configuration. Once you start saving session state to a SQL database it will also persist through web server restarts and reboots.

For reliability you should consider storing session state for a web farm on a SQL cluster.

Creating the database

  • Start Query Analyzer, connected to the server you want to use for state storage.
  • Open and execute InstallSqlState.sql script file. By default, InstallSqlState.sql is located in one of the following folders; system drive/WINNT/Microsoft.NET/Framework/version/
    system drive/Windows/Microsoft.NET/Framework/version/
  • If you are using trusted connections to connect to your server, you must change ownership of the state database to sa after creation. In Query Analyzer run use ASPState
    exec sp_changedbowner 'sa','true'
  • If you are using SQL authentication create a user and password for session state to use. At a minimum this user should havepermissions to execute the stored procedures in the ASPState database. You will have to manually set these, or if you're feeling dangerous, give the state user dbo rights to ASPState.

Configuring ASP.Net

To switch ASP.Net to use SQL you must update the <sessionState> element of your application's Web.config file as follows;

  • Set the mode attribute of the <sessionState> element to SQLServer.
  • Set the sqlConnectionString attribute to specify the connection string to your SQL Server

For example

<sessionState mode="SQLServer" sqlConnectionString="data source=server;user id=uid;password=pwd" cookieless="false" timeout="20" />

If you specify integrated security/trusted connections in the database connection string (ie. "trusted_connection=true", or "integrated security=sspi"), you cannot use impersonation in, as your database connection will then run the context of the impersonated user, which will not have rights to the state database. You can, of course grant connections to that user context. KB 326606 has more details.

If you are configuring session state to be stored on a cluster you must manually override the .net machine keys on each server. KB 323262 has more details.

Configuring SQL SP3

So, you have created the database, you've created the user, given it rights to all the stored procedures in the ASPState database, you've up to date with patches, Windows, IIS SQL, and you connect to your web site...

SELECT permission denied on object 'ASPStateTempApplications', database 'tempdb', owner 'dbo'.
INSERT permission denied on object 'ASPStateTempApplications', database 'tempdb', owner 'dbo'.
SELECT permission denied on object 'ASPStateTempApplications', database 'tempdb', owner 'dbo'.

SP3 for SQL 2000 adds a new security feature, disabled by default, cross-database ownership chaining (see KB 810474 for details). When this feature is disabled, ASP.Net session state stops working.

To reconfigure SQL 2000 SP3 for session state you must run

use master go EXEC sp_configure 'Cross DB Ownership Chaining', '0'; RECONFIGURE go

Now restart your SQL server, then run

use master go EXEC sp_dboption 'ASPState' , 'db chaining', 'true' go

Timeouts under heavy load

If your web servers are under heavy load it may be useful to increase the time out for session state access. You can add the stateNetworkTimeout attribute to the sessionState settings in web.config and machine.config

<sessionState stateNetworkTimeout="15" />

If a Web server or a state server is under stress and cannot complete session accesses on time, event ID 1072 and event ID 1076 may be logged in the event log.

Caveats when using SQL session state

Using SQL is slower than using InProc session state. When storing basic data types (string, int, etc), ASP.Net can take 10%-25% longer to store their values. Complex types take even longer. Of course because you are connecting to a separate server it does use bandwidth on your network.

When using SQL Server mode, objects stored in session state are serialised and deserialised when a request is processed. So any objects which do not support serialisation cannot be stored in session state. In ASP.Net v1.0 a bug means that attempting to store a non-serialisable object does not throw an error, and so will probably pass unnoticed.

For session state to be maintained across different web servers in a web farm (the main reason for moving session state to SQL), the Application Path of the website (For example /LM/W3SVC/2) in the IIS Metabase should be identical in for all the web servers in the web farm. Microsoft's KB 325056 details this problem.

If you wish to persist session state through SQL server reboots you must follow the instructions in KB 311209 to move the session state tables from tempdb to the ASPState database.

Other resources

Peter Bromberg's Session State FAQ
KB 317604 HOW TO: Configure SQL Server to Store ASP.NET Session State

Note that a few of the Microsoft KB articles show sample code which uses SA as the username and a blank password. As the SQL Slammer worm showed, this is not a good idea!

Unable to use SQL Server because ASP.NET version 2.0 Session State is not installed on the SQL serve

  • broze
  • broze
  • 2014年01月09日 16:48
  • 1410


这个其实网上说的有不少,但我觉得说得完全明白的还是没有,或者我没太怎么仔细看,不过我自己这么折腾一痛,基本算是搞定了。 这个基本上就分两种情况:一种就是在多台服务器上,在IIS中建立的网站的标识符一...
  • ahywg
  • ahywg
  • 2014年09月12日 17:32
  • 3590 SessionState的sqlserver模式的配置及使用

1.由于项目dll文件变动比较频繁,而保存登陆的状态又保存在Session中,所以导致用户经常无故掉线。(dll变动的时候导致Session丢失) 2.有一种方法可以长期保存session,那就是s...
  • aiouwen521
  • aiouwen521
  • 2014年01月03日 15:06
  • 6752


前言: 配置Session的mode为State_Server模式,不说明,请按照如下配置 mode="StateServer" stateConnectionString="tcpip=192.16...
  • iml6yu
  • iml6yu
  • 2017年03月18日 12:43
  • 470

解决【Unable to make the session state request to the session state server】

错误描述: Unable to make the session state request to the session state server. Please ensure that t...
  • ldl22847
  • ldl22847
  • 2012年11月02日 14:39
  • 17207

如何使用SQLServer Session

  • fwj380891124
  • fwj380891124
  • 2014年09月29日 14:50
  • 2711

找到 sql server 里面产生 block 的 session

;with ps as ( select blocked as RootID, spid asChildID, 1 as LevelBlock frommaster.sys.sysprocesse...
  • wujiandao
  • wujiandao
  • 2015年04月01日 09:42
  • 847


1、更改web.config 中的 tcpip=localhost:42424" cookieless="false"/>  注:tcpip=localhost:42424  tcpip的值可以设置...
  • xiaxiaoying2012
  • xiaxiaoying2012
  • 2016年11月29日 21:04
  • 1140

SQLServer保存ASP.NET Session状态

SQLServer保存ASP.NET Session状态   一、在SQL Server中安装必要数据库表、存储过程等      若要使用 SQL Server,首先在将存储会话状态的 SQL Ser...
  • ikmb
  • ikmb
  • 2008年03月02日 11:52
  • 1464

ASP.NET Session State Partitioning using State Server Load Balancing

  • canduecho
  • canduecho
  • 2009年08月12日 09:15
  • 576
您举报文章:Using SQL Server for session state