What Is Replication in SQL Server?

Replication in SQL Server copies chosen tables to other servers and keeps those copies current as the data changes. That is the whole idea. Everything else is detail about which tables, how often, and what happens when two copies disagree.

Three Names to Learn

Microsoft borrowed the words from publishing, and once you see that the jargon stops being difficult.

The publisher is the server that owns the original data. The subscriber is a server that receives a copy. The distributor sits between them, holding changes and delivering them. A publication is the set of tables you chose to send, and each table in it is an article.

You pick the articles. That is the first thing that separates replication from every other way of copying a database. You are not sending a database, you are sending a list of tables, and you can send five out of four hundred.

The Three Kinds

Snapshot replication takes the whole set of chosen tables and copies it, replacing what was there. Nothing is tracked in between. It suits small tables that change rarely, such as a list of countries pushed out nightly. It is simple and it is heavy, because every run sends everything.

Transactional replication is the one most people mean. The initial copy goes out once, then each change is read from the log and sent along, usually within seconds. It is one way. The subscriber is a read copy, and changes made there are not sent back.

Merge replication lets both ends change the same rows, then reconciles them. It exists for the case where a laptop or a branch office works offline and syncs later. It is also where conflicts come from, because two people can edit the same row in different places, and something has to decide which edit wins. Merge is the most capable and by a distance the most work to run.

What It Is Not

This is where people go wrong, so it is worth being blunt.

Replication is not high availability. There is no automatic failover and no promise that the copy is current at any given moment. If the publisher stops, nothing steps in.

Replication is not a backup. A bad delete on the publisher is replicated faithfully and promptly to every subscriber. You now have the mistake in more places.

Replication is not a migration tool, though people use it as one. It can move data while a system stays up, and that works, but it brings a lot of machinery for a job you intend to do once.

When It Is the Right Answer

Replication earns its keep when you want some of the data, somewhere else, and you cannot simply restore a backup there.

Reporting is the classic case. Send the six tables the reports touch to a second server, point the reports at it, and the order system stops being slowed down by them. Another is distribution, where a head office pushes a price list out to thirty shops that each keep their own working data.

If what you actually want is a standby copy of a whole database for when the main one fails, you want an availability group or log shipping instead. Those copy everything and are built for failover. Replication copies a selection and is built for sharing.

Finding Out What You Have

Walking onto an unfamiliar server, this tells you whether replication is set up at all:

SELECT name, is_published, is_subscribed, is_merge_published, is_distributor
FROM sys.databases
WHERE is_published = 1
   OR is_subscribed = 1
   OR is_merge_published = 1
   OR is_distributor = 1;

No rows means no replication on this instance. Any row means somebody built something, and you should find out what before you change anything. I have not configured replication on my own machine, so this returns nothing here.

One warning worth carrying. Replication holds the transaction log until changes have been read. If a subscriber has been offline for a week and nobody noticed, log_reuse_wait_desc on the publisher will say REPLICATION and the log will be growing steadily. That is a genuinely common cause of a full drive on an otherwise healthy server.

The Honest Summary

Replication is powerful, it is well proven, and it is more moving parts than most people expect. Agents, a distribution database, monitoring, and a plan for what happens when a subscriber falls behind.

Before setting it up, I ask one question. Would a scheduled copy of the data, or a readable secondary, do the job instead. When the answer is yes, that is usually the better choice, because it is far less to look after at two in the morning.

Replication is not a safety net, it is a delivery service that will deliver your mistakes too.

This post was rewritten from scratch in September 2026. The original, published on 2013-10-16, was a short announcement about something that no longer exists. The address is the same, the subject is now a basic idea worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.