I ran into an interesting issue while installing SQL 2012 SP1 in a failover cluster configuration and utilizing the local TempDB feature.  If you are unfamiliar with the new feature in SQL 2012 that allows you store TempDB on a local drive in a failover cluster you can read more here.  Here is a screen shot showing where during the install you can configure your data directories.  You can click on the image for a larger view.

You can see that I put TempDB on the local C:\ drive.  Now let me clarify that this was on a test virtual machine and putting TempDB on the operating system drive is a BAD practice.  In the words of The Offspring, “Don’t do this at home or at all!”  When you click next on that dialog you get a popup that there are warnings and to look below for more information.  You’ll notice a new warning area shows up at the bottom of that dialog.  Here is a screen shot, but the message gets cut off and you have to hover over it to see the whole thing.  It says, “You have specified a local directory C:\MSSQL11.INST01\MSSQL\Data as the tempdb data or log directory for a SQL Server cluster.  To avoid possible failures during a failover, you must make sure that the same directory exists on each cluster node and grant read/write permission to the SQL Server service.”

If that directory does not currently exist, SQL Server will both create it and give the SQL Server service account permission to it.  At this point we don’t have to worry about anything as the install will take care of it.  What that message really means is that you have to make sure that it exists on each cluster node except this one.

This is also where the story takes an even more interesting twist.  After seeing that warning, I would fully expect to see it again when adding a node.  However, I don’t get a warning at all.   Maybe it created the directory for me since the first node did?  Now a lot of people might not think twice and assume everything is good since there was no warning or they simply might not remember once they get to the other node.  I’m not one of those people, so I’ll go look to see if the directory exists.  What happens if you don’t and try to failover to that node?

It will fail over again to the original node or another node.  You should see 5 events in the event log.  Here’s what I see on my test system.

CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘C:\MSSQL11.INST01\MSSQL\Data\tempdb.mdf’.

FCB::Open failed: Could not open file C:\MSSQL11.INST01\MSSQL\Data\tempdb.mdf for file number 1.  OS error: 3(The system cannot find the path specified.).

Unable to open the physical file “C:\MSSQL11.INST01\MSSQL\Data\tempdb.mdf”. Operating system error 3: “3(The system cannot find the path specified.)”.

CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.

That’s pretty much what I would expect to see.  That last error is misleading of course because we know the drive is not full, it’s because the directory does not exist.  At this point the fix is an easy one.  We just need to go to that node and create the directory C:\MSSQL11.INST01\MSSQL\Data and make sure that the account running the SQL Server service has change control on it.

I’m almost positive that adding a node gave a warning in a previous version like the RTM or a CTP, but I would have to go try that out to be sure.  On one hand Microsoft did warn you on the first node, but I still think it would be nice have it show up when adding nodes as well.  Besides, you might be adding a node 6 months to a year later and not remember.