SQL Server Extended Event Handling 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

On to the video. YouTube, take it away.

Four events were captured and handled:

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.