Solved

MS SQL Cluster TEMPDB Failover with IIS Server Session Information

Posted on 2004-09-30
10
553 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
OSQL to execute sql command 26 26
SQL Dump exec output to table 3 23
Transact SQL - Frequency of Length of Distinct Values 3 26
Evaluate Twice? 2 11
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Are you looking to recover an email message or a contact you just deleted mistakenly? Or you are searching for a contact that you erased from your MS Outlook ‘Contacts’ folder and now realized that it was important.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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…

820 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