PowerShell: SQL Server Services Startup Mode Change

This is one of those extremely simple posts where everyone who does PowerShell knows how ridiculously simple it is to do and you will too today. You will look like a magician to the uninitiated.

Today, I had about 5 hosts running 4 instances of SQL Server each and I had to

  1. Stop all SQL Server related services
  2. Change the StartupType of each SQL related service to “Disabled”

This is in preparation for decommissioning the servers after a period of time in this state.

If you are thinking about RDP’ing into the servers, you are certainly thinking it wrong. With PowerShell, we are taking about saving hundred or more mouse clicks, at least.

Listing SQL Server Services:

To list all the Windows Services related to SQL Server, simply issue

Get-Service *sql*

This should give you back something like below:

PS C:\Users\MyWinID> get-service *sql*

Status   Name               DisplayName
------   ----               -----------
Running  MSSQL$Inst01      SQL Server (Inst01)
Stopped  MSSQL$Inst02      SQL Server (Inst02)
Stopped  MSSQL$Inst03      SQL Server (Inst03)
Stopped  MSSQL$Inst04      SQL Server (Inst04)
Running  SQLAgent$Inst01   SQL Server Agent (Inst01)
Stopped  SQLAgent$Inst02   SQL Server Agent (Inst02)
Stopped  SQLAgent$Inst03   SQL Server Agent (Inst03)
Stopped  SQLAgent$Inst04   SQL Server Agent (Inst04)
Running  SQLBrowser         SQL Server Browser
Running  SQLWriter          SQL Server VSS Writer

If you are doing this from another host, just include the ComputerName

Get-Service -ComputerName MySQLServerHost *sql*

Stopping SQL Server Services:

All the above services are the ones I need to stop. To do so, you simply pipe all the SQL Services to “Stop-Service” commandlet!

Get-Service *sql* | Stop-Service -Force

Run this a few times if there dependent services that are not getting stopped. After a few attempts, all SQL Server services should be stopped. Do you see how simple it is?!

Again, include the “ComputerName” parameter if you are doing it from your own machine and the SQL Server host is different.

Verify that all the services are stopped:

Get-Service *sql*

Change the StartupType of SQL Server Services:

After stopping all the services, I want to set the “StartupType” of the SQL Services to “Disabled” (you could set it to Manual/Automatic/Disabled).

To make the change to the services, I do this:

Get-Service *SQL* | Set-Service –StartupType Disabled

..and verify that they did get changed:

Get-Service *SQL* | Select-Object *

The above code selects all the properties of the service instead of just the key/default ones that PowerShell decides to show.

You could also do something like:

Get-Service *SQL*  |
   Select-Object Name, Status, StartType |
   Format-Table -AutoSize

Notice above where we select just the properties we want to confirm!

Name              Status StartType
----              ------ ---------
MSSQL$Inst01    Stopped  Disabled
MSSQL$Inst02    Stopped  Disabled
MSSQL$Inst03    Stopped  Disabled
MSSQL$Inst04    Stopped  Disabled
SQLAgent$Inst01 Stopped  Disabled
SQLAgent$Inst02 Stopped  Disabled
SQLAgent$Inst03 Stopped  Disabled
SQLAgent$Inst04 Stopped  Disabled
SQLBrowser       Stopped  Disabled
SQLWriter        Stopped  Disabled

Everything Together For a Remote Host!

That’s it! Let us see all the commands together had I run it from my Laptop going against a different server host.

#List the SQL Server Services and their state
Get-Service -ComputerName MySQLServerHost *sql* 

#Stop the SQL Server Services
Get-Service -ComputerName MySQLServerHost *sql* |
Stop-Service -Force

#Verify if the SQL Server Services got stopped
Get-Service -ComputerName MySQLServerHost *sql* 

#Change the StartupType to Disabled (could be Manual/Automatic/Disabled)
Get-Service -ComputerName MySQLServerHost *sql*  |
    Set-Service –StartupType Disabled

#Verify that the StartupType changed
Get-Service -ComputerName MySQLServerHost *SQL*  |
    Select-Object Name, Status, StartType |
    Format-Table -AutoSize

Checking multiple hosts at once:

If you wanted to check the status of all the hosts on which this change was made, we don’t even have to replace the text each time. We just pipe array of hosts to the service status check. Notice the ForEach-Object which loops through and the “$_” that substitutes the current value of the loop inside.

@('MyHost1','MyHost2','MyHost3','MyHost4','MyHost5') |
ForEach-Object {
   Get-Service -ComputerName $_ *SQL* |
   Select-Object Name, Status, StartType
} |
Format-Table -AutoSize

You would get back the list for all 4 instances in the 5 hosts like this:

Name              Status StartType
----              ------ ---------
MSSQL$DEVINST01    Stopped  Disabled
MSSQL$DEVINST02    Stopped  Disabled
MSSQL$DEVINST03    Stopped  Disabled
MSSQL$DEVINST04    Stopped  Disabled
SQLAgent$DEVINST01 Stopped  Disabled
SQLAgent$DEVINST02 Stopped  Disabled
SQLAgent$DEVINST03 Stopped  Disabled
SQLAgent$DEVINST04 Stopped  Disabled
SQLBrowser       Stopped  Disabled
SQLWriter        Stopped  Disabled
MSSQL$QAINST01    Stopped  Disabled
MSSQL$QAINST02    Stopped  Disabled
MSSQL$QAINST03    Stopped  Disabled
MSSQL$QAINST04    Stopped  Disabled
SQLAgent$QAINST01 Stopped  Disabled
SQLAgent$QAINST02 Stopped  Disabled
SQLAgent$QAINST03 Stopped  Disabled
SQLAgent$QAINST04 Stopped  Disabled
SQLBrowser       Stopped  Disabled
SQLWriter        Stopped  Disabled
MSSQL$PRODINST01    Stopped  Disabled
MSSQL$PRODINST02    Stopped  Disabled
MSSQL$PRODINST03    Stopped  Disabled
MSSQL$PRODINST04    Stopped  Disabled
SQLAgent$PRODINST01 Stopped  Disabled
SQLAgent$PRODINST02 Stopped  Disabled
SQLAgent$PRODINST03 Stopped  Disabled
SQLAgent$PRODINST04 Stopped  Disabled
SQLBrowser       Stopped  Disabled
SQLWriter        Stopped  Disabled
MSSQL$BIINST01    Stopped  Disabled
MSSQL$BIINST02    Stopped  Disabled
MSSQL$BIINST03    Stopped  Disabled
MSSQL$BIINST04    Stopped  Disabled
SQLAgent$BIINST01 Stopped  Disabled
SQLAgent$BIINST02 Stopped  Disabled
SQLAgent$BIINST03 Stopped  Disabled
SQLAgent$BIINST04 Stopped  Disabled
SQLBrowser       Stopped  Disabled
SQLWriter        Stopped  Disabled
MSSQL$DWINST01    Stopped  Disabled
MSSQL$DWINST02    Stopped  Disabled
MSSQL$DWINST03    Stopped  Disabled
MSSQL$DWINST04    Stopped  Disabled
SQLAgent$DWINST01 Stopped  Disabled
SQLAgent$DWINST02 Stopped  Disabled
SQLAgent$DWINST03 Stopped  Disabled
SQLAgent$DWINST04 Stopped  Disabled
SQLBrowser       Stopped  Disabled
SQLWriter        Stopped  Disabled

There you go folks! Hope you will save a ton of time doing this in the future. If you needed to start all the services back up and change the mode, just pipe to Start-Service instead of Stop-Service and also set the Set-Service mode to Automatic!

Conclusion:

This is not limited to SQL Server and can be done with any Windows Service. You can apply these simple ideas starting right now! You will look like a magician to the uninitiated.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s