Database Replication

A request for "replication" can mean a reporting copy, a standby server, or a way to get last Tuesday's data back. SQL Server has a separate mechanism for each, and they are not interchangeable. A company that treats a replicated reporting copy as its backup can find out the difference when someone deletes the wrong rows.

Replication distributes data

SQL Server replication copies data and database objects from a publisher to one or more subscribers and keeps them synchronized. Transactional replication delivers changes in near real time, in the same order and transaction boundaries as the publisher, which suits reporting servers and read-heavy copies. Merge replication lets several sites change data and synchronize later, with rules for detecting and resolving conflicts. Ordinarily a delete on the publisher is replicated to the subscribers like any other change (an article can be configured not to deliver deletes), which is why replication does not replace backups.

Log shipping keeps a warm standby

Log shipping backs up the transaction log on the primary, copies the file to one or more secondary servers and restores it there on a schedule. It does not fail over automatically: if the primary is lost, the secondary is brought online by hand, and it is only as current as the last log restored. Between restore jobs a secondary can be kept in standby mode for limited read-only use.

Backups are the recovery point

Restoring to a point in time comes from full, differential and log backups, which we cover on the Disaster Recovery page.

We also build custom synchronization where business rules decide which rows move, for example sending only approved orders to a partner database. Those jobs need their own logging and a way to re-run a failed batch without inserting duplicates.