I have SQL server 2016 and 2017
How to Monitor availability of SQL server instance hosting the mirror database using SQL Query or PowerShell.
Please help
I have SQL server 2016 and 2017
How to Monitor availability of SQL server instance hosting the mirror database using SQL Query or PowerShell.
Please help
Hi @MSTechie-7364,
We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it is not, please let us know. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
Hi @MSTechie-7364,
Did you want to use scripts to monitor database mirroring? If I misunderstood, please let me know.
Please check below T-SQL. Please refer to below blogs to get more scripts about this.
DECLARE @Mirror TABLE
(database_name SYSNAME,[role] INT,mirroring_state INT
,witness_status INT,log_generation_rate INT,unsent_log INT
,send_rate INT,unrestored_log INT,recovery_rate INT
,transaction_delay INT,transactions_per_sec INT,average_delay INT
,time_recorded DATETIME,time_behind DATETIME,local_time DATETIME)
-- @id will increment and pull each mirrored database
DECLARE @id INT
,@db VARCHAR(256)
,@command VARCHAR(2000)
SELECT @id = MIN(database_id) FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL
-- Loop through each database to pull mirror monitor information
WHILE @id IS NOT NULL
BEGIN
SELECT @db = d.name FROM sys.databases d WHERE d.database_id = @id
SELECT @command = 'EXEC msdb.dbo.sp_dbmmonitorresults @database_name='+QUOTENAME(@db)+''
PRINT @command
INSERT INTO @Mirror
EXEC (@command)
--Increment @id
SELECT @id = MIN(database_id) FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL AND database_id > @id
END
-- Your WHERE clause/values will vary
SELECT * FROM @Mirror
WHERE unsent_log > 10
Database Mirror Monitoring with Scripts
Custom Metrics for Monitoring Database Mirroring
We can also use Database Mirroring Monitor in SSMS to monitor SQL mirroring. Right click mirror DB > Task> Launch Database Mirroring Monitor. Refer to MS document Start Database Mirroring Monitor (SQL Server Management Studio).
I have SQL server 2016 and 2017
The principal and mirror server instances must be running on the same version of SQL Server. While it is possible for the mirror server to have a higher version of SQL Server, this configuration is only recommended during a carefully planned upgrade process.
If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.
15 people are following this question.