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.

Type

Definition

1

MSSQLSERVER is the SQL Server service.

2

SQLSERVERAGENT is the SQL Server Agent service.

3

MSFTESQL is the SQL Server Full-text Search Engine
service.

4

MsDtsServer is the SQL Server SSIS Server service.

5

MSSQLServerOLAPService is the SQL Server Analysis Server
service.

6

ReportServer is the SQL Server Reporting Services service.

7

SQLBrowser is the SQL Server Browser service.

8

NsService is the SQL Server Notification Services service.

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

State

Definition

1

Stopped. The service is stopped.

2

Start Pending. The service is waiting to start.

3

Stop Pending. The service is waiting to stop.

4

Running. The service is running.

5

Continue Pending. The service is waiting to continue.

6

Pause Pending. The service is waiting to pause.

7

Paused. The service is paused.

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.

Start
Mode

Definition

2

Service
is started automatically.

3

Service
is started manually.

4

Service
is disabled.

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.

Latest Articles