Post reply

Alerts for Database Mirroring

  • July 15, 2013 at 8:30 am

    #279913

    Hi

    I'm trying to get SQL Server to alert me via email when the Primary instance loses connection with Mirrored instance (i.e. mirroring becomes suspended/inactive)

    So far, the methods I've found require the Service Broker to be enabled on the Mirrored DB, unfortunately, this cannot be enabled in this scenario.

    Is there any other simple way that I can simply set MSSQL to notify me (via email) when Mirroring isn't active?

    Thanks.

  • July 15, 2013 at 8:39 am

    #1632498

    Hi,

    If you run the following query:

    select * from sysmessages where description like '%mirror%'

    You'll get a list of error messages that relate to mirroring. Looks like error 1454 will be fired for the situation you describe. Using that error no. and the SQL Server Agent you can setup an alert for when that error no. occurs.

    To do that, right-click alerts, new alert, and set the configuration for the error nos. you want to be alerted to. You'll need to configure an operator if you haven't already.

    Thanks,

    Simon


    MCSE: Data Platform
    MCSE: Business Intelligence
    Follow me on Twitter: @WazzTheBadger
    LinkedIn Profile: Simon Osborne

  • July 15, 2013 at 8:49 am

    #1632502

    Below is code my colleagues have used to check status of mirroring:

    SELECT distinct d.name,

    m.mirroring_role_desc,

    m.mirroring_state_desc, m.mirroring_safety_level_desc--,

    --m.mirroring_partner_name, m.mirroring_partner_instance,

    --m.mirroring_witness_name, m.mirroring_witness_state_desc

    FROM sys.database_mirroring m JOIN sys.databases d

    ON m.database_id = d.database_id

    where m.mirroring_role_desc is not null

  • July 19, 2013 at 8:43 am

    #1634057

    I wrote a query that solves this problem for our SQL Monitor tool. You should be able to modify it for your own uses. Here's the query.[/url]

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • July 29, 2013 at 8:34 am

    #1636664

    Hi

    Problem with that is that the alert will not trigger because the event does not get written to the logs, this is something I've tested.

    Effectively, I just need an alert to trigger when the Principal database loses connection the Mirroring database, in the event of a network issue for example.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply