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”
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.”
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
- 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
- How to Repair Suspect Database in SQL Server
- SQL Server: How to Start SQL Server with Minimal Configuration
- How to Start SQL Server in Single User Mode?
- How to Identify the Location of Resource Database in SQL Server
- What are Virtual Log Files in SQL Server Transaction Log File?
- How to Backup and Restore Resource Database in SQL Server
- Steps to Connect to SQL Server When all System Administrators are Locked Out
- SQL Server Configuration Manager
- How to Get SQL Server Instance Information
- Different States of SQL Server Database
- Discover SQL Server Components Installed Using SQL Server Discovery Tool
- Does SQL Server Database Use Any Enterprise Edition Features?
- How to Configure a Contained Database Feature in SQL Server
- How to Manage SQL Server Failover Cluster using Command Line
- How to Identify SQL Server Cluster Node and Shared Drives Information Using TSQL Queries
- How to Identify CPU Bottlenecks in SQL Server Using Performance Counters