Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Failover Clustering in the Cloud - Understanding Your Options - Free Webinar
 

SQL Server Database Mirroring Inventory and Monitoring Scripts


By:   |   Last Updated: 2018-10-08   |   Comments   |   Related Tips: More > Database Mirroring

Problem

Whether you inherited a set of servers supporting SQL Server database mirroring or plan to implement database mirroring in the future, it is a very good idea to know exactly which servers have mirroring configured and the on-going status of each of them to know when to react if there are any issues.

Solution

I will be presenting two scripts that can help you keep a track of SQL Server database mirroring and these can even automated. One script will be used to build the inventory of SQL Server instances that have at least one database with mirroring configured. The other script will be used to gather basic, but very useful information for each database in each instance.

Building the SQL Server Database Mirroring Inventory

The purpose of the following T-SQL code is to gather the list of databases configured with SQL Server database mirroring, but only where the role is “Principal”.

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

Here you can see an example of the output this generates.  The database size is in MB.

sql server result set for database mirroring status

Complemented this script with a PowerShell script, you can execute the above T-SQL code against each SQL Server instance under your care and store everything in a central database (which will be very helpful for the next section’s script).

The PowerShell script below has XXX in all the places where you will have to place the values according to your own environment.

$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!"

Checking the current status of each database from the SQL Server Database Mirroring Inventory

The mirroring inventory is built first because that way you can run this second script against a smaller subset of SQL Server instances. You can probably do it with one run against all servers, but that wouldn’t be as efficient.

Now that you have all your inventory in a central place, you can run the following T-SQL code against each SQL Server instance that is currently acting as the Principal.

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'

Here you can look at a quick sample of the output this generates:

sql server result set for database mirroring status

Complemented with a PowerShell script, you can execute the above T-SQL code against each SQL Server instance in the inventory table, so you can have the status each one of the databases.

The PowerShell script below has XXX in all the places where you will have to place the values according to your own environment.

$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!"

All the code shared and described within this tip has been successfully tested against the following versions of SQL Server: 2005, 2008, 2008R2, 2012, 2016 and 2017.

With this final output, you can choose to do whatever you want:

Next Steps
  • You can add as many performance counters you like.
  • Remember that database mirroring is now considered a deprecated feature, so every piece of code and information shared within this tip will not be valid once Microsoft decides to pull the plug in a future release of SQL Server, but for not it is still a supported feature for many versions of SQL Server.


Last Updated: 2018-10-08


next webcast button


next tip button



About the author
MSSQLTips author Alejandro Cobar Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools