Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Moving TempDB of the Cluster Resource Expand / Collapse
Author
Message
Posted Monday, February 14, 2011 12:46 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, September 25, 2014 3:17 PM
Points: 450, Visits: 1,341
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
Post #1063808
Posted Monday, February 14, 2011 12:54 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:09 AM
Points: 2,049, Visits: 3,585
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
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #1063814
Posted Monday, February 14, 2011 1:00 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:09 AM
Points: 2,049, Visits: 3,585
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
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #1063820
Posted Monday, February 14, 2011 1:35 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, September 25, 2014 3:17 PM
Points: 450, Visits: 1,341
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
Post #1063845
Posted Monday, February 14, 2011 1:43 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:09 AM
Points: 2,049, Visits: 3,585
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
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #1063850
Posted Thursday, February 17, 2011 7:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 8:25 AM
Points: 163, Visits: 604
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...
Post #1065660
Posted Friday, February 18, 2011 7:48 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 1,522, Visits: 1,609
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.)
Post #1066405
Posted Friday, February 18, 2011 8:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 25, 2014 1:03 PM
Points: 2,917, Visits: 2,527
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
Post #1066413
Posted Wednesday, July 27, 2011 11:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 28, 2011 6:43 AM
Points: 5, Visits: 5
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.
Post #1149813
Posted Saturday, November 30, 2013 4:41 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 12:19 AM
Points: 523, Visits: 568
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?
Post #1518641
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse