Set Warnings (Alerts & Thresholds) in MSSQL Replication Monitor using TSQL

Question

How can I configure the settings on this screen in Replication Monitor using TSQL? For reference, we're on SQL 2014.

Answer 1

Detailed information about programmatically monitoring replications can be found on the official Microsoft Docs site:

Reference: Programmatically Monitor Replication

There is detailed information on all the stored procedures required to query monitoring details:

To monitor Publishers, publications, and subscriptions from the Distributor

• sp_replmonitorhelppublisher
• sp_replmonitorhelppublication
• sp_replmonitorhelpsubscription

To monitor transactional commands waiting to be applied at the Subscriber

• sp_replmonitorsubscriptionpendingcmd

To monitor merge changes waiting to be uploaded or downloaded

• At the Publisher: sp_showpendingchanges

  returns a result set showing information on changes that are waiting to be replicated to Subscribers

• At the Subscriber: sp_showpendingchanges

  returns a result set showing information on changes that are waiting to be replicated to the Publisher

To monitor Merge Agent sessions

• sp_replmonitorhelpmergesession (either at the subscriber or distributor)
• sp_replmonitorhelpmergesessiondetail (either at the subscriber or distributor)

Answering your question

The stored procedures required to read or change the monitoring thresholds are the sp_replmontirohelppublicationthresholds and sp_replmonitorchangepublicationthreshold procedures.

To view the monitor threshold metrics for a publication

• sp_replmonitorhelppublicationthresholds

sp_replmonitorhelppublicationthresholds 
          [ @publisher = ] 'publisher'  
        , [ @publisher_db = ] 'publisher_db'  
        , [ @publication = ] 'publication'   
    [ , [ @publication_type = ] publication_type ]   
    [ , [ @thresholdmetricname = ] 'thresholdmetricname'  

The parameters are:

@publisher : the name of the publisher
@publisher_db : the name of the published database
@pbulication : the name of the publication
@publication_type : 0 = transactional publication
................ 1 = snapshot publication
................ 2 = merge publication

To modify the monitor threshold metrics for a publication

• sp_replmonitorchangepublicationthreshold

sp_replmonitorchangepublicationthreshold 
          [ @publisher = ] 'publisher'  
        , [ @publisher_db = ] 'publisher_db'  
        , [ @publication = ] 'publication'   
    [ , [ @publication_type = ] publication_type ]   
    [ , [ @metric_id = ] metric_id ]   
    [ , [ @thresholdmetricname = ] 'thresholdmetricname'   
    [ , [ @value = ] value ]   
    [ , [ @shouldalert = ] shouldalert ]   
    [ , [ @mode = ] mode ]  

The parameters are:

@publisher : the name of the publisher
@publisher_db : the name of the published database
@pbulication : the name of the publication
@publication_type : 0 = transactional publication
................ 1 = snapshot publication
................ 2 = merge publication
@metric_id : 1 = expiration
................ 2 = latency
................ 4 = mergeexpiration
................ 5 = mergeslowrunduration
................ 6 = mergefastrunduration
................ 7 = mergefastrunspeed
................ 8 = mergeslowrunspeed
@thresholdmetricname: the name of the publication threshold
................ (either this parameter or @metric_id is required)
@value : the new metric value
@shouldalert : generate alert (1=yes;0=no)
@mode : monitoring mode (1=on;2=off)