October 2, 2007 at 10:06 am
Let say we have the current setup:
2 Active node and 1 passive
Am I going to have a tempDB on each node independent of others tempBD (so a total of 3 tempDB) or 1 shared amongst all nodes?
If I'm having a total of 3 tempDB, can I use a local drive to host it which is not visible to the cluster?
As well for others systems databases. Is it going to be the same or they are going to be shared?
Thks
October 2, 2007 at 10:43 am
The number of tempdb's is dependant on the number of virtual instances. From your description, there are 2 virtual instances (2 active, one passive), therefore you should have 2 tempdb's.
October 2, 2007 at 11:13 am
Just to make sure, a cluster node = virtual instance?
Or I will have as many tempDB as active node is that so?
And what about other system databases? (master, msdb and model?)
THks
October 2, 2007 at 12:42 pm
Active or passive, how many instances of SQL Server do you have? That's how many tempdb's you'll have.
And they can't go onto local system drives (least not that I've ever seen work).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 2, 2007 at 12:53 pm
Inside the cluster, I'm having 2 active node and 1 passive node.
Each cluster node only contain 1 SQL Server instance.
If each SQL Server have it's own tempDB, how come can't I set it into it's own local drive? Is it SQL which prevent that? And for which reasons?
Thks
October 2, 2007 at 1:06 pm
It's just how clustering works. The clustering service shares the disk storage and expects to find the databases there when a failover occurs.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 2, 2007 at 1:12 pm
SQLSERVER while installed into a cluster cannot "see" local drives. That's because when there's a failure forcing a failover to another node, that drive wouldn't be visible. And yes - that's SQLServer doing that.
Typically when a server fails over, there will be any number of things going on, transactions open, backups, etc - losing your "scratch pad" in the middle of a long running transaction could be disastrous. All of those activities get reviewed and either committed or rolled back during the failover process (the bringing back up on node X process specifically). There's no telling what it would do if it tried to recover and ran across a tempDB in an entirely different "state" than it's expecting.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 2, 2007 at 1:20 pm
Also - let's not confuse multiple tempDB databases with adding multiple files to tempDB. Each instance of SQL Server gets one tempDB only, but you can add multiple filegroups and/or files to that database to increase its performance. But that ultimately has nothing to do with whether you cluster it or not.
The most common recommendation I seem to come across is to have as many files in tempDB as there are processors dedicated to running SQLSERVER, as long as they're on different LUN's or VLUN's.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 2, 2007 at 1:28 pm
Ok thks to all of you.
I think I understand.
There's only one tempDB per SQL Server instance cluster or not.
That tempDB can be divided into filegroups which then can be moved onto different drives (which must be visible if clustered)
Am I right?
October 2, 2007 at 2:00 pm
Right on. Just remember - no local only drives ....
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply