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

I recently was re-exposed to the PAL (Performance Analysis of Logs) Tool available on CodePlex at a wonderful SQLskills class.  I had played with the tool in the past, but at the time the server I had for setting things up and automating processes was Windows 2000, so I forgot about this tool.  Now I have Windows 2008 server so that I can re-explore the use of this tool.    This will be a multi-part post to go through how I set up an automatic system for collecting performance counters for SQL Server.

Part 1: Create tables to drive automation and create Performance Monitor Data Collector / Counter Log on each server

Step 1: Create Server Table

Give that I have over 100 hundred SQL Servers ranging from 2000 to 2008 R2 and using Windows Server 2000 thought Windows Server 2008 R2 I wanted a process that could be run on each system automatically by turning on a flag.  The first thing we need a database (mine is called DBA) to house our table that list out our SQL Servers and whether to collect the PAL logs.  Once you create your database, create the table below.

CREATE TABLE [dbo].[DBA_Server]
    (
      [Server_ID] [decimal](5, 0) NOT NULL ,
      [Server_Name] [varchar](20) NOT NULL ,
      [PAL] [bit] NOT NULL DEFAULT 0
        CONSTRAINT [PK_DBA_Server]
        PRIMARY KEY CLUSTERED ( [Server_ID] ASC ) ,
        CONSTRAINT [AK_DBA_Server] UNIQUE NONCLUSTERED ( [Server_Name] ASC )
    )
GO

Step 2: Create table to hold PAL Parameters needed for processing

Next, we need a table for hold parameters for the PAL tool.  Below is a print screen of the options PAL uses to process the Performance Counter Log:

Here is a script to create the table for the PAL options:

CREATE TABLE [dbo].[DBA_Server_PAL_Options]
	(
	  [Server_ID] [decimal](5, 0) NOT NULL ,
	  [Processors] [tinyint] NOT NULL ,
	  [Three_GB_Switch] [bit] NOT NULL ,
	  [bit64] [bit] NOT NULL ,
	  [Memory] [decimal](4, 2) NOT NULL ,
	  [RAID5_Drives] [varchar](10) NULL ,
	  [RAID1_Drives] [varchar](10) NULL ,
	  [Location] [varchar](30) NULL ,
	  [Windows_Version] [char](8) NULL ,
	  CONSTRAINT [PK_DBA_Server_PAL_Options] PRIMARY KEY CLUSTERED
		( [Server_ID] ASC )
	)
GO

Step 3: Export the threshold file for SQL Server from the PAL Tool

You also need to use PAL and export the Perfmon Template File on the Threshold File tab, or you can download mine here (save this file as an SQLServer2005+.txt and SQLServer2005+.xml) into our PowerShell scripts folder.

Step 5: Create a Share name on each server to save the files

You will need a share setup on each server that you want to collect information from (I use one called SQL_Trace because I also have a process that automatically runs server traces and processes them on a remote server). I

Step 6: Create Performance Monitor Data Collector or Counter Log and Start Collecting

The final script goes out to each server using PowerShell to populate the Options table.   The only thing it does not populate is the RAID5_Drives and RAID1_Drives.  This has to be populated manually and should be a comma-delimited list of disks, i.e., C, D, E. To take advantage of this script you must use the SQLPSX (SQL Server PowerShell Extensions) available on CodePlex.  Every time you need to start collecting performance counters on a new system change the PAL bit in the server table then execute the below script.

# Collection information from systems in order to provide parameters for running PAL.
# Import SQLPerf profile to remote servers using logman
# Start data collection.

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

$Sharename = "SQL_Trace"	#<----Change to youre Sharename on local servers for Perf files
$drivearray = "C", "D", "E", "F", "Y"	#<----list of local drives $sharename folder could be located. On my systems they can be on different drives.
$PALFolder = "D:\Scripts"	#<----Folder containing XML and TXT Templates exported for PAL

# 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 s.Server_Name FROM dbo.DBA_Server s WHERE Status = 1 AND PAL = 1 ORDER BY Server_Name"

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

	$objWin32OS = Get-WmiObject -class Win32_OperatingSystem -computerName $strComputer
	$version = $objWin32OS.Version

	$objWin32CS = Get-WmiObject -Class Win32_ComputerSystem -computername $strComputer 

	foreach ($objCSItem in $objWin32CS)
	{
	 	$Processors = $objCSItem.NumberOfProcessors
		$MemoryBytes = $objCSItem.TotalPhysicalMemory
		$Architecture = $objCSItem.SystemType
		$3GB = 0

		foreach ($strSystemStartupOptions in $objCSItem.SystemStartupOptions)
		{
			if ($strSystemStartupOptions -ne $null)
			{
				$3GB = $strSystemStartupOptions.Contains("/3GB")
				if ($3GB -eq "True")
				{
					# Once 3GB is found exit early
					$3GB = 1
					break
				}
				else
					{$3GB = 0}
			}
		}
	}

    #Find out location location of Sharename.  This assumes you are local admin on the machine.
    $location = ""

    foreach ($driveletter in $drivearray)
    {
        if (Test-Path -path "\\$strComputer\$driveletter$\$Sharename")
        {
            $location = $driveletter + ":\$Sharename"
            break
        }
    }
	$location = ""

	foreach ($driveletter in $drivearray)
	{
		if (Test-Path -path "\\$strComputer\$driveletter$\$Sharename")
		{
			$location = $driveletter + ":\$Sharename"
			break
		}
	}

    if ($location -ne "")
    {
        # Connect to SQL Server and insert values needed for PAL
        $strSQL = "exec dbo.DBA_Server_PAL_Options @Server_Name = '$strComputer', @Processors = $Processors, @Three_GB_Switch = $3GB, @Architecture = '$Architecture', @MemoryBytes = $MemoryBytes, @Location = '$location', @Windows_Version = '$version'"
        invoke-sqlcmd @params -Query $strSQL

        #Create template file for the server if sharename is not on D drive so the files will created locally
        if ($location -ne "D:\$Sharename")
        {
            $xml = "$PALFolder\$strComputer.xml"
            Get-Content "$PALFolder\SQLServer2005+.txt" |
                Foreach-Object {$_ -replace "D:\$Sharename", "$location"} |
                Set-Content $xml
        }
        elseif ($location -eq "D:\$Sharename")
            {$xml = "$PALFolder\SQLServer2005+.xml"}

        #Check to see if SQLPerf data collector/counter log already exist on server
        $logman = Logman query SQLPerf -s $strComputer
        if ($logman -like "Error:*")
        {    
            #Import SQL Perf data collector
            $logman2 = logman import -n SQLPerf -s $strComputer -xml $xml
            if ($logman -like "Error:*")
            {
                #Older operating systems import is not valid logman comamnd so use create counter
                $location = "$location\SQLPAL.blg"
                logman create counter SQLPerf -s $strComputer -f bin -o $location --v -cf $PALFolder\SQLServer2005+C.txt -u SQLExec "sqlexec"        
            }

            #Start collecting the SQLPerf template counters
            logman start SQLPerf -s $strComputer
        }
    }
    else
        {Write-Host "Setup $Sharename share on server: $strcomputer" `n}
}

Next post will be a script to stop the SQLPerf collector on each server each night and rename the file with the date if on an older operating system.  If you have any questions or comments, please leave them below.