SQL Server 2012 Cluster with TempDB on Local Disk

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.