This object discovers SQL Server Instances which are currently being monitored by a SQL DM installations. The discovery runs on machines with SQL DM installations and examines the SQL DM database to identify managed instances.
Knowledge Base article:
Summary
This object discovers SQL Server Instances which are currently being monitored by a SQL DM installations. The discovery runs on machines with SQL DM installations and examines the SQL DM database to identify managed instances.
#==================================================================================
# Sub: Log-TxtFile
# Purpose: Logs an informational event to TXT log file, only
# if Debug key is true in Windows Registry >> SQLdm
#==================================================================================
function Log-TxtFile {
param($logPath,$message)
#==================================================================================
# Sub: LogDebugEvent
# Purpose: Logs an informational event to the Operations Manager event log
# only if Debug argument is true
#==================================================================================
function Log-DebugEvent
{
param($eventNo,$message,$logPath)
if ($debugTxt -eq $true){
Log-TxtFile $logPath $message
}
#==================================================================================
# Sub: Get-SqlQuery
# Purpose: Runs a SQL Query using the SqlClient data library
# Returns: A powershell variation on a data table - an object array with a count,
# followed by a series of DataRow objects.
#==================================================================================
function Get-SqlQuery( $connstr, $querydm ) {
# Action of connecting to the Database and executing the query and returning results if there were any.
$conn = New-Object System.Data.SqlClient.SQLConnection
$conn.ConnectionString = $connstr
$conn.Open()
$cmd = New-Object System.Data.SqlClient.SqlCommand($querydm, $conn)
$cmd.CommandTimeout = 120
$dset = New-Object System.Data.DataSet
$da = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($dset)
$conn.Close()
return $dset
}
#==================================================================================
# Sub: Get-DmServerInstance
# Purpose: Read 'SQLdmManagementService.exe.Config' file to get DM server instance
# Returns: The serverInstance as a string
#==================================================================================
function Get-DmServerInstance( $dmPath ) {
[xml]$dmxml = (Get-Content ($dmPath + "SQLdmManagementService.exe.Config"))
$serverInstance = $dmxml.SelectSingleNode("/configuration/Idera.SQLdm/Services/ManagementService/@repositoryServer").Value
return $serverInstance
}
# GET MOM SCRIPTING OBJECT
# Start by setting up API object and creating a discovery data object.
$api = New-Object -comObject 'MOM.ScriptAPI'
Log-DebugEvent $EVENT_LEVEL_INFO 'Creating MOMScriptAPI object: This object is the top-level object for the scripting API.' $dmPath
# GET INFORMATIONAL PROPERTIES
$messageParam = @"
Input parameters
Computer Name : {0}
SQLdm - Database name : {1}
SQLdm - Installation path: {2}
Source ID : {3}
Managed entity ID : {4}
"@
Log-DebugEvent $SCRIPT_STARTED ($messageParam -f $computerName, $dmDb, $dmPath, $sourceId, $managedEntityId) $dmPath
if (Test-Path -Path 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server') {
$sqlInstances = (get-itemproperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances
$instances = ""
foreach ($instance in $sqlInstances) {
$instances = $instances + ' . ' + $instance
}
$messageInstances = 'SQL Server instances' + "`n" + `
' InstalledInstances: ' + $instances
Log-DebugEvent $EVENT_LEVEL_INFO $messageInstances $dmPath
} else {
Log-DebugEvent $EVENT_LEVEL_WARNING 'No data to display for SQL Server instances' $dmPath
}
# DEFINE TRAP TO CAPTURE ERRORS
# Trap statement will catch any Exceptions and send details to the event log before exiting
# We always send direct to the event log - overriding the $debug flag - so we always log errors
trap {
$tmsg = "SQL DM Instance Discovery Script experienced an exception:`n`n$_"
Log-DebugEvent $SCRIPT_FAILED $tmsg $dmPath
$api.LogScriptEvent($SCRIPT_NAME,$SCRIPT_FAILED,$EVENT_LEVEL_ERROR,$tmsg)
exit
}
#Discovery data object requires the MPElement and Target/ID variables. The first argument in the method is always 0.
Log-DebugEvent $EVENT_LEVEL_INFO 'Creating MOMDiscoveryData object' $dmPath
$discoveryData = $api.CreateDiscoveryData(0, $sourceId, $managedEntityId)
# Read server table from SQLdm database
$qrystr = @"
SELECT TOP 300 [InstanceName]
,isnull([RealServerName], 'N/A') as [RealServerName]
,isnull([ServerVersion], 'N/A') as [ServerVersion]
,isnull([ServerEdition], 'N/A') as [ServerEdition]
,isnull([MaintenanceModeEnabled], 'N/A') as [MaintenanceModeEnabled]
FROM [dbo].[MonitoredSQLServers]
WHERE [Active] = 1
ORDER BY [InstanceName]
"@
if ($instances.Tables[0].Rows.count -lt 1) {
# No instances found so don't create a Property Instanceound"
Log-DebugEvent $DUMMY_PROPERTYBAG 'No monitored SQL Server instances found' $dmPath
exit
} else {
# LOOP ROUND EACH MONITORED SQL INSTANCE ROW
foreach ($row in $instances.Tables[0].Rows)
{
# Logging row values to know if these is or not NULL
# In NULL case, the exception message for MOMClassInstance object is:
# ID: ComMethodTargetInvocation, Message: Exception has been thrown by the target of an invocation.
$message = 'Created SqlInstance class instance' + "`n" + `
' Computer Name : ' + $computerName + "`n" + `
' Instance Name : ' + $row.InstanceName + "`n" + `
' Real Server Name: ' + $row.RealServerName + "`n" + `
' Server Version : ' + $row.ServerVersion + "`n" + `
' Server Edition : ' + $row.ServerEdition + "`n" + `
' Maintenance Mode: ' + $row.MaintenanceModeEnabled
Log-DebugEvent $CLASS_CREATED $message $dmPath
#Create a SqlInstance class instance for each monitored SQL server row we found in the DB.
Log-DebugEvent $EVENT_LEVEL_INFO 'CreateClassInstance: Creates a new monitoring class instance.' $dmPath
$instance = $discoveryData.CreateClassInstance("$MPElement[Name='Idera.SQLDM.SqlInstance']$")
#Since the Idera.SQLDM.SqlInstance class is hosted, we need to provide it's key property and the key properties of any parents.
#The key property of SqlInstance is InstanceName.
#The hosting class for SqlInstance is DmService which has no key property.
#The hosting class for DmService is Windows Computer which has a key property of PrincipalName.
$instance.AddProperty("$MPElement[Name='Windows!Microsoft.Windows.Computer']/PrincipalName$", $computerName)
$instance.AddProperty("$MPElement[Name='Idera.SQLDM.SqlInstance']/InstanceName$", $row.InstanceName)
$instance.AddProperty("$MPElement[Name='Idera.SQLDM.SqlInstance']/RealServerName$", $row.RealServerName)
$instance.AddProperty("$MPElement[Name='Idera.SQLDM.SqlInstance']/ServerVersion$", $row.ServerVersion)
$instance.AddProperty("$MPElement[Name='Idera.SQLDM.SqlInstance']/ServerEdition$", $row.ServerEdition)
$instance.AddProperty("$MPElement[Name='Idera.SQLDM.SqlInstance']/MaintenanceModeEnabled$", $row.MaintenanceModeEnabled)
Log-DebugEvent $EVENT_LEVEL_INFO 'AddInstance: Adds an item to the collection of discovery data.' $dmPath
$discoveryData.AddInstance($instance)
}
}
Home