SQL Server Performance

TempDB placement in Clustered Environment

Discussion in 'SQL Server Clustering' started by stolarski, Nov 14, 2003.

  1. stolarski New Member

    Hi, I have a question on where to place TEMPDB in my clustered environment. Hardware layout is as follows:
    6x36 gb Raid 5 drive for Data files
    4x18 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?
  2. Luis Martin Moderator

    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
  3. stolarski New Member

    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.
  4. Twan New Member

    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
  5. stolarski New Member

    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=':)' />
  6. Twan New Member


    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
  7. satya Moderator

    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
  8. stolarski New Member

    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.
  9. stolarski New Member

    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.
  10. satya Moderator

    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
  11. stolarski New Member

    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.
  12. Argyle New Member

    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
  13. stolarski New Member

    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.

Share This Page