sqler monitor 部分新增加复制延迟异常排查
浏览数：37 /
时间：2015年06月12日
在复制系统中因为一些配置上失误和人为的失误操作导致复制堵塞,Sqler Monitor 新增加了分析复制延迟邮件,配合复制错误监控邮件和延迟邮件,和复制元数据采集 可以在第一时间准确定位到问题,适合大批量,自动化运维.
复制堵塞分析报警
复制延迟邮件
复制错误分析邮件
复制 元数据采集
元数据采集脚本:
1 Workflow Sqler_G_Mssql_ReplScript 2 { 3 param 4 ([Array] $servers 5 ,[string] $ClientDB 6 ,[string] $ClientSqlAccount 7 ,[string] $ClientSqlPassWord 8 ,[string] $ModuleFile 9 ,[string] $ScriptPath 10 ,[string] $LogPath=‘c:‘) 11 12 $i=0 13 $mutilline=10 14 while($i -le $servers.count) 15 { 16 if(($i+$mutilline) -lt $servers.count ) 17 { 18 $ss=$servers[($i)..($i+($mutilline-1))] 19 } 20 else 21 { 22 $ss=$servers[($i)..($servers.count-1)] 23 } 24 Foreach -parallel($s in $ss) 25 { 26 inlinescript 27 { 28 try 29 { 30 $SqlServer=$using:s.MsSqlHost ; 31 $Server=$using:s.Host ; 32 $Port=$using:s.MsSqlPort; 33 $Path=$using:ScriptPath; 34 $OutPutPath=$Path+"\"+$Server; 35 36 37 38 [void] [System.Reflection.Assembly]::LoadFile("$using:ModuleFile\Microsoft.SqlServer.Rmo.dll") ; 39 [void] [System.Reflection.Assembly]::LoadFile("$using:ModuleFile\Microsoft.SqlServer.Replication.dll"); 40 $SqlString="Data Source="+$SqlServer+","+$Port+";uid="+$using:ClientSqlAccount+";Password="+$using:ClientSqlPassWord; 41 $SqlConn = [System.Data.SqlClient.SqlConnection] $SqlString; 42 $repsvr=New-Object "Microsoft.SqlServer.Replication.ReplicationServer" $SqlConn; 43 if($repsvr.ReplicationDatabases.Count -gt 0) 44 { 45 $scriptargs = [Microsoft.SqlServer.Replication.scriptoptions]::Creation ` 46 -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeArticles ` 47 -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludePublisherSideSubscriptions ` 48 -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeCreateSnapshotAgent ` 49 -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeGo ` 50 -bor [Microsoft.SqlServer.Replication.scriptoptions]::EnableReplicationDB ` 51 -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludePublicationAccesses ` 52 -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeCreateLogreaderAgent ` 53 -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeCreateQueuereaderAgent ` 54 -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeSubscriberSideSubscriptions; 55 56 $sb1= New-Object -TypeName "System.Text.StringBuilder"; 57 $day=(Get-Date -Format "yyyyMMdd").tostring(); 58 foreach($replicateddatabase in $repsvr.ReplicationDatabases) 59 { 60 61 62 63 if(($replicateddatabase.TransPublications.Count +$replicateddatabase.MergePublications.Count )-gt 0) 64 { 65 if(!(Test-Path $Path) ){ [void]( mkdir $Path)}; 66 if(!(Test-Path $OutPutPath) ){ [void](mkdir $OutPutPath)}; 67 } 68 69 if ($replicateddatabase.TransPublications.Count -gt 0) 70 { 71 foreach($tranpub in $replicateddatabase.TransPublications) 72 { 73 [void] $sb1.clear(); 74 [void] $sb1.Append("/********************************************************************************/ `n"); 75 [void] $sb1.Append("/***** Writing to file script for TransPublications: " + $tranpub.Name+" *****/ `n"); 76 [void] $sb1.Append("/********************************************************************************/ `n"); 77 [void] $sb1.Append(""+$tranpub.script($scriptargs).tostring()+" `n"); 78 $Newsb1=$sb1.ToString(); 79 if((Test-Path ("$OutPutPath\$SqlServer"+"_"+$Port+"_"+$day+"_ReplScript_"+$replicateddatabase.Name+"_"+$tranpub.Name+"_Trans"+".Sql"))) 80 { [void]( del ("$OutPutPath\$SqlServer"+"_"+$Port+"_"+$day+"_ReplScript_"+$replicateddatabase.Name+"_"+$tranpub.Name+"_Trans"+".Sql")) 81 }; 82 $Newsb1|Out-File -FilePath ("$OutPutPath\$SqlServer"+"_"+$Port+"_"+$day+"_ReplScript_"+$replicateddatabase.Name+"_"+$tranpub.Name+"_Trans"+".Sql" ) -Append -Force; 83 84 85 86 } 87 } 88 if ($replicateddatabase.MergePublications.Count -gt 0) 89 { 90 foreach($mergepub in $replicateddatabase.MergePublications) 91 { 92 [void] $sb1.clear(); 93 [void] $sb1.Append("/********************************************************************************/ `n"); 94 [void] $sb1.Append("/****** Writing to file script for MergePublications: " + $mergepub.Name+" ******/ `n"); 95 [void] $sb1.Append("/********************************************************************************/ `n"); 96 [void] $sb1.Append(""+$mergepub.script($scriptargs).tostring()+" `n"); 97 $Newsb1=$sb1.ToString(); 98 if((Test-Path ("$OutPutPath\$SqlServer"+"_"+$Port+"_"+$day+"_ReplScript_"+$replicateddatabase.Name+"_"+$mergepub.Name+"_Merge"+".Sql" ))) 99 { [void]( del ("$OutPutPath\$SqlServer"+"_"+$Port+"_"+$day+"_ReplScript_"+$replicateddatabase.Name+"_"+$mergepub.Name+"_Merge"+".Sql" )) 100 }; 101 $Newsb1|Out-File -FilePath ("$OutPutPath\$SqlServer"+"_"+$Port+"_"+$day+"_ReplScript_"+$replicateddatabase.Name+"_"+$mergepub.Name+"_Merge"+".Sql") -Append -Force; 102 103 } 104 } 105 106 } 107 108 [void] $sb1.clear(); 109 [void] (Remove-Variable -name sb1); 110 111 } 112 $SqlConn.Close(); 113 114 } 115 catch 116 { 117 $day=(Get-Date -Format "yyyyMMdd").tostring(); 118 ( ‘SqlerGetSqlReplScript : ‘+$using:server+$_.Exception.Message )|Out-File -FilePath "$using:LogPath\SqlerGet_$day.log" -Append -Force 119 } 120 } 121 122 } 123 $i=$i+$mutilline 124 Start-Sleep -Seconds 10 125 } 126 }
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
