Using PowerShell to Setup Performance Monitor Data Collector Sets. Updated 2020-04-02 for 0x80300103 fix.

Setting up Performance Monitor Data Collector Sets can be a time consuming exercise, especially when you have to do it on multiple servers.
Using sources from multiple places, here is some code I use to create a Data Collector Set, and inside that a number of Data Collectors for Microsoft SQL Server instances running on the server.

Please let me know if I’ve made any typos, and kindly test before using on a Production machine.
It’s modified heavily from my original as I’ve tried to add comments to help explain what I’m trying to achieve.

Firstly, set up 2 files containing the counters you wish to capture. These are just examples. You can use any available counter.
SQLAudit-Server.XML

<PerformanceCounterDataCollector>
    <Counter>\Processor(*)\% Processor Time</Counter>
</PerformanceCounterDataCollector>

SQLAudit-Instance.XML

<PerformanceCounterDataCollector>
    <Counter>\%instance%:SQL Statistics\Batch Requests/sec</Counter>
    <Counter>\%instance%:Buffer Manager\Page life expectancy</Counter>
</PerformanceCounterDataCollector>

The value %instance% is replaced by the PowerShell script when it runs.
This Data Collector Set, is scheduled to stop / start at 5am, and in my next blog, I’ll show you how to keep it running. (if you don’t do this you’ll find the collector won’t be running on alternate days)

So….here is the script.

Param(
#default to the current server. Can be a remote server.
[string]$Server = $env:ComputerName,
#if this switch is used, the Data Collectors will be cleared, and new ones created based on the SQLAudit-Server / SQLAudit-Instance XML files.
[switch]$updateDC
)

## Script starts here ##
$DCSName = "SQLAudit"; #Set this to what you want the Data Collector Set to be called.
Write-Host "Running Perfmon-Collector to create / update Perfmon Data Collector Set $DCSName on $Server" -ForegroundColor Green
#Directory for the output Perfmon files.
$SubDir = "C:\PerfMon\PerfmonLogs"
# Location of the Scripts/Files. SQLAudit-Server.XML, SQLAudit-Instance.XML
$ScriptDir = "C:\Scripts" 

# Create directories if they do not exist.
Invoke-Command -ComputerName $Server -ArgumentList $SubDir,$ScriptDir -ScriptBlock {
    param($SubDir,$ScriptDir)
    If (!(Test-Path -PathType Container $SubDir))
    {
        New-Item -ItemType Directory -Path $SubDir | Out-Null
    }
    If (!(Test-Path -PathType Container $ScriptDir))
    {
        New-Item -ItemType Directory -Path $ScriptDir | Out-Null
    }
}

# DataCollectorSet Check and Creation
$DCS = New-Object -COM Pla.DataCollectorSet

try # Check to see if the Data Collector Set exists
{
    $DCS.Query($DCSName,$Server)
}
# Need to catch both exceptions. Different O/S have different exceptions.
catch [System.Management.Automation.MethodInvocationException],[System.Runtime.InteropServices.COMException]
{
    Write-Host "Creating the $DCSName Data Collector Set" -ForegroundColor Green
    $DCS.DisplayName = $DCSName;
    $DCS.Segment = $true;
    $DCS.SegmentMaxDuration = 86400; # 1 day duration
    $DCS.SubdirectoryFormat = 1; # empty pattern, but use the $SubDir
    $DCS.RootPath = $SubDir;

    try #Commit changes
    {
        CommitChanges $DCS $DCSName

        Invoke-Command -ComputerName $Server -ArgumentList $DCSName -ScriptBlock {
            param($DCSName)
            $Trigger = @()
            #Start when server starts.
            $Trigger += New-ScheduledTaskTrigger -AtStartup
            #Restart Daily at 5AM. Note: I have not used Segments.
            $Trigger += New-ScheduledTaskTrigger -Weekly -DaysOfWeek Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday -at 05:00
            $Path = (Get-ScheduledTask -TaskName $DCSName).TaskPath
            #This setting in the Windows Scheduler forces the existing Data Collector Set to stop, and a new one to start
            $StopExisting = New-ScheduledTaskSettingsSet
            $StopExisting.CimInstanceProperties['MultipleInstances'].Value=3
            Set-ScheduledTask -TaskName $DCSName -TaskPath $Path -Trigger $Trigger -Settings $StopExisting | Out-Null
        }
        $DCS.Query($DCSName,$Server) #refresh with updates.
    }
    catch
    {
        Write-Host "Exception caught: " $_.Exception -ForegroundColor Red
        return
    }
}

#If updateDC parameter is supplied, Stop the existing data collectors and clear the data collectors from the data collector set.

If ($updateDC) {
    If ($DCS.Status -ne 0) {
        try {
                $DCS.Stop($true)
            }
        Catch {
                 Write-Host '-updateDC parameter was supplied but collectors did not stop successfully. Script exiting.' -ForegroundColor Red
                 Exit 1
            }
    }
    $DCS.DataCollectors.Clear()
    CommitChanges $DCS $DCSName
}

#DataCollector - SQLAudit-Server
$DCName = "$DCSName-Server";

# If the Data Collector does not exist, create it!
If (!(CheckCollector $DCS $DCName))
{
    Write-Host "Creating the $DCName Data Collector in the $DCSName Data Collector Set" -ForeGroundColor Green
    CreateCollectorServer $DCS $DCName
    CommitChanges $DCS $DCSName
}

#Data Collector - SQLAudit-Instances. Loop through installed instances and create collector for each if they do not exist.
$Instances = Get-SQLInstances -Server $Server

foreach ($Instance in $Instances) {
    If ($Instance -eq "MSSQLSERVER") {
        $ReplaceString = "SQLServer";
        }
        ELSE {
        $ReplaceString = "MSSQL`$$Instance";
        }
    $DCName = "$DCSName-$Instance";

    If (!(CheckCollector $DCS $DCName))
    {
        Write-Host "Creating the $DCName Data Collector in the $DCSName Data Collector Set" -ForeGroundColor Green
        CreateCollectorInstance $DCS $DCName $ReplaceString
        CommitChanges $DCS $DCSName
    }
}

# Start the data collector set.
try {

    If ($DCS.Status -eq 0) {
    $DCS.Start($true)
    Write-Host "Successfully created $DCSName and started the collectors." -ForeGroundColor Green
    }
}
catch {
    Write-Host "Exception caught: " $_.Exception -ForegroundColor Red
    return
}

Here are the functions used. These would normally be placed above ## Script starts here ##

#Functions start here.
Function CheckCollector([System.Object]$DCS,[string]$DCName)
{
    # Check if the data collector exists in the DataCollectorSet
    If (($DCS.DataCollectors | Select Name) -match $DCName)
        { Return $true }
    ELSE
        { Return $false }
}

Function CreateCollectorServer([System.Object]$DCS,[string]$DCName)
{
     $XML = Get-Content $ScriptDir\SQLAudit-Server.xml
     $DC = $DCS.DataCollectors.CreateDataCollector(0)
     $DC.Name = $DCName
     $DC.FileName = $DCName + "_";
     $DC.FileNameFormat = 0x0003;
     $DC.FileNameFormatPattern = "yyyyMMddHHmm";
     $DC.SampleInterval = 15;
     $DC.LogFileFormat = 0x0003;
     $DC.SetXML($XML);
     $DCS.DataCollectors.Add($DC)
}

Function CreateCollectorInstance([System.Object]$DCS,[string]$DCName,[string]$ReplaceString)
{
     $XML = (Get-Content $ScriptDir\SQLAudit-Instance.xml) -replace "%instance%", $ReplaceString
     $DC = $DCS.DataCollectors.CreateDataCollector(0)
     $DC.Name = $DCName
     $DC.FileName = $DCName + "_";
     $DC.FileNameFormat = 0x0003;
     $DC.FileNameFormatPattern = "yyyyMMddHHmm";
     $DC.SampleInterval = 15;
     $DC.LogFileFormat = 0x0003;
     $DC.SetXML($XML);
     $DCS.DataCollectors.Add($DC)
}
Function CommitChanges([System.Object]$DCS,[string]$DCSName)
{
    $DCS.SetCredentials($null,$null) # clear credentials 0x80300103 fix
    $DCS.Commit($DCSName,$Server,0x0003) | Out-Null
    $DCS.Query($DCSName,$Server) #refresh with updates.
}

Function Get-SqlInstances {
  Param($Server = $env:ComputerName)

  $Instances = @()
  [array]$captions = gwmi win32_service -computerName $Server | ?{$_.Caption -match "SQL Server*" -and $_.PathName -match "sqlservr.exe"} | %{$_.Caption}
  foreach ($caption in $captions) {
    if ($caption -eq "MSSQLSERVER") {
      $Instances += "MSSQLSERVER"
    } else {
      $Instances += $caption | %{$_.split(" ")[-1]} | %{$_.trimStart("(")} | %{$_.trimEnd(")")}
    }
  }
  $Instances
}

10 Comments

  1. How can I use it for multiple servers? I have a txt file with Server names, and I want this script to run on all these servers. How should I chane this script? Sorry but I am a beginner and I can’t find how to do this. And it would be great to have even the counters writen in a txt file (only name), and the srcipt can automatically from the txt get the counter names. Thank you!

    • Hi Andrey,
      To run for multiple servers, just write a powershell script to take input from the file with the server names, and pass them as a parameter when calling the script.
      For example, get the Server name into a variable called $ServerName, and then run “perfmonCollector.ps1 -Server $ServerName”
      For the counters in a txt file, you can use txt instead of XML if you want, but you will need to get the counter details. If you set up perfmon manually, I think there might be a way to export the configuration.

      -rob

  2. Hi Rob,
    an excellent piece of work!
    Still: while running your script (Windows Server Standard 2016) I get an error in the Function: CommitChanges
    “A user account is required in order to commit the current Data Collector Set properties. (Exception from HRESULT: 0x80300103)”
    Any idea why?
    Thanks

    • We had code based on this article and also had the 0x80300103 error on Windows Server 2016 (weirdly not on Windows 2012 R2). After half a day troubleshooting, turned out it was a result of calling CommitChanges more than once. After changing our code to only commit once the collector set was completely configured, the error stopped happening. Hope this helps.

      • Thanks for the update Steven.
        I haven’t had the time to try and recreate.
        All I could determine was the error is “A user account is required in order to commit the current Data Collector Set properties”
        Were you running against a local machine, or a remote one, and is the machine domain joined?
        If you could share the additional commit that’s not required, I can update it here as well with a note for 2016 users.

        Thank you once again.
        -rob

        • No problem, thought the information might be useful to other people.

          It could be permission/access related because I could reproduce this on any of our internally hosted Windows Server 2016 instances (domain joined and logged in with domain account). However I could not reproduce the problem on a local VM (VirtualBox) while logged in as Administrator. Same version of PowerShell installed and almost exactly the same OS patch level (14393.3564 vs 14393.3595).

          Can only think that after the first call to CommitChanges after creating the collector, the cached credentials for LocalSystem (default unless another user specified) were cleared on the in-memory object. Calling the CommitChanges again threw the exception because there were effectively no credentials on the object any longer. However, I can’t explain why it would not happen in my local environment.

          • Ahh, that’s interesting.
            I wonder if some administrative setting is doing this.

            Prad is a colleague of mine, so I’ll touch base with him when he’s doing this next so I can have a look myself.

            Thanks for your feedback!
            -rob

          • Hi Steven,
            Post has been updated with the fix.
            In the CommitChanges function, an additional line is added.

            $DCS.SetCredentials($null,$null) # clear credentials 0x80300103 fix

            After the initial data collector set is committed, it was picking up the username ‘NT AUTHORITY\SYSTEM.’
            By clearing the username, the commit works fine and the script continues.

            -rob

Leave a Reply to admin Cancel reply

Your email address will not be published. Required fields are marked *