SQL Server Database Mirroring Inventory

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:

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:

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

Download the scripts for this module

Next Steps

With the information presented in this article, you can setup a routine (through an SQL Agent job, schedule task, etc.) to have always up-to-date information about databases with mirroring within your environment. You could build an alerting mechanism around it to warn you when databases are not fully synchronized so that you can proactively address any issues.
Check out the other parts of this series:

Resources

Community

Subscribe