SQL Server Database Mirroring Inventory


By:   |   Updated: 2021-07-02   |   Comments   |   Related: More > Database Mirroring


Problem

Nowadays SQL Server Always On Availability Groups is the solution most DBAs use to cover their HA/DR needs. However, this doesn’t mean that Database Mirroring isn’t still being used in your environment, even if it’s a deprecated feature. If you manage dozens or hundreds of instances, and use many of them to house databases configured with mirroring (for whatever reason), wouldn’t you like to have a quick way to have an inventory of databases under such configuration?  Well, this article will provide you the tool to have such information at the tip of your fingers.

Solution

I will present in this tutorial a PowerShell script that will collect information about all the databases using SQL Server Database Mirroring throughout your entire database infrastructure.

Prerequisites

In order to use this module, you’ll need the create the core objects found here.

Database Objects

For this particular module, one table will be created (in case it doesn’t already exist).  Below is more information about the table, so you that can get an idea of what data will be stored.

*If you want to add/remove columns, make sure to adjust the respective structure within the PowerShell script and adapt the respective logic that will handle what you want to modify.

Tables

  • inventory.Mirroring
    • principal_serverId: the id of the instance that ties back to inventory.MasterServerList.
    • dr_serverId: the id of the instance that ties back to inventory.MasterServerList.
    • witness_serverId: the id of the instance that ties back to inventory.MasterServerList (NULL if the setup isn’t using a witness instance).
    • database_name: the name of the database under database mirroring.
    • state: the current state of the synchronization (SYNCHRONIZED/SYNCHRONIZING).
    • operating_mode: the mode that mirroring is currently operating with (Synchronous/Asynchronous).
    • data_collection_timestamp: the timestamp value when the record was fetched from the instance.

Important Note:

There will be times when you simply do not want to collect this information from all the registered instances you have in your Master Server List. To address this, simply modify the query text inside the variable $instanceLookupQuery within the PowerShell script and you should be good to go.

PowerShell Script

The PowerShell script that collects the information from each instance is called:

  • Get-MSSQL-Instance-Mirroring.ps1

The script has some validations that will help you check if some key elements are missing for the script to run successfully. For instance, it will confirm that the inventory.MasterServerList table exists and that it has at least 1 active instance registered to be able to have something to work with.

If you have followed along the other modules, you will notice that we have been storing all objects in "C:\temp", but you can use any folder you want. If you make a change to the central folder location, you will need to edit the first line in the following PowerShell script to specify the new folder location.

How to Use

Navigate to the folder where you created the files and you can run the PowerShell script as follows:

Option 1

  • Right click on Get-MSSQL-Instance-Mirroring.ps1 and select Run with PowerShell

Option 2

  • Open a command window and navigate to the folder where you saved the above files and run
powershell "C:\temp\Get-MSSQL-Instance-Mirroring.ps1"

Check Creation of Database Objects

Here are the database objects that you should see in your central database, after executing the PowerShell script:

Output

To demonstrate the output that the script produces, I have configured a test database (called "db2") in one of my instances. Therefore, after the script is executed, the output of the inventory.Mirroring table will look like this:

query results from data collection

Notice that I’m using "High Performance" mode and that the state is "SYNCHRONIZED".

Now, let me change the operating mode to "High Safety" and re-execute the PowerShell script to see what the table now has stored:

query results from data collection

Perfect, the script capture that modification and it is successfully reflected in the inventory.Mirroring table.

Important Note:

Every time the script is executed, the information from the inventory.Mirroring table is cleared and the most recent information is stored. If you’d like to keep some sort of historical information, you can adapt that logic in the PowerShell script.

Checking for Errors

To check for errors query the monitoring.ErrorLog table using the following query:

SELECT *
FROM monitoring.ErrorLog
WHERE script = 'Get-MSSQL-Instance-Mirroring'

If you’d like to know the SQL Server instance that got the errors, you would have to issue the query like this:

SELECT 
   CASE WHEN msl.instance = 'MSSQLSERVER' THEN msl.server_name ELSE 
   CONCAT(msl.server_name,'\',msl.instance) END AS instance,
   e.script,
   e.message,
   e.error_timestamp
FROM monitoring.ErrorLog e
JOIN inventory.MasterServerList msl ON msl.serverId = e.serverId
WHERE e.script = 'Get-MSSQL-Instance-Mirroring'

Useful Queries

Check if there’s at least one database that is not fully Synchronized:

SELECT *
FROM inventory.Mirroring
WHERE state <> 'SYNCHRONIZED'

Sort the inventory of mirrored databases by witness so that you can see which databases don’t have automatic failover:

SELECT *
FROM inventory.Mirroring
ORDER BY witness

Get the full output of the inventory.Mirroring table using the server names instead of their IDs:

SELECT
   (SELECT CASE WHEN instance = 'MSSQLSERVER' THEN server_name ELSE CONCAT(server_name,'\',instance) END FROM inventory.MasterServerList WHERE serverId = m.principal_serverId) AS [Principal],
   (SELECT CASE WHEN instance = 'MSSQLSERVER' THEN server_name ELSE CONCAT(server_name,'\',instance) END FROM inventory.MasterServerList WHERE serverId = m.dr_serverId) AS [DR],
   CASE WHEN witness_serverId IS NULL THEN 'N/A' ELSE (SELECT CASE WHEN instance = 'MSSQLSERVER' THEN server_name ELSE CONCAT(server_name,'\',instance) END FROM inventory.MasterServerList WHERE serverId = m.witness_serverId) END AS [Witness],
   database_name,
   state,
   operating_mode
FROM inventory.Mirroring m

Download Scripts

Next Steps





get scripts

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


Article Last Updated: 2021-07-02

Comments For This Article





download














get free sql tips
agree to terms