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 


[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(); 


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:


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(); 


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(); 


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(); 

Inside SQL Server 2005 Tools
Inside SQL Server 2005 Tools
ISBN: 0321397967
EAN: 2147483647
Year: 2006
Pages: 149 © 2008-2017.
If you may any questions please contact us: