Multiple instances tempDB on the same local disk in sql2012 cluster

Question

Post reply

Multiple instances tempDB on the same local disk in sql2012 cluster

Illwill

Mr or Mrs. 500

Points: 572
More actions
January 8, 2014 at 6:50 am

#280935

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

thisted SSCrazy Points: 2131 More actions · Answer 1

No, SQL Will not allow you to use a disk that is not part of the cluster group.

Illwill Mr or Mrs. 500 Points: 572 More actions · Answer 2

Yes, it will, in sql 2012 it's possible, not in earlier versions.

Ivan Donev SSC Eights! Points: 957 More actions · Answer 3

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

Illwill Mr or Mrs. 500 Points: 572 More actions · Answer 4

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

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 on googles mail service

thisted SSCrazy Points: 2131 More actions · Answer 6

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 SSC Eights! Points: 957 More actions · Answer 7

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 Mr or Mrs. 500 Points: 572 More actions · Answer 8

Plain answer after testing today is that it works like a charm. Thanks for all the replys 🙂