Automation of PAL (Performance Analysis of Logs) Tool for SQL Server Using Powershell – Part 2

This entry is part 2 of 3 in the series Automation of PAL

In this part of the process, we will set up a process to automatically stop the data collector or counter log on the remote server.  Then rename the file with a time stamp if it is an older version of Windows.  Finally, we will start the data collector or counter log on the remote servers for today.  To take advantage of this script you must use the SQLPSX (SQL Server PowerShell Extensions) available on CodePlex.  On my system, I run this every night at midnight to start a new file for analysis.

You will need the table create in Part 1.  For Part 1, visit this link.

Part 2:  Stop the Performance Monitor Data Collector or Counter Log Each Night

# Stop SQLPerf Collector
# Rename files on Windows 2000 and Windows 2003 because you can't auto set a timestamp on them
# Start SQLPerf Collector

# Load SQL Snapins
Get-PSSnapin -Registered | Where-Object { $_.Name -like "SqlServer*" } | ForEach-Object { Write-Host "Adding Snapin " + $_.Name; Add-PSSnapin $_.Name }
clear

$Sharename = "SQL_Trace"    #Sharename on local servers for Perf files
$filedate = (get-date).AddDays(-1).ToString("yyyyMMdd")    # used when renaming SQLPerf files on local servers

# Connect to SQL Server and pull list of servers to monitor
$params = @{'server'='<servername>';'Database'='DBA'} #<----Replace with your server name where you create the tables above
$servers = invoke-sqlcmd @params -Query "SELECT    Server_Name, left(Windows_Version, 1) Windows_Version FROM dbo.DBA_Server_PAL_Options o INNER JOIN dbo.DBA_Server s ON o.Server_ID = s.Server_ID WHERE PAL = 1 ORDER BY Server_Name"

foreach ($entry in $servers)
{
$Computer = $entry.Server_Name

# Stop current data collector or counter log
logman stop SQLPerf -s $Computer

# Windows 2003 R2 or below rename old file with timestamp
if ($entry.Windows_Version -eq "5")
{
$oldfile = "\\$computer\$Sharename\SQLPAL.blg"
$newfile = $computer + "_SQLPAL$filedate.blg"

if((Test-Path -Path $oldfile))
{Rename-Item -Path $oldfile -NewName $newfile}
}

# Start today's log
logman start SQLPerf -s $Computer
}

Part 3 will be the process that creates all the reports and zips up the performance counter file on a central server.

Series Navigation<< Automation of PAL (Performance Analysis of Logs) Tool for SQL Server Using Powershell – Part 1Automation of PAL (Performance Analysis of Logs) Tool for SQL Server Using Powershell – Part 3 >>

Related Posts

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.