Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Nov 20, 2007

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

By DatabaseJournal.com Staff

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM