In the past few weeks I have seen a plethora of posts and articles about virtualization. There was a great article in the latest issue of SQL Server Magazine that had a handful of quotes from Brent. They even had his photo in there. Maybe one day I could be as lucky…
Anyway, in all of these talks about virtualization, as well as SAN usage, I hear mention of tempdb very briefly. And when I do hear of it, I usually hear something like “…put tempdb on its own drive…”, or something similar. So today I emailed Brent to tell him that he should go one step further when he does his presentations. It is not enough to just mention that tempdb should be placed on a separate drive. Why?
Well, let’s say your company uses SAN replication. Would you want to replicate your tempdb across the WAN?
No, of course not. But I bet there are more than a handful of people out there doing just that. There is no need to replicate your tempdb. All it does is clog your pipes. So, if you are going through the pain of virtualization, make sure you dedicate drives for the tempdb for each instance. And if you are building up your SAN, make certain you keep tempdb on a local, non-replicated drive.
UPDATE: Pay attention to SQLCraftsman’s comment below…SQL clusters are a different animal.
The only problem with putting tempdb on a local disk is that you cannot do that with SQL Clustering.
good point, i had forgotten about clusters because we only had one and i pushed it off the roof about a year ago.
The other thing you can do is use a separate array on the SAN just for TempDB, or use a shared array for TempDB from several different servers. Then in the SAN replication software settings, don’t replicate that array.
Keep up the good work, son, and some day you’ll be in magazines too. Maybe even with your shirt buttoned.
I agree with Brent. Definitely use a different array for the TempDB. We do that. I’ll have to ask around about the SAN replication, though. It’s definitely a good thing to consider ommitting from the SAN replication. Thanks for the tip, SQLBatman!