Home > SQL Server PowerShell Scripts > Real Time Replication Agents Monitoring

Real Time Replication Agents Monitoring

I wrote this script for fun , but this turnout to be really very helpful when u have a daily monitoring task of a replication setup which is huge.Yes the setup is really huge.

Script monitors Replication Agents (Log Reader,Distributor & Snapshot agents) current activity. Script connect to the distributor Server and monitors all the distribution database

Script takes 2 parameter

$AgentStatus=Read-Host "What you want to Montior [L]Logreader,[D] Distributor and [S]snapshot Agent?:" $RefreshTimings=Read-Host "Refresh Rate (seconds):"

  1. $AgentStatus : Which agent you want to monitor
  2. $RefreshTimings : This is refresh rate. Value provided is considered in seconds by default.
  3.                                If you wish to refresh after 5  minutes, you should pass the value as 300. (5 * 60 = 300 seconds)

Snapshot Agent Status Image

Log Reader Agent Status Image

Distributor Agent Status Image

PS Script 

#Load SQL Server snap-in ##If not Loaded, Please Load SQLServer Snapins Into PowerShell By Running The Following Cmdlets: #Import-Module SQLPS param ( [string]$AgentStatus ) $SQLserverDB=@() $InstanceName="DBPROX" [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Replication") | Out-Null [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.RMO") | Out-Null #Add-PSSnapin SqlServerCmdletSnapin100 # Add-PSSnapin SqlServerProviderSnapin100 #Set-ExecutionPolicy RemoteSigned $RepInstanceObject = New-Object "Microsoft.SqlServer.Replication.ReplicationServer" "$InstanceName" $RepInstanceStatus = New-Object "Microsoft.SqlServer.Replication.ReplicationMonitor" "$InstanceName" function Get-SnapshotAgentstatus { Write-Host "" Write-Host "[+]Snapshot Agent Current Status" -BackgroundColor Green -ForegroundColor Black Write-Host "" foreach($SMonitorServers in $RepInstanceStatus.EnumSnapshotAgents()) { foreach($SMon in $SMonitorServers.Tables) { foreach($SnapshotAgent in $SMon | ` SELECT dbname,name,status,publisher,publisher_db,publication,subscriber,subscriber_db,starttime,time,duration,comments) { Write-Host "dbname :" $SnapshotAgent.dbname Write-Host "Snapshot Agent :" $SnapshotAgent.name -ForegroundColor Green write-host "status :" $SnapshotAgent.status write-host "publisher :" $SnapshotAgent.publisher write-host "publisher_db :" $SnapshotAgent.publisher_db write-host "publication :" $SnapshotAgent.publication write-host "subscriber :" $SnapshotAgent.subscriber write-host "subscriber_db :" $SnapshotAgent.subscriber_db write-host "starttime :" $SnapshotAgent.starttime write-host "time :" $SnapshotAgent.time write-host "duration :" $SnapshotAgent.duration write-host "comments :" $SnapshotAgent.comments -ForegroundColor Green write-host "*********************************************************************" } } } } function Get-LogReaderAgentstatus { Write-Host "" Write-Host "[+]LogReader Agent Current Status" -BackgroundColor Green -ForegroundColor Black Write-Host "" foreach($PMonitoreServers in $RepInstanceStatus.EnumLogReaderAgents()) { foreach($PubMon in $PMonitoreServers.Tables) { foreach($LogAgent in $PubMon | ` SELECT dbname,name,status,publisher,publisher_db,publication,subscriber, ` subscriber_db,starttime,time,duration,comments) { Write-Host "dbname :" $LogAgent.dbname Write-Host "LogReader Agent :" $LogAgent.name -ForegroundColor Green write-host "status :" $LogAgent.status write-host "publisher :" $LogAgent.publisher write-host "publisher_db :" $LogAgent.publisher_db write-host "publication :" $LogAgent.publication write-host "subscriber :" $LogAgent.subscriber write-host "subscriber_db :" $LogAgent.subscriber_db write-host "starttime :" $LogAgent.starttime write-host "time :" $LogAgent.time write-host "duration :" $LogAgent.duration write-host "comments :" $LogAgent.comments -ForegroundColor Green write-host "*********************************************************************" } } } } function Get-DitributorAgentstatus { Write-Host "" Write-Host "[+]Distributor Agent Current Status" -BackgroundColor Yellow -ForegroundColor Black Write-Host "" foreach($DMonitorervers in $RepInstanceStatus.EnumDistributionAgents()) { foreach($DisMon in $DMonitorervers.Tables) { foreach($DisAgent in $DisMon | ` SELECT dbname,name,status,publisher,publisher_db,publication,subscriber, ` subscriber_db,starttime,time,duration,comments) { Write-Host "dbname :" $DisAgent.dbname Write-Host "Distributor Agent:" $DisAgent.name -ForegroundColor Yellow write-host "status :" $DisAgent.status write-host "publisher :" $DisAgent.publisher write-host "publisher_db :" $DisAgent.publisher_db write-host "publication :" $DisAgent.publication write-host "subscriber :" $DisAgent.subscriber write-host "subscriber_db :" $DisAgent.subscriber_db write-host "starttime :" $DisAgent.starttime write-host "time :" $DisAgent.time write-host "duration :" $DisAgent.duration write-host "comments :" $DisAgent.comments -ForegroundColor Yellow write-host "*********************************************************************" } } } } if ($AgentStatus -eq "D") { Get-DitributorAgentstatus } elseif ($AgentStatus -eq "L") { Get-LogReaderAgentstatus } elseif ($AgentStatus -eq "S") { Get-SnapshotAgentstatus } else { Get-DitributorAgentstatus Get-LogReaderAgentstatus Get-SnapshotAgentstatus } Calling Script $AgentStatus=Read-Host "What you want to Montior [L]Logreader,[D] Distributor and [S]snapshot Agent?:" $RefreshTimings=Read-Host "Refresh Rate (seconds):" for(;;) { try { CLs Write-Host "******************************************************************" Write-Host "Monitoring " $AgentStatus $Command= "C:\PowershellScript\RMO_ReplicationAgentStatus.ps1" +" "+ $AgentStatus Write-Host "" Invoke-Expression $Command } catch { } Write-Host "Script will execute every " $RefreshTimings " secs" Start-Sleep -Seconds $RefreshTimings }

 

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: