Solved

MS SQL Cluster TEMPDB Failover with IIS Server Session Information

Posted on 2004-09-30
10
559 Views
Last Modified: 2013-11-15
Hi All:

We have a web based applicatoin that uses SQL based session information storage in the TEMPDB.  This is a .NET applicaiton.  After initial testing, we found out that the TEMPDB is "recreated" after a failover by default and the session informattion is no longer available becuse the TEMPDB has been recerated.

What is the accepted way to fix this?  Can the tempdb be set not to be recreated on SQL Engine startup?
IIS (.net) creates stored procedures that use the TEMPDB by default to write / read session information, can this be reconfigured to use a different database of out choosing?

The main point is that we would like to keep a session alive during / after a cluster failover event.

Any help would be appreciated.

Thanks
0
Comment
Question by:ort11
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 12189708
Yes, change the location of your system state database:

http://codeproject.com/aspnet/ASPNETSessionInternals.asp
0
 
LVL 7

Expert Comment

by:FDzjuba
ID: 12189792
can you just create normal database and and dedicate it for sessions instead of using TEMPDB. tempdb is system database and holds temp data while processing data, you shouldn't use for storing any kind of data in a long one.
0
 
LVL 1

Author Comment

by:ort11
ID: 12190064
Thanks for the pointer.  I am positng this for the web programmer here (I am working with the cluster).   I assume that the statement that sys the SQL server connection can be specified in the conneciton string, this means that we could specify a database other than TEMPDB?  There are automatically generated stored procedures for reading and writing session information, so I would assume that these would generate different code to point to a different database?

Thanks
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 1

Author Comment

by:ort11
ID: 12190254
Ok, I have a bit more information...

The StoredProcedures are created with a script from the framework.  This script uses the TEMPDB as a default database to store session information.  It looks like we can simply change the script to generate stored procedures to use a different database?  
0
 
LVL 7

Accepted Solution

by:
FDzjuba earned 20 total points
ID: 12190824
tempdb is probably set to default, cos this table 100% would be on the server, in order not to force user to create new db and reconfigure the whole thing, its been set to TEMPDB. If you change it to the other database, i don't think you will have any problems.
0
 
LVL 34

Expert Comment

by:arbert
ID: 12191113
Did you look at the above link?  It tells you exactly how to change it in your CONFIG  "Session state is stored outside ASP.NET worker process in SQL Server database. Location of this database is represented by sqlConnectionString attribute."
0
 
LVL 1

Author Comment

by:ort11
ID: 12192161
Thanks for the replies.  Yes, I read the statement, but then what changes the SQL script to create the stored procedures to look at a database other than TEMPDB.  Does this happen automatic from the CONFIG, or will it have to be done by hand?

Thanks
0
 
LVL 34

Assisted Solution

by:arbert
arbert earned 230 total points
ID: 12192395
There is a script that ships with the .NET SDK (state.sql) that needs to be run--just like was done originally:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnaspnet/html/asp12282000.asp
0
 
LVL 1

Author Comment

by:ort11
ID: 12194114
Here are a couple of links that seem to answer the question...

http://support.microsoft.com/default.aspx?scid=kb;EN-US;311209

http://support.microsoft.com/default.aspx?kbid=317604&product=aspnet

It looks like the 1st one answers my question.  Can I award the points to myself?????

The bottom line is that we need to run the installpersistentsqlstate script instead of the installsqlstate script.  The first one will use the ASP datase and the 2nd one will use the tempdb database....

Thanks for all of the help

Moderator, what to do here, can I award some points to the first answer?
0
 
LVL 34

Expert Comment

by:arbert
ID: 12194256
Request a refund of the points--glad you figured out how to search the knowledge base.  Database state isn't something to mess around with if you don't know what you're doing--performance can be a real problem....

Good luck
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question