Collect Perfmon Data via Powershell

Quick script to collection permon data via powershell. This will continuously spit out just the physical disk stats to a csv file (max 1GB) every 10 seconds. Use at your own risk. Barely tested.

function Get-PhysicalDisk{
    param(
        [Parameter(Mandatory=$true,ValueFromPipeline=$false)] 
        $ComputerName,
        [Parameter(Mandatory=$true,ValueFromPipeline=$false)] 
        $FileName
    )

    $outputs = @();
    $1GB = 1GB

    if(Test-Path $FileName){
        Remove-Item $FileName -Force
    }

    $physDiskCounters = Get-Counter -ComputerName $ComputerName -ListSet PhysicalDisk 
    Get-Counter -ComputerName $ComputerName -Counter $physDiskCounters.Paths -Continuous -SampleInterval 10 | Export-Counter -Path $FileName -FileFormat "CSV" -Circular -MaxSize $1GB -Force

}

I usually start this in a job on multiple servers via the Start-Job cmdlet so it will run in the background like so:

gc -Path C:\Servers.txt | %{
    $srvName = $_
    $fileName = "c:\Test\$($srvName)_PhysicalDisk.csv"

    $job = Start-Job {
        param(
            $compName,
            $fName
        )
        . "C:\PathToGetPhysicalDiskFile\Get-PhysicalDisk.ps1"
        Get-PhysicalDisk $compName $fName
    } -ArgumentList @($srvName, $fileName)
}

Add a linked server & remote login to Sql Server via Powershell

Sort of. Trying to do this via just SMO and powershell was immensely frustrating when dealing with the remotelogin security. What I wanted the login to do was to use a sql login no matter the context (the bottom-most radio button in the screenshot below).

Alas, every time I set the remotelogin property and called the SetRemotePassword() method it would automatically add it to the Local server login to remote server login mappings list.

Not what I want. In order to determine the proper…properties to set I created a new linked server and scripted it out to have a gander at the properties.

Two properties stood out. The @locallogin and the @useself. Okay, cool. Easy enough, the smo linkedserverlogin has a properties collection in it, should be easy enough to set. Except, the bloody properties never get populated. Even after calling the Initialize($true) on the LinkedServerLogin smo object, said properties are not there.

Okay, let’s add them. Nope. There is no Add() method on the SqlPropertyCollection (or on the smo PropertyCollection inherited class). In the end, I ended up just having to execute a sql call in order to get this to work, as I’d already spent 4 stinking hours trying to get this to work correctly. I detest having to resort to doing this, but I HAVE to move on.

Here it is. If you have this figured, please please let me know what the poop is.

Import-Module sqlps -DisableNameChecking

$srvName = 'ServerName'
$linkedServerLogin = 'LinkedServerLoginName'
$pwd = 'LinkedServerPassword'
$servers = gc -Path C:\LinkedServers.txt

cls

try{
    $srv = New-Object Microsoft.SqlServer.Management.smo.server $srvName
    $servers | %{
        if($srv.LinkedServers.Contains($_)){
            return;
        }
        
        $linkedServer = New-Object Microsoft.SqlServer.Management.Smo.LinkedServer 
        $linkedServer.Parent = $srv
        $linkedServer.Name = $_
        $linkedServer.DataSource = $_
        #$srv.LinkedServers.Add($linkedServer);
        $linkedServer.Create();

        $sql = "
        USE [master]
        GO
        EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'$($_)', @locallogin = NULL , @useself = N'False', @rmtuser = N'$($linkedServerLogin)', @rmtpassword = N'$($pwd)'
        GO
        "
        $srv.ConnectionContext.ExecuteNonQuery($sql);

    }
}
catch{
    $_ | fl -Force
}
finally{

}

Turn off AutoShrink on databases via powershell

Easy peasy script to turn off AutoShrink on all databases. I’ll let Paul Randal do the talking as to why you shouldn’t have autoshrink turned on.

Import-Module sqlps -DisableNameChecking

$ignoreDBs = @('IgnoreDB')

try{
    gc -Path 'c:\Servers.txt' | %{
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $_
        $srv.Databases | where{-not $_.IsSystemObject -and $_.Name -notin $ignoreDBs} | %{
            if($_.AutoShrink -eq $true){
                $_.AutoShrink = $false
                $_.Alter();
            }
        }        
    }
}
catch{
    $srvName
    $_ | fl -Force
}
finally{

}