Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multiple instances tempDB on the same local disk in sql2012 cluster


Multiple instances tempDB on the same local disk in sql2012 cluster

Author
Message
Illwill
Illwill
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 430
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
thisted
thisted
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 343
No, SQL Will not allow you to use a disk that is not part of the cluster group.
Illwill
Illwill
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 430
Yes, it will, in sql 2012 it's possible, not in earlier versions.
Ivan Donev
Ivan Donev
SSChasing Mays
SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)

Group: General Forum Members
Points: 603 Visits: 414
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 Smile

Regards,
Ivan
Illwill
Illwill
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 430
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 Wink

Thanks for the info!

//Pontus
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5935 Visits: 8297
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
thisted
thisted
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 343
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.
Ivan Donev
Ivan Donev
SSChasing Mays
SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)

Group: General Forum Members
Points: 603 Visits: 414
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
Illwill
Illwill
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 430
Plain answer after testing today is that it works like a charm. Thanks for all the replys Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search