-
-
Why do you need Sync with Backup in Transactional Replication? This was one of the questions asked to my friend in an interview and we were discussing the possible answers after interview. This is what I came up with.
1. Turn this option ON in the distribution database so that the transaction log of the publication database does not get truncated until they have been backed up in the distribution database. This means that the distribution database can be restored to the last backup and if there any transactions which are missing then they get transferred from publication database to the distribution database without affecting the replication.
This causes a delay in the truncation of the transaction log in publication database as it has to wait until the transactions in the distribution database has been backed up.
2. When this option is turned ON in the publication database then the transactions are not delivered to the distribution database until they are backed up at the publication database. This means that the last publication database backup can be restored at the publisher without any mismatch of transactions with the distribution database. This affects latency as the distribution database will not receive the transactions until they have been backed up at the publisher.
Please let me know what your experiences have been as comments.