In an interview, how do you position SQL Server replication versus Always On availability groups and log shipping?
Replication copies and distributes data and objects from a publisher to subscribers via a distributor — often for reporting, scale-out reads, geo copies, or upgrade/migration cutovers. AGs are HA within a group (plus optional read scale). Log shipping is warm standby by applying log backups on a secondary. Line: “Different tools: replication is publish/subscribe data movement; AG is group HA; log shipping is log replay standby.”
Name the three topology roles in SQL Server replication and what each does.
Publisher — source of the data. Distributor — holds the distribution database and runs agents that move data to subscribers. Subscriber — receives publications. The distributor can live on the publisher, subscriber, or a dedicated host.
What is snapshot replication, and when is it a good fit?
A point-in-time, static copy of published articles (the whole picture at run time); the next snapshot replaces or updates per article settings. Typical uses: initial load for other replication types; slowly changing or read-only subsets; periodic refresh of a reporting copy. Tradeoff: simplest mentally, highest latency between refreshes, and each run can be heavy on large articles.
What is transactional replication, and what problems does it usually solve?
Incremental delivery of committed row changes: a log-based reader at the publisher feeds the distribution database, then subscribers receive ongoing changes. Typical uses: near real-time reporting databases, read offload, sharding or edge subscribers, and upgrade cutovers (sync then switch). Latency can stay low.
Walk through the agents in transactional replication from publisher to subscriber.
Log Reader Agent reads the transaction log for published articles and writes to the distribution database. Distribution Agent applies those changes to subscribers. Snapshot is often the first phase (initial sync), then the log reader takes over for ongoing changes.
Are transactional replication subscribers automatic failover targets like an AG secondary?
No — subscribers are not automatic failover targets unless you design procedures such as role swap or re-initialization. Low latency replication ≠ built-in HA semantics; treat failover as operational design, not a product guarantee.
What is merge replication, and why is it less common in new cloud OLTP designs?
Bidirectional sync: subscribers can publish changes back; the engine tracks row versions / metadata and applies conflict detection/resolution (e.g. publisher wins, subscriber wins, custom). Typical use: occasionally connected clients (legacy mobile/field). Ops overhead is higher than one-way transactional; many modern server-centric apps use other patterns — check Microsoft lifecycle notes for your SQL version.
What is peer-to-peer transactional replication, and what Enterprise constraint applies?
A multi-master–style transactional topology: all nodes can accept writes (with conflict handling constraints). Enterprise feature. Conflicts must be avoided by partitioning writes or handled where supported — not “everyone writes anywhere” without design. Many shops prefer AG + read scale or application-level patterns for similar goals.
What is the difference between push and pull in replication?
Push: the distributor connects out to the subscriber to deliver changes. Pull: the subscriber pulls from the distributor. Choice affects network, security (who initiates), and operational habits — same data model, different delivery initiation.
Name legacy transactional replication patterns interviewers might mention — what depth do you need?
Updatable subscriptions (often deprecated or limited in modern versions — know the name if asked), plus immediate updating and queued updating. Depth: recognize them as older patterns; don’t memorize internals unless the role requires it.
Is native SQL Server replication (publisher / distributor / subscriber) supported on Amazon RDS for SQL Server?
No — per AWS unsupported-features guidance, that topology is not supported on RDS. Use read replicas, DMS, backup/restore, or self-managed SQL on EC2 for comparable scenarios. Panel line: “I wouldn’t plan publisher/distributor on RDS; I’d pick managed alternatives or EC2.”
Contrast replication with Change Data Capture (CDC) and with Always On / log shipping in one table.
Replication — copy data to subscriber databases via publication topology. CDC — change feed for ETL/integrators, not a subscriber database product. Always On AG — HA and optional read scale inside a group, not publisher/subscriber replication. Log shipping — secondary applies log backups; usually a single readable/warm standby per chain.
Give the interview closing line that lists all four replication types and when you actually see them.
“I’d name snapshot, transactional, merge, and peer-to-peer transactional. In practice I see transactional most for reporting and migration cutovers, snapshot for initial loads or periodic copies, and merge rarely on new server-centric systems — peer-to-peer only where Enterprise and ops can own the conflict model.”
Why might snapshot still matter even when the story is “transactional replication for reporting”?
Snapshot is often the first phase of transactional replication — the initial consistent copy of articles — before the Log Reader and Distribution agents take over for ongoing incremental changes. Without that baseline, you don’t have a clean starting state for the log-driven stream.
