A SQL Server replication type that streams individual data changes (inserts, updates, and deletes) from a publisher database to one or more subscriber databases in near real time, typically achieving sub-second to low-second latency.
- Replication process: Transactional Replication reads committed transactions from the publisher’s transaction log using the Log Reader Agent and delivers them to subscribers through the Distribution database.
- Common use cases: Transactional Replication is commonly used to distribute data to read-scale subscribers, synchronize branch office databases with headquarters, and provide near real-time data feeds to downstream systems.
- Latency monitoring: Replication latency is measured using tracer tokens in Replication Monitor. A well-tuned environment typically achieves end-to-end latency of less than five seconds.
- Distribution database monitoring: DBAs should closely monitor the health and size of the Distribution database. Continuous growth often indicates subscriber latency and can eventually lead to disk space exhaustion on the Distributor.
- Updateable subscriptions: Transactional Replication supports updateable subscriptions that allow limited write operations on subscribers. However, this approach is complex and has largely been replaced by Always On Availability Groups for most high availability scenarios.
- Related terms: Merge Replication, Replication Monitor, Log Shipping, Change Data Capture.
