Archive

Archive for the ‘SQL Server PowerShell Scripts’ Category

Real Time Replication Agents Monitoring

November 15, 2014 Leave a comment

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 }

 

Executing SQL Script on multiple Servers/Databases

November 11, 2014 Leave a comment

On a daily basis DBA’s has to monitor on multiple database instances and what if you have to run a script on multiple SQL Server Instances and Multiple databases

While working on a project where we have more than 15 SQL Instances and on a daily basis we have been asked by dev team to run sql script on each of the server on specific databases. so I developed a small PowerShell script which calls the sql server script and applies to all the databases which are online or to the databases which are not mentioned in the exclude database parameter


param ([string]$DatabaseToBeExcluded)
 
if (-not (Get-Module SQLPS)) {Import-Module SQLPS
}

$ExcludeDatabases=$DatabaseToBeExcluded #‘1,2,3,4’

$localpath=split-path $SCRIPT:MyInvocation.MyCommand.Path -parent


foreach($line in (Get-Content $localpath\ServerList.txt))
{
$line
Invoke-Sqlcmd -InputFile $localpath\PermissionScript.sql-Variable ExDb=$ExcludeDatabases -ServerInstance $line -ErrorAction SilentlyContinue -Verbose -QueryTimeout 0 -Database master
}

SQL Script –:setvar ExDb "1,2,3,4"

SET NOCOUNT ON

DECLARE @ExDb varchar(100)

DECLARE @Start int

DECLARE @End int

DECLARE @Databasename varchar(100)

DECLARE @sql nvarchar(500)

SET @ExDb= ‘$(ExDb)’

–print @ExDb

DECLARE @DBName TABLE (ID INT IDENTITY(1,1),DBName varchar(100))

SET @sql=’SELECT name from sys.databases where convert(varchar(10),database_id) not in (‘+@ExDb+’) and state=0′

INSERT INTO @DBName

EXEC (@sql)

SET @End=@@Rowcount

SET @Start=1

WHILE @Start <=@End

BEGIN

SELECT @Databasename=DBName FROM @DBName WHERE ID=@Start

SET @sql=”

BEGIN

SET @sql=’Write SQL Command Sscript’

END

–Print @sql

exec (@sql)

SET @Start=@Start + 1

END