up vote 0 down vote favorite
share [g+] share [fb]

We are considering clustering our SQL Server 2008 EE instances. The storage is on SAN. Can we put tempdb data/log files on SAN? What are the pros and cons on this solution? For optimal performance, can we create the same amount of tempdb data files as processor cores?

link|improve this question
feedback

3 Answers

up vote 2 down vote accepted

Your only option is to put TempDB on a shared disk resource, meaning the SAN. Standard disk I/O contention is what you're concerned about, moreso for TempDB since it's a central bottleneck for the entire instance.

As for how many files for TempDB, see Paul Randal's post on this subject. He's responsible for the storage engine, so he is THE expert on the subject: http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx

link|improve this answer
feedback
up vote 0 down vote

If you're using MS SQL clustering, my understanding is that all volumes must be on the shared storage, which will typically be a SAN.

link|improve this answer
feedback
up vote -1 down vote

Yes, you can put tempdb data and log files on SAN. You don't necessarily have to. You could (and emphasis on be CAREFUL here) have tempdb on direct attached storage (DAS) on each node, however the drive letter and the sizes must match, and you'll run into problems. Pros and Cons would be pretty much the same as regular DB files on SAN, and same HA concepts apply. IO performance might be a concern, so place it wherever you get better performance.

As for 'optimal performance' - there's no set in stone rule of how many files you should have for tempdb. Start with 1/4-number of cores, test your workloads, increase if need be. If you have too many files for tempdb, you might end up getting a bottleneck on your IO subsystem if it can't keep up.

link|improve this answer
Can you have non-shared storage for data and log volumes for any database at all in a SQL cluster? – mfinni Feb 24 '11 at 19:41
This is not correct if you mean DAS local to the server. You cannot put any SQL Server database on any disk that the SQL Server resource is not dependent on. So local disk is automatically out: connect.microsoft.com/SQLServer/feedback/details/532759/… – K. Brian Kelley Feb 26 '11 at 0:36
@K. Brian - whilst what you've said is technically correct, there is a how-to hack on how to mount TempDB on a Fusion IO that's direct-attached (one in each server) by changing the unique identifier of the disk so that they appear to all be the same clustered disk. I don't know what the ramifications of that are though in the case of a node failure. – Mark Henderson♦ Feb 26 '11 at 0:52
2  
My guess is that since it's a hack, you lose all support from Microsoft. Not a good situation to be in. – K. Brian Kelley Feb 26 '11 at 0:59
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.