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.

MDCCL
  • 8,530
  • 3
  • 32
  • 63
Steven Barnhill
  • 51
  • 4

2 Answers2

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.

David Browne - Microsoft
  • 49,000
  • 3
  • 53
  • 102
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

Kin Shah
  • 62,545
  • 6
  • 124
  • 245