SQL Server Database Mirroring Inventory and Monitoring Scripts


By:   |   Updated: 2018-10-08   |   Comments (3)   |   Related: 1 | 2 | 3 | 4 | 5 | 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


get scripts

next tip button



About the author




More SQL Server Solutions











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.





Friday, December 20, 2019 - 7:10:30 PM - Alejandro Cobar Back To Top

I forgot 1 very important detail:

In the list of instances that you create, you don't have to specify only the primary instance name, but all the instances under your support. The idea/spirit behind this script is that it always assumes that you don't know which instances have database mirroring configured in them, so it traverses every single one of the instances you specified and tells you which have mirroring.

Imagine if you are working with another DBA and he/she configures mirroring in a pair of instances without informing you... this script would show you exactly that. 

Hope it helps!


Friday, December 20, 2019 - 7:04:20 PM - Alejandro Cobar Back To Top

Hi bk,

Yes, you need to create a table that looks like this:

 This is an example of the result set that your query must return
 ############################
 # instance                 #
 ############################
 # server1\MSSQLSERVER1     #
 # server2                  #
 # server3                  #
 # server4\MSSQLSERVER_2K14 #
 # server5\SQLSERVER2016    #
 ############################

I always tell other DBAs that they should start with 1 instance in the instances table, try executing the script and then add the rest.

Let me know if it works for you.

Tuesday, December 10, 2019 - 6:44:58 AM - bk Back To Top

Please update me how we can achive this?do i need to create table where Instance primary instance name is stored?

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


download


Recommended Reading

SQL Server Database Mirroring Status Check and Manual Failover T-SQL Scripts

SQL Server Database Mirroring Status Check and Manual Failover PowerShell Scripts

SQL Server Database Mirroring Missing Logins Report

Synchronize SQL Server Agent Jobs for Database Mirroring

Configure SQL Server Database Mirroring Using SSMS





get free sql tips
agree to terms


Learn more about SQL Server tools