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

Automate SQL server restart in a clustered environment Expand / Collapse
Author
Message
Posted Monday, August 18, 2014 9:10 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 10:07 AM
Points: 57, Visits: 336
All,

I need to automate SQL server restart(daily due to performance issue- outside sql) in a clustered environment. What is the best way to accomplish this. If anyone can provide code/commands that would be great!
Environment:

Windows server 2008
Sql 2005 - 2 node (active/passive)

Many thanks in advance!
Post #1604505
Posted Monday, August 18, 2014 9:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:32 AM
Points: 5,369, Visits: 9,922
I recommend you investigate the reasons behind your performance issue before implementing this workaround. If you must do it, you can control the cluster from a command line - see this. I'm sure there are equivalent PowerShell commands as well, if you prefer that.

John
Post #1604509
Posted Monday, August 18, 2014 10:17 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 10:07 AM
Points: 57, Visits: 336
John: Thanks for your input!
Post #1604547
Posted Tuesday, August 19, 2014 3:55 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 19, 2014 2:46 AM
Points: 2,879, Visits: 3,229
If you really need to restart your SQL Server clustered instance, you need to use the cluster commands to put the SQL cluster group offline, then put it back online.

If you just stop SQL Server using a NET STOP command you may cause a failover to happen.

But John has good advice - you should find out why you think you need to restart SQL Server. The problem can almost certainly be solved without the need to restart SQL Server.


Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1604818
Posted Wednesday, August 20, 2014 5:48 AM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:27 PM
Points: 6,381, Visits: 13,724
John Mitchell-245523 (8/18/2014)
I recommend you investigate the reasons behind your performance issue before implementing this workaround. If you must do it, you can control the cluster from a command line - see this. I'm sure there are equivalent PowerShell commands as well, if you prefer that.

John

Since this is Windows 2008, the cluster.exe documentation is useless. The Powershell commands are

To import the Failovercluster Powershell module
import-module failoverclusters

List cluster groups
get-clustergroup


Stop a cluster group
stop-clustergroup "groupname"

Start a cluster group
start-clustergroup "groupname"

Rather than stopping the whole group just stop the SQL Server resource (takes the agent down too)
Get the resources using
get-clusterresource | ?{$_.resourcetype -match "SQL Server"}

Stop the sql server resource using
stop-clusterresource "sql server resource name"

Start SQL server resource by starting the agent first
start-clusterresource "sql agent resource name"



-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1605377
Posted Wednesday, August 20, 2014 7:56 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 10:07 AM
Points: 57, Visits: 336
Thanks Perry!
Post #1605448
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse