February 14, 2011 at 12:46 pm
I've got a client that wants to install local drives on both nodes of their cluster and move TempDB from the shared drive to the local drive. This means TempDB will be on a drive that isn't part of a clustered resource.
In theory since TempDB is rebuilt on a restart this should be possible, but everything I've read so far says its not supported by Microsoft.
Has anyone ever tried this? We are currently building a cluster to test it but I'm not sure if when we will get a change to finish this. I'm interested to know if anyone has tried to do it.
Current indications are we will get this error, but this is from an old posting on another blog.
Server: Msg 5184, Level 16, State 1, Line 1
Cannot use file 'c:\MSSQL\data\tempdb.mdf' for clustered server. Only
formatted files on which the cluster resource of the server has a dependency
can be used.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
February 14, 2011 at 12:54 pm
There is a registry modification / hack that I found some time ago that gives instructions on how to do this, primarily so that SSD's can be used for tempdb in the scenario that you describe. Check around google for something like "putting tempdb on a non-cluster drive" and you will find stuff. I don't know that this is supported though.
One question too; how are they determining that tempdb needs to be put on local drives? Why not properly configure a SAN presentation to support the activity?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 14, 2011 at 1:00 pm
Please not - BIG disclaimer - FLASHING - I don't believe this is supported. Read and apply at your own risk....
http://nyc-dba.blogspot.com/2008/10/using-local-disks-in-cluster.html
The key pieces of information are in the comments section.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 14, 2011 at 1:35 pm
David Benoit (2/14/2011)
One question too; how are they determining that tempdb needs to be put on local drives? Why not properly configure a SAN presentation to support the activity?
They had a nice shiny new SAN installed to improve performance, but nobody told them that to get the volume they were using 600GB drives, the result is SQL is sharing disk resources with things like Outlook and half a dozen other applications. Their system architect thinks moving the high IO TempDB files off the SAN may help.
The SAN administrators feel the SAN isn't the issue. To some extend I must agree, the vendor is running an old version of JDE on SQL 2005 SP2. The DB is 600GB, but still all in one data file, they are also using non-JDE apps to access some of the data and they are struggling to get through month ends.
One problem is the server is old hardware, so can't take SSDs. The local disk will have to be SCSI (NOT even iSCSI). We've raised the idea of putting SSDs in the SAN but it sounds like the cost is prohibitive.
Everything I've read says TempDB on local disk within a cluster isn't supported by MS and requires some hacking to get it to work. If anything turns to custard MS won't help much.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
February 14, 2011 at 1:43 pm
Have you determined how much of the IO / what percentage of IO is tempdb related? If it is high then it might be a quick fix but it is truly a hack. Best to configure the SAN appropriately and then move forward from there.
I'm guessing too that you could probably find some heavy IO queries outside of tempdb that might help alleviate things as well. Might be worth a look....
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 17, 2011 at 7:30 am
Have you considered block replication software like Steeleye or DoubleTake?
I'm running FusionIO drives (local storage) and in my QA environment configured a GEO Cluster resource on the windows cluster.
Simply put, the replication software permits local drives on each node to act as part of the cluster.
And - you might even consider configuring the GEO cluster resource and disabling the actual replication because you don't need the tempdb data being replicated.
Might be worth checking out...
February 18, 2011 at 7:48 am
Doesn't help now, but for general info:
At the Minneapolis PASS group this week, one of the MS guys did a preview of the features coming in Denali. One of the changes is to allow tempdb on local disks (without needing to hack it).
(To pass on his disclaimer, it's in CTP1, and that may/not be what's actually in the GA release.)
February 18, 2011 at 8:04 am
We have gone through the pains of having everything on the SAN, and when there were performance issues the SAN vendor kept saying throw more devices at it. Performance might improve a little and because the vendor says - we add more to it and again the vendor says...
It was a painful experience but I did finally take the tempdb's and place them on extrernal drives in all of my active/passive clusters. As long as the external drives had the same drive letters we did not have any issues when there was a failover and we did not have to hack the registry.
BTW - a lot of performance issues went away.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
July 27, 2011 at 11:16 pm
Yep, you can use DataKeeper to put tempdb on a local drive and disable the actual replication. It requires a little knowledge of the product to disable the replication but it can be done.
November 30, 2013 at 4:41 am
Server: Msg 5184, Level 16, State 1, Line 1
Only formatted files on which the cluster resource of the server has a dependency can be used.
For all those who got this error message after removing a node from a clustered SQL instance: for some reason the node-removal operation also removes the disk dependency in SQL resource definition (at least that was my experience in SQL version 2008 running on 2008 R2). The solution is to open Cluster Manager's SQL resource definition and add the clustered disk dependency manually, under the cluster name using 'AND' dependency.
Why is the SQL removing that dependency when you remove a node?
December 5, 2013 at 3:45 am
Leo.Miller (2/14/2011)
David Benoit (2/14/2011)
They had a nice shiny new SAN installed to improve performance,
Storage Area Networks are implemented for consolidation not performance, much like virtualisation is implemented for consolidation. You will always have an overhead above physically attached storage, dependant on the following (this list is not exhaustive!)
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply