This module type is used to discover instances of Microsoft SQL Server 2008 Mirrored Databases Witnesses. The module connects to SQL Server and discovers all mirrored databases witnesses together with properties.
Dim oAPI
Set oAPI = MOMCreateObject("MOM.ScriptAPI")
Function MomCreateObject(ByVal sProgramId)
Dim oError
Set oError = New Error
On Error Resume Next
Set MomCreateObject = CreateObject(sProgramId)
oError.Save
On Error Goto 0
If oError.Number <> 0 Then ThrowScriptError "Unable to create automation object '" & sProgramId & "'", oError
End Function
'#Include File:ErrorHandling.vbs
Const EVENT_TYPE_ERROR = 1
Class Error
Private m_lNumber
Private m_sSource
Private m_sDescription
Private m_sHelpContext
Private m_sHelpFile
Public Sub Save()
m_lNumber = Err.number
m_sSource = Err.Source
m_sDescription = Err.Description
m_sHelpContext = Err.HelpContext
m_sHelpFile = Err.helpfile
End Sub
Public Sub Raise()
Err.Raise m_lNumber, m_sSource, m_sDescription, m_sHelpFile, m_sHelpContext
End Sub
Public Sub Clear()
m_lNumber = 0
m_sSource = ""
m_sDescription = ""
m_sHelpContext = ""
m_sHelpFile = ""
End Sub
Public Default Property Get Number()
Number = m_lNumber
End Property
Public Property Get Source()
Source = m_sSource
End Property
Public Property Get Description()
Description = m_sDescription
End Property
Public Property Get HelpContext()
HelpContext = m_sHelpContext
End Property
Public Property Get HelpFile()
HelpFile = m_sHelpFile
End Property
End Class
Function ThrowScriptErrorNoAbort(ByVal sMessage, ByVal oErr)
On Error Resume Next
oAPI.LogScriptEvent WScript.ScriptName, 4001, EVENT_TYPE_ERROR, sMessage & ". " & oErr.Description
End Function
Function ThrowScriptError(ByVal sMessage, ByVal oErr)
On Error Resume Next
ThrowScriptErrorNoAbort sMessage, oErr
Quit()
End Function
Sub WriteToEventLogAndExit(ByVal message)
oAPI.LogScriptEvent WScript.ScriptName, 4002, EVENT_TYPE_ERROR, message
WScript.Quit()
End Sub
'#Include File:WMI.vbs
Function EscapeWQLString (ByVal strValue)
On Error Resume Next
Err.Clear
EscapeWQLString = Replace(strValue, "'", "\'")
End Function
Function WMIGetProperty(oWmi, sPropName, nCIMType, ErrAction)
Dim sValue, oWmiProp
If Not IsValidObject(oWmi) Then
If (ErrAction And ErrAction_ThrowError) = ErrAction_ThrowError Then _
ThrowScriptErrorNoAbort "Accessing property on invalid WMI object.", Err
If (ErrAction And ErrAction_Abort) = ErrAction_Abort Then _
Quit()
WMIGetProperty = ""
Exit Function
End If
On Error Resume Next
Set oWmiProp = oWmi.Properties_.Item(sPropName)
If Err.Number <> 0 Then
If (ErrAction And ErrAction_ThrowError) = ErrAction_ThrowError Then _
ThrowScriptErrorNoAbort "An error occurred while accessing WMI property: '" & sPropName & "'.", Err
If (ErrAction And ErrAction_Abort) = ErrAction_Abort Then _
Quit()
End If
On Error GoTo 0
If IsValidObject(oWmiProp) Then
sValue = oWmiProp.Value
If IsNull(sValue) Then
'
' If value is null, return blank to avoid any issues
'
WMIGetProperty = ""
Else
Select Case (oWmiProp.CIMType)
Case wbemCimtypeString, wbemCimtypeSint16, wbemCimtypeSint32, wbemCimtypeReal32, wbemCimtypeReal64, wbemCimtypeSint8, wbemCimtypeUint8, wbemCimtypeUint16, wbemCimtypeUint32, wbemCimtypeSint64, wbemCimtypeUint64:
If Not oWmiProp.IsArray Then
WMIGetProperty = Trim(CStr(sValue))
Else
WMIGetProperty = Join(sValue, ", ")
End If
Case wbemCimtypeBoolean:
If sValue = 1 Or UCase(sValue) = "TRUE" Then
WMIGetProperty = "True"
Else
WMIGetProperty = "False"
End If
Case wbemCimtypeDatetime:
Dim sTmpStrDate
'
' First attempt to convert the whole wmi date string
'
sTmpStrDate = Mid(sValue, 5, 2) & "/" & _
Mid(sValue, 7, 2) & "/" & _
Left(sValue, 4) & " " & _
Mid (sValue, 9, 2) & ":" & _
Mid(sValue, 11, 2) & ":" & _
Mid(sValue, 13, 2)
If IsDate(sTmpStrDate) Then
WMIGetProperty = CDate(sTmpStrDate)
Else
'
' Second, attempt just to convert the YYYYMMDD
'
sTmpStrDate = Mid(sValue, 5, 2) & "/" & _
Mid(sValue, 7, 2) & "/" & _
Left(sValue, 4)
If IsDate(sTmpStrDate) Then
WMIGetProperty = CDate(sTmpStrDate)
Else
'
' Nothing works - return passed in string
'
WMIGetProperty = sValue
End If
End If
Case Else:
WMIGetProperty = ""
End Select
End If
Else
If (ErrAction And ErrAction_ThrowError) = ErrAction_ThrowError Then _
ThrowScriptErrorNoAbort "An error occurred while accessing WMI property: '" & sPropName & "'.", Err
If (ErrAction And ErrAction_Abort) = ErrAction_Abort Then _
Quit()
WMIGetProperty = ""
End If
If (ErrAction And ErrAction_Trace) = ErrAction_Trace Then _
WScript.Echo " + " & sPropName & " :: '" & WMIGetProperty & "'"
End Function
Function WMIExecQuery(ByVal sNamespace, ByVal sQuery)
'
' WMIExecQuery :: Executes the WMI query and returns the result set.
'
'
Dim oWMI, oQuery, nInstanceCount
Dim e
Set e = New Error
On Error Resume Next
Set oWMI = GetObject(sNamespace)
e.Save
On Error GoTo 0
If IsEmpty(oWMI) Then
ThrowScriptErrorNoAbort "Unable to open WMI Namespace '" & sNamespace & "'. Check to see if the WMI service is enabled and running, and ensure this WMI namespace exists.", e
ThrowEmptyDiscoveryData
End If
On Error Resume Next
Set oQuery = oWMI.ExecQuery(sQuery)
e.Save
On Error GoTo 0
If IsEmpty(oQuery) Or e.Number <> 0 Then
ThrowScriptError "The Query '" & sQuery & "' returned an invalid result set. Please check to see if this is a valid WMI Query.", e
End If
'Determine if we queried a valid WMI class - Count will return 0 or empty
On Error Resume Next
nInstanceCount = oQuery.Count
e.Save
On Error GoTo 0
If e.Number <> 0 Then
ThrowScriptError "The Query '" & sQuery & "' did not return any valid instances. Please check to see if this is a valid WMI Query.", e
End If
Set WMIExecQuery = oQuery
End Function
Function GetFirstItemFromWMIQuery(ByRef oQuery)
ON ERROR RESUME NEXT
Err.Clear
Dim oResult
Set oResult = oQuery.ItemIndex(0)
if Err.number <> 0 then
Err.Clear
Dim oObject
For Each oObject in oQuery
Set oResult = oObject
Exit For
Next
end if
Set GetFirstItemFromWMIQuery = oResult
End Function
'#Include File:ConnectionString.vbs
Function BuildConnectionString(strServer, strDatabase)
ON ERROR RESUME NEXT
Err.Clear
Dim dataSource
dataSource = BuildServerName(strServer, "")
BuildConnectionString = "Data Source=" & EscapeConnStringValue(dataSource) & ";Initial Catalog=" & EscapeConnStringValue(strDatabase) & ";Integrated Security=SSPI"
End Function
Function BuildConnectionStringWithPort(ByVal strServer, ByVal strDatabase, ByVal tcpPort)
ON ERROR RESUME NEXT
Err.Clear
Dim dataSource
dataSource = strServer
If ((tcpPort <> "0") And (tcpPort <> "")) Then
dataSource = dataSource & "," & tcpPort
End If
BuildConnectionStringWithPort = "Data Source=" & EscapeConnStringValue(dataSource) & ";Initial Catalog=" & EscapeConnStringValue(strDatabase) & ";Integrated Security=SSPI"
End Function
' This function should be used to escape Connection String keywords.
Function EscapeConnStringValue (ByVal strValue)
ON ERROR RESUME NEXT
Err.Clear
EscapeConnStringValue = """" + Replace(strValue, """", """""") + """"
End Function
Function EscapeWQLString (ByVal strValue)
ON ERROR RESUME NEXT
Err.Clear
EscapeWQLString = Replace(strValue, "'", "\'")
End Function
Function GetTcpPort (ByVal strServer)
ON ERROR RESUME NEXT
Err.Clear
Dim tcpPort
tcpPort = ""
Call BuildServerName(strServer, tcpPort)
GetTcpPort = tcpPort
End Function
Function BuildServerName(ByVal strServer, ByRef tcp)
ON ERROR RESUME NEXT
Err.Clear
Dim pathArray, instanceName, computerName, ip, serverName
Dim oWMI, oQuery
ip= ""
pathArray = Split(strServer, "\")
computerName = pathArray(0)
instanceName = "MSSQLSERVER"
if (pathArray.Count > 1) Then
instanceName = pathArray(1)
End If
serverName = strServer
Set oWMI = GetObject("winmgmts:\\" & computerName & "\root\Microsoft\SqlServer\" & SQL_WMI_NAMESPACE)
Set oQuery = oWMI.ExecQuery("SELECT * FROM ServerNetworkProtocolProperty WHERE ProtocolName = 'Tcp' AND InstanceName = '"& EscapeWQLString(instanceName) &"' AND PropertyName = 'ListenOnAllIPs'")
If oQuery.Count >0 Then
Dim isListenAll
Set isListenAll = GetFirstItemFromWMIQuery(oQuery)
If(isListenAll.PropertyNumVal = 1) Then
Set oQuery = oWMI.ExecQuery("SELECT * FROM ServerNetworkProtocolProperty WHERE ProtocolName = 'Tcp' AND InstanceName = '"& EscapeWQLString(instanceName) &"' AND IPAddressName = 'IPAll' AND (PropertyName = 'TcpPort' OR PropertyName = 'TcpDynamicPorts') AND PropertyStrVal <> ''")
If (oQuery.Count > 0) Then
tcp = GetFirstItemFromWMIQuery(oQuery).PropertyStrVal
If ((tcp <> "0") And (tcp <> "")) Then
serverName = serverName & "," & tcp
Else tcp = ""
End If
End If
Else
Set oQuery = oWMI.ExecQuery("SELECT * FROM ServerNetworkProtocolProperty WHERE ProtocolName = 'Tcp' AND InstanceName = '"& EscapeWQLString(instanceName) &"' AND IPAddressName <> '' AND PropertyName = 'Enabled' AND PropertyNumVal = 1")
If (oQuery.Count > 0) Then
Dim ipAddressName
ipAddressName = GetFirstItemFromWMIQuery(oQuery).IPAddressName
Set oQuery = oWMI.ExecQuery("SELECT * FROM ServerNetworkProtocolProperty WHERE ProtocolName = 'Tcp' AND InstanceName = '"& EscapeWQLString(instanceName) &"' AND IPAddressName = '"& EscapeWQLString(ipAddressName) &"' AND (PropertyName = 'TcpPort' OR PropertyName = 'TcpDynamicPorts') AND PropertyStrVal <> ''")
If (oQuery.Count > 0) Then
tcp = GetFirstItemFromWMIQuery(oQuery).PropertyStrVal
End If
Set oQuery = oWMI.ExecQuery("SELECT * FROM ServerNetworkProtocolProperty WHERE ProtocolName = 'Tcp' AND InstanceName = '"& EscapeWQLString(instanceName) &"' AND IPAddressName = '"& EscapeWQLString(ipAddressName) &"' AND PropertyName = 'IpAddress' AND PropertyStrVal <> ''")
If (oQuery.Count > 0) Then
ip = GetFirstItemFromWMIQuery(oQuery).PropertyStrVal
End If
If ip <> "" Then
serverName = ip
End If
If ((tcp <> "0") And (tcp <> "")) Then
serverName = servername & "," & tcp
Else tcp = ""
End If
End If
End If
End If
On Error Goto 0
BuildServerName = serverName
End Function
Public Function IsValidDestination(dbConnection, serverName, instanceName, isADODB)
Dim destinationTestQuery
destinationTestQuery = "select SERVERPROPERTY('MachineName') as ServerName, @@servicename as InstanceName"
if 0 = Err.number then
Dim queryResult
if isADODB then
Set queryResult = dbConnection.ExecuteQuery(destinationTestQuery)
else
Set queryResult = dbConnection.Execute(destinationTestQuery)
end if
if Not queryResult.EOF then
Dim queryServerName : queryServerName = UCase(queryResult("ServerName").Value)
Dim queryInstanceName : queryInstanceName = UCase(queryResult("InstanceName").Value)
Dim serverNameWithoutDomain : serverNameWithoutDomain = serverName
Dim dotPosition : dotPosition = InStr(1, serverName, ".")
if Not IsNull(dotPosition) And (dotPosition > 0) then
serverNameWithoutDomain = Left(serverName, dotPosition - 1)
end if
if (UCase(serverNameWithoutDomain) = queryServerName) And (UCase(instanceName) = queryInstanceName) then
IsValidDestination = true
Exit Function
end if
end if
end if
IsValidDestination = false
End Function
'NOTE: This will NOT work without SQLADODB.vbs /DKalinin/
'RETURNS:
Public Function SmartConnect(cnADOConnection, connectionStr, tcp, serverName, instanceName, databaseName)
ON ERROR RESUME NEXT
'try to use SQL server browser
Dim strProv : strProv = BuildConnectionStringWithPort(connectionStr, databaseName, "")
Err.Clear
Dim res : res = cnADOConnection.Open(strProv, "sqloledb", 10)
'use original tcp port and try to connect again
if (0 <> Err.number) Or (Not IsValidDestination(cnADOConnection, serverName, instanceName, true)) then
strProv = BuildConnectionStringWithPort(connectionStr, databaseName, tcp)
Err.Clear
res = cnADOConnection.Open(strProv, "sqloledb", 10)
'get fresh tcp port and try to connect again
if (0 <> Err.number) Or (Not IsValidDestination(cnADOConnection, serverName, instanceName, true)) then
Err.Clear
strProv = BuildConnectionString(connectionStr, databaseName)
res = cnADOConnection.Open(strProv, "sqloledb", 30)
if (0 <> Err.number) Or (Not IsValidDestination(cnADOConnection, serverName, instanceName, true)) then
cnADOConnection.HandleOpenConnectionErrorContinue databaseName, serverName, instanceName
SmartConnect = False
Exit Function
end if
end if
end if
SmartConnect = res
End Function
'NOTE: This WILL work without SQLADODB.vbs /DKalinin/
Public Function SmartConnectWithoutSQLADODB(connectionStr, tcp, serverName, instanceName, databaseName)
ON ERROR RESUME NEXT
Dim cnADOConnection
Set cnADOConnection = MomCreateObject("ADODB.Connection")
cnADOConnection.Provider = "sqloledb"
cnADOConnection.ConnectionTimeout = 30
'try to use SQL server browser
Dim strProv : strProv = BuildConnectionString(connectionStr, databaseName)
Err.Clear
cnADOConnection.Open strProv
'use original tcp port and try to connect again
if (0 <> Err.number) Or (Not IsValidDestination(cnADOConnection, serverName, instanceName, false)) then
Err.Clear
strProv = BuildConnectionStringWithPort(connectionStr, databaseName, tcp)
cnADOConnection.Open strProv
'get fresh tcp port and try to connect again
if (0 <> Err.number) Or (Not IsValidDestination(cnADOConnection, serverName, instanceName, false)) then
Err.Clear
strProv = BuildConnectionString(connectionStr, databaseName)
cnADOConnection.Open strProv
if (0 <> Err.number) Or (Not IsValidDestination(cnADOConnection, serverName, instanceName, false)) then
cnADOConnection.HandleOpenConnectionErrorContinue databaseName, serverName, instanceName
Set SmartConnectWithoutSQLADODB = Nothing
Exit Function
end if
end if
end if
Set SmartConnectWithoutSQLADODB = cnADOConnection
End Function
'#Include File:DatabaseHelpers.vbs
Function ExtractHostNameFromEndpoint(ByVal sEndpoint)
If sEndpoint = "" Then
ExtractHostNameFromEndpoint = ""
Exit Function
End If
Dim oRegEx, oMatches
Set oRegEx = MomCreateObject("VBScript.RegExp")
oRegEx.Pattern = "://(.+):"
Set oMatches = oRegEx.Execute(sEndpoint)
If oMatches.Count > 0 Then
If oMatches(0).SubMatches.Count > 0 Then
ExtractHostNameFromEndpoint = oMatches(0).SubMatches(0)
Else
ExtractHostNameFromEndpoint = sEndpoint
End If
Else
ExtractHostNameFromEndpoint = sEndpoint
End If
End Function
Function GetMirroringLevelName(ByVal nSafetyLevel, ByVal sWitnessName)
GetMirroringLevelName = "Unknown state"
If nSafetyLevel = 1 Then
GetMirroringLevelName = "High-performance mode"
Else
If sWitnessName = "" Then
GetMirroringLevelName = "High-safety mode without automatic failover"
Else
GetMirroringLevelName = "High-safety mode with automatic failover"
End If
End If
End Function
Function IsExcluded(ByVal sDatabase, ByVal sExcludeList)
Dim aExcludes, bMatch, nIndex
bMatch = False
If Trim(sExcludeList) = "*" Then
bMatch = True
Else
aExcludes = Split(sExcludeList, ",")
For nIndex = 0 To UBound(aExcludes)
If LCase(sDatabase) = LCase(Trim(aExcludes(nIndex))) Then
bMatch = True
End If
Next
End If
IsExcluded = bMatch
End Function
'#Include File:DiscoverSQL2008MirroringWitness.vbs
'Copyright (c) Microsoft Corporation. All rights reserved.
' Parameters that should be passed to this script
' 0 MPElement ID ($MPElement$)
' 1 Target Id for ME this rule is running against ($Target/Id$)
' 2 Computer Name
' 3 SQL Connection String for the instance that the DBs are being discovered on
' 4 SQL Instance that this rule is being run for
' 5 Exlcuded database list (prefixed with Exclude:)
Dim oDiscoveryData
Set oDiscoveryData = oAPI.CreateDiscoveryData(0, SourceId, ManagedEntityId)
If DoWitnessDiscovery() < 0 Then
oDiscoveryData.IsSnapshot = False
End If
Call oAPI.Return(oDiscoveryData)
WScript.Quit()
Function DoWitnessDiscovery()
If Len(ExcludeList) < 0 Then
WriteToEventLogAndExit("Database exclusion list invalid in Mirroring Witness Discovery. Aborting discovery.")
End If
Dim e
Set e = New Error
Dim cnADOConnection
Set cnADOConnection = SmartConnectWithoutSQLADODB(ConnectionString, sTcpPort, TargetComputer, InstanceName, "master")
if cnADOConnection Is Nothing Then
DoWitnessDiscovery = SQL_DISCOVERY_CONNECT_FAILURE
ThrowScriptErrorNoAbort "Cannot connect to SQL instance '" & InstanceName & "'", e
Exit Function
End If
' Query for the list of mirrored databases
Dim oResults
e.Clear
On Error Resume Next
Set oResults = cnADOConnection.Execute(GetMirroredDatabasesQuery)
e.Save
On Error Goto 0
If e.Number <> 0 Then
DoWitnessDiscovery = SQL_DISCOVERY_QUERY_FAILURE
ThrowScriptErrorNoAbort "Query execution failed for SQL instance '" & InstanceName & "'", e
Exit Function
End If
Dim bWitnessRoleCreated, oWitnessRole
bWitnessRoleCreated = False
Dim oWitnessInstance, oMirroringGroup, oRelationship
Dim sDBName, sMirroringGUID, sPrincipalInstance, sMirrorInstance, nState, nSafetyLevel
Do While Not oResults.EOF
sDBName = oResults(0)
If Not(IsExcluded(sDBName, ExcludeList)) Then
sMirroringGUID = oResults(1)
sPrincipalInstance = oResults(2)
sMirrorInstance = oResults(3)
nState = oResults(4)
nSafetyLevel = oResults(5)
If Not bWitnessRoleCreated Then
Set oWitnessRole = oDiscoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SQLServer.2008.Mirroring.WitnessRole']$")
With oWitnessRole
.AddProperty "$MPElement[Name='System!System.Entity']/DisplayName$", "Witness Role"
.AddProperty "$MPElement[Name='Windows!Microsoft.Windows.Computer']/PrincipalName$", TargetComputer
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.ServerRole']/InstanceName$", InstanceName
End With
Call oDiscoveryData.AddInstance(oWitnessRole)
bWitnessRoleCreated = True
End If
Set oWitnessInstance = oDiscoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SQLServer.2008.Mirroring.Witness']$")
With oWitnessInstance
.AddProperty "$MPElement[Name='System!System.Entity']/DisplayName$", "Witness for '" & sDBName & "' database"
.AddProperty "$MPElement[Name='Microsoft.SQLServer.2008.Mirroring.Witness']/DatabaseName$", sDBName
.AddProperty "$MPElement[Name='Microsoft.SQLServer.2008.Mirroring.Witness']/Principal$", ExtractHostNameFromEndpoint(sPrincipalInstance)
.AddProperty "$MPElement[Name='Microsoft.SQLServer.2008.Mirroring.Witness']/Mirror$", ExtractHostNameFromEndpoint(sMirrorInstance)
.AddProperty "$MPElement[Name='Microsoft.SQLServer.2008.Mirroring.Witness']/Level$", GetMirroringLevelName(nSafetyLevel, ConnectionString)
End With
Call oDiscoveryData.AddInstance(oWitnessInstance)
Set oMirroringGroup = oDiscoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SQLServer.2008.Mirroring.Group']$")
With oMirroringGroup
.AddProperty "$MPElement[Name='System!System.Entity']/DisplayName$", "'" & sDBName & "' database mirror"
.AddProperty "$MPElement[Name='Microsoft.SQLServer.2008.Mirroring.Group']/MirroringGUID$", sMirroringGUID
End With
Call oDiscoveryData.AddInstance(oMirroringGroup)
Set oRelationship = oDiscoveryData.CreateRelationshipInstance("$MPElement[Name='Microsoft.SQLServer.2008.Mirroring.GroupContainsWitness']$")
oRelationship.Source = oMirroringGroup
oRelationship.Target = oWitnessInstance
Call oDiscoveryData.AddInstance(oRelationship)
End If
oResults.MoveNext
Loop
Set oResults = Nothing
cnADOConnection.Close
DoWitnessDiscovery = SQL_DISCOVERY_SUCCESS
End Function </Script></ScriptBody>
<TimeoutSeconds>$Config/TimeoutSeconds$</TimeoutSeconds>
</DataSource>
</MemberModules>
<Composition>
<Node ID="DS"/>
</Composition>
</Composite>
</ModuleImplementation>
<OutputType>System!System.Discovery.Data</OutputType>
</DataSourceModuleType>
Home
ENU 
Show References: