September 11, 2014 at 8:36 am
We are seeing very high Average Disk Queue Length numbers in one of our clusters (both nodes of the cluster are Virtual, but have their own dedicated virtual environments). Our main data drive also houses TempDB, which I would like to move.
Each node in the Active/Passive cluster are running Windows Server 2012 Standard 64bit and SQL Server 2012 Enterprise 64bit. There is a separate drive for Log files and data files.
The data files also have TempDB on them as previously mentioned. I am reading that you can set up a local disk on each node of the cluster, with the same drive letter and path and then move tempdb as you would with a stand alone SQL Server. To be honest, this seems to easy to be true.
Can anyone point me to a link or give me some direction on doing this?
Any and all help will be greatly appreciated.
September 11, 2014 at 10:30 am
I can see how this would be ok, because the SQL service effectively restarts in the event of a failover, at which point the tempdb is recreated. So as the long as the drive and folder structure structure exists on each node, the tempdb will be created there on whichever becomes the active node. Do you have the resource to test out the process first of you are not 100 percent confident it will work?
September 11, 2014 at 11:08 am
from the what's new in SQL Server, it is possible you just need to make sure that the same path is on all nodes of the cluster.
Local Disk is now a supported storage option for tempdb for SQL Server failover cluster installations.
Since your cluster node are virtual I have to ask, Is the shared drive used for the data files on different storage than the local disk for each virtual server? If your C: drive and the cluster disks share storage then you may not get any improvement as the IOs would all be going to the same place they would just be logically separated. There would be some configurations to consider whether you might get better performance: are you using vdk or rdm? are all virtual disks(clustered&local) sharing lun/diskgroup?
September 12, 2014 at 1:36 pm
Thanks for the replies!!! I will get with our Sys Admin/VMWare guy about those quesions.
So it does seem to be easy it sounds?
September 12, 2014 at 1:52 pm
GBeezy (9/12/2014)
So it does seem to be easy it sounds?
it's as easy as
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'C:\MSSQL\Data\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'C:\MSSQL\Data\templog.ldf');
GO
just make sure path exists on all nodes and that the sql server service has permission to write to the directory
May 19, 2018 at 4:49 pm
Guys
I am having a similar issue My problem is one of the drives that the TEMPDB files are located on did not fail over to NODE 2
So I created a new LUN formatted the drive new drive letter O it works on both NODE 1 and Node 2
My problem is I can not move the TEMPDB files to the new Drive O
Using your example above
use master;
go
Alter database tempdb
modify file (name = tempdev, filename = 'O:\SQL-Temp\tempdb.mdf');
go
Alter database tempdb
modify file (name = templog, filename = 'O:\SQL-Temp\templog.mdf');
GO
my results are
Msg 5184, Level 16, State 1, Line 3
Cannot use file 'O:\SQL-Temp\tempdb.mdf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.
Msg 5184, Level 16, State 1, Line 6
Cannot use file 'O:\SQL-Temp\templog.mdf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.
Any ideas?
Thanks Tom
May 19, 2018 at 6:18 pm
Tom what is the SQL version you are using?
May 19, 2018 at 6:29 pm
SQL 2014 Enterprise Clustered on Windows 2012 R2 Servers which are both VM's (VMWARE) The LUN's are via my NAS device using iSCSI
May 19, 2018 at 7:21 pm
Being on the NAS may be the problem - drive should be local, not networked.
This probably should be added as a disk through VM for it to work outside the cluster resources.
May 19, 2018 at 7:36 pm
I have three sql instances on this same cluster server and all are using Networked iSCSI LUNS all work but one drive that is why I created a new LUN.
The error message is talking about doe not have a dependency on it
The disk is in the cluster group
So why can't I move the tempdb?
May 20, 2018 at 7:39 am
Frederico
I figured it out
Start Failover Cluster Manger
Select Roles
Then chose the Role in which you need to add the LUN to
In the bottom window look for Other Resources
There you will see the SQL Server Instance right click and select properties
Then select the Dependencies Tab
Click on insert and find the new LUN to add
Then back to the top window right click on the Role select More Actions and click on Show Dependency Report this validates your change
May 20, 2018 at 8:36 am
That is not a local drive then - it is a cluster resource which is not what this thread is all about
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply