November 6, 2007 at 12:55 pm
We have just loaded SQL Server on a clustered (active/passive) server for the first time. I wanted to put the TempDB database files on the local server drives rather than our SAN to maximize I/O on queries with order by, group by, etc. Is this practice not allowed in a clustered environment?
November 6, 2007 at 1:36 pm
In a clustered install of SQL server, local drives are not visible as resources in the cluster and therefore cannot be used in the SQL install. That's "by design", a.k.a. on purpose by Microsoft preventing you from doing that.
Think about it though - you REALLY don't want that anyway. If you were in the midst of some transaction which relied on stuff being built in tempdb, and the server failed and failed over - how would it know what the other instance was up to... all of those things "in flight" would be missing, so the server wouldn't know how to roll back or forward, so this would be really really bad...
Last - if it this early and you're worried about the SAN not keeping up with local disks, then it might be worth checking that your SAN config (i.e. how you've got the devices set up/how the server is talking to it/etc). Theoretically - SAN devices should be AT LEAST as fast as local devices, some times several times faster, due to all of the caching they have etc.... Just put it through its paces to make sure that you DO in fact have it right before starting to rely on it heavily.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 6, 2007 at 1:48 pm
Our theory on using local drives was based on the data being transitory and having the local and SAN drives providing multiple I/O pathways. The in flight transaction issue would be the one that I can see as a show stopper. Our current SAN is VERY old, so the local drives are at least equivalent. We have a new SAN out for bid, but it could take a significant amount of time before it is here, assuming we get it at all.
Anyway, we’ll have to make due with what we’ve got. I’ll chalk this up as another time when the DBMS prevents us from the possibility of making a mistake.
November 10, 2007 at 11:04 am
I had been considering the same thing, a fast RAID local running tempdb that has an identical space on both nodes. The theory was, that in a failover, when SQL Server goes offline, bringing it back online would rebuild a new tempdb just like restarting the server. I guess this is not the case?
It does make sense, explaining it in a mid-transaction sort of scenario, that tempdb would remain intact in a failover to maintain the transactions, but since the SQL Services would have to start up on the other node, wouldn't it create a new tempdb?
Drive speed on the SAN is not really an issue in my environment, but the concept of reducing throughput on the FC by localizing tempdb activity seemed like a better-than-ideal scenario.
Ed
DBA
November 10, 2007 at 4:10 pm
My example is not the greatest, however the part that still stands is that it won't allow the use of non-shared or non-clustered resources.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 26, 2007 at 12:39 pm
Tim Talbert (11/6/2007)
Anyway, we’ll have to make due with what we’ve got. I’ll chalk this up as another time when the DBMS prevents us from the possibility of making a mistake.
THe problem is a bit larger than that, really. SQL Server would have to be made to longer pass the "ACID" test to be able to support this functionality. This is akin to requests to not have a log file in certain cases, which, in very limited, specific cases, may seem like a good thing, but not allowing it is such a trivial sacrifice when compared to what they are providing as a product.
One thing you could look into is purchasing a very cheap set of shared drives as a stopgap solution. I'm not sure what's out there, but you could get a white-box solution for the temp drives only, if you are willing to risk it. What I've found, however, is that the more visible and painful the problem is, the faster the bidding process will go (assuming the powers that be really care)... Good luck. I used to work for the state of MN, so I feel you pain.
Eric
November 28, 2007 at 7:59 am
I'd just take the route of isolating tempdb onto seperate spindles. Maybe even split tempdb into multiple data files and place those on seperate spindles too.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply