Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Using WMI

By Haidong Ji,

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.
Total article views: 9643 | Views in the last 30 days: 3
 
Related Articles
BLOG

Power Shell or Windows – Rebooting of servers local or remote computers

Rebooting of servers local or remote Using PowerShell and Windows Powershell command Start->Run->P...

FORUM

SERVER REBOOT REPORT

SERVER REBOOT REPORT

BLOG

Reboot

Apologies, a minor reboot of the db server this morning. Somewhere we're leaking memory, more detail...

FORUM

Manage system performance from SQL2005

Manage the computer couters from server where is instaled SQL2005

FORUM

Services won't Autostart after rebooting server

SQL Server and SQL Server Agent wdo not auto start when server reboot eventhough option are checked

Tags
administration    
configuring    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones