Introduction
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.
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 organizations 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 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.
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.”
Click OK and continue with rest of the installation on Primary Node of the Failover Cluster.
How to Configure SQL Server 2012 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.
Action Item
- 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.
Read more: http://www.mytechmantra.com/LearnSQLServer/Configure-TempDB-on-Local-Disk-in-SQL-Server-2012-Failover-Cluster-to-Improve-Performance/#ixzz3h4aLGdUa
Follow us: @MyTechMantra on Twitter | MyTechMantra on Facebook
