SQL Server 2012 Cluster with TempDB on Local Disk

By:   |   Comments (18)   |   Related: > Clustering


Problem

With the introduction of SQL Server 2012 Microsoft officially supports local disk TempDB in SQL Server cluster configurations. This tip will show you how to configure TempDB on a local disk when installing your SQL Server 2012 cluster. We will also discuss the benefits of using TempDB on a local disk and point out potential issues that may arise and how to avoid them.

Solution

The use of local disks for TempDB allows us to have more flexibility when configuring for optimal performance. It is a common performance recommendation to create the TempDB database on the fastest storage available. With the capability to utilize local disk for TempDB placement we can easily utilize disks that are larger, have a higher rotational speed or use SSD disks. SSDs are becoming more and more common and are available in multiple form factors. PCIe board SSDs, such as FusionIO and OCZ, offer even greater potential for performance improvement as they utilize the PCIe bus to provide more throughput than would be possible going through a disk interface or HBA.

Another advantage of placing TempDB on a local disk is that it creates separate paths of traffic by having your data and log files on the SAN while TempDB is on the local disk. Whether using a PCIe SSD or traditional hard drive SSDs, operations for TempDB will bypass your HBAs. This helps TempDB operations avoid congestion or contention on a shared storage network or array.

Additionally, for geographically dispersed cluster's this is a desired feature as you no longer have to replicate TempDB between sites. This translates to increased bandwidth availability and faster failovers.

Configuring SQL Server 2012 Cluster TempDB Local Directory

Prerequisites

Prior to installing a clustered SQL Server instance you should already have a working Windows 2008 cluster with a shared quorum disk as well as a clustered Distributed Transaction Coordinator.

The following layout would be representative of a typical cluster install for one SQL Server 2012 instance and will be utilized for this tip. For more complex configurations you may wish to add additional cluster disks for system databases, backups, etc.

Sample Cluster Disk Layout

Disk Letter Disk Type Purpose
C:\ Local OS System Drive
T:\ Local SQL TempDB
Q:\ SAN Cluster Quorum
M:\ SAN Cluster DTC
S:\ SAN SQL Data
L:\ SAN SQL Log

Installing the First SQL Server Cluster Node

  1. Launch setup from the SQL Server 2012 media.
  2. Start your SQL Server 2012 cluster installation by clicking New SQL failover cluster installation from the Installation page.

    SQL Server Installation Center - Installation Screen

  3. Proceed through the installation steps, accepting the license, selecting the desired features, naming the instance and specifying a cluster resource group name.
  4. When you get to the Cluster Disk Selection screen select the shared cluster disks that you will use for data and log directories.

    SQL Server Installation - Cluster Disk Selection

  5. Continue through the installation, providing the cluster network information and service accounts.
  6. When you get to the Database Engine Configuration specify your SAN disks for the Data and Log directories. Specify the local disk for your TempDB directories.

    Install a SQL Server Failover Cluster - Database Engine Configuration

    A warning indication will appear advising you to ensure that the same local path exists on every cluster node. As we will see cluster failover will fail if the exact local path is not defined on all cluster nodes.

    A warning indication for local directory usage

  7. Continue through the remaining cluster install steps and complete the installation.

    Continue through the remaining SQL Server cluster install steps

  8. When finished your disks in Windows Explorer should look as follows. Note that the Operating System and TempDB are local disks while the remainder are SAN disks.
Windows Explorer - Operating System and TempDB are local disks and remainder are SAN disks

Installing Additional SQL Server 2012 Cluster Nodes

When adding additional nodes to the cluster the key is to ensure the folders are created with the same path for the local TempDB directory. The SQL Server cluster group will fail to come online if the full path is not created on the additional instance.

  1. Launch the SQL Server 2012 setup on to the next node of your cluster, click Add node to a SQL Server failover cluster on the Installation page as shown below.

    SQL Server Installation Center - Add node to a SQL Server failover cluster

  2. Proceed through the Add Node steps, selecting the same settings as configured for the first node. You do not need to specify the path to any disks during an Add Node installation.
  3. Complete the installation on the additional node.
Add a Failover Cluster Node - Complete

SQL Server Cluster Failover Validation

You have now completed installing SQL Server 2012 on at least 2 nodes to form a cluster. The next step would be to verify cluster failover between nodes.

  • On the first node Open the Failover Cluster Manager.
  • Select Move this service or application to a different node on the SQL Server group as shown below.

    Failover Cluster Manager - Move this service or application to a different node

  • It is possible that the SQL Server resource and the SQL Agent resource fail to come up on the second cluster node.

    It is possible that the SQL Server resource and the SQL Agent resource fail to come up on the second cluster node

  • Open the system Event Viewer to check for any messages related to the resource not coming online.
  • You will see Error Events 5123 and 17204 in the Event Viewer Application Log.

    Event Viewer Application Log - Error Events 5123 and 17204

  • These errors are related to the local path for TempDB not being created on the additional cluster node.

    Windows Event Log - Errors are related to the local path for TempDB not being created on the additional cluster node

  • Create the same exact path for TempDB on the additional node.
    Windows Explorer - Create the exact directories

  • Now attempt to move the SQL Server group to the second node again. If the same local path is available the TempDB files will be created. SQL Server will automatically apply the proper permissions to the directory.
    Windows Explorer - TempDB data and transaction log directory

    The cluster should now fail over between all nodes while utilizing local disk for TempDB on each node.

    Successful cluster fail over between nodes while utilizing local disk for TempDB

    Next Steps


    • sql server categories

    sql server webinars

    subscribe to mssqltips

    sql server tutorials

    sql server white papers

    next tip



    About the author
    MSSQLTips author Dan Quinones Dan Quinones is an Architect/Systems Engineer with over 11 years of experience specializing in Microsoft Server and Database technologies.

    This author pledges the content of this article is based on professional experience and not AI generated.

    View all my tips


    Comments For This Article




    Tuesday, January 5, 2021 - 7:00:35 AM - Theo Ekelmans Back To Top (88000)
    There is a caveat here.... if the local disk with the tempdb fails, SQL will *not* initiate a failover.

    My colleague Twan and I have written an article detailing that on https://www.sqlservercentral.com/articles/do-not-place-tempdb-on-a-local-disk-in-a-sql-failover-cluster

    TLDR: you need to check in SQL for the loss of the local disk, and initiate the failover yourself.

    Wednesday, September 2, 2015 - 5:38:20 AM - SANDEEP SAWANT Back To Top (38584)

    Really good tips...Appreciated.

    We can tale leverage of local disk for best performance.

    Friday, May 17, 2013 - 8:52:21 AM - Victor Shahar Back To Top (24011)

    Great article, now let's think about having more than one log file per database and posibility to set it for tables as we want, having by that a kind of balanced log resources consumed by our database data changes.

     

    thanks for this article, it's great !

     

    Victor

    Friday, March 22, 2013 - 12:24:11 PM - mike good Back To Top (22963)

    Thanks for good article, was helpful.  I did waste some time trying to figure out where F: came from, and what happened to T: though.  I think would be better if you said F: instead of T: in the "sample cluster disk layout" table at top. 

     

    Sunday, March 3, 2013 - 4:40:22 AM - Shiv Shankar Khanna Back To Top (22532)

    Great !

     

    I have a doubt. Keeping tempdb in local instead of SAN wherein we can have stipped HDD having more then 6-7 spindle may slow down performace. Kindly guide.

    Sunday, December 23, 2012 - 8:22:45 PM - Dharmendra Keshari Back To Top (21105)

    Thank you Dan for making it crystal clear !!!!

    Sunday, December 23, 2012 - 11:16:45 AM - Dan Quinones Back To Top (21102)

    Ok, let me try to rephrase.

    you stated: This is why, in case of SQL 2008 and 2008 R2, you need to patch both the nodes separately (one by one). In the result of this, two resourceDB get created -  one will be on the Node1 local drive and another will be on the Node2 local drive.

    That is not entirely true.  It is still one resourceDB, but two copies of the DB files.  Since the resourceDB files are local, there must be a copy on each node in the cluster; however there is still only one resourceDB database for the instance.  The same is for tempDB, with locally defined tempDB you must have the data and log files defined on each node however it is still only one tempDB (unless you add more tempDB's).

    Hope that answer is clearer.

    Thanks,
    Dan Quinones

    Saturday, December 22, 2012 - 9:01:14 AM - Dharmendra Keshari Back To Top (21092)

    Hi Dan,


    Thanks you for your valuable input!


    But I did not get the answer what I was looking for…..


    In SQL Server 2008 onwards, the resource database is no longer tied to the master database, and exists in the Binn folder instead. So basically, the resource database is a part of the instance binaries from SQL 2008 onwards. This is why, in case of SQL 2008 and 2008 R2, you need to patch both the nodes separately (one by one). In the result of this, two resourceDB get created -  one will be on the Node1 local drive and another will be on the Node2 local drive.


    When you say that SQL Server 2012 supports local disk TempDB in SQL Server cluster configurations----


    It means Tempdb will be available on the local drive of Node and When Tempdb will be on the local drive of any Node, it cannot be accessed by other Node.


    So my question is  - Two Tempdb databases will be created - One will be on the Node1 local drive and another will be on the Node2 local drive when we go for SQL Server 2012 Cluster with TempDB on Local Disk configuration - like as I have stated above that we have two copies of ResourceDB database - One for Node1 and another one for Node2.


    Please help me to understand it.


    Thank in advance for your input!

    Friday, December 21, 2012 - 1:26:13 PM - Dan Quinones Back To Top (21085)

    Hi Dharmendra,

    The resourceDB will still have the number of tempDB databases defined for it on a single node.  Those also need to be created on any other cluster nodes, but they are not aggregrated together for a greater number of concurrent tempDBs.  For example in the scenario you bring up, you will have one tempDB database defined.  That does need to exist on both nodes for failover, yet only one will be in use at any time, based on which is the active cluster node.

    If you were to add additional tempDB databases they would need to be defined on all nodes for use in the event of failover.

    Multiple tempDB databases are further discussed in the follow up tip SQL Server 2012 Best Practices Analyzer TempDB Recommendations.

    Thanks for the comments.

    Thursday, December 20, 2012 - 10:03:44 AM - Dharmendra Keshari Back To Top (21054)

    Suppose, there is a two nodes cluster and as you have stated above that tempdb will be on the local drive. Does it mean that like Resoucedb we will be also having two tempdb databases -  One will be on the Node1 local drive and another will be on the Node2 local drive? Please correct me, i am missunderstanding anything here?

    Thanks!

    Thursday, December 20, 2012 - 9:56:45 AM - Dharmendra Back To Top (21053)

    Really superb Article...Thank you so much for sharing with us!

    Thursday, December 20, 2012 - 5:28:08 AM - philiphgray Back To Top (21050)

    I'd second John's experiences with junction points on the cluster drive - I've been running a similar setup for over a year without issue.

    I posted a very basic walkthrough of how I'd done it on MSDN in September 2011: 

    http://social.msdn.microsoft.com/Forums/hu-HU/sqldatabaseengine/thread/7858a037-e445-4385-bfaa-283de838240e

    This setup is still running fine.

     

     

     

     

    Wednesday, December 19, 2012 - 11:35:15 AM - John Back To Top (21029)

    I've been doing this in an unsupported fashion for years, utilizing a directory junction on the cluster drive that points to the local drive. This has worked flawlessly for many years. I even took it a stop further and created an RAMdrive and pointed the directory junction to that. tempdb has never run faster, and it's clustered to boot! It's nice to see Microsoft catching up with the times. Now if only they provided a supported RAMdrive solution out of the box (ahem linux!) so I didn't have to buy one.

    Thursday, November 29, 2012 - 5:01:07 AM - veeresh Back To Top (20623)

    excellent feature..need to test..thanks

    Wednesday, November 28, 2012 - 7:49:49 PM - Dan Quinones Back To Top (20611)

    Bill - Correct, follow the steps as described in http://msdn.microsoft.com/en-us/library/ms345408.aspx .  The TempDB files do not need to be moved as they are recreated when the instance restarts.  The original TempDB files can be deleted from the original directory once the file location is updated and the instance is restarted. 

    In this case ensure that permissions are explicitly specified as commented above, as the installer is not running to apply the appropriate permissions.  Also, as mentioned in the tip, the same updated path for TempDB needs to exist on all nodes for successful failover.

    Thanks for the comments.

     

     

    Wednesday, November 28, 2012 - 9:36:45 AM - Bill Back To Top (20590)

    Great article.  What about migrating clustered TempDB files on shared SAN storage to local drives?  Is it as simple as applying the BOL steps for migrating TempDB?

    Tuesday, November 27, 2012 - 7:53:17 PM - bass_player Back To Top (20575)

    While it is safe to assume that SQL Server will grant the proper permissions on the folder, it won't hurt to explicitly add the appropriate permissions on the tempdb folder. Explicitly add the Full Control permission on the tempdb folder to the SQL Server service account and test the failover accordingly.

    Tuesday, November 27, 2012 - 7:12:10 AM - Ajay Back To Top (20559)

    It is  a blast really...very gud update...5 stars....I have to test it yet...space will be save also..














    get free sql tips
    agree to terms