SQL Server Database Mirroring Inventory
By: Alejandro Cobar | Updated: 2021-07-02 | Comments | Related: More > Database Mirroring
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.
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.
In order to use this module, you’ll need the create the core objects found here.
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.
- 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.
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.
The PowerShell script that collects the information from each instance is called:
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:
- Right click on Get-MSSQL-Instance-Mirroring.ps1 and select Run with PowerShell
- Open a command window and navigate to the folder where you saved the above files and run
Check Creation of Database Objects
Here are the database objects that you should see in your central database, after executing the PowerShell script:
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.
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'
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
- 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:
- Monitoring SQL Server with PowerShell Core Object Setup
- Monitoring SQL Server with PowerShell Instance Data Collection
- Monitoring SQL Server with PowerShell Instance Jobs Collection
- Monitoring SQL Server with PowerShell Instance Jobs Last Execution
- Monitoring SQL Server with PowerShell Instance Backups
- Monitoring SQL Server with PowerShell Instance Database and Database Files
About the author
View all my tips
Article Last Updated: 2021-07-02