Correct way to stop SQL Server (through automation)

Question

Question

• Is it safe to start & stop SQL's services using PowerShell's Start-Service and Stop-Service commands?
• If not, is there a recommended way to start & stop SQL's services through automation?

Additional Info

We're in the process of creating scripts to bring down our systems cleanly before maintenance windows, then to bring them back up afterwards. By cleanly I mean it stops the application's services, then stops the database services (i.e. in dependency order), setting all to DISABLED, so that the infrastructure team can apply windows updates / perform any work, safely rebooting servers as required, before running the scripts to bring the system back up in the correct (dependency aware) order.

Our DBA mentioned that the correct way to stop services is through SQL's Configuration Manager; implying that that's different to just using services.msc (or for automation, powershell's stop-service / command line's sc \\someServer stop someService.

• The Note at the top of this article implies something similar: https://technet.microsoft.com/en-us/library/ms175516(v=sql.105).aspx
• This article points out that SQL Server Configuration Manager has some advantages for some functions: https://msdn.microsoft.com/en-us/library/ms174212.aspx

However I've not been able to find anything explicitly saying it's incorrect to start/stop services through the regular means, or what extra protection/functionality SQL Configuration Manager gives with regards to simply starting & stopping services.

Answer 1

You are safe either which way you go. SERVICES.MSC and POWERSHELL command lines are EQUAL functions to MSSQL Configuration manager...

Configuration manager is also used to control instances, etc etc but the function of starting and stopping a service is the same no matter which method you choose.

Some people have their own ways of doing things, for example to quit an application, you can do File > Exit. Or you can do ALT+F4, they are both the same function...

Please view this link below for additional information and helpful details. This covers all of the different methods (SQL configuration mgr, SSMS, Commandline etc)

https://msdn.microsoft.com/en-us/library/hh403394.aspx

UPDATE regarding Failover Cluster Instance...

If the instance is clustered, use of any of the above suggestions does not apply and should not be used. For a FCI you need to utilize Cluster Manager or the FailoverClusters PowerShell module commands.

Answer 2

The recommended way is to always use configuration manager for two reasons:

i) If you want to change the password, using configuration manager you don't need to restart sql engine or agent services for the new password to take in effect. More detail can be found here.

ii) If you are managing a sql cluster and try to modify the password through services.msc it will failover to another node during restart of node1, sql configuration manager avoids this.

That being said, i don't have a script to do all the tasks through configuration manager, would be interesting to know if someone has that :).

Answer 3

From Microsoft SQL server Configuration Tools and right click sql server services and stop.

OR Using Transact-SQL

SHUTDOWN;

To stop the Database Engine immediately, execute the following statement. SHUTDOWN WITH NOWAIT;

See the link Start, Stop, Pause, Resume, Restart the Database Engine