question

MSTechie-7364 avatar image
0 Votes"
MSTechie-7364 asked Cathyji-msft commented

Monitor availability of SQL server instance hosting the mirror database via Query

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

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·

1 Answer

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

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.




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.