Distribution Replication Agent Test

The Distribution Agent is used with snapshot replication and transactional replication. It applies the initial snapshot to the Subscriber and moves transactions held in the distribution database to Subscribers. The Distribution Agent runs at either the Distributor for push subscriptions or at the Subscriber for pull subscriptions.

If replication is taking longer than usual, you may want to check how each of the agents involved in the replication process is performing, so that you can isolate the agent that could be causing the delay. The Distribution Replication Agent test helps you run frequent health checks on the Distribution agent and reveals whether latencies (if any) in the operations of this agent are the root-cause for the replication slowdown.

This test has been disabled by default. To enable this test, go to the enable / disable tests page using the menu sequence : Agents -> Tests -> Enable/Disable, pick Microsoft SQL as the Component type, Performance as the Test type, choose this test from the disabled tests list, and click on the << button to move the test to the ENABLED TESTS list. Finally, click the Update button.

Target of the test : A Microsoft SQL server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for the Microsoft SQL server being monitored

Configurable parameters for the test
  1. TEST PERIOD - How often should the test be executed
  2. Host – The IP address of the Microsoft SQL server.
  3. Port - The port number through which the Microsoft SQL server communicates. The default port is 1433.
  4. instance – The name of a specific Microsoft SQL instance to be monitored. The default value of this parameter is “default”. To monitor a Microsoft SQL instance named “CFS”, enter this as the value of the “instance” parameter.
  5. ISPASSIVE – If the value chosen is Yes, then the Microsoft SQL server under consideration is a passive server in a SQL cluster. No alerts will be generated if the server is not running. Measures will be reported as “Not applicable" by the agent if the server is not up.
Measurements made by the test
Measurement Description Measurement Unit Interpretation

Commands Delivered:

Indicates the rate at which the Distribution agent delivered commands to the Subscriber.

Commands/Sec

 

Transactions Delivered:

Indicates the rate at which the Distribution agent delivered transactions to the Subscriber.

Trans/Sec

A high value is desired for this measure. If the value of this measure dips consistently over time, it is indicative of a slowdown.

Latency:

Indicates the current amount of time, in milliseconds, elapsed from when transactions are delivered to the Distributor to when they are applied at the Subscriber.

MilliSeconds

Ideally, the value of this measure should be 0 or very low. A high value indicates that the Distribution agent is taking too much time to push transactions to the Subscriber.

The possible causes for this are as follows:

  • A series of transactions could be trying to move a large batch of commands to the Subscribers
  • Blocking of the Distribution Agent by another Replication Agent
  • Long execution times in the INS/UPD/DEL stored procedures used to apply transactions to the subscriber
  • SQL statements not being replicated as ‘parameters’