One of the cool new features that was introduced in SQL Server 2012 was official support for using a local drive for TempDB in a SQL Server Failover Cluster Instance. In prior versions, this was unofficially supported (it was a bit of hack), but it made sense—TempDB is ephemeral (it gets recreated every time SQL Server restarts) and the increased prevalence of PCI SSD cards we are a great fit for the random IO patterns of TempDB. The only caveat to this is that the drive letter for TempDB needed to exist on all nodes in the cluster (and this doesn’t get checked as part of the cluster setup process, so you’ll want to failover to all nodes as part of your testing).
Recently, I had a chance to test out this setup—but in a slightly more complicated environment. In a two-node cluster with a single instance this is really straightforward, simply create a drive T: (or the drive letter of your choice) on both nodes and you are done. In the case I was working on, I had a four node cluster with three instances of SQL Server—so I would need three TempDB volumes on each node for a total of twelve. I could just assign each volume its own drive letter, but one of the concerns in a multi-instance cluster is running out of drive letters. In order to get around the limitation of only 25 available drive letters in a cluster (remember Windows always needs C:\) is to use mount points.
So I applied the same rules to TempDB—I created 3 volumes on the local SSD on each node:
T:\Instance1\TempDB_SSD
T:\Instance2\TempDB_SSD
T:\Instance3\TempDB_SSD
After this was completed, I failed each instance onto each node in the cluster to ensure that everything worked as expected. This setup is pretty straightforward, but not well documented for this scenario. So, have fun and enjoy good TempDB performance (don’t forget to create at least four files, either!)
Tried same worked great in my active passive cluster…….. but now when I want to add files to tempdb the local ssd disk does not show as a choice, is there another step