I will need to setup transactional replication for a big database. I use backup/restore to initialize the database because it's too big to use snapshot over slow network. So the following steps are used
- Backup the database
- Copy the the backup files
- Restore the database on subscriber
- Setup replication
However, What if there are some data change (insert/update/delete) between after step 1 and before step 4? Will these transactions lost? Or the replication setup will fail? How to resolve the problem?
Best Answer
To initialize a Transactional subscription from a backup you actually end up creating the publication first, enabling it to initialize subscriptions from a backup, then take a backup, and then initialize subscriptions with the backup.
Changes that occur after taking the backup and before initializing the subscription(s) will be stored in the distribution database, which acts as a store-forward queue. Those changes will then be replicated to subscribers after they have been initialized from the backup.
Note that by default the distribution cleanup jobs clears transactions older than 72 hours from the distribution database which is based on the publication retention period. You may want to temporarily disable this job before the backup and re-enable it after you initialize the subscriptions from the backup.
Have a look at Initialize a Transactional Subscription Without a Snapshot and Initialize a Transactional Subscription from a Backup.