Push vs Pull Replication Agents Across a Site VPN

Agent location, not data location, is the decision that shapes a SQL Server subscription. The machine that runs the agent opens the connection, runs under a specific account, and takes the blame when synchronisation stalls. On a site-to-site VPN that difference is the design rather than a detail. Draw the agent connections before you draw the network.

Three roles, not two

The Publisher owns the article data, the Distributor owns the distribution database, and the Subscriber receives changes. The Log Reader Agent runs at the Distributor for transactional replication. The Distribution Agent is the one whose location changes with subscription type, which is why it drives the network design. Get those roles straight before you talk about VPN rules.

A push subscription runs the Distribution Agent at the Distributor, and a pull subscription runs it at the Subscriber, as Microsoft's subscription documentation states. Everything about the network conversation follows from which machine that is. Write down four facts for each subscription: the agent's host, its process identity, the database logins it uses, and the resources it must reach. Those facts, not a diagram arrow, tell you which direction has to work.

Push means the agent lives at the Distributor

With a push subscription, the Distributor opens the connection to the Subscriber across the VPN. The subscriber-side listener, firewall and SQL login have to accept connections arriving from the remote side. The Distributor's service account also needs permission on the subscriber database. Plan that inbound path before you create the subscription, because discovering it afterwards usually means recreating it.

Snapshot delivery adds a path that steady-state replication never exercises. If the snapshot is staged on a file share, the Distribution Agent reads from that share and the subscriber's account has to reach the same UNC path. A change stream that flows for weeks says nothing about whether a reinitialisation will work. That is why the snapshot share belongs in the plan from the start.

Do not read push as one-way traffic. The agent needs replies, acknowledgements and its own authentication exchanges. The path has to be bidirectional at the connection level even when data moves in one direction.

Pull means the agent lives at the Subscriber

A pull subscription flips the connection: the Subscriber runs the Distribution Agent and reaches out to the Distributor. That arrangement suits a VPN policy that permits outbound connections from the branch and blocks inbound ones. It also puts the scheduling decision in the hands of whoever looks after the branch server. Choose it when the branch team should own its own synchronisation and notice its own failures.

The subscriber's SQL Server Agent service account needs read access to the distribution database and to the snapshot share. That account is the identity that appears in replication errors, and a local service account with no domain rights fails at a point that looks like a network fault but is really a credential fault. Check the account before you debug the tunnel. Fixing the login is usually faster than proving the network innocent.

Ownership follows the agent. Choose push when the central team should own both schedule and failures. Whichever you pick, write it next to the subscription so the next engineer knows where to look first.

Republishing changes fan-out, not ownership

A subscriber can be configured to republish to further subscribers, letting one branch database serve several local consumers without each one pulling across the WAN. The middle database now holds a publication and its own agents. It also inherits permissions, monitoring and a recovery plan it did not have before. That extra role is the price of removing repeated WAN delivery.

The documented order matters: initialise the incoming subscription before creating the downstream publication. Treat that as a lifecycle step with a verification rather than paperwork to skip. Reversing it produces a chain that looks configured and delivers nothing.

State the write model explicitly. Replicating a read-only dataset to a local consumer is a different design from accepting writes at several sites, and a chain of arrows does not create conflict resolution. If the branch must write, that is a separate decision with its own topology. Also make the dependency visible: when the republisher is down, every consumer below it is stale.

Prove the placement end to end

Read the subscription type from the distribution database instead of trusting memory, then check that changes actually arrive:

USE distribution;
SELECT publication, subscriber_db, subscription_type
FROM dbo.MSsubscriptions;
EXEC sp_replmonitorsubscriptionpendingcmds
  @publisher = 'PUB1', @publisher_db = 'Sales',
  @publication = 'SalesPub', @subscriber = 'BR1',
  @subscriber_db = 'Sales';

A subscription_type of 0 is push and 1 is pull, and it cannot be changed in place, so a mismatch means recreating the subscription rather than editing it. The pending-command call tells you whether the chosen agent is moving work or merely running. A subscription that reports zero pending commands while the subscriber table stays empty is a different fault from one that reports a growing backlog, and the two lead to different owners.

Finish with a real change: insert or update one row at the Publisher, wait for the agent to report, and read that row at the Subscriber. Do it once more across a brief VPN interruption to see how the agent recovers. A subscription that survives that test has a placement you can document and defend.