Solved

MS SQL Cluster TEMPDB Failover with IIS Server Session Information

Posted on 2004-09-30
10
552 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
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
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.
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

822 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