Monitor Replication Performance with PowerShell

Say what you want about SQL Server transactional replication, but when it’s configured right it’s a powerful way to have a near-real time replica of your data segregated for reporting. I plan on doing a lot of blogs on replication (set up, troubleshooting, and monitoring) but for now, let’s talk about how transactions get delivered and how you can tell if they are.

The definition of insanity

Okay, so you have yourself a SQL Server, and you have transnational replication set up. It’s working, because your reporting server has data that exists and has been changed in production. Awesome! You get up to go get some coffee and return to your desk to find a couple of voice mails.  Users are complaining about data that isn’t showing up at one of your subscribers. You check production and sure enough, the row in question has the correct data, but on your reporting box, no such luck. How did this happen, why did it happen, and more importantly, how can you fix it?

At the simplest level, transnational replication works like this: when a transaction commits at your publisher, if data was updated, inserted, or deleted, those transactions are written to the transaction log of the database in question, and are eventually committed. Going a step farther, if an article (usually a table, but you can also replicate views and stored procedures) is marked for replication, a super-duper little helper known as the log reader agent scans the transaction log for changes to the marked article and queues up the same commands to the distribution database. These commands are stored in the distribution database to be “shipped” to your subscribers. And that’s when things start to go sideways. Different indexes (which is a great feature of replication), missing data, or even existing connections and transactions can stop transactions from being committed on your subscribers.

Sometimes the fix is as easy as killing an existing SPID that is blocking replicated transactions from committing, but usually it’s far more nefarious. I will go into some of those issues in later posts, but for now, let’s figure out just how far behind replication is so you can let your users know.

The oldness: Replication Monitor

There’s a really handy tool already built into Management Studio called replication monitor that can tell you latency and transactions that are waiting to be applied to a subscriber. It also provides you with a great interface to look at errors that might be causing you stress at your subscribers and publisher. The problem I have with replication monitor is that it’s slow. If there’s a ton of transactions piled up, a lot of times you’ll get just a blank field on the number of transactions waiting to apply. Thankfully, Microsoft gives us some awesome libraries to load up in PowerShell to monitor replication that is far more responsive.

The hotness: Show-SQLReplicationTransactions.ps1

The code below is for a handy script I wrote that, when called via PowerShell, will allow you to specify (at a minimum) the host name of your publisher and it will return all publications and all subscribers of a publication, the number of pending transactions, and the expected latency (in seconds). This method is extremely fast and simple, and you can run it pretty easily to get a quick status on your replicated transactions. Source code is below, but first we have to talk about the GAC.

Put your pants on one leg at a time

If you run this code “as-is” you should know that it’s probably not going to work. And that’s because the main libary used by this script Microsoft.SqlServer.Replication.dll isn’t in the Global Assembly Cache (GAC) by default. A lot of SQL Server .dlls are, but this one isn’t. So to run this script, you have to do one of two things:

1. Run the commented line in the script: Add-Type -Path 'C:\Program Files\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.Replication.dll'. This will actually load up the library into your assembly cache at run time, but it won’t stay after a restart. This is a good method to use if you plan on testing the script. Note that depending on your version of SQL Server, you might need to change the path from 120 to 110, or even 100.
2. Add the library to the GAC.¹ This is preferred method. You can use a command line utility gacutil -i C:\Program Files\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.Replication.dll From here on out, the dll will be available for you to use without needing to add it each time.

Conclusion

This quick script is a great example of how you can really go in-depth with PowerShell to monitor your SQL Servers. I hope you find it helpful! Please share your experiences or questions in the comments below. I will be keeping these scripts as up to date as possible, so check back for updates.

The usual disclaimer

Okay look, you probably already know this but this code you’re about to use was written by me. It works for me as described above, but computers are hard and sometimes things don’t work right. Therefore, I’d like to casually remind you that you probably shouldn’t load this script up in your production environment all willy-nilly. As with any new software or functionality, test it first on your local instance or in a development environment.

<#
.SYNOPSIS
   Returns pending transaction count of all publications and their subscribers.
.DESCRIPTION
   This script will connect to a SQL server replication publisher, enumerate all publications and their subscriptions, and then get a pending transaction count for each.
   The script will then output the counts to a formatted table.
   You can provide a specific database and/or subscriber (hostname) as optional arguments.

.EXAMPLE
   Show-SQLReplicationTransactions -PublisherName "hostname"
   This will return all publications and their subscriptions from the publisher, as well as pending transaction counts

.OUTPUTS
   List of all sql related services
.NOTES
   v1.0, Drew Furgiuele (@pittfurg) 2014/07/16
#>
param(
    [Parameter(Mandatory=$true)] [string]$PublisherName,
    [Parameter(Mandatory=$false)] [string]$SubscriberName = $null,
    [Parameter(Mandatory=$false)] [string]$DatabaseName = $null
)

##Add-Type -Path 'C:\Program Files\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.Replication.dll' -ErrorAction SilentlyContinue

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.RMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Replication") | Out-Null
$RepInstanceObject = New-Object "Microsoft.SqlServer.Replication.ReplicationServer" $PublisherName
$RepStatusObject = New-Object "Microsoft.SqlServer.Replication.ReplicationMonitor" $PublisherName

$statsTable = New-Object System.Data.DataTable
$statsTable.Columns.Add("Publication") | Out-Null
$statsTable.Columns.Add("Publisher") | Out-Null
$statsTable.Columns.Add("DistributionDB") | Out-Null
$statsTable.Columns.Add("DatabaseName") | Out-Null
$statsTable.Columns.Add("Pending Commands") | Out-Null
$statsTable.Columns.Add("Est Time") | Out-Null

$subscriptions = @()
$opt = New-Object "Microsoft.SqlServer.Replication.Subscriptionoption"

if (!$DatabaseName) 
{
    $publications = $RepInstanceObject.DistributionPublishers.DistributionPublications
}
else 
{
    $publications = $RepInstanceObject.DistributionPublishers.DistributionPublications | Where-Object {$_.PublicationDBName -eq $DatabaseName}
}

ForEach ($pub in $publications)
{
    if (!$SubscriberName)
    {
        $subList = $pub.DistributionSubscriptions
    }
    else
    {
        $subList = $pub.DistributionSubscriptions | Where-Object {$_.SubscriberName -eq $SubscriberName}
    }
    ForEach ($sub in $subList)
    {
        $subscription = New-Object System.Object
        $subscription | Add-Member -type NoteProperty -name PubName -Value $pub.Name
        $subscription | Add-Member -type NoteProperty -name SubName -Value $sub.Name
        $subscription | Add-Member -type NoteProperty -name DistributionDB -Value $sub.DistributionDBName
        $subscription | Add-Member -type NoteProperty -name Publisher -Value $sub.PublisherName
        $subscription | Add-Member -type NoteProperty -name PublisherDBName -Value $sub.PublicationDBName
        $subscription | Add-Member -type NoteProperty -name Subscriber -Value $sub.SubscriberName
        $subscription | Add-Member -type NoteProperty -name SubscriberDBName -Value $sub.SubscriptionDBName
        $subscription | Add-Member -type NoteProperty -name PendingXActions -Value $null
        $subscription | Add-Member -type NoteProperty -name EstLatency  -Value $null
        $subscriptions += $subscription
    }
}

ForEach ($sub in $subscriptions)
{
    $monitor = $RepStatusObject.PublisherMonitors[$PublisherName].PublicationMonitors | Where-Object {$_.Name -eq $sub.PubName}
    $pending = $monitor.TransPendingCommandInfo($sub.Subscriber,$sub.SubscriberDBName,$opt)
    $sub.PendingXactions = $pending.PendingCommands
    $sub.EstLatency = $pending.EstimatedTimeBehind
}


$subscriptions | Format-Table Publisher,PubName,PublisherDBName,Subscriber,SubscriberDBName,PendingXActions, EstLatency -AutoSize

1. http://msdn.microsoft.com/en-us/library/dkkx7f79(v=vs.110).aspx