Microsoft Windows PowerShell and SQL Server 2005 WMI Providers – Part 1

by

Yan Pan

Windows Management Instrumentation (WMI) is Microsoft’s
primary technology for managing Windows systems. WMI allows system components
on Windows-based operating systems to be monitored and controlled, both locally
and remotely.

Windows
PowerShell is the new scripting language introduced by Microsoft. This admin-focused
language, with access to WMI, provides database administrators a powerful tool
to administer hundreds of servers with minimum administrative effort.

In this article series, I am going to illustrate how to
access the SQL Server 2005 WMI Providers using Window PowerShell and perform
administrative tasks.

Microsoft SQL Server 2005 has two WMI Providers: WMI
Provider for Configuration Management and WMI Provider for Server Events

The WMI provider for Configuration Management is a published
layer that is used with the SQL Server Configuration Manager snap-in for
Microsoft Management Console (MMC) and the Microsoft SQL Server Configuration
Manager. It means that you can do everything that you can do with the
Configuration Manager by writing a Windows PowerShell script to connect to this
provider. Part 1 of this series is going to show you how to manage SQL
Server services, client and server protocols, and server aliases, using Window PowerShell
and the WMI Provider for Configuration Management.

The WMI Provider for Configuration Management provides access
to WMI objects in the root\Microsoft\SqlServer\ComputerManagement namespace. The
Get-WmiObject cmdlet in Windows PowerShell is used to make the connection into
WMI providers. Since objects are instances of classes, let’s look at the
classes available under the root\Microsoft\SqlServer\ComputerManagement
namespace. The backstroke character (`) is used to concatenate the command line.

PS > Get-WmiObject `
  -namespace root\Microsoft\SqlServer\ComputerManagement -list
__SecurityRelatedClass	__NTLMUser9X
__PARAMETERS		__SystemSecurity
__NotifyStatus		__ExtendedStatus
__SystemClass		__Provider
__Win32Provider		__thisNAMESPACE
__IndicationRelated		__EventGenerator
__TimerInstruction		__IntervalTimerInstruction
__AbsoluteTimerInstruction	__Event
__NamespaceOperationEvent	__NamespaceDeletionEvent
__NamespaceCreationEvent	__NamespaceModificationEvent
__InstanceOperationEvent	__MethodInvocationEvent
__InstanceCreationEvent	__InstanceModificationEvent
__InstanceDeletionEvent	__ExtrinsicEvent
__SystemEvent		__EventDroppedEvent
__ConsumerFailureEvent	__QOSFailureEvent
__EventQueueOverflowEvent	__ClassOperationEvent
__ClassModificationEvent	__ClassCreationEvent
__ClassDeletionEvent		__TimerEvent
__AggregateEvent		__EventConsumer
__FilterToConsumerBinding	__TimerNextFiring
__EventFilter		__ProviderRegistration
__EventProviderRegistration	__EventConsumerProviderRegistration
__PropertyProviderRegistration	__ObjectProviderRegistration
__InstanceProviderRegistration	__ClassProviderRegistration
__MethodProviderRegistration	__NAMESPACE
ClientNetworkProtocol		ClientNetworkProtocolProperty
SqlServiceAdvancedProperty	ServerNetworkProtocolProperty
ClientSettingsGeneralFlag	SecurityCertificate
ServerSettingsGeneralFlag	SqlService
ClientNetLibInfo		ServerNetworkProtocolIPAddress
ServerNetworkProtocol		ClientSettings
SqlServerAlias		RegServices
ServerSettings	

The extension classes (the classes without the two
underscores prefix) are the classes we are interested in. They can be used to
instantiate the objects for SQL Server services, client and server protocols,
and server aliases.

Manage SQL Server services

The SqlService extension class shown above represents the
objects for SQL services.

To view a list of available services on the local computer,
type:

PS > Get-WmiObject `
	-namespace root\Microsoft\SqlServer\ComputerManagement `
	-class SqlService | Select-Object ServiceName, DisplayName, SQLServiceType, State
ServiceName	DisplayName			SQLServiceType	State
———–	———–			————–	—–
MsDtsServer	SQL Server Integration Ser…		4		1
MSFTESQL		SQL Server FullText Search…		3		1
MSSQLSERVER	SQL Server (MSSQLSERVER)		1		4
SQLBrowser		SQL Server Browser			7		1
SQLSERVERAGENT	SQL Server Agent (MSSQLSER…		2		1

In this example, the local computer has only the default
instance MSSQLSERVER installed. The command returns five service instances.
They present Integration Services, FullText Search Service for the default
instance, SQL Server Service, SQL Server Browser Service, and SQL Server Agent
Service. As you can see, each service corresponds to a different SQLServiceType.
The service types are defined in the table below.

The running state of each service is also represented by a
value.

As we can see above, out of the five services we got, only
the SQL Server Service is running. All the other four services are stopped.

To see all of the methods you can apply to the service
instances, type:

PS > Get-WmiObject `
	-namespace root\Microsoft\SqlServer\ComputerManagement `
	-class SqlService `
	-filter “ServiceName=’MSSQLSERVER'” | Get-Member -MemberType method
   TypeName: System.Management.ManagementObject#root\Microsoft\SqlServer\ComputerManagement\SqlService
Name			MemberType		Definition
—-			———-		———-
PauseService		Method		System …
ResumeService		Method		System …
SetServiceAccount		Method		System …
SetServiceAccountPassword	Method		System …
SetStartMode		Method		System …
StartService		Method		System …
StopService		Method		System …

We can use these methods to manage the state of the
services, and change the service accounts.

If we want to stop the SQL Server service, change its
service account from the local system account to a domain account mydomain\SqlService,
and then restart the service. A sample of such a script is shown here.

$strUserName = “mydomain\SqlService”
$strPassword= “password”
$sqlservice = Get-WmiObject `
	-namespace root\Microsoft\SqlServer\ComputerManagement `
	-class SqlService `
	-filter “ServiceName=’MSSQLSERVER'” 
$sqlservice.SetServiceAccount($strUserName, $strPassword) 
$sqlservice.StopService()
$sqlservice.StartService()

The script first uses Get-Wmiobject to create an object
associated with the default SQL Server service, MSSQLSERVER. Then it invokes
the SetServiceAccount method to change the service account to the domain
account mydomain\SqlService. After the service account has been changed, it
invokes the StopService and StartService methods to restart the SQL Server
service.

As mentioned earlier, WMI allows system components to be
controlled remotely. The -computerName flag is used to specify the name of the
remote computer to connect with. If we would like to change a named instance,
Instance1, on a remote computer, remoteserver, we can change the first line of
the above script to:

$sqlservice = Get-WmiObject `
	-computerName remoteserver `
	-namespace root\Microsoft\SqlServer\ComputerManagement `
	-class SqlService `
	-filter “ServiceName=’MSSQL`$Instance1′”

The backstroke character (`) is used to escape the $
character in the service name of the SQL Server named instance.

A complete sample script can be found in
ManageSQLServerService.ps1.

We can also change the start mode of a SQL Server service. For
example, by default, the SQL Server Browser Service is started manually for
default instances. If we want to start the SQL Server Browser automatically to
support dynamically assigned ports, we can run
the autostartSQLServerBrowser.ps1 script as shown here.

$strComputer = “.”
$sqlservice = Get-WmiObject `
	-computerName $strComputer `
	-namespace root\Microsoft\SqlServer\ComputerManagement `
	-class SqlService -filter “ServiceName=’SQLBrowser'”
$sqlservice.SetStartMode(2)

The parameter 2 corresponds to the auto start mode. The
table below lists the definitions.

Manage client network protocols

The ClientNetworkProtocol class represents the objects for
client network protocols. To see the list of protocols, type:

PS > Get-WmiObject `
	-namespace root\Microsoft\SqlServer\ComputerManagement `
	-class ClientNetworkProtocol  | Select-Object ProtocolName, ProtocolDisplayName, ProtocolOrder
ProtocolName	ProtocolDisplayName			ProtocolOrder
————	——————-			————-
sm		Shared Memory			1
tcp		TCP/IP				2
np		Named Pipes			3
via		VIA				0

The ProtocolOrder column specifies the order
number of the currently referenced client network protocol. In this example,
the Microsoft SQL Server clients on this computer first use the Shared Memory
protocol. If the protocol does not work (i.e., the connection is remote), then
the clients try the TCP/IP protocol, and the Named Pipes protocol. The zero
order of the VIA protocol means it is disabled.

To see all of the methods you can apply to the client
network protocol instances, type:

PS > Get-WmiObject `
	-namespace root\Microsoft\SqlServer\ComputerManagement `
	-class ClientNetworkProtocol | get-member -memberType Method | Select-Object Name 
   TypeName: System.Management.ManagementObject#root\Microsoft\SqlServer\ComputerManagement\ClientNetworkProtocol
Name
—-
GetNextOrderValue
SetDisable
SetEnable
SetOrderValue
SetProtocolsOrder

These methods can be used to disable/enable a protocol, or
change its order. For example, if we want to disable the named pipes protocol,
we use this command:

$clientprotocol=Get-WmiObject `
	-namespace root\Microsoft\SqlServer\ComputerManagement `
	-class ClientNetworkProtocol `
	-filter “ProtocolName=’np'”
$clientprotocol.SetDisable()

To verify the named pipes protocol is disabled, we can run
the previous command again.

PS > Get-WmiObject `
	-namespace root\Microsoft\SqlServer\ComputerManagement `
	-class ClientNetworkProtocol  | Select-Object ProtocolName, ProtocolDisplayName, ProtocolOrder
ProtocolName	ProtocolDisplayName		ProtocolOrder
————	——————-		————-
sm		Shared Memory			1
tcp		TCP/IP				2
np		Named Pipes			0
via		VIA				0

As we can see here, the order of the named pipes protocol
changes to zero, i.e., it is disabled. If we want to re-enable the named pipes
protocol, and place it above the TCP/IP protocol in the list, we can just run
this command:

$clientprotocol.SetOrderValue(2)

To verify the result, we can run the previous command again.

PS > Get-WmiObject `
	-namespace root\Microsoft\SqlServer\ComputerManagement `
	-class ClientNetworkProtocol  | Select-Object ProtocolName, ProtocolDisplayName, ProtocolOrder
ProtocolName	ProtocolDisplayName		ProtocolOrder
————	——————-		————-
sm		Shared Memory			1
tcp		TCP/IP				3
np		Named Pipes			2
via		VIA				0

There are settings specific to each protocol. For example,
the TCP/IP protocol must define a default listening port. The properties
specific to each protocol can be accessed through the ClientNetworkProtocolProperty
class.

PS > Get-WmiObject `
	-namespace root\Microsoft\SqlServer\ComputerManagement `
	-class ClientNetworkProtocolProperty | select PropertyName, ProtocolName
PropertyName			ProtocolName
————			————
Default Port			Tcp
KEEPALIVE (in milliseconds)		Tcp
KEEPALIVEINTERVAL (in milliseconds)	Tcp
Default Pipe			Np
Default Server Port			Via
Default Client NIC			Via

The TCP/IP protocol owns the first three properties. If we
want to change the default port of the TCP/IP protocol from 1433 to 7001, we
can run a script as shown here.

$protocolproperty=Get-WmiObject -namespace root\Microsoft\SqlServer\ComputerManagement -class ClientNetworkProtocolProperty `
 -filter “PropertyName=’Default Port'”
$protocolproperty.SetNumericalValue(7001)

This changes the default port to 7001.