SQL Server Performance Forum – Threads Archive
TempDB placement in Clustered Environment
Hi, I have a question on where to place TEMPDB in my clustered environment. Hardware layout is as follows:6×36 gb Raid 5 drive for Data files
4×18 gb Raid 0+1 drive for transaction log
The above is out on the shared array. I also have internal storage available on each server (2 internal controllers, 1 onboard). However, I believe I must place TEMPDB on the shared array, correct? If this is true, am I better off placing it on the same drive with the Transaction log, the Raid 5 drive, or adding 2 more mirrored drives (Raid 1) on the shared array and putting tempdb here? In the latter option, the added disks would be behind the same I/O channel as the Raid 0+1 and Raid 5, but this would still minimize disk contention. Thoughts?
My thought is tempdb on RAID 1, specially if application has temporary tables.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
Thanks. Is it true that Tempdb must be on the shared storage in a cluster? I don’t see how it could be local but just wanted to confirm this.
I don’t see why it must be on the shared storage… if the SQL server fails over then it will restart and so tempdb will get re-created anyway, regardless of where you place tempdb… Cheers
Twan
Has anyone ever tried placing tempdb on a local drive in a clustered environment? My concern is this: Say that D: is a local drive. And E: and F: are clustered disk resources (on a shared disk array). I’m running Active/Passive so when I run the "alter database" SQL to move TEMPDB to the D: drive, this file will be moved to the local Active Server’s drive only. Now, does this mean I would have to make a copy of an empty TEMPDB file and place it on the Passive server’s D:drive (same path). I’m just not sure how this could work. I know that SQL Server rebuilds tempdb when it restarts but would this work if no file previously existed? I can try it but I don’t want to break our cluster since everything is working so smoothly right now. <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />
The fact that a tempdb file exists is irrelevant. You could stop a SQL service, delete the tempdb files and start the service, and SQL will just recreate tempdb. Having said that I’m not currently on a clustered server site, but can;t see why it would be any different Cheers
Twan
In Cluster :
Data and log devices, as well as tempdb, should be placed on separate disks using as many different channels as possible, with the caveat that this will limit the number of instances that can be installed on the cluster. As a thumb of rule, if your system is very large, or has hotspots, you may decide to use filegroups as a method of splitting up the disk I/O. Further segmenting this by putting filegroups on different disks, on different channels, can result in a performance boost. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Thank you Twan. I will try placing Tempdb on a local drive and see if this works. As for Satya’s post, this was my reason for wanting to place tempdb on a local drive. We are trying to take advantage of an additional internal controller. Thanks for the replies.
Just to follow up, I did try running the following SQL to move the TEMPDB to a local drive: use master
go
Alter database tempdb modify file (name = tempdev, filename = ‘c: empdb empdb.mdf’)
go
Alter database tempdb modify file (name = templog, filename = ‘c: empdb emplog.ldf’)
go The following errors were received: Server: Msg 5184, Level 16, State 1, Line 1
Cannot use file ‘c: empdb empdb.mdf’ for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used.
Server: Msg 5184, Level 16, State 1, Line 1
Cannot use file ‘c: empdb emplog.ldf’ for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. As I suspected, unless someone knows a way to work around this, I do not believe this will work.
Make sure the C: volume’s disk is within the SQL Server resource group (as opposed to
being within the Cluster Resource Group). Follow this KBA ://support.microsoft.com/support/kb/articles/q295/7/32.asp which explains the scenario. Error represents SQL cannot access cluster resources (such as volumes/disks) do not reside in its
resource group. For relevance refer to this topichttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1773 also. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Satya- The problem with this is that the C: drive must be configured as a Clustered Resource which does not seem to be possible since it is not a *shared* drive– it is the internal drive on each separate box– again, not configured as a clustered drive. Please let me know if you still have advice for making this scenario work. Thanks for your help.
You have to put the databases on a shared disk, even tempDB. No other option is supported and that’s why you get the error message you get when trying to create a database on local disk like C:. /Argyle
Thanks Argyle. That’s what I thought but I figured if any SQL component might be capable of being stored locally, TEMPDB would be it (since it’s so dynamic and rebuilt each time the service is started). Thanks again to everyone else for their attempts at providing an answer to my question.
]]>