How to Use a Local Drive for TempDB in a Multi-Instance Cluster

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!)

 

About jdanton1
A DBA, cyclist, cook (who likes to play chef occasionally)

One Response to How to Use a Local Drive for TempDB in a Multi-Instance Cluster

  1. timc says:

    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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: