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

MS Techie 2,686 Reputation points
2021-09-05T11:21:30.943+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,136 questions
{count} votes

1 answer

Sort by: Most helpful
  1. CathyJi-MSFT 21,111 Reputation points Microsoft Vendor
    2021-09-06T03:26:20.683+00:00

    Hi @MS Techie ,

    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.

    0 comments No comments