Moving Tempdb in Failover Cluster

Question

Moving Tempdb in Failover Cluster

SQLbee

Ten Centuries

Points: 1009
More actions
October 28, 2009 at 12:00 am

#136893

Guys,
I have a simple question. I need to move the tempdb of a failover cluster with 2 nodes.
I am currently thinking that I can just run the query to move it and then failover to other node. This should automatically moe the tempdb to another location without having to explicitly restart SQL server (since restarting the sql server service will cause several problems in this case).
Can anyone please verify if this will work, or if it is necessary to restart the service.
Regards
Akhil

Viewing 5 posts - 1 through 4 (of 4 total)

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

sejal p gudhka SSCrazy Points: 2102 More actions · Answer 1

Since failover will start sql server services on other node it will also move tempdb. I have not tried it but definitely it will move as internally failover is going to restart sql server.

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 2

There's nothing different about moving tempdb in a cluster (other than making sure the same drive letter is mapped on all nodes).

Failing over will cause it to move, but.... you also said

since restarting the sql server service will cause several problems in this case

Failing over is the same as restarting the service, it's just that the service starts up on the other node, i.e. the service is stopped, resources are moved to the other node, and the service is started.

So, whatever problems you are anticipating will also happen when you fail over.

@SQLNumpty SSCrazy Points: 2940 More actions · Answer 3

Hi.

Providing you have correctly changed the location settings for TempDB, this will move when you fail over.

From experience, stopping and restarting the SQL Server resource is much faster then a complete fail over and should give you the same result. Why do you feel the need to fail over?

SQLbee Ten Centuries Points: 1009 More actions · Answer 4

Thanks guys. I got my answer. I just wanted to avoid a restart since I believe that the server will handle the application connectivity during that time of 5-10 sec than restarting.

When I say I have lots of issues with restarting the sql server service, this is what I meant. The application needs to access the server continiously and involves lots of configuration settings in case I wanna take it down, but I am aware of the effects a failover has, since I have done it in the past.