?
Solved

MS SQL Cluster TEMPDB Failover with IIS Server Session Information

Posted on 2004-09-30
10
Medium Priority
?
562 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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
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 60 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 690 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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

This article shows how to use a free utility called 'Parkdale' to easily test the performance and benchmark any Hard Drive(s) installed in your computer. We also look at RAM Disks and their speed comparisons.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
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…

741 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