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.
