Solved

MS SQL Cluster TEMPDB Failover with IIS Server Session Information

Posted on 2004-09-30
10
548 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
 
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Give your grad a cloud of their own!

With up to 8TB of storage, give your favorite graduate their own personal cloud to centralize all their photos, videos and music in one safe place. They can save, sync and share all their stuff, and automatic photo backup helps free up space on their smartphone and tablet.

Question has a verified solution.

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

A Bare Metal Image backup allows for the restore of an entire system to a similar or dissimilar hardware. They are highly useful for migrations and disaster recovery. Bare Metal Image backups support Full and Incremental backups. Differential backup…
VM backup deduplication is a method of reducing the amount of storage space needed to save VM backups. In most organizations, VMs contain many duplicate copies of data, such as VMs deployed from the same template, VMs with the same OS, or VMs that h…
This tutorial will walk an individual through the process of configuring basic necessities in order to use the 2010 version of Data Protection Manager. These include storage, agents, and protection jobs. Launch Data Protection Manager from the deskt…
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…

912 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now