SQL Server Session State on a Web Farm: ASP Alliance

Print Add To Favorites Email To Friend Rate This Article
SQL Server Session State on a Web Farm

page 1 of 1

Published: 03 Mar 2004

Unedited - Community Contributed

Abstract
Storing session data in SQL server for a web farm is nice and easy. There are also a few things you need to watch for. If you don’t hit a few specific configuration settings or requirements you will lose session state when your connection switches servers in the farm.

by Eric Renken
Feedback

Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 9470/ 12

SQL Session State

I found out the hard way that storing session in a SQL server takes a little more work then most of the books that I have read talk about. Most articles don’t talk about what settings you should have when you are in a web farm. This article will hopefully point you in the right directions and keep you from the headaches that I had.

One of the first things to check is the easiest to fix. You will need to make sure you have a constant machine key between servers. The machine key is used for encryption and decryption of cookie data. Unless you set the machine key to a specific code on each machine they will use their own unique key, so when your connection switches from one server to the other server it can’t decrypt the data and you lose your session.

Microsoft has a couple articles on their site on how to create the machine keys and where to place them. You can place this machine key in your machine.config or web.config files.

312906 HOW TO: Create Keys by Using Visual C# .NET for Use in Forms Authentication
313091 HOW TO: Create Keys by Using Visual Basic .NET for Use in Forms Authentication

The other fix is a little more complicated to change and is less likely to be a problem for everyone. This problem happens when you add more websites to one server then to the other server then you try to load balance two websites. What happens is that the application path for the websites is different on each server. For example on one server you have a web with an application path of \\LM\W3SVC\2 and on the other server it has an application path of \\LM\W3SVC\4. The problem is IIS uses the application path to store the data, and if they don’t match you will lose your session. You will have to synchronize the application path for the websites on all your web servers in the web farm.

Microsoft has an article on this problem and how to fix it. If you think this is your problem please check out this article.

325056 PRB: Session State Is Lost in Web Farm If You Use SqlServer or StateServer Session Mode

When I was going through this we had both of these problems, and after running though these steps we are now using SQL session state on our website, on a three server web farm.

User Comments

Title: Sr. Web Developer
Name: Karl Sieburg
Date: 2008-08-06 3:12:40 AM
Comment:
Thanks a ton! I was up til 3 trying to get our web farm configured. I found a lot of articles that mentioned the IIS site id needing to match, but yours was the first one I came across that made me realize the machine keys have to match too! It's 3 am and I'm finally going to bed! Thanks!

Title: Balajee
Name: Eric Renken
Date: 2005-07-12 10:02:16 AM
Comment:
I would say the problem is that you are not closing your connections. You need to do this after you are done with your connections. So make sure to call connection.Close() or connection.Dispose() when you are done with your connections.

Title: Try to Solve this
Name: Balajee
Date: 2005-07-12 7:34:58 AM
Comment:
Hi,
i just try to open a connection with sql server using ASP.net the error is Connection Timeout and the line showing error is connection.open() why is it so?

Title: Mahmound
Name: Eric Renken
Date: 2004-10-13 1:04:02 PM
Comment:
You didn't leave your email address, so I can't send you any suggestions; however I would suggest signing up for and posting your question to the email lists at: http://aspadvice.com/. Your problem doesn't sound like a SQL one, it sounds like you typeing in the correct address for your web site. Check this site out for more information http://www.codetoad.com/directory_type367.asp

Title: Please Help me!
Name: Mahmoud Tahoon
Date: 2004-10-13 12:47:23 PM
Comment:
Hello Mr.Eric Renken
sorry but when I program any SQL server programs with asp.net i had the same error every time and cannot resolve it , the error is : web server doesn't exist or Access denied please email me about how to solve that error
oh, i have a SQL-Server 2000 on my computer and i don't know how to use it ,
thank you

Product Spotlight