SQL Replication Monitor.

Version 4

    Checks a SQL Table for Replication Status.

    Use the below code to create the table required for the replication information to reside in.

     

    CREATE TABLE PublicationInfo

    (

    publisher_db sysname,

    publication sysname,

    publication_id int,

    publication_type int,

    status int,

    warning int,

    worst_latency int,

    best_latency int,

    average_latency int,

    last_distsync datetime,

    retention int,

    latencythreshold int,

    expirationthreshold int,

    agentnotrunningthreshold int,

    subscriptioncount int,

    runningdistagentcount int,

    snapshot_agentname sysname,

    logreader_agentname sysname,

    qreader_agentname sysname NULL,

    worst_runspeedPerf int,

    best_runspeedPerf int,

    average_runspeedPerf int,

    retention_period_unit int,

    publisher sysname

    )

     

    Use the beow code in a scheduled SQL Job on the Distribution database server to populate the table with data needed every X minutes. ( I run it every 4 minutes, runs in under 1 second)

     

    TRUNCATE TABLE PublicationInfo

    EXEC sp_replmonitorhelppublication

    INSERT INTO PublicationInfo

     

    Then customise the template variables with the names of your own publications.

    We have approx 15 publications, just create a new component for each one to report individually on your own publications.