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

Multiple instances tempDB on the same local disk in sql2012 cluster Expand / Collapse
Author
Message
Posted Wednesday, January 8, 2014 6:50 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:41 PM
Points: 202, Visits: 373
Hello!

I'm wondering, in a clustered SQL2012 environment you can put your tempdb on a local disk for performance improvements. Is it possible to put tempdb from multiple instances on the same disk, or will something prevent this (all instances points it tempdb to t:\data\instancename\tempdb\, where t:\ is a local disk)?

I can't really see any technical problems with this, but unfortunately I don't have the possibility to try this myself, and google didn't help me.

It's a windows failover cluster (WSFC) I'm thinking about, with two or more instances.

Thanks in advance.

Pontus
Post #1528877
Posted Wednesday, January 8, 2014 8:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 7:46 AM
Points: 47, Visits: 264
No, SQL Will not allow you to use a disk that is not part of the cluster group.
Post #1528904
Posted Wednesday, January 8, 2014 8:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:41 PM
Points: 202, Visits: 373
Yes, it will, in sql 2012 it's possible, not in earlier versions.
Post #1528913
Posted Wednesday, January 8, 2014 10:53 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: Saturday, September 6, 2014 11:24 AM
Points: 568, Visits: 379
There is nothing to stop you doing it (from technical perspective).

I would still consider this as an approach, as you might get this drive hammered down by one of the instances, which will ultimately affect the rest of the instances, using the same drive for it's tempdb (if they are sharing the same active note, of course). I guess you already have that in mind, but still - I need to mention it :)

Regards,
Ivan
Post #1529001
Posted Wednesday, January 8, 2014 11:10 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:41 PM
Points: 202, Visits: 373
Great, I know about the possible performance implications, but both nodes are equipped with a "fusio-io like" card, so I hope it will handle whatever we throw at it ;)

Thanks for the info!

//Pontus
Post #1529009
Posted Wednesday, January 8, 2014 1:05 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:22 PM
Points: 4,356, Visits: 6,191
I didn't verify the statement that this IS available in SQL 2012, but if it turns out to not be I do know there has been an (unsupported) workaround for this for many years. It has been used by many installations to do just what you are doing.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1529064
Posted Wednesday, January 8, 2014 3:23 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 7:46 AM
Points: 47, Visits: 264
Sorry, for tempDB it may be different. For user databases

CREATE DATABASE [testDB]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'testDB', FILENAME = N'q:\testDB.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'testDB_log', FILENAME = N'q:\testDB.ldf' , SIZE = 9216KB , FILEGROWTH = 10%)

Msg 5184, Level 16, State 2, Line 1
Cannot use file 'q:\testDB.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 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Post #1529124
Posted Wednesday, January 8, 2014 3:41 PM
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: Saturday, September 6, 2014 11:24 AM
Points: 568, Visits: 379
We are talking about tempdb of a clustered SQL Server 2012 instance and since that release Microsoft supports tempdb on a local drive. For detailed instruction, you can check here:http://www.mssqltips.com/sqlservertip/2817/sql-server-2012-cluster-with-tempdb-on-local-disk/

HTH,
Ivan
Post #1529130
Posted Monday, January 13, 2014 3:02 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:41 PM
Points: 202, Visits: 373
Plain answer after testing today is that it works like a charm. Thanks for all the replys :)
Post #1530484
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse