2

I'm trying to change SQL server Configuration manager settings using Microsoft.SqlServer.Management.Smo.Wmi.Service. Microsoft provided with a list of methods & properties here. I used the following code to change the Service account of a particular service:

$MyServerObject.SetServiceAccount($UName, $PWord)

because there exists a method called SetServiceAccount. But I do not understand/know how to set StartMode which a property. I know that currently my service says it is automatic,coz I can verify it using this line of code

$MyServerObject.StartMode

and the result is: Auto

Anybody knows how to set this property?

If someone wants to see what I'm talking about run these lines in PowerShell (Run as Administrator) on a computer where SQL Server is installed:

#Load assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null

#Create Server Object
$SMOWmiserver = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer')

#Query Server object
$SMOWmiserver.Services | 
    select name, type, ServiceAccount, DisplayName, Properties, StartMode, StartupParameters | 
        Format-List

#Select a service
$MyServerObject=$SMOWmiserver.Services | where {$_.name -eq "MSSQLSERVER"}
$MyServerObject.StartMode
2

Hopefully I can answer your question and explain how I did so.

To alter the service start mode using your code above you run

# Check Start Mode
$MyServerObject.StartMode
# Set Start Mode
$MyServerObject.StartMode ='Manual'
# Alter Start Mode
$MyServerObject.Alter()
# Check Start Mode again
$MyServerObject.StartMode

You can establish by using Get-Member as shown below

$MyServerObject|Get-Member

In the list of results you will see

StartMode Property Microsoft.SqlServer.Management.Smo.Wmi.ServiceStartMode StartMode {get;set;}

The {get;set;} shows that you can alter the property

You will also see

Alter Method void Alter(), void IAlterable.Alter()

This is the method that you will use to apply the change to the object.

You can use the same principle throughout your PowerShell use.

It is also possible to do this remotely, if you have the correct privileges for your account, to do the following

# Get Service                                         
$Service = Get-WmiObject win32_service -ComputerName COMPUTERNAME -Filter "Name = 'MSSQLSERVER'"            
# Get Start Mode                         
$Service.StartMode     
# Change Start Mode                                                                                                                
$Service.ChangeStartMode('Manual') 
#Check Start Mode                                                                                                                    
$Service.StartMode                                                                                                                     

to achieve the same result

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.