Why TEMPDB is configured in Local Disk in Sql Cluster 2012

From Sql 2012 onwards Tempdb  can be  configured in local disk unlike it was placed in shared disks in early versions before Sql 2012. 

In this post we will see the benefits of placing the Tempdb in Local disks and how to configure .

As we know Tempdb process more IO load as it uses row versioning and also used by all databases. Now on  a busy environment high IO on tempdb can lead to slow response due to system HBA's and storage arrays. 

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.

How to configure Tempdb in local Disks. 

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
F:\	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. 
   
   
   
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. 
   
   
   
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. 
   
   
   
   
   
   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.
   
7. Continue through the remaining cluster install steps and complete the installation. 
   
   
   
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.

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.
   
   
   
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.

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.

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. 

These 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. 

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. 

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

How to configure though T SQL .

In order to move the location of TempDB we will be using the same ALTER DATABASEcommands.

Alter database tempdb modify file (name='tempdev',filename='C:\SQLData\tempdb.mdf')
Go
Alter database tempdb modify file (name='templog',filename='C:\SQLData\templog.ldf')
Go

After running above command in the message section we have below message written:

Local directory 'c:\sqldata\tempdb.mdf' is used for tempdb in a clustered server. This directory must exist on each cluster node and SQL Server Service has read/write permission on it.
The file "tempdev" has been modified in the system catalog. The new patch will be used the net time the database is started.

Local directory 'c:\sqldata\templog.ldf' is used for tempdb in a clustered server. This directory must exist on each cluster node and SQL Server Service has read/write permission on it.
The file "templog" has been modified in the system catalog. The new patch will be used the net time the database is started. 

Courtesy and Ref : 

Dan Quinones, https://www.mssqltips.com/sqlservertip/2817/sql-server-2012-cluster-with-tempdb-on-local-disk/

• http://gursethi.blogspot.fr/2012/05/sql-2012-cluster-tempdb-on-local-disks.html