Archive

Archive for November, 2014

Article wise Pending Transaction (Transactional Replication)

November 25, 2014 Leave a comment

While troubleshooting transactional replication issues , the very common question people ask is for which tables/articles transaction are waiting to be replicated and how much?

 

The script below will answer the question

 

Script

select
count(*) as [# of commands] ,
f.publication,
c.publisher_database_id,
d.publisher_db,
c.article_id,
e.article,
entry_time
from MSrepl_commands  c with (NOLOCK)
inner join MSrepl_transactions  t  with (NOLOCK) on c.publisher_database_id = t.publisher_database_id
and c.xact_seqno = t.xact_seqno
inner join MSpublisher_databases d  with (NOLOCK) on d.id = c.publisher_database_id
inner join MSarticles e  with (NOLOCK) on e.publisher_db = d.publisher_db and e.article_id = c.article_id
inner join MSpublications f  with (NOLOCK) on f.publisher_db = e.publisher_db and e.publication_id = f.publication_id
–where f.publication = ‘(publication_name)’
group by   
c.publisher_database_id,
f.publication,
d.publisher_db,
c.article_id,
e.article,
entry_time
order by [# of commands] desc

 

image

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 }

 

Database Mirroring Sync type (Operating Mode)

November 14, 2014 Leave a comment
select DB_NAME(database_id) dbname,CASE mirroring_safety_level WHEN 0 THEN 'Unknown' WHEN 1 THEN'asynchronous' WHEN 2 THEN 'synchronous' END from sys.database_mirroring

image

Categories: Database Mirroring

Database user Permission and Server Role

November 14, 2014 Leave a comment

The script pulls user database and server level permission

 

Script

DECLARE @DB_USers TABLE (DBName sysname, UserName sysname, LoginType sysname, AssociatedRole varchar(max),create_date datetime,modify_date datetime) INSERT @DB_USers EXEC sp_MSforeachdb ' use [?] SELECT ''?'' AS DB_Name, case prin.name when ''dbo'' then prin.name + '' (''+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'' and owner_sid!=''sa'') + '')'' else prin.name end AS UserName, prin.type_desc AS LoginType, isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole ,create_date,modify_date FROM sys.database_principals prin LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and prin.type_desc!=''DATABASE_ROLE'' and prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%''' SELECT t1.dbname,t1.username ,t1.logintype ,t1.create_date ,t1.modify_date,t1.Permissions_user DatabaseRole, t2.RoleName ServerRole FROM (SELECT dbname,username ,logintype ,create_date ,modify_date , STUFF((SELECT ',' + CONVERT(VARCHAR(500),associatedrole) FROM @DB_USers user2 WHERE user1.DBName=user2.DBName AND user1.UserName=user2.UserName FOR XML PATH('') ),1,1,'') AS Permissions_user FROM @DB_USers user1) t1 left JOIN (SELECT Logins.name AS UserName, Roles.name AS RoleName --,'EXEC sp_dropsrvrolemember '+QUOTENAME(Roles.name,'''')+','+QUOTENAME(Logins.name,'''') + ';', --'EXEC sp_addsrvrolemember '+QUOTENAME(Roles.name,'''')+','+QUOTENAME(Logins.name,'''') + ';' FROM sys.server_role_members RoleMembers JOIN sys.server_principals Logins ON RoleMembers.member_principal_id = Logins.principal_id JOIN sys.server_principals Roles ON RoleMembers.role_principal_id = Roles.principal_id ) t2 on t1.UserName=t2.UserName Group by t1.dbname,t1.username ,t1.logintype ,t1.create_date ,t1.modify_date,t1.Permissions_user, t2.RoleName

 

image

Categories: SQL Server Security

SQL Server Engine Forums Contribution

November 12, 2014 Leave a comment
Categories: F-SQL Server Engine

Transact-SQL Forums Contribution

November 12, 2014 Leave a comment
Categories: F-Transact-SQL

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