Replication
Overview
Replication is a two hop pipeline with a separate agent on each hop. The log reader moves committed transactions from the published database into the distribution database; the distribution agent moves them from there to the subscriber. Almost every real failure is one of those two agents stopping while the other carries on.
This report answers what is published, who subscribes, and whether any of it is still moving.
What this replaces
The old page was one node in ReportsXmlConfig.xml: a grid printing nine columns of definition. Publisher, publisher database, publication, subscriber, subscriber database, type, status, subscription type, last sync. Every one of those describes how replication was set up and not one says whether it works. A subscription stalled since breakfast and one six seconds behind produced identical rows.
It was also wrong in four ways, which is why this is a rewrite rather than a chart bolted on:
| Fault | What it meant |
|---|---|
| Last Sync was the empty string | For every transactional and snapshot publication. The column existed and never had a value. |
| One status code, two vocabularies | Active for merge and Subscribed for transactional, printed into the same column. |
| Subscriber joins matched on publication name | Two same-named publications in different databases traded subscribers. |
| Staged through a global temp table | Two copies of the tool running at once collided. |
Where to find it
| Route | How |
|---|---|
| Server tree | Right-click the server → Instance Reports → Replication |
The page title reads Replication for <server name>.
Requirements
- Access to the distribution database, for anything about subscriptions, agents or backlog. See the section below, because this is the single biggest thing that decides what the page can tell you.
db_ownerin the published database for the Conflicts view. Without it the view reports that it could not read the conflict tables and says what SQL Server said.- The main read has a 180 second timeout, deliberately longer than the base default: counting undelivered commands walks
MSrepl_commands, and on a distributor with a real backlog that is genuinely slow rather than accidentally slow. The tracer token is capped at 30 seconds, and the conflict read at 60 seconds per published database. - Every one of those reads is awaited rather than run on the message loop, so a slow instance costs a loading panel over a live window rather than an application that has stopped responding.
There is no auto-refresh. Agent state moves in seconds and so does the cost of asking. Refresh is a button.
Where you are connected matters
Almost everything worth knowing lives in the distribution database, and a remote distributor is the normal configuration rather than the odd one.
This page does not open a connection to the distributor. It names it, says which columns are unreadable from here and why, and draws those legs as unknown rather than as healthy:
This instance publishes, but its distributor is somewhere else. The distributor is DIST01, and the subscriptions, the agents and the backlog all live in its distribution database. Open this report against DIST01 to see them.
Publisher side topology and log reader latency still work, so the page is never empty on a publisher. That is the same call the Availability Groups report makes about a secondary that cannot see the send queues: name the gap, do not fill it with zeros, and do not connect somewhere the user did not ask for.
Peer to peer
Peer to peer topologies are detected and said out loud rather than drawn wrongly. Every node in a peer to peer set is both publisher and subscriber, so the left to right pipeline the flow view draws is one direction of something circular. The notice line says so.
The five views
Replication fails in distinguishable ways, and each one has a shape.
Flow
The view no other tool draws, and the reason the control exists.
Two ribbons per leg, coloured by the worst state on that hop and sized by what is queued behind it. The two common failures then look nothing like each other:
- A fat red ribbon to the right of the distributor is a distribution agent.
- The same ribbon to the left is a log reader.
In a grid both are a row with a large number in a column called Latency.
The distributor is drawn as its own middle column rather than as decoration. It is a real component with its own retention window and its own backlog, and it is the thing that runs out of room while everybody is looking at the subscriber.
Latency
Three marks per subscription on one log scaled axis anchored at now: the last transaction committed at the publisher, the last one that reached the distributor, and the last one applied at the subscriber. The gap between two marks is the hop between them.
Log scaled, because a configuration routinely mixes a subscription six seconds behind with one two hours behind, and a linear axis puts all the healthy ones in the same three pixels.
Agents
A matrix: five agent kinds across a list of publications.
| Agent | Pipeline |
|---|---|
| Snapshot | Initial and reinitialize |
| Log Reader | Transactional, publisher to distributor |
| Distribution | Transactional and snapshot, distributor to subscriber |
| Merge | Merge, both directions |
| Queue Reader | Updatable subscriptions |
A matrix because a set of independent yes or no answers is best drawn as a grid, where a column of failures reads as one dead SQL Agent job rather than as four separate coincidences.
Cells for an agent a publication does not use are drawn as absent rather than as passing. A merge publication has no log reader, and colouring that cell green would be inventing a fact.
Backlog
A bullet chart against the one deadline replication actually enforces.
Every subscription is racing its retention window. When the window closes the subscription does not merely fall further behind, it expires, and the only route back is a new snapshot, which is a full copy of the data rather than a restart.
That number is on no screen in Replication Monitor, and it is the one that turns an incident into a weekend.
Conflicts
Merge only. One row per retained conflict, with what happened, which side was discarded, and which resolver decided.
Conflicts are a merge idea: transactional and snapshot publications resolve nothing, because only the publisher changes the data. On an instance with no merge publications the view says so rather than showing an empty grid.
Reading the grids
The page has three grids, and which one is shown follows the view.
Subscriptions
| Column | What it is |
|---|---|
| Publication | The publication name. |
| Publisher Database | Where it is published from. |
| Subscriber | The subscribing server. |
| Subscriber Database | The subscribing database. |
| Type | Transactional, snapshot or merge, with push, pull or anonymous. |
| State | What this subscription is actually doing. |
| Latency | End to end, publisher commit to subscriber apply. |
| Waiting | Transactions queued and not yet delivered. Counted up to 250,000 and shown with a trailing + past that, because reading tens of millions of rows to be exact about a backlog that size changes nothing anybody would do about it. |
| Last Delivered | When something last arrived. |
| Expires In | How long before the retention window closes and a reinitialize is required. |
| Agent | The agent responsible, and its state. |
Publications
Publication, publisher database, type, articles, subscriptions, agents, status and Needs Attention, which names the checks that did not pass rather than making you infer them.
Conflicts
Article, publication, database, type, what happened, which side was discarded, when, state, code and resolver.
The toolbar
| Button | What it does |
|---|---|
| Flow / Latency / Agents / Backlog / Conflicts | The view. |
| Post tracer token | Measures a real round trip. See below. |
| Refresh | Re-reads everything. |
The one button that writes
Post tracer token puts a marked empty transaction into a published database so the round trip can be measured rather than inferred. It is the single most useful thing Replication Monitor does.
- It asks first, and names the publication it is about to write to.
- It is only offered on transactional publications, because there is no log reader in the merge pipeline for a token to travel through.
Everything else on this page reads.
How to read the report
- Check the notice line. If the distributor is remote, the subscription, agent and backlog columns are blank by design and the line names the server to open this report against instead.
- Start on Flow. Which side of the distributor is the fat ribbon on? Left is a log reader, right is a distribution agent. That alone halves the search.
- Switch to Agents if more than one publication looks wrong. A whole column failing is one dead SQL Agent job, not four problems.
- Switch to Backlog before doing anything slow. If a subscription is close to its retention window, the clock matters more than the diagnosis: a reinitialize is a full snapshot.
- Use Latency to confirm which hop owns the delay once you know which subscription to care about.
- Post a tracer token when the timestamps disagree with what people are telling you. It is the only number on the page that is measured end to end rather than inferred.
Common patterns
A fat red ribbon left of the distributor, everything right of it healthy. The log reader. Transactions are not leaving the published database, so nothing downstream has anything to deliver and every subscription looks stalled.
One subscription behind, the rest current. The distribution agent for that subscription, or the subscriber itself. Not the publisher.
A whole column red in the Agents view. SQL Agent, not replication. Check the service before reading anything else.
Backlog bars approaching the window on several subscriptions. Something stopped a while ago and nobody noticed. Priority is stopping the expiry, not finding the root cause.
Merge conflicts climbing steadily. Two sites editing the same rows. The resolver is doing its job; the question is whether it is picking the winner you would pick.
Where the data comes from
- The publisher side catalog for publications and articles.
- The distribution database for subscriptions, agent state, latency and undelivered commands.
MSmerge_conflicts_infofor the Conflicts view, one query per published database rather than the per article walksp_helpmergearticleconflictsdoes.
Counting undelivered commands walks the distribution database’s command table, which is why the timeout is 180 seconds and why nothing here runs on a timer.
Nothing is stored. There is no history behind this page.
Settings
| Setting | Default | Values |
|---|---|---|
ReplicationView |
flow |
flow, latency, agents, backlog, conflicts |
Per-user rather than per-instance.
Messages you may see
Not configured:
Replication is not configured on this instance. SQL01 is not a publisher, a distributor or a subscriber. Nothing has been published from it and nothing subscribes to it.
Remote distributor:
This instance publishes, but its distributor is somewhere else.
Distributor with nothing on it:
This instance is a distributor with nothing published through it. SQL01 holds a distribution database, but no publication has been created against it yet.
Published but unsubscribed:
Nothing subscribes to anything published here.
Conflicts, no merge replication:
There is no merge replication here to have conflicts. Conflicts are a merge replication idea. Transactional and snapshot publications resolve nothing because only the publisher changes the data.
Conflicts, no permission:
The merge conflict tables could not be read. Reading conflicts needs db_owner in the published database.
Timed out:
The replication lookup did not finish in time. Counting undelivered commands walks the distribution database’s command table.
Related reports
| Report | Why you would go there |
|---|---|
| Availability Groups | The other standing copy of your data, with the same question about lag. |
| Failed Jobs | Replication agents are SQL Agent jobs. This is where they show up when they fail. |
| Job History | Agent run history over time, rather than the current state. |
| Disk Space | A distribution database that cannot grow stops replication for everyone. |
| Linked Servers | The other standing outbound trust between instances. |
Frequently asked questions
Why are the subscription columns blank? Because the distributor is on another server and this page does not connect to it. Open the report against the distributor.
Why does it not just connect to the distributor for me? For the same reason the Availability Groups page does not connect to the primary. A monitoring tool opening a connection to a server you did not ask about is not something that should happen quietly.
What does Expires In mean, exactly? How long until the retention window closes for that subscription. When it does, the subscription expires and needs a new snapshot, which is a full copy rather than a restart.
Why is a cell in the Agents view blank rather than green? Because that publication does not use that agent. A merge publication has no log reader, and marking it as passing would be inventing a fact.
Is the tracer token safe? It writes a marked empty transaction into a published database. It asks first and names the publication. That is a real write, which is why it is the only button on the page that does one.
Why is my peer to peer topology drawn as a straight line? Because it is drawn as one direction of something circular, and the notice line says so. Drawing peer to peer properly is a separate piece of work.