The behavior of ASP.NET will largely depend the Web application configuration. Session state is by default kept in-memory in the server serving the current request. That means that if you have a load-balanced cluster, the state will not be consistent among servers in the farm. This is bad for your users, for sure.
You have the option of storing the session state either in a separated centralized server (an ASP.NET State Server) or in SQL Server. This can be easily configured through the web.config file for the application:
<!-- sessionState Attributes: mode="[Off|InProc|StateServer|SQLServer]" stateConnectionString="tcpip=server:port" stateNetworkTimeout="timeout for network operations with State Server, in seconds" sqlConnectionString="valid System.Data.SqlClient.SqlConnection string, minus Initial Catalog" cookieless="[true|false]" timeout="timeout in minutes" lockAttributes="sqlConnectionString, stateConnectionString" -->
This section is taken from the machine.config file, which shows all available options. Each setting has its trade-offs. State Server is a separate machine, which must have the "ASP.NET State Service" service running. However, state is kept in-memory still, so if this state machine dies, so do all your users sessions, irrespective of the Web server that served the request. SQL Server, on the other hand, brings all the persistence and scalability advantages of a full-blown database, complete with yet another cluster support, replication, backups, etc. It's the most expensive of all approaches, but sessions will be preserved always.
If you want to know my opinion, I believe that if you're willing to pay the network cost of going to another machine for each hit (whenever you access session state, of course) to look for state, then, storing it in SQL Server makes the most sense. State Server is a hybrid that will incur the network cost anyway, without really offering much benefit as compared to the SQL Server approach. Remember, the database is highly optimized; stored procedures to manage state are precompiled and cached; etc. So comparatively, the network cost is, in my opinion, much bigger than the local database access in this state machine.
Take a look at this link for more information about configuring this mode.
This was first published in July 2003