SQL Server Articles, Tips, FAQs, Reviews, Whitepaper, News etc - MyTechMantra.com

How to Configure TempDB on Local Disk in SQL Server 2012/2014 Failover Cluster to Improve Performance

Starting SQL Server 2012 Failover Cluster installation supports Local Disk to be used for TempDB Data and Log files. This article explains the steps to be followed by the DBA to configure SQL Server 2012 Failover Cluster to use Local Disk for TempDB Data and Log Files.

The steps mentioned in this article also applies for SQL Server 2012, SQL Server 2014 and Higher Versions.

Advantages of TempDB Located on Local Disk in SQL Server Failover Cluster

  • If you are experiencing heavy TempDB Usage then by having TempDB data and log files stored on Local Disk can help you achieve significant performance benefits.
  • Having TempDB Data and Log files located on Local Disk will help you reduce the I/O requests from shared storage and thereby helps to improve the performance of shared storage considerable.
  • Cost of Solid State Disks (SSDs) is falling and many organisations who wish to obtain better performance are now looking forward to use SSDs for TempDB data and log files in Failover Cluster configurations

How to Configure SQL Server 2012/2014 Failover Cluster TempDB Local Directory During Installation

During SQL Server 2012 Failover Cluster installation DBA will have to specify the Local Disk for TempDB Data and Log Files. Read SQL Server Database Engine Service Startup Options.

Learn “How to Configure SQL Server 2012 Failover Cluster with TempDB on Local Disk”

Configure TempDB on Local Disk in SQL Server 2012 Failover Cluster to Improve Performance

Warning Message

You may end up seeing a warning message within the Database Engine Configuration screen as shown in the snippet below.

“You have specified a local directory as the TempDB data or log directory for a SQL Server Cluster. To avoid possible failures during a failover, you must make sure that the same directory exists on each cluster node and grant read/write permission to SQL Server service.”
you must make sure that the same directory exists on each cluster node and grant readwrite permission to SQL Server service

Click OK and continue with rest of the installation on Primary Node of the Failover Cluster.

Learn How to Perform DBCC CHECK Database Consistency Checker (DBCC) For Analysis Services Database in SQL Server 2016 for Tabular and Multidimensional Databases

TempDB Database On a Local Disk for an Failover Clustered Instances

How to Configure SQL Server 2012/2014 Failover Cluster TempDB Local Directory During Installation on Secondary Node

1. On the Secondary Node of Failover Cluster launch SQL Server 2012 setup and click on Add node to a SQL Server Failover Cluster option on the Installation Page to “Launch wizard to add a node to an existing SQL Server 2012 Failover Cluster”.

2. Continue with installation by selecting the same settings which was used for configuring the first node. Here, there is no need to specify the path to any disk or directories while installing SQL Server 2012 on the secondary node.

3. Once the Failover Cluster Installation is completed successfully on the Secondary Node then create the same folder structure on Secondary Node where TempDB Data and Log file will reside i.e., M:\MSSQL11\MSSQL\Data (in this example) and grant read/write permission to SQL Server Service.

Configuration Best Practices for SQL Server Tempdb Placement on Failover Clustered Instances

Next Steps

  • Perform SQL Server Failover Cluster Validation by initiating the failover from Primary to Secondary Node and Vice-Versa. If you haven’t created the same folder structure on secondary node and have granted grant read/write permission then the SQL Server Service will not start on the secondary node.
  • In Event Viewer look for Error Events such as 5123, 17204 or any other messages related to the SQL Server Resource not coming online.
  • Learn How to Move TempDB to Another Drive in SQL Server.

SQL Server Disaster Recovery Articles and Tips

Chetna Bhalla

Chetna Bhalla

LESS ME MORE WE

Chetna Bhalla, the founder of MyTechMantra.com, believes that by sharing knowledge and building communities, we can make this world a better place to live in. Chetna is a Graduate in Social Sciences and a Masters in Human Resources and International Business. She is an alumnus of Vignana Jyothi Institute of Management, Hyderabad, India. After graduation, Chetna founded this website, which has since then become quite a favorite in the tech world. Her vision is to make this website the favorite place for seeking information on Databases and other Information Technology areas. She believes that companies which can organize and deploy their data to frame strategies are going to have a competitive edge over others. Her interest areas include Microsoft SQL Server and overall Database Management. Apart from her work, Chetna enjoys spending time with her friends, painting, gardening, playing the violin, and spending time with her son.

Add comment

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us...

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Recent SQL Server Tips