Microsoft® SQL Server® 2008 Administration with Windows PowerShell™

Chapter 10. WMI Provider for Server Events

In addition to the WMI Provider for Configuration Management discussed in Chapter 9, SQL Server 2008 includes one other WMI provider: the WMI Provider for Server Events. This provider transforms SQL Server event data into WMI class instances, and enables you to use WMI to monitor events in SQL Server. This chapter shows you how to monitor Data Definition Language (DDL) and trace events in a SQL Server instance by leveraging this provider in Windows PowerShell. This chapter covers the following:

• WMI Provider for Server Events

• WMI Query Language (WQL)

• Event handling with Windows PowerShell 2.0

• Monitoring errors from the SQL Server error log

• Monitoring deadlocks

• Monitoring blockings

• Monitoring login changes and failed login attempts

• Monitoring databases and database objects

WMI Provider for Server Events

The WMI Provider for Server Events turns SQL Server into a managed WMI object and enables you to use WMI to monitor server events. This provider manages a WMI namespace for each instance of SQL Server 2008. The name of the namespace is in the format root\Microsoft\SqlServer\ServerEvents\instance_name.

For a default instance, the namespace is root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER.

For a named instance INSTANCE1, the namespace is root\Microsoft\SqlServer\ServerEvents\INSTANCE1.

To monitor a default instance on a remote computer—for example, DEMOPC—you can use a named space, \\DEMOPC\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER.

The WMI Provider ...