Start, stop, pause, resume, restart SQL Server services
-
Article
-
01/03/2022
- 9 minutes to read
-
17 contributors
Is this page helpful?
Yes
No
Any additional feedback?
Feedback will be sent to Microsoft: By pressing the submit button, your feedback will be used to improve Microsoft products and services. Privacy policy.
Submit
Thank you.
In this article
Applies to: SQL Server (all supported versions)
This topic describes how to start, stop, pause, resume, or restart the SQL Server Database Engine, the SQL Server Agent, or the SQL Server Browser service by using SQL Server Configuration Manager, SQL Server Management Studio (SSMS), net commands from a command prompt, Transact-SQL, or PowerShell.
How should I start, stop or restart SQL services if it is in a cluster? Archived Forums >SQL Server High Availability and Disaster Recovery Question0 Sign in to vote Hi, For migration purpose I need to stop sql server services in a cluster. I don't want to failover the cluster I just want to stop the sql server services before the windows system admin shutdowns the windows server and later start again once migration is done. This is clone migration so I don't have to do anything except stopping and starting the services of sql. I found two places there to take service offline and online, one is just clicking the services and application and in the right hand side directly right clicking the instance and take service offline, another is expanding the instance and taking offline agent and sql server service one by one. I don't know which is the way. Please some one help me.
- Edited by
- Moved by Moved from "Database Engine" to a more related forum.
Answers0 Sign in to vote Hello, I would only use the cluster administrator or cmdlets for failoverclusters in powershell. Doing it through SSCM or any other local means will look as a failure of the service in a cluster as it will not communicate with the cluster resources correctly.
Sean Gallardy | Blog | Microsoft Certified Master - Proposed as answer by
- Marked as answer by
Answerer
All replies0 Sign in to vote Hello,
You can use SQL Server Configuration Manager, SQL Server Management Studio, Cluster Administrator.
Do not use the Services applet in Control Panel.
Hope this helps. Regards, Alberto Morillo
SQLCoffee.com 0 Sign in to vote Hello, I would only use the cluster administrator or cmdlets for failoverclusters in powershell. Doing it through SSCM or any other local means will look as a failure of the service in a cluster as it will not communicate with the cluster resources correctly.
Sean Gallardy | Blog | Microsoft Certified Master - Proposed as answer by
- Marked as answer by
Answerer 0 Sign in to vote Go into Cluster Administrator,Just take the sql resource offline and online, this will avoid the faiover and sql instance will get restarted...
Raju Rasagounder Sr MSSQL DBA 0 Sign in to vote Yes Raju, I found two places there one is directly offline the whole sql resource group and another is just expand the resource group and take only sql services like agent and sql server service (there are other services like drives, MSDTC and other services as well) offline. Which one Should I do? 0 Sign in to vote Yes Raju, I found two places there one is directly offline the whole sql resource group and another is just expand the resource group and take only sql services like agent and sql server service (there are other services like drives, MSDTC and other services as well) offline. Which one Should I do?
You don't need to take the entire resource group offline. Just take the SQL resource offline - this way you can have access to the DB files and other resources on the disks after SQL is offline
Satish Kartan www.sqlfood.com 0 Sign in to vote Stopping only SQL Service = take only SQL resource offline. If you take complete group offline, disks would not be accessible on the node.
Balmukund Lakhani
Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog | Team Blog | @Twitter | Facebook
Author: SQL Server 2012 AlwaysOn - Paperback, Kindle 0 Sign in to vote Here you go... always you have to take sql offline but anyway all other depend resources( disks ) will go offline... then in the same way you can bring online as well
Raju Rasagounder Sr MSSQL DBA
0 Sign in to vote Here you go... always you have to take sql offline but anyway all other depend resources( disks ) will go offline... then in the same way you can bring online as well
Raju Rasagounder Sr MSSQL DBA
Taking SQL offline will not cause Disks to go offline. SQL is dependent on Disks not other way around so Disk would stay online.
Balmukund Lakhani
Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog | Team Blog | @Twitter | Facebook
Author: SQL Server 2012 AlwaysOn - Paperback, Kindle 0 Sign in to vote Thanks Balmukund. But here if i offline sql server network name resource instead off sql server resource, will there be any issue as sql will also not visible. Regards
Manish
Stopping Service in ClusterRechana Rajan SSCertifiable Points: 7678 More actions May 9, 2017 at 3:53 am #325378 Stopped SQL service from Configuration manager in a cluster, is the failover supposed to happen? Will stopping SQL Server resource or the role from one node in cluster failover manager will initiate a failover? rvsc48 SSCertifiable Points: 7491 More actions May 9, 2017 at 7:18 am #1941778 Hi, Stopped SQL service from Configuration manager in a cluster, is the failover supposed to happen?
A: You should always use FCM (Failover Cluster Manager) to stop clustered services (roles). My guess is, if you stop the services in Config manager, a failover would not occur and act the same as if you used FCM, hopefully.
Will stopping SQL Server resource or the role from one node in cluster failover manager will initiate a failover?
A: No, simply stopping the role will only stop the services and should not initiate a failover. RandomEvent SSCrazy Points: 2963 More actions May 9, 2017 at 7:54 am #1941785 Will depend on the dependancies within the cluster. If that service is high in the dependency list which I would hope it is, then the failure or stopping of the service could be seen as serious enough to force the failover. RVSC48 is right though this sort of thing should be done from within the Cluster Management console. RandomEvent SSCrazy Points: 2963 More actions May 9, 2017 at 8:01 am #1941791 Hang on should have read that fully. So you stopped the SQL Service ? did you confirm which node was Active at the time you did this ? stopping the SQL Service on an Active node will always generate a failover if you have a clustered SQL Instance. Rechana Rajan SSCertifiable Points: 7678 More actions May 10, 2017 at 3:19 am #1941936 RandomEvent - Tuesday, May 9, 2017 8:01 AM Hang on should have read that fully. So you stopped the SQL Service ? did you confirm which node was Active at the time you did this ? stopping the SQL Service on an Active node will always generate a failover if you have a clustered SQL Instance.
You can stop service only on active node as the service is already in stopped state at passive node. Rechana Rajan SSCertifiable Points: 7678 More actions May 10, 2017 at 3:20 am #1941937 RVSC48 - Tuesday, May 9, 2017 7:18 AM Hi, Stopped SQL service from Configuration manager in a cluster, is the failover supposed to happen?
A: You should always use FCM (Failover Cluster Manager) to stop clustered services (roles). My guess is, if you stop the services in Config manager, a failover would not occur and act the same as if you used FCM, hopefully.
Will stopping SQL Server resource or the role from one node in cluster failover manager will initiate a failover?
A: No, simply stopping the role will only stop the services and should not initiate a failover.
Thanks RVSC48, So only scenario where the failover occurs is when the whole server is down? rvsc48 SSCertifiable Points: 7491 More actions May 10, 2017 at 7:34 am #1941993 So only scenario where the failover occurs is when the whole server is down?
A: I wouldn't say only. You can always manually failover, but, for automatic failovers, it's usually some issue at the OS level on the node thatcan trigger a failover. There have been many times that you have to scan the event logs on the nodes and review the cluster manager log to determine the reason foran auto failover.And of course, with a cluster, your single point of failure for a cluster is your storage due to (if)being shared. Rechana Rajan SSCertifiable Points: 7678 More actions May 11, 2017 at 12:02 am #1942119 RVSC48 - Wednesday, May 10, 2017 7:34 AM So only scenario where the failover occurs is when the whole server is down?
A: I wouldn't say only. You can always manually failover, but, for automatic failovers, it's usually some issue at the OS level on the node thatcan trigger a failover. There have been many times that you have to scan the event logs on the nodes and review the cluster manager log to determine the reason foran auto failover.And of course, with a cluster, your single point of failure for a cluster is your storage due to (if)being shared.
Thanks again RVSC48 Perry Whittle SSC Guru Points: 233792 More actions May 11, 2017 at 7:13 am #1942157 Rechana Rajan - Tuesday, May 9, 2017 3:53 AM Stopped SQL service from Configuration manager in a cluster, is the failover supposed to happen? Will stopping SQL Server resource or the role from one node in cluster failover manager will initiate a failover?
Out of the box, stopping the clustered sql server service from either FCM or SSCM will not initiate a failover.
Can you provide more detail on the failover policy configuration from the cluster group and its resources please, have they been changed?
Check your sql server error log and the cluster events for more information too ----------------------------------------------------------------------------------------------------------- "Ya can't make an omelette without breaking just a few eggs" 😉 Rechana Rajan SSCertifiable Points: 7678 More actions June 7, 2017 at 3:45 am #1945750 Perry Whittle - Thursday, May 11, 2017 7:13 AM Rechana Rajan - Tuesday, May 9, 2017 3:53 AM Stopped SQL service from Configuration manager in a cluster, is the failover supposed to happen? Will stopping SQL Server resource or the role from one node in cluster failover manager will initiate a failover?
Out of the box, stopping the clustered sql server service from either FCM or SSCM will not initiate a failover.
Can you provide more detail on the failover policy configuration from the cluster group and its resources please, have they been changed?
Check your sql server error log and the cluster events for more information too
Thanks Perry. I have changed the policy in FCM
If Resource Fails,attempt restart on current node- to 1 minute and I turned off the SQL Service from SQL Configuration Manager to Test whether it will come online after 1 min but it didn't. Perry Whittle SSC Guru Points: 233792 More actions June 7, 2017 at 4:05 am #1945753 Rechana Rajan - Wednesday, June 7, 2017 3:45 AM Perry Whittle - Thursday, May 11, 2017 7:13 AM Rechana Rajan - Tuesday, May 9, 2017 3:53 AM Stopped SQL service from Configuration manager in a cluster, is the failover supposed to happen? Will stopping SQL Server resource or the role from one node in cluster failover manager will initiate a failover?
Out of the box, stopping the clustered sql server service from either FCM or SSCM will not initiate a failover.
Can you provide more detail on the failover policy configuration from the cluster group and its resources please, have they been changed?
Check your sql server error log and the cluster events for more information too
Thanks Perry. I have changed the policy in FCM
If Resource Fails,attempt restart on current node- to 1 minute and I turned off the SQL Service from SQL Configuration Manager to Test whether it will come online after 1 min but it didn't.
stopping a clustered instance of sql server is supported either via FCM or SCM, it is not supported via services.msc ----------------------------------------------------------------------------------------------------------- "Ya can't make an omelette without breaking just a few eggs" 😉 Rechana Rajan SSCertifiable Points: 7678 More actions June 7, 2017 at 8:12 am #1945822 Thanks perry Perry Whittle - Wednesday, June 7, 2017 4:05 AM Rechana Rajan - Wednesday, June 7, 2017 3:45 AM Perry Whittle - Thursday, May 11, 2017 7:13 AM Rechana Rajan - Tuesday, May 9, 2017 3:53 AM Stopped SQL service from Configuration manager in a cluster, is the failover supposed to happen? Will stopping SQL Server resource or the role from one node in cluster failover manager will initiate a failover?
Out of the box, stopping the clustered sql server service from either FCM or SSCM will not initiate a failover.
Can you provide more detail on the failover policy configuration from the cluster group and its resources please, have they been changed?
Check your sql server error log and the cluster events for more information too
Thanks Perry. I have changed the policy in FCM
If Resource Fails,attempt restart on current node- to 1 minute and I turned off the SQL Service from SQL Configuration Manager to Test whether it will come online after 1 min but it didn't.
stopping a clustered instance of sql server is supported either via FCM or SCM, it is not supported via services.msc [/quote] ThanksPerry.
As Imentionedearlier Ididitfrom SCM Perry Whittle SSC Guru Points: 233792 More actions June 7, 2017 at 10:03 am #1945851 Rechana Rajan - Wednesday, June 7, 2017 8:12 AM Thanks perry Perry Whittle - Wednesday, June 7, 2017 4:05 AM Rechana Rajan - Wednesday, June 7, 2017 3:45 AM Perry Whittle - Thursday, May 11, 2017 7:13 AM Rechana Rajan - Tuesday, May 9, 2017 3:53 AM Stopped SQL service from Configuration manager in a cluster, is the failover supposed to happen? Will stopping SQL Server resource or the role from one node in cluster failover manager will initiate a failover?
Out of the box, stopping the clustered sql server service from either FCM or SSCM will not initiate a failover.
Can you provide more detail on the failover policy configuration from the cluster group and its resources please, have they been changed?
Check your sql server error log and the cluster events for more information too
Thanks Perry. I have changed the policy in FCM
If Resource Fails,attempt restart on current node- to 1 minute and I turned off the SQL Service from SQL Configuration Manager to Test whether it will come online after 1 min but it didn't.
stopping a clustered instance of sql server is supported either via FCM or SCM, it is not supported via services.msc
ThanksPerry.
As Imentionedearlier Ididitfrom SCM [/quote]
Should be fine then ----------------------------------------------------------------------------------------------------------- "Ya can't make an omelette without breaking just a few eggs" 😉 Rechana Rajan SSCertifiable Points: 7678 More actions June 8, 2017 at 2:43 am #1945945 Perry Whittle - Wednesday, June 7, 2017 10:03 AM Rechana Rajan - Wednesday, June 7, 2017 8:12 AM Thanks perry Perry Whittle - Wednesday, June 7, 2017 4:05 AM Rechana Rajan - Wednesday, June 7, 2017 3:45 AM Perry Whittle - Thursday, May 11, 2017 7:13 AM Rechana Rajan - Tuesday, May 9, 2017 3:53 AM Stopped SQL service from Configuration manager in a cluster, is the failover supposed to happen? Will stopping SQL Server resource or the role from one node in cluster failover manager will initiate a failover?
Out of the box, stopping the clustered sql server service from either FCM or SSCM will not initiate a failover.
Can you provide more detail on the failover policy configuration from the cluster group and its resources please, have they been changed?
Check your sql server error log and the cluster events for more information too
Thanks Perry. I have changed the policy in FCM
If Resource Fails,attempt restart on current node- to 1 minute and I turned off the SQL Service from SQL Configuration Manager to Test whether it will come online after 1 min but it didn't.
stopping a clustered instance of sql server is supported either via FCM or SCM, it is not supported via services.msc
ThanksPerry.
As Imentionedearlier Ididitfrom SCM
Should be fine then[/quote] Thanks Perry. If I do simulate failure from FCM it comes online immediately in same node,but if i stop SQL service from SQL Server Configuration manager it wont come online after 1 minute nor its doing a failover. Perry Whittle SSC Guru Points: 233792 More actions June 8, 2017 at 3:24 am #1945954 Rechana Rajan - Thursday, June 8, 2017 2:43 AM Perry Whittle - Wednesday, June 7, 2017 10:03 AM Rechana Rajan - Wednesday, June 7, 2017 8:12 AM Thanks perry Perry Whittle - Wednesday, June 7, 2017 4:05 AM Rechana Rajan - Wednesday, June 7, 2017 3:45 AM Perry Whittle - Thursday, May 11, 2017 7:13 AM Rechana Rajan - Tuesday, May 9, 2017 3:53 AM Stopped SQL service from Configuration manager in a cluster, is the failover supposed to happen? Will stopping SQL Server resource or the role from one node in cluster failover manager will initiate a failover?
Out of the box, stopping the clustered sql server service from either FCM or SSCM will not initiate a failover.
Can you provide more detail on the failover policy configuration from the cluster group and its resources please, have they been changed?
Check your sql server error log and the cluster events for more information too
Thanks Perry. I have changed the policy in FCM
If Resource Fails,attempt restart on current node- to 1 minute and I turned off the SQL Service from SQL Configuration Manager to Test whether it will come online after 1 min but it didn't.
stopping a clustered instance of sql server is supported either via FCM or SCM, it is not supported via services.msc
ThanksPerry.
As Imentionedearlier Ididitfrom SCM
Should be fine then Thanks Perry. If I do simulate failure from FCM it comes online immediately in same node,but if i stop SQL service from SQL Server Configuration manager it wont come online after 1 minute nor its doing a failover.[/quote]
What policy settings do you have set for the group and the network name resource ----------------------------------------------------------------------------------------------------------- "Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 21 total) You must be logged in to reply to this topic. Login to reply How to stop and start SQL Server servicesBy: Daniel Farina | Updated: 2020-02-06 | Comments (2) | Related: More > Database Administration
Free MSSQLTips Webinar: How to Avoid Worst Practices as a SQL Server DBALearn from 20 years of experience about how to turn SQL Server Worst Practices such as database corruption, security, scaling, monitoring, cloud cost management, and SQL Agent monitoring into Best Practices to properly manage your database environment.
ProblemThere are often times that you need to stop and start SQL Server services, so in this tip we will look at various methods of doing this. SolutionHandling SQL Server services can be a challenging task for people other than experienced SQL Server DBAs. If you are a DBA you may think that I am exaggerating, but I have seen along my career cases of sysadmins and even SQL Server developers that don’t know how to start and stop SQL Server services. In most of the cases, they don’t know which of the SQL Server related services is the one they need to start. Five Different Methods To Start, Stop, And Restart SQL Server ServicesHomeTools & technologiesFive Different Methods To Start, Stop, And Restart SQL Server Services When we install the SQL Server, it also installs the following services: - SQL Server database engine service to manage and access data in SQL Server.
- SQL Server Integration Service for the ETL process – we install it when including Integration services during setup.
- SQL Server Reporting Service to manage the SQL Server reports.
- The SQL Server Agent Service. Note that it is not available in the SQL Server Express edition.
In this article, we are going to learn the following methods to manage SQL Server services: - Start, stop, and restart SQL Server services using the SQL Server configuration manager.
- Start, stop, and restart SQL Server services using Services MMC (Microsoft Management Console).
- Start, stop, and restart SQL Server services using the PowerShell script.
- Start, stop, and restart SQL Server services in Ubuntu Linux.
- Start, stop, and restart SQL Server services using the Windows server failover cluster manager.
Let us examine all these methods. How to stop a clustered instance of SQL Server (Service Manager)SQL Server Setup Helpprevious page next page SQL Server Setup HelpHow to stop a clustered instance of SQL Server (Service Manager)To stop a clustered instance of SQL Server - Type the name of the virtual Microsoft® SQL Server™ in the Server box. If it is a default instance, you only need to specify the virtual server name. If it is a named instance, you must enter VIRTUALSERVER\Instance.
- In the Services box, click SQL Server.
- Click Stop. This pauses the cluster resource, and then stops the SQL Server service, which does not cause a failover of SQL Server.
See AlsoStopping SQL Server previous page start next page
|
