In my last article, I wrote about using VBScript to automate tasks. I provided 2 working examples to get you started. In this article, I will write about WMI and how it can be used to automate SQL Server operations.

What is WMI

WMI stands for Windows Management Instrumentation. It is Microsoft's implementation of WBEM. No, WBEM is not your favorite radio station;-) It stands for Web-Based Enterprise Management, an initiative to establish standards for accessing and sharing management information over an enterprise network. WMI is WBEM-compliant and provides integrated support for the Common Information Model (CIM), the data model that describes the objects that exist in a management environment. You will see CIM in the code example below.

WMI provides fully integrated operating system support for your system and applications management. It gives you a consistent and richly descriptive model for the configuration, status, and operational aspects of Windows. Short of doing dishes and vacuuming carpet for you, it can do pretty much anything else: computer management (devices, drivers, ports, reboot), managing files and folders, logs, monitoring, networking, printing, to name just a few. Because SQL Server is so tightly integrated with Windows, it helps a lot to add WMI scripting to your skill repertoire.

WMI is tightly integrated with VBScript. Although you can also use Visual C++ to work with it, you will see most of examples available on the web using VBScript.

Example 1: Script to reboot a server

Many of us do a lot of work during database maintenance window, like checking database integrity, backup, cleanup, etc. Whenever possible, you should always reboot the server. However, our maintenance window usually appears on nights or weekends, not exactly the time that you want come in and push the power button.

Using the following VB/WMI script, you can schedule a reboot at the time of your choosing. The code should be self-explanatory. For rebooting a local computer, define "." as the computer name. Replace "." with the computer name if you want to reboot a remote computer.

'Customize code here. Replace . with a computer name to reboot a remote server 'that you have access to. To reboot the local machine, this code should work without 'any modification
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate,(Shutdown)}!\\" & strComputer & "\root\cimv2")
Set colOperatingSystems = objWMIService.ExecQuery _
("Select * from Win32_OperatingSystem")
For Each objOperatingSystem in colOperatingSystems
ObjOperatingSystem.Reboot()
Next

Example 2: Script to list all server services

Ideally, you want to SQL Server to run on a box that is just meant for SQL Server. Realistically, that does not happen very often. To get an inventory of what you have on your server, you can use the following code sample to enumerate services installed on your server. In the example, I used the Echo method to display services. However, you might find it more useful to put that information into a file. As an exercise for you, you can try to see if you could modify the script and save service information into a file.

Hint: use Example 2 of my last article as a template for file output.

'Customize code here. Replace . with a computer name to enumerate services 'on a remote server that you have access to. To work on the local machine, 'this code should work without any modification 
strComputer = "."
Set objServices = GetObject("winmgmts:\\" & strComputer)
Set objObjectSet = objServices.InstancesOf("Win32_Service")
For Each wbemObject In objObjectSet
    WScript.Echo "Display Name:  " & wbemObject.DisplayName & vbCrLf & _
                 "   State:      " & wbemObject.State       & vbCrLf & _
                 "   Start Mode: " & wbemObject.StartMode
Next

Conclusion

Continuing the series I had last week, this article talks about how to use WMI to automate SQL Server related tasks. When writing these series of articles, I strive to use examples that are easy to follow, touch key aspects of the technology, and provide a template for you to work on. Hopefully you are able to learn something about WMI and put it into use.