My Technical Notes

Sunday, 2 April 2017

SQL Server 2016 Services on Windows Professional

Installing SQL Server 2016 on Windows 10 Professional will result in a prethora of services being installed, as can be seen in the installer window:

Many of these services have their start type set to `Automatic`, which means that they will startup with Windows. However, you might not need SQL Server running in the background all the time.

Below, I am using PowerShell to change the startup configuration of these services.

Listing all SQL Server Services   The following code will get all services whose name starts with "SQL":


Get-Service | ? { $_.DisplayName -match '^SQL' }

which results in the following data (I've used `Format-Table -AutoSize` to make it prettier):


Status  Name                                     DisplayName                                      
------  ----                                     -----------                                      
Running MsDtsServer130                           SQL Server Integration Services 13.0             
Running MSOLAP$SQL2016                           SQL Server Analysis Services (SQL2016)           
Running MSSQL$SQL2016                            SQL Server (SQL2016)                             
Running MSSQLFDLauncher$SQL2016                  SQL Full-text Filter Daemon Launcher (SQL2016)   
Running MSSQLLaunchpad$SQL2016                   SQL Server Launchpad (SQL2016)                   
Running ReportServer$SQL2016                     SQL Server Reporting Services (SQL2016)          
Stopped SQL Server Distributed Replay Client     SQL Server Distributed Replay Client             
Stopped SQL Server Distributed Replay Controller SQL Server Distributed Replay Controller         
Stopped SQLAgent$SQL2016                         SQL Server Agent (SQL2016)                       
Running SQLBrowser                               SQL Server Browser                               
Stopped SQLPBDMS$SQL2016                         SQL Server PolyBase Data Movement (SQL2016)      
Stopped SQLPBENGINE$SQL2016                      SQL Server PolyBase Engine (SQL2016)             
Running SQLTELEMETRY$SQL2016                     SQL Server CEIP service (SQL2016)                
Running SQLWriter                                SQL Server VSS Writer                            
Running SSASTELEMETRY$SQL2016                    SQL Server Analysis Services CEIP (SQL2016)      
Running SSISTELEMETRY130                         SQL Server Integration Services CEIP service 13.0

Listing SQL Services that start automatically   Here is how to list SQL services that start automatically with Windows:


Get-Service | 
    ? { $_.DisplayName -match '^SQL' -and $_.StartType -eq 'Automatic' } | 
    select Name, DisplayName, Status, StartType

which returns:


Name                   DisplayName                                        Status StartType
----                   -----------                                        ------ ---------
MsDtsServer130         SQL Server Integration Services 13.0              Running Automatic
MSOLAP$SQL2016         SQL Server Analysis Services (SQL2016)            Running Automatic
MSSQL$SQL2016          SQL Server (SQL2016)                              Running Automatic
MSSQLLaunchpad$SQL2016 SQL Server Launchpad (SQL2016)                    Running Automatic
ReportServer$SQL2016   SQL Server Reporting Services (SQL2016)           Running Automatic
SQLBrowser             SQL Server Browser                                Running Automatic
SQLPBDMS$SQL2016       SQL Server PolyBase Data Movement (SQL2016)       Running Automatic
SQLPBENGINE$SQL2016    SQL Server PolyBase Engine (SQL2016)              Stopped Automatic
SQLTELEMETRY$SQL2016   SQL Server CEIP service (SQL2016)                 Running Automatic
SQLWriter              SQL Server VSS Writer                             Running Automatic
SSASTELEMETRY$SQL2016  SQL Server Analysis Services CEIP (SQL2016)       Running Automatic
SSISTELEMETRY130       SQL Server Integration Services CEIP service 13.0 Running Automatic

Setting Automatically Starting SQL Services to Manual Startup   Before changing the startup types of any SQL services, you should first take a backup of how these services are configured to start up. Do:


Get-Service | 
    ? { $_.DisplayName -match '^SQL' } | 
    select Name, DisplayName, Status, StartType | 
    Export-Csv "SqlServerServices.csv"

As with any other backup procedure, keep this CSV file safe, as you may need to restore the default startup configuration of these services.

To change all the above services to not start up with Windows, i.e. make it's startup `Manual`, run the following code (as Administrator):


Get-Service | 
    ? { $_.DisplayName -match '^SQL' -and $_.StartType -eq 'Automatic' } | 
    Set-Service -StartupType Manual

Restoring Automatically Starting SQL Services   Read the CSV file, then use `Set-Service` to set the startup type back to `Automatic` (only for those services whose startup type was `Automatic` to begin with). Assuming that `SqlServerServices.csv` is in the working directory, do:


Import-Csv .\SqlServerServices.csv | 
    ? { $_.StartType -eq 'Automatic' } |
    % Name | 
    Get-Service | 
    Set-Service -StartupType Automatic

No comments: