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 = &h10Const 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 NextNext