Monitoring Server Events

SQL Server Database Engine exposes certain internal events via its WMI provider. Clients using SMO can optionally subscribe to these events and have them routed to a particular object of interest to monitor any changes made externally to an object, such as monitoring for table structure changes, audit logins, and so on. The Internal SMO event routing mechanism is shown in Figure 11-6. Notice how events can be aggregated at the higher-level object. For example, any table object event such as creation of an index can be propagated to the database object or even to the server object, depending upon the level at which you sign up for it.

Figure 11-6. Server events.

To help refine your understanding of this sort of monitoring, let's examine some server monitoring scenarios.

Monitor Table Create/Drop Events at the Server Instance Level

To set up monitoring of table create/drop events at the server instance level, begin by declaring an event callback function that prints the contents of each event to the console.

Visual Basic .NET

Private Sub OnDdlEvent(ByVal sender As Object, ByVal args As ServerEventArgs)
    SyncLock Me
        Console.WriteLine("------ {0} ------", args.EventType.ToString())
        Console.WriteLine("SPID : {0}", args.Spid)
        Console.WriteLine("Time : {0}", args.PostTime)
        Console.WriteLine("Instance: {0}", args.SqlInstance)
        Console.WriteLine()
        For Each EventProperty entry in args.Properties
            Dim valueType As String
            If entry.Value Is Nothing Then
                valueType = String.Empty

            Else
               valueType = Entry.Value.GetType().ToString()
            End If
            Console.WriteLine("{0,25}: {1} ({2})",  entry.Name, entry.Value, valueType)
        Next
    End SyncLock
End Sub

C# .NET

[View full width]public void OnServerEvent (object sender, ServerEventArgs args)
{
    lock (this)
    {
       Console.WriteLine("------ {0} ------", args.EventType.ToString());
       Console.WriteLine("SPID    : {0}", args.Spid);
       Console.WriteLine("Time    : {0}", args.PostTime);
       Console.WriteLine("Instance: {0}", args.SqlInstance);
       Console.WriteLine();

       foreach(EventProperty entry in args.Properties)
       {
           Console.WriteLine("{0,25}: {1} ({2})",      entry.Name, entry.Value,     (entry
.Value != null)?     entry.Value.GetType().ToString() : string.Empty);
       }
    }
}

Next, simply subscribe to events and start monitoring:

Visual Basic .NET

Dim serverEventSetInstance As New ServerEventSet
serverEventSetInstance.CreateTable = True
serverEventSetInstance.DropTable = True
Dim serverEventHandlerInstance As ServerEventHandler
serverEventHandlerInstance = New ServerEventHandler(AddressOf
OnServerEvent)
serverInstance.Events.SubscribeToEvents(serverEventSetInstance,
serverEventHandlerInstance)
serverInstance.Events.StartEvents();

C# .NET

serverInstance.Events.ServerEvent += new ServerEventHandler(this.
OnServerEvent);
serverInstance.Events.SubscribeToEvents(ServerEvent.CreateTable);
serverInstance.Events.SubscribeToEvents(ServerEvent.DropTable);
serverInstance.Events.StartEvents();

Upon program exit make sure you unsubscribe to all events:

serverInstance.Events.UnsubscribeAllEvents();

Monitor an Index Creation Event on a Table Object Level

Using a previously declared event handler function, you can subscribe to an index creation event as follows:

Visual Basic .NET

Dim tableInstance As Table
tableInstance = serverInstance.Databases("pubs"). Tables("authors")
Dim tableEventSetInstance As New TableEventSet
tableEventSetInstance.CreateIndex = True
Dim serverEventHandlerInstance As ServerEventHandler
serverCreateEventHandler = New ServerEventHandler(AddressOf
OnServerEvent)
serverInstance.Events.SubscribeToEvents(tableEventSetInstance,
serverEventHandlerInstance)
tableInstance.Events.StartEvents();

C# .NET

Table table = serverInstance.Databases["pubs"].Tables["authors"];
table.Events.SubscribeToEvents(TableEvent.CreateIndex);
table.Events.ServerEvent += new ServerEventHandler(OnServerEvent);
table.Events.StartEvents();

Monitoring a Trigger Alter or Drop at the Object Level

SMO enables you to monitor for any external modifications to a database object, provided your application that is calling into the SMO function is running while modification is taking place.

Visual Basic .NET

Dim db As Database
Dim trig As Trigger
db = serverInstance.Databases("AdventureWorks")
trig = db.Tables("Address", "Person").Triggers("TRIG_ADDRESS")
Dim triggerEventSet As New ObjectEventSet
triggerEventSet.Alter = True
triggerEventSet.Drop = True
Dim serverEventHandlerInstance As ServerEventHandler
serverCreateEventHandler = New ServerEventHandler(AddressOf OnServerEvent)
serverInstance.Events.SubscribeToEvents(triggerEventSet, serverEventHandlerInstance)
trig.Events.StartEvents();

C# .NET

Database db = serverInstance.Databases["AdventureWorks"];
Trigger trigger = db.Tables["Address",
"Person"].Triggers["TRIG_ADDRESS"];
trigger.Events.ServerEvent += new ServerEventHandler(this.OnServerEvent);
trigger.Events.SubscribeToEvents(ObjectEvent.Alter + ObjectEvent.Drop);
trigger.Events.StartEvents();