SQL Server Database Mirroring Inventory and Monitoring Scripts

$labServer = "XXX"
$inventoryDB = "XXX"

#Clean the table where the inventory data will be stored
Invoke-Sqlcmd -Query "TRUNCATE TABLE XXX" -Database $inventoryDB -ServerInstance $labServer

#Fetch all the instances under your support
$instanceLookupQuery = /* Query that returns to you the list of instances under your support */

$instances = Invoke-Sqlcmd -ServerInstance $labServer -Database $inventoryDB -Query $instanceLookupQuery

#Create a temporary table to store all the results fetched from all the instances
$tmpTableQuery = "
CREATE TABLE ##MirroringInformation(
   [PrincipalServer] [nvarchar](255) NOT NULL,
   [MirrorServer] [nvarchar](255) NOT NULL,
   [DatabaseName] [nvarchar](255) NOT NULL,
   [DatabaseSize] [int] NOT NULL,
   [OperatingMode] [nvarchar](20) NOT NULL,
   [Port] [int] NOT NULL
)
"
Invoke-Sqlcmd -ServerInstance $labServer -Database 'tempdb' -Query $tmpTableQuery

$mirroringQuery = "
SELECT 
   SERVERPROPERTY('ServerName') AS Principal,
   m.mirroring_partner_instance AS Mirror,
   DB_NAME(m.database_id) AS DatabaseName,
   SUM(f.size*8/1024) AS DatabaseSize,
   CASE m.mirroring_safety_level
      WHEN 1 THEN 'HIGH PERFORMANCE'
      WHEN 2 THEN 'HIGH SAFETY'
   END AS 'OperatingMode',
   RIGHT(m.mirroring_partner_name, CHARINDEX( ':', REVERSE(m.mirroring_partner_name) + ':' ) - 1 ) AS Port
FROM sys.database_mirroring m
JOIN sys.master_files f ON m.database_id = f.database_id
WHERE m.mirroring_role_desc = 'PRINCIPAL'
GROUP BY m.mirroring_partner_instance, m.database_id, m.mirroring_safety_level, m.mirroring_partner_name
"

#For each instance, grab the mirroring information
foreach ($instance in $instances){
    #Go grab the mirroring information for the instance
   Write-Host "Fetching Mirroring information for instance" $instance.instance
   $results = Invoke-Sqlcmd -Query $mirroringQuery -ServerInstance $instance.name -ErrorAction Stop -querytimeout 30

   #Build the INSERT statement if it returned at least 1 row
    if($results.Length -ne 0){

      #Build the insert statement
      $insert = "INSERT INTO ##MirroringInformation VALUES"
      foreach($result in $results){
         $insert += "
         (
         '"+$result.Principal+"',
         '"+$result.Mirror+"',
         '"+$result.DatabaseName+"',
         '"+$result.DatabaseSize+"',
         '"+$result.OperatingMode+"',
         "+$result.Port+"
         ),
         "
      }

      #Store the results in the temp table ##MirroringInformation table, residing in your central environment
      Invoke-Sqlcmd -Query $insert.Substring(0,$insert.LastIndexOf(',')) -ServerInstance $labServer -Database $inventoryDB
   }
}

 #Store the final result in your central database
 $finalInsert = "
 INSERT INTO XXX
 SELECT PrincipalServer,MirrorServer,DatabaseName,DatabaseSize,OperatingMode,Port
 FROM ##MirroringInformation
 "
 Invoke-Sqlcmd -Query $finalInsert -ServerInstance $labServer -Database $inventoryDB

Write-Host "Done!"

$labServer = "XXX"
$inventoryDB = "XXX"

#Clean the MonitoringStatus table 
#The schema of the table has to be such that it can fit the results from the $mirroringInformation query described below
Invoke-Sqlcmd -Query "TRUNCATE TABLE XXX" -Database $inventoryDB -ServerInstance $labServer

#Fetch all the instances under your support
$instanceLookupQuery = /* Query that returns to you the list of instances built from the mirroring inventory script */

$instances = Invoke-Sqlcmd -ServerInstance $labServer -Database $inventoryDB -Query $instanceLookupQuery

$mirroringInformation = "
SELECT
   SERVERPROPERTY('ServerName') AS Principal,
   m.mirroring_partner_instance AS DR, 
   DB_NAME(m.database_id) AS [Database],
   m.mirroring_state_desc AS [State], 
   CASE m.mirroring_safety_level_desc WHEN 'OFF' THEN 'High Performance' ELSE 'High Safety' END AS [OperatingMode],
   CAST((pc.cntr_value)/1024/1024 AS DECIMAL(10,3)) AS unsentGB
FROM sys.database_mirroring m
JOIN sys.dm_os_performance_counters pc ON DB_NAME(m.database_id) = pc.instance_name
WHERE m.mirroring_state IS NOT NULL
  AND m.mirroring_state <> 4
  AND pc.object_name LIKE '%Database Mirroring%'
  AND pc.counter_name = 'Log Send Queue KB'
"

#For each instance, grab the mirroring information
foreach ($instance in $instances){
   $mirroringStatusQuery = Invoke-Sqlcmd -ServerInstance $labServer -Database $inventoryDB -Query $mirroringInformation -MaxCharLength 8000
   
    #Go grab the mirroring information for the instance
   $results = Invoke-Sqlcmd -Query $mirroringStatusQuery.tsql -ServerInstance $instance.name -ErrorAction Stop -querytimeout 30

   if($results.Length -ne 0){

      #Build the insert statement
      $insert = "INSERT INTO XXX VALUES"
      foreach($result in $results){
         $insert += "
         (
         '"+$result.Principal+"',
         '"+$result.DR+"',
         '"+$result.Database+"',
         '"+$result.State+"',
         '"+$result.OperatingMode+"',
         "+$result.unsentGB+"
         ),
         "
      }

      #Store the results in the table of your central database where you will have the information of all instances
      Invoke-Sqlcmd -Query $insert.Substring(0,$insert.LastIndexOf(',')) -ServerInstance $labServer -Database $inventoryDB
   }
}

Write-Host "Done!"