此監視器會檢查資料庫鏡像見證是否可存取。
此監視器會查看 Microsoft® SQL Server™ 所回報的資料庫鏡像見證狀態。其使用方式是對 SQL Server 執行個體的 master 資料庫執行查詢,並傳回該資料庫鏡像見證的狀態。如果您從此監視器收到警示,就需要採取動作才能將資料庫鏡像見證回復到運作的狀態。
狀況不佳的狀態表示 SQL Server™ 資料庫鏡像見證目前無法使用。除了 CONNECTED 與 NO-WITNESS 狀態以外的所有狀態,都會造成狀況不佳的狀態。可能的狀態詳述如下:
鏡像見證狀態 | 描述 | MOM 健全狀況狀態 |
CONNECTED | 這是正常運作狀態。鏡像夥伴可以存取見證伺服器。 | 狀況良好 |
NO-WITNESS | 此狀態表示沒有為鏡像工作階段指定任何見證。對於高效能模式以及不具有自動容錯移轉的高安全性模式而言,這是正常運作狀態。 | 狀況良好 |
UNKNOWN | 鏡像夥伴與見證伺服器之間發生一些連接問題。 | 警告 |
DISCONNECTED | 鏡像夥伴與見證伺服器之間沒有任何連接。 | 重大 |
若要解決此問題,請嘗試下列動作::
確定鏡像組態設定完成,而且鏡像處理序正常執行。
檢查鏡像夥伴與見證伺服器之間的實體連結。
根據端點組態設定這兩個伺服器的防火牆規則。
名稱 | 描述 | 預設值 |
警示優先程式 | 定義警示優先順序。 | 一般 |
警示嚴重性 | 定義警示嚴重性。 | MatchMonitorHealth |
已啟用 | 啟用或停用該工作流程。 | 是 |
產生警示 | 定義工作流程是否會產生警示。 | 是 |
間隔 (秒) | 執行工作流程的週期性時間間隔 (秒)。 | 900 |
同步處理時間 | 使用 24 小時制指定的同步處理時間。可以予以省略。 |
|
逾時 (秒) | 指定工作流程在關閉並標記為失敗前所能執行的時間。 | 300 |
Target | Microsoft.SQLServer.2012.Mirroring.Database | ||
Parent Monitor | System.Health.AvailabilityState | ||
Category | AvailabilityHealth | ||
Enabled | True | ||
Alert Generate | True | ||
Alert Severity | MatchMonitorHealth | ||
Alert Priority | Normal | ||
Alert Auto Resolve | True | ||
Monitor Type | Microsoft.SQLServer.2012.Mirroring.ScriptedThreeStateType | ||
Remotable | True | ||
Accessibility | Public | ||
Alert Message |
| ||
RunAs | Microsoft.SQLServer.SQLProbeAccount |
<UnitMonitor ID="Microsoft.SQLServer.2012.Mirroring.WitnessStateMonitor" Accessibility="Public" Enabled="true" Target="SQL2012Mirroring!Microsoft.SQLServer.2012.Mirroring.Database" ParentMonitorID="Health!System.Health.AvailabilityState" TypeID="Microsoft.SQLServer.2012.Mirroring.ScriptedThreeStateType" Remotable="true" Priority="Normal" ConfirmDelivery="false" RunAs="SQL!Microsoft.SQLServer.SQLProbeAccount">
<Category>AvailabilityHealth</Category>
<AlertSettings AlertMessage="Microsoft.SQLServer.2012.Mirroring.WitnessStateMonitor.AlertMessage">
<AlertOnState>Warning</AlertOnState>
<AutoResolve>true</AutoResolve>
<AlertPriority>Normal</AlertPriority>
<AlertSeverity>MatchMonitorHealth</AlertSeverity>
<AlertParameters>
<AlertParameter1>$Target/Property[Type="SQL!Microsoft.SQLServer.Database"]/DatabaseName$</AlertParameter1>
<AlertParameter2>$Target/Host/Property[Type="SQL!Microsoft.SQLServer.ServerRole"]/InstanceName$</AlertParameter2>
<AlertParameter3>$Target/Host/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/PrincipalName$</AlertParameter3>
<AlertParameter4>$Target/Property[Type="SQL2012Mirroring!Microsoft.SQLServer.2012.Mirroring.Database"]/Witness$</AlertParameter4>
</AlertParameters>
</AlertSettings>
<OperationalStates>
<OperationalState ID="Good" MonitorTypeStateID="Good" HealthState="Success"/>
<OperationalState ID="NeitherGoodNorBad" MonitorTypeStateID="NeitherGoodNorBad" HealthState="Warning"/>
<OperationalState ID="Bad" MonitorTypeStateID="Bad" HealthState="Error"/>
</OperationalStates>
<Configuration>
<ScriptName>GetSQL2012MirroringWitnessState.vbs</ScriptName>
<ScriptBody> '#Include File:GetSQL2008MirroringWitnessState.vbs
'Copyright (c) Microsoft Corporation. All rights reserved.
' This script takes a single parameter
' Param 0: The SQL connection string to connect to
Const SQL_MONITORING_CONNECT_FAILURE = -1
Const SQL_MONITORING_QUERY_FAILURE = -2
Const SQL_MONITORING_SUCCESS = 0
Const SCRIPT_EVENT_ID = 4001
Dim GetWitnessStatusQuery
GetWitnessStatusQuery = "SELECT " &_
" d.name, " &_
" CASE WHEN dm.mirroring_witness_name = '' THEN 'NO-WITNESS' ELSE ISNULL(dm.mirroring_witness_state_desc, 'UNKNOWN') END " &_
" FROM sys.databases d " &_
" INNER JOIN sys.database_mirroring dm ON dm.database_id = d.database_id " &_
" WHERE dm.mirroring_guid IS NOT NULL"
Call GetWitnessAccessibility()
Sub GetWitnessAccessibility()
If WScript.Arguments.Count = 4 Then
Dim oBag
Set oBag = oAPI.CreatePropertyBag()
Dim state, serviceName
serviceName = GetSQLServiceName(WScript.Arguments(2))
state = GetServiceState(WScript.Arguments(1), serviceName)
if (state <> "Running") And (state <> "Unknown") Then
Call oAPI.Return(oBag)
WScript.Quit()
End If
Dim nResult
nResult = WitnessAccessibility(WScript.Arguments(0), oBag, WScript.Arguments(1), WScript.Arguments(2), WScript.Arguments(3))
Call oAPI.Return(oBag)
On Error Resume Next
Call GlobalErrorListToEventLog()
Else
On Error Resume Next
Call GlobalErrorListToEventLog()
Wscript.Quit()
End If
End Sub
Function WitnessAccessibility(ByVal sSQLConnectionString, ByRef oBag, ByVal sComputerName, ByVal sInstanceName, ByVal sTcpPort)
Dim e
Set e = New Error
Dim cnADOConnection
Set cnADOConnection = SmartConnectWithoutSQLADODB(sSQLConnectionString, sTcpPort, sComputerName, sInstanceName, "master")
if cnADOConnection Is Nothing Then
WitnessAccessibility = SQL_MONITORING_CONNECT_FAILURE
ThrowScriptErrorNoAbort "Cannot connect to '" & sSQLConnectionString & "' SQL Server", e
Exit Function
End If
Dim oResults
e.Clear
On Error Resume Next
Set oResults = cnADOConnection.Execute(GetWitnessStatusQuery)
e.Save
On Error Goto 0
If e.Number <> 0 Then
WitnessAccessibility = SQL_MONITORING_QUERY_FAILURE
ThrowScriptErrorNoAbort "Query execution failed for '" & sSQLConnectionString & "' SQL Server", e
If (oResults <> null) Then oResults.Close
cnADOConnection.Close
Exit Function
End If
Do While Not oResults.EOF
Call oBag.AddValue(oResults(0) & "-State", CStr(oResults(1)))
oResults.MoveNext
Loop
cnADOConnection.Close
WitnessAccessibility = SQL_MONITORING_SUCCESS
End Function
</ScriptBody>
<IntervalSeconds>900</IntervalSeconds>
<SyncTime/>
<TimeoutSeconds>300</TimeoutSeconds>
<ConnectionString>$Target/Host/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/ConnectionString$</ConnectionString>
<GoodExpression>
<Or>
<Expression>
<SimpleExpression>
<ValueExpression>
<XPathQuery Type="String">Property[@Name='$Target/Property[Type="SQL!Microsoft.SQLServer.Database"]/DatabaseName$-State']</XPathQuery>
</ValueExpression>
<Operator>Equal</Operator>
<ValueExpression>
<Value Type="String">CONNECTED</Value>
</ValueExpression>
</SimpleExpression>
</Expression>
<Expression>
<SimpleExpression>
<ValueExpression>
<XPathQuery Type="String">Property[@Name='$Target/Property[Type="SQL!Microsoft.SQLServer.Database"]/DatabaseName$-State']</XPathQuery>
</ValueExpression>
<Operator>Equal</Operator>
<ValueExpression>
<Value Type="String">NO-WITNESS</Value>
</ValueExpression>
</SimpleExpression>
</Expression>
</Or>
</GoodExpression>
<NeitherGoodNorBadExpression>
<SimpleExpression>
<ValueExpression>
<XPathQuery Type="String">Property[@Name='$Target/Property[Type="SQL!Microsoft.SQLServer.Database"]/DatabaseName$-State']</XPathQuery>
</ValueExpression>
<Operator>Equal</Operator>
<ValueExpression>
<Value Type="String">UNKNOWN</Value>
</ValueExpression>
</SimpleExpression>
</NeitherGoodNorBadExpression>
<BadExpression>
<SimpleExpression>
<ValueExpression>
<XPathQuery Type="String">Property[@Name='$Target/Property[Type="SQL!Microsoft.SQLServer.Database"]/DatabaseName$-State']</XPathQuery>
</ValueExpression>
<Operator>Equal</Operator>
<ValueExpression>
<Value Type="String">DISCONNECTED</Value>
</ValueExpression>
</SimpleExpression>
</BadExpression>
</Configuration>
</UnitMonitor>