In this post on collecting xEvent query execution data to a central database I will cover the logic in the powershell script (provided as a link below) that determines the instances to collect from and then connect and collect the data to the central database.
First a refresher with an image from my previous post.
As you can see above, the powershell script first queries the central database (DB_ServerMain table) to determine what instances to collect from. Then it loops through those instances and executes the xEvents tSQL queries from my previous posts. Let’s take a quick look at some parts of the PS1 file.
Determine What Instances to Collect From
At the top of the powershell file is a query and then the beginning of a loop:
The first part of the PS1 file opens a connection to the central database and executes the query against DB_ServerMain to determine which SQL instances to collect xEvent query data from. At the bottom of the snippet above, you can see that it assumes all .XEL files will be in “C:\DBAMonitoring\traces\” on the remote machine. It them goes into a foreach loop iterating through the list of remote SQL Server instances to collect from.
Within the loop, a connection string for source (remote server) and destination (central collection server) are defined, some remote instance specific variables are set (for incremental loading) and then the SQL statement is defined dynamically:
Note that xel filename and fileoffset are embedded in the dynamic SQL prior to it’s execution. Then the actual call to run the query on the remote server and save the results to the central server is executed in a TRY/CATCH block:
The code first defines the destination table name in the central DB instance (“XEvents_Queries”). Then it opens the connection to the remote instance, creates a SqlCommand object (with the query AND the source connection) and then executes the query (with the .ExecuteReader() method of the command object). At this point, nothing has been written to the central collection database. If there is an error in the execution of the query on the remote instance, then the CATCH block will fire. In the powershell script, my catch block basically assumes there is a file rollover condition by checking for the phrase “The offset” in the caught exception: $ex1.Contains(“The offset”). If the named xel file and offset are no longer valid (due to a file rollover), it basically resets the sql query to call the system function with NULLs instead of named incremental starting point (like this: FROM sys.fn_xe_file_target_read_file(‘C:\DBAMonitoring\traces\sql2012*.xel’, null,NULL,NULL))
The final actionable piece of the script uses the SQLBulkCopy object to connect to the central collection database, and then write resultset from the remote instance to it:
Basically, it connected to the central collection server with the conneciton string declared at the beginning of the script. It also defines the destination table, bacthsize and timeout values. The .WriteToServer method is called with the $Reader variable that contains the resultset from the remote instance. The .WriteToServer method will perform a BCP insert operation in chunks of 5000 rows.
That’s pretty much it for the powershell script. You can download the full version of it here FetchQueryMonitorxEvents.ps1.