SQL Server 2005 introduces a new WMI Provider that allows configuration of Services, Client Network Protocols, and Server Protocols. This is a great improvement for DBA that are managing many SQL Servers, as WMI allows you to query and modify SQL Server configuration settings remotely.

Now, everyone who has worked with WMI knows it's not easy to write a WMI program. You need to know the namespace, class names, etc. Everything that helps developers so well in VS (IntelliSense, object browsers) is not available to the scripters that are working on a bare system. I typically have a couple of WMI templates on my pen drive and start from there.

I recently ran into Scriptomatic. It's a great tool to give you good starting points for creating WMI scripts. It provides you with a dropdown of namespaces available on the system, and classes within the selected namespace. It even allows creation of Perl scripts...!

Download it here and then select the root\Microsoft\SqlServer\ComputerManagement namespace; select the SqlService class and voila, a ready to run script is displayed in the window. Quite nice! Here is an VbScript example:

On Error Resume Next

Const wbemFlagReturnImmediately = &h10
Const wbemFlagForwardOnly = &h20

arrComputers = Array("C3PO")
For Each strComputer In arrComputers
   WScript.Echo
   WScript.Echo "=========================================="
   WScript.Echo "Computer: " & strComputer
   WScript.Echo "=========================================="

   Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\ComputerManagement")
   Set colItems = objWMIService.ExecQuery("SELECT * FROM SqlService", "WQL", _
                                          wbemFlagReturnImmediately + wbemFlagForwardOnly)

   For Each objItem In colItems
      WScript.Echo "AcceptPause: " & objItem.AcceptPause
      WScript.Echo "AcceptStop: " & objItem.AcceptStop
      strAdvancedProperties = Join(objItem.AdvancedProperties, ",")
         WScript.Echo "AdvancedProperties: " & strAdvancedProperties
      WScript.Echo "BinaryPath: " & objItem.BinaryPath
      WScript.Echo "Clustered: " & objItem.Clustered
      strDependencies = Join(objItem.Dependencies, ",")
         WScript.Echo "Dependencies: " & strDependencies
      WScript.Echo "Description: " & objItem.Description
      WScript.Echo "DisplayName: " & objItem.DisplayName
      WScript.Echo "ErrorControl: " & objItem.ErrorControl
      WScript.Echo "ExitCode: " & objItem.ExitCode
      WScript.Echo "HostName: " & objItem.HostName
      WScript.Echo "ProcessId: " & objItem.ProcessId
      WScript.Echo "ServiceName: " & objItem.ServiceName
      WScript.Echo "SQLServiceType: " & objItem.SQLServiceType
      WScript.Echo "StartMode: " & objItem.StartMode
      WScript.Echo "StartName: " & objItem.StartName
      WScript.Echo "State: " & objItem.State
      WScript.Echo
   Next
Next