As the title suggests this blog post is about a neat trick, which a decent DBA typically wouldn’t consider if he/she had other options. It worked for me and it was a well thought option to go for. I am not very proud of this trick (therefore “dirty”) however it saved my day and perhaps it’s a valid option for you to go as well. Remember that you use SQL server in a non-supported way doing this and don’t blame on me if anything goes wrong (well…to be realistic there’s not much to go wrong apart from having an unplanned outage (which could be quite severe I know)….if it doesn’t work just switch back onto your tempdb on SAN drive).

The problem

You have got a SQL Server cluster running SQL Server 2008 or 2008 R2. Such a cluster uses a shared resource like SAN storage which enables the other (dormant) cluster node to access database files and continue work after a failover. For more details on that I recommend this blog post by Kendra Little. This kind of shared storage truly makes sense for most databases. However tempdb is kind of different as it’s recreated each time the SQL service is restarted. Therefore there’s no need to access the same tempdb files the cluster node just failing over from has written to.

However SQL server rewards you with error message 5184: Cannot use file ‘%.*ls’ for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. There is a connect item describing that problem which lead to its fix in SQL Server 2012.

The dirty solution

Actually SQL server just performs this check if you issue an ALTER DATABASE ADD FILE or ALTER DATABASE MODIFY FILE or use the Files tab in Database properties to do the same (which generates these commands). Therefore the trick is to create all tempdb files as they should be on a shared volume with a dependency on the cluster resource. The volume should have the same folders and the same drive letter as your SSD you want to use.

1. Take the partition on your SSD drive offline.
2. Create a LUN on your SAN identical to the SSD partition for tempdb and add it to Windows Server with the same drive letter.
3. Move your tempdb files from within sql server using a command like this
   
ALTER DATABASE tempdb MODIFY FILE (name='tempdev', filename='T:\MSSQL10.MSSQLSERVER\MSSQL\Data\tempdev.mdf', SIZE = 40GB, FILEGROWTH = 0);
ALTER DATABASE tempdb MODIFY FILE (name='tempdev2', filename='T:\MSSQL10.MSSQLSERVER\MSSQL\Data\tempdev2.ndf', SIZE = 40GB, FILEGROWTH = 0);
ALTER DATABASE tempdb MODIFY FILE (name='tempdev3', filename='T:\MSSQL10.MSSQLSERVER\MSSQL\Data\tempdev3.ndf', SIZE = 40GB, FILEGROWTH = 0);
ALTER DATABASE tempdb MODIFY FILE (name='tempdev4', filename='T:\MSSQL10.MSSQLSERVER\MSSQL\Data\tempdev4.ndf', SIZE = 40GB, FILEGROWTH = 0);
ALTER DATABASE tempdb MODIFY FILE (name='tempdev5', filename='T:\MSSQL10.MSSQLSERVER\MSSQL\Data\tempdev5.ndf', SIZE = 40GB, FILEGROWTH = 0);
ALTER DATABASE tempdb MODIFY FILE (name='tempdev6', filename='T:\MSSQL10.MSSQLSERVER\MSSQL\Data\tempdev6.ndf', SIZE = 40GB, FILEGROWTH = 0);
ALTER DATABASE tempdb MODIFY FILE (name='tempdev7', filename='T:\MSSQL10.MSSQLSERVER\MSSQL\Data\tempdev7.ndf', SIZE = 40GB, FILEGROWTH = 0);
ALTER DATABASE tempdb MODIFY FILE (name='tempdev8', filename='T:\MSSQL10.MSSQLSERVER\MSSQL\Data\tempdev8.ndf', SIZE = 40GB, FILEGROWTH = 0);
ALTER DATABASE tempdb MODIFY FILE (name='templog', filename='T:\MSSQL10.MSSQLSERVER\MSSQL\Data\templog.ldf', SIZE = 2GB , FILEGROWTH = 500MB);


4. Stop the SQL server service
5. Take the SAN drive offline in Windows
6. Activate the SSD…it should have the same drive letter assigned (T in my example) and the same folder structure available.
7. Start the SQL server service…all your files should now be created on your local SSD
8. Test failover

Of course there is a downside (which every dirty trick tends to have):

• In case you run into file space issues you won’t be able to expand your tempdb files online on your SSD. So make sure to presize your tempdb somewhat bigger than acutally needed.
• Resizing your files needs you to go back to a cluster dependant SAN drive, do the file resizing there and flip back in your SSD.