| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-10-28 : 08:26:23
|
| Bill writes "When setting up SQL2000 in a cluster environment, can one locate the tempdb on a non-share local drive. The logic beging that all temp work for a connection is destroyed when the connection is broken. When a failover occurs, all connection are broken. Hence I think this should be possible.The servers we will be clustering have 120 gig of local RAID5 drive space plus access to a fiber channel SAN. I thought we could utilize this local space.Bill Florac" |
|
|
royv
Constraint Violating Yak Guru
455 Posts |
Posted - 2002-10-28 : 09:57:33
|
| It seems possible, but I don't think you should do this. In the case of a failover, what happens when the secondary server takes over and can't find tempdb? I'm not saying this would be a problem, but it could, and therefore should be left on the shared disk array. I have to admit, a local RAID 5 solution of 120GB is alot, what is the local space necessary for?***************************************Death must absolutely come to enemies of the code! |
 |
|
|
bflorac
Starting Member
2 Posts |
Posted - 2002-10-30 : 01:39:31
|
| Each server has it's own 120G drive so it should be able to find the tempdb. However, if SQL used the tempdb for it's own operations, it might get confused.We are currently running on a new single server, non-clustered. So 120G fits us well. However Dell made a mistake and shipped us a second server too. The offered a great deal to keep it and we did. At the same time, we got final approval for our SAN and now we are putting both of them on the SAN in a cluster. |
|
|
|
solart
Posting Yak Master
148 Posts |
Posted - 2002-10-30 : 12:37:13
|
| On failover, the server picking up the work MUST be able to find the log file. It will go through a "recovery" process (meaning it will want to back out work from the database which has not been committed AND make sure work which has been committed is written to the database).solart |
|
|
|
solart
Posting Yak Master
148 Posts |
Posted - 2002-10-30 : 12:41:18
|
| Even though its TEMPDB, it seems to me the following would be true.Think about where you have ##tables.On failover, the server picking up the work MUST be able to find the log file. It will go through a "recovery" process (meaning it will want to back out work from the database which has not been committed AND make sure work which has been committed is written to the database).What say others.solart |
|
|
|
solart
Posting Yak Master
148 Posts |
Posted - 2002-10-30 : 12:45:45
|
| I think the normal mode of operation for SQL Server is when it starts up it recreates TEMPDB. IF this is true, then the log would not go through a recovery process.Therefore I am not sure how ##tables would be dealt with.solart |
|
|
|
royv
Constraint Violating Yak Guru
455 Posts |
Posted - 2002-10-30 : 12:47:11
|
| From my understanding of clustering solart, you are correct. Therefore all data files associated with SQL Server should be located on the shared disk array so that when failover occurs, the clustering will have no problem finding the necessary files. Therefore bflorac, the tempdb must be on the fiber channel SAN.***************************************Death must absolutely come to enemies of the code! |
|
|
|
solart
Posting Yak Master
148 Posts |
Posted - 2002-10-30 : 12:53:12
|
| I am really showing my ignorance, please excuse me.Maybe it's possible when the failover occurs, that "recovery" does occur for TEMPDB, and then this is followed by the "recreation" of TEMPDB.I suggest that the safest course of action is to follow Royv advice.solart |
|
|
|
|
location of tempdb in a cluster setup