Windows Management Instrumentation (WMI) is a set of functions embedded into the kernel of Microsoft Operating Systems and Servers, including SQL Server. The purpose of WMI is to allow local and remote monitoring and management of servers. It is a standards-based implementation based on the Distributed Management Task Force’s (DMTF) Web-Based Enterprise Management (WBEM) and Common Information Model (CIM) specifications.
What you need to know is that WMI has many events for SQL Server. Search for WMI to get you started in Books Online, and you will discover the many, many events. You can create alerts on these events. Included are Data Definition Language (DDL) events that occur when databases are created or dropped and when tables are created or dropped, for example.
WMI has a language to query these events called Windows Management Instrumentation Query Language (WQL). It is very much like T-SQL, and you will get comfortable with it immediately.
Browse Books Online for the kind of event you wish to monitor. Each event will have a list of attributes, just like a table has a list of columns. Using WMI, you can select the attributes from the event in an alert.
To create an alert, use SQL Serve Management Studio. In Object Explorer, open the SQL Server Agent tree node, right-click Alerts, and choose New Alert. In the Alert Type drop-down box, choose WMI Event Alert.
The namespace will be populated based on the server you are connected to and should look like this:
\\.\root\Microsoft\SqlServer\ServerEvents\SQL2008
The period (.) represents the server name, which you can change, such as
\\MYSQLSERVER\. The last node should be MSSQLSERVER for a default instance and the <instance name> for named instances. In the preceding example, the instance I was running is called SQL2008.
In the textbox, you enter your WQL query, as shown here:
SELECT * FROM DDL_DATABASE_LEVEL_EVENTS
Or you could use this query:
Select TSQLCommand from DDL_DATABASE_LEVEL_EVENTS
To select only the TSQLCommand Attribute. There will be a pause when you click OK. If your namespace is incorrect, or the syntax or event/attribute names are incorrect, you will get a message immediately.
Then, in your job, you may use the WMI (attribute) event token — in this case:
Print ‘$(ESCAPE_SQUOTE(WMI(TSQLCommand)))’
To get events from a database, Service Broker notifications must be turned on for that database. To turn on Service Broker notifications for AdventureWorks2008, use the following syntax:
ALTER DATABASE AdventureWorks2008 SET ENABLE_BROKER;
If your alerts are occurring but the text replacement for the WMI token is not being done, you probably need to turn on the Service Broker for your database.
The service account that SQL Server Agent uses must have permission on the namespace and ALTER ANY EVENT NOTIFICATION permissions. This will be done automatically if you use SQL Server Configuration Manager to set up accounts. However, to adjust these settings manually, from the Run prompt, type wmimgmt.msc. An administrative dialog will appear, allowing you to set up permissions.
There is a test program for WMI on your server. To run it from the command line, type WBEMTest. It is installed in the WBEM directory of your Windows system directory.
