locked
how to get notification email or other means when replication is down? RRS feed

  • Question

  • hi, Sir and madam,

    i hit critical issues . finally i found out the reason is sql server 2014 peer to peer is down due to some reasons

    to fix asap next time , how to get notification email or other means when replication is down?

    thank you very much


    delaynomore.

    Thursday, December 12, 2019 3:19 AM

All replies

  • I have not used peer to peer replication, but generally, you get some default alerts under SQL Server agent -- alerts. you can set them to notify an operator. 

    you can create custom alerts as well

    one other option, is to read error log and check for replication error and send an email alert.


    Hope it Helps!!

    Thursday, December 12, 2019 3:56 AM
  • I do 4 things. I first monitor msdistribution_history for errors. You will need to filter out a lot of transient error messages.

    Secondly, you can also monitor msrepl_errors. When you get an error you can email yourself.

    I thirdly inject tracer tokens and when latency is too large, I get emails. Here is the core of it.

    declare @table table([name] sysname, subscriber_db sysname, publisher_commit datetime, distributor_commit datetime, [Time to Subscriber] int, subscriber_commit datetime, [Time to Publisher] int)
    insert into @table 
    
    select distinct syss.name, msda.subscriber_db, publisher_commit,distributor_commit,[Time to Subscriber]=datediff(ss, publisher_commit,distributor_commit)
    ,subscriber_commit, datediff(ss, distributor_commit, subscriber_commit) [Time to Publisher] 
    from MStracer_history msth
    join MSdistribution_agents msda on msth.agent_id = msda.id
    join sys.servers syss on msda.subscriber_id = syss.server_id
    join MStracer_tokens on msth.parent_Tracer_id=MStracer_tokens.tracer_id
    where subscriber_commit >dateadd(mi,-20,getdate())
    --testing latency – note here alerts go out if the distribution agent is taking more that 10 minutes
    if exists( select  *   From @table where [time to subscriber]>600 or [Time to Publisher]>600)
    begin
    select @subject='Latency problem on '+@@Servername 
    print 'send out latency alert'
    EXEC msdb.dbo.sp_send_dbmail @recipients ='Operators@domain.com',@subject = @subject, @body = @subject, @profile_name = 'MAIL',@body_format = 'HTML' ;
    end
    
    
    4thly you can set up replication alerts. You will need to drill down on the individual agent for this. This will alert on latency.

    Thursday, December 12, 2019 3:06 PM
    Answerer
  • Hi delaynomore,

    >> how to get notification email or other means when replication is down?

    We can use the alert in SQL server agent job or the alters in replication monitor. You can try to configure predefined replication alerts to get the information.  Please refer to Configure Predefined Replication Alerts and Use Alerts for Replication Agent Events to get more information.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Friday, December 13, 2019 10:00 AM
  • I have used this method for a long time.

    https://www.mssqltips.com/sqlservertip/2258/monitor-sql-server-replication-latency-using-tracer-tokens/

    Create a SQL Agent job to run this script on a regular basis and then use normal SQL Agent alerts on job failure. 

    The advantage of using a SQL Agent job, vs regular alerting, is there may be times when you expect it not to work (maintenance windows, overnight, etc) and you can simply disable the job or schedule around them, instead of ignoring error emails.

    Friday, December 13, 2019 12:43 PM
  • Hi delaynomore,

    Any update? Did you set the alert successfully? If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members reading the thread.
    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, December 16, 2019 7:28 AM