Managing SQL Server Services with WMI Provider

The WMI Provider for Configuration Management provides access to WMI objects in the root\Microsoft\SqlServer\ComputerManagement namespace for SQL Server 2005,and the root\Microsoft\SqlServer\ComputerManagement10 namespace for SQL Server 2008.

To list all the classes under the namespace:

Get-WmiObject -namespace root\Microsoft\SqlServer\ComputerManagement10 –list | Where-Object {-not ($_.Name -like '___*')}       2008

 

To view a list of available services of SqlService class on your local computer:

Get-WmiObject -namespace root\Microsoft\SqlServer\ComputerManagement10 -class SqlService | Select-Object ServiceName, DisplayName, SQLServiceType, State,ProcessId | Format-Table -wrap

 

To see all the methods that you can apply to the service instances:

Get-WmiObject -namespace root\Microsoft\SqlServer\ComputerManagement10 -class SqlService | Get-Member -MemberType method

 

To change the SQL Server service account from the local system account to a domain account, PowerDomain\SqlService, and then restart the service for the change to take effect:

$strUserName = "PowerDomain\SqlService"
# Password for the PowerDomain\SqlService account
$strPassword= "P@ssw0rd"
$sqlservice = Get-WmiObject –namespace root\Microsoft\SqlServer\ComputerManagement10 -class SqlService –filter "ServiceName=’MSSQLSERVER’"
$sqlservice.SetServiceAccount($strUserName, $strPassword)
$sqlservice.StopService()
$sqlservice.StartService()

To change a named instance called CH0DE1 on a remote computer DEMOPC. You can change the first line of the preceding script as follows:

$sqlservice = Get-WmiObject –computerName DEMOPC –namespace root\Microsoft\SqlServer\ComputerManagement10 -class SqlService –filter "ServiceName=’MSSQL`$CH0DE1’"

 

To change the start mode from manually to automatically,you can run the AutostartSQLServerAgent.ps1 script shown here:

$strComputer = "."
$sqlservice = Get-WmiObject –computerName $strComputer –namespace
root\Microsoft\SqlServer\ComputerManagement10 `
-class SqlService –filter "ServiceName=’SQLSERVERAGENT’"
$sqlservice.SetStartMode(2)

Start Mode      Description
2                     Service is started automatically
3                     Service is started manually
4                     Service is disabled