We may have performance issues with high tempdb usage even though we have the faster SSDs (Solid State Disks) in a Windows Server Failover Cluster with SQL Server Clustered Instance Installed. This article will be helpful in configuring TempDB on a local Disk instead of shared storage in SQL Server 2012/2014. This will reduce the I/O requests from the shared storage and overall it can improve performance.

Prerequsites

Before we start with the installation, lets assume we have the following things in place. First, we have two Windows Server 2012 Nodes: WINSRVNODE1 and WINSRVNODE2. These nodes are part of a Windows Cluster: WINSRVCLU01.

There is a SQL Server Network Instance Name for the cluster: SQLSRVCLU. To install the SQL Failover Clustered Instance, we are going to use a domain login part of Administrator Group on both the nodes.

Installation

On Node 1( WINSRVNODE1): Starting from the SQL Server Installation Center, the installation would remain same until the Database Engine Configuration (This is the stage where we use Local Disk as directory for tempdb instead of of Shared Storage.

     1. Once we are at the Database Engine Configuration Tab,we specify SAN Disk for Data & Log files but Local Disk for Tempdb Database.

        Note: It is always recommeded to use isolated disks for data file,log  files and tempdb files.

     2. I have my DataFiles on S Drive, Log Files on L Drive (S & L are from SAN) and my Tempdb database on F Drive(F is a Local Disk on Node 1)

Please ignore the warning. This is advice to ensure the same path exists on all the cluster Nodes. Click OK to continue until the installation is successfully completed on Node 1 ( WINSRVNODE1).

On Node 2 (WINSRVNODE2)

1. Starting with Installation Center as we did on Node 1, we select 'Add Node to a SQL Server Failover Cluster' i.e. SQLSRVCLU and proceed with same steps involving Support Setup Files Installation, Cluster Node Configuration till adding Node is successfully completed.
2. Now, please create the same folder structure for the tempdb database on the local disk of Node 2 (WINSRVNODE2). If you use a different path, the SQL Instance will fail to come online in case of a failover.

           In my case, it is F:\MSSQL11.SQL01\MSSQL\Data.

This concludes configuring tempdb database on Local Disk on SQL Server 2012/2014 Failover Clustered Instances.

Thank you.