Microsoft Windows PowerShell and SQL Server 2005 WMI Providers - Part 1November 20, 2007 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 servicesThe 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 protocolsThe 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. |
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
