0

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?

3 Answers 3

2

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

0

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.

-1

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.

4
  • Can you have non-shared storage for data and log volumes for any database at all in a SQL cluster?
    – mfinni
    Feb 24, 2011 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/… Feb 26, 2011 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. Feb 26, 2011 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. Feb 26, 2011 at 0:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .