3

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.

  • 2
    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
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.

  • 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
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

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.