Transactional Replication Reinitialize

Question

I'm using SQL Server transactional replication and looking for ideas on reducing the amount of downtime caused by re-initializing subscriptions.

I have multiple publishers pushing data to a single subscriber across the WAN. I recently had to recover the subscriber from backup. Upon restoring the backup, the subscriber was out of sync with all of the publishers. The only way I knew to get the subscriber back in sync with all of the publishers was to re-initialize all the subscriptions.

Since the publishers and subscriber are separated by a WAN connection, this process was extremely time consuming. In some cases it takes 24+ hours to generate a snapshot.

Any ideas on how to reduce the downtime caused by this type of scenario? The only idea I have at this point is to use Always On with the subscriber.

Maybe test this: Stop the log reader agent jobs, do a final LOG backup on the subscriber, do the restore (FULL + LOG chain, including final LOG backup) and then start the log reader agent jobs. No reinit necessary. — Tara Kizer, Jun 12 '18 at 18:39

David Browne - Microsoft · Answer 1 · 2018-06-13T14:51:15.407

1

Whether you use a backup or a snapshot, you need to routinely pre-stage the data needed for reinitialization near the subscriber. The goal is to always have a backup/snapshot that is within the Distribution Retention Period, and close to the subscriber.

Also whenever you are using a snapshot to initialize a remote subscriber, it's better to copy the snapshot local and use an Alternate Snapshot Location.

Also note that if you have a single subscriber database with multiple subscriptions, you can't initialize from a backup.

edited Jun 13 '18 at 14:51

answered Jun 13 '18 at 13:33

David Browne - Microsoft

46,306
3
46
96

So it is possible to generate a snapshot without importing it to the subscribers? – Steven Barnhill Jun 13 '18 at 14:40
Sure. You can run the Snapshot agent whenever you want. You should have an unscheduled job created on your publisher for it. Just change the schedule to generate snapshots periodically. – David Browne - Microsoft Jun 13 '18 at 14:50

Kin Shah · Answer 2 · 2018-06-12T20:32:18.127

0

Initialization from backup as suggested by @Tara is the way to go if you are going to stick with replication.

To get it done is a bit complicated and needs practice.

Below will summarize what you need to do :

change the publication property --> subscription options --> Allow initialization from backup files to True .
stop/disable log reader agent jobs.
stop/disable distribution agent job.
Take full backup (do not append to existing backups).
Take a log backup.
Restore the full with NORECOVERY and log backup with KEEP_REPLICATION and RECOVERY option.
enable / start the agents that are disabled.
Check if all is working fine or not.

Refer to : Deep Dive on Initialize from Backup for Transactional Replication

edited Jun 12 '18 at 20:32

answered Jun 12 '18 at 20:24

Kin Shah

62,225
6
120
236

Keep in mind my backups would need to come across the WAN, which is sometimes a fairly slow connection. I believe performing these steps would take just as long as a snapshot. – Steven Barnhill Jun 12 '18 at 21:08
You can compress the backup. – Kin Shah Jun 12 '18 at 21:09

Transactional Replication Reinitialize

2 Answers2

Linked