SQL Server Database Mirroring Inventory and Monitoring Scripts



By:   |   Read 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.


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.


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:

  • Build a report, through
    Reporting Services and automate it as needed.
  • Build an HTML table and have it sent to you.

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 Update:


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


Leave a Reply

Your email address will not be published. Required fields are marked *

HashFlare