2017-01-09

SQL Server Extended Event Handling via Powershell

This is part of a series of posts that explores Event Handling for the SQL Server Database Engine:

Dave Mason - SQL Server Event Handling: Extended Events Watcher via PowerShell

Powershell takes center stage for this post. Previously, I showed how to handle a SQL Server Extended Event in C# by accessing the event_stream target. We can do the same thing in PowerShell. The code translates mostly line-for-line from C#. Check out the last post if you want the full back story. Otherwise, continue on for the script and some PoSh-specific notes.


 #Specify these three parameters.
$SharedPath = "C:\Program Files\Microsoft SQL Server\110\Shared";
$SqlInstanceName = ".\SQLExpress";
$xeSessionName = "system_health";

$xeCore = [System.IO.Path]::Combine($SharedPath, "Microsoft.SqlServer.XE.Core.dll");
$xeLinq = [System.IO.Path]::Combine($SharedPath, 
    "Microsoft.SqlServer.XEvent.Linq.dll");
Add-Type -Path $xeLinq;

# Notes on "Microsoft.SqlServer.XE.Core.dll":
#  • For SQL 2014, it is a dependency of "Microsoft.SqlServer.XEvent.Linq.dll".
#  • For SQL 2012, the file does not exist.
if( [System.IO.File]::Exists($xeCore) )
{
    Add-Type -Path $xeCore;
}

[Microsoft.SqlServer.XEvent.Linq.QueryableXEventData] $xEvents = $null;

try
{
    #Connection string builder for SQL (Windows Authentication is assumed).
    $csb = New-Object System.Data.SqlClient.SqlConnectionStringBuilder;
    $csb["Data Source"] = $SqlInstanceName;
    $csb["Initial Catalog"] = "master";
    $csb["Integrated Security"] = $true;

    $xEvents = 
        New-Object -TypeName Microsoft.SqlServer.XEvent.Linq.QueryableXEventData(
            $csb.ConnectionString,
            $xeSessionName,
            [Microsoft.SqlServer.XEvent.Linq.EventStreamSourceOptions]::EventStream,
            [Microsoft.SqlServer.XEvent.Linq.EventStreamCacheOptions]::DoNotCache
        );

    foreach($publishedEvent in $xEvents)
    {
        Write-Host $publishedEvent.Name -ForegroundColor Green;

        foreach ($fld in $publishedEvent.Fields)
        {
            Write-Host "`tField: " $fld.Name " = " $fld.Value -ForegroundColor Yellow;
        }

        foreach ($act in $publishedEvent.Actions)
        {
            Write-Host "`tField: " $act.Name " = " $act.Value -ForegroundColor Yellow;
        }

        Write-Host "`n";    #Whitespace

        #TODO: 
        #Handle the event here. (Send email, log to database/file, etc.)
        #This could be done entirely via PowerShell.
        #Another option is to invoke a stored proc and 
        #handle the event from within SQL Server.

        #This simple example plays a "beep" 
        #when an event is received.
        [System.Media.SystemSounds]::Beep.Play();
    }
}
catch
{
    write-host "Exception Message: $($_.Exception.Message)" -ForegroundColor Red
}
finally
{
    if ($xEvents -is [IDisposable]) 
    { 
        $xEvents.Dispose();
    }
}


Notes

  • Add-Type: one or more .NET Framework types need to be added to the PowerShell session, based on the SQL Server version. Look for them in the "shared" folder (eg "C:\Program Files\Microsoft SQL Server\110\Shared\"), which varies by SQL version. Make sure you assign the proper folder name to $SharedPath. The .NET Framework types are:
    • SQL 2012: "Microsoft.SqlServer.XEvent.Linq.dll"
    • SQL 2014: "Microsoft.SqlServer.XEvent.Linq.dll" and "Microsoft.SqlServer.XE.Core.dll"
    • SQL 2016: unknown. It's probably similar to SQL 2014. If you are able to verify, please leave a comment!
  • SQL Connectivity: if you want to run this code yourself, you'll need to specify your SQL connection. Here, I want to connect to a named instance of SQL ("SQLExpress") on the local host. I specify Windows Authentication ($csb["Integrated Security"] = $true;), but you can use SQL Authentication, if desired. (Check out the SqlConnectionStringBuilder class for details.)
  • Session Name: I'll use the built-in "system_health" XEvent session for the demo, but you can specify any session you like.

  • On to the video. YouTube, take it away.


    Four events were captured and handled:

  • scheduler_monitor_system_health_ring_buffer_recorded
  • wait_info
  • scheduler_monitor_system_health_ring_buffer_recorded (a second time)
  • wait_info (also a second time)
  • When we attempt to stop script execution, the ISE status changes to "Stopping" and (eventually) to "Stopped", with a notable delay. I suspect this delay has to do with the "One Event Behind" peculiarity of the event_stream target. If I manually force a level-20 severity error in T-SQL (error_reported is one of the events specified by the system_health XEvent session), script execution usually stops and the ISE state changes to "Stopped". I can reproduce that behavior, but not with 100% consistency.


    References

    One of the first articles I read about the QueryableXEventData class was Introducing the Extended Events Reader. It includes some C# examples for creating an instance of the QueryableXEventData class (it has more than one constructor) and how to loop through the collection of events. Those code samples were the starting point for what I've created here. As far as I know, there's no command in PoSh that corresponds to the "using" statement in C# for IDisposable class objects. Dave Wyatt (b|T) shows a nice way of working with them in this post. It's the basis for the "finally" block in my code.


    SHARE