Initializing replication from backup

by Aug 16, 2018

Initialization of a transactional replication subscriber is a highly valuable tool. For very large databases (VLDBs), snapshot generation and application can have a considerable performance impact and take a long time. Consequently, initializing a subscription from a snapshot can be prohibitive.

The next logical step in the evolution of replication was to make better use of the backups that were already being applied to the subscribers. Initialization from backup was designed to alleviate some of the problems faced by administrators of VLDBs. However, there are perceived limitations and restrictions regarding initialization from backup for transactional replication, but those are not insurmountable barriers to using this process. You can still use initialization from backup with a little creativity and by utilizing log or filegroup backups.

The reasons often given for not using initialization from backup include a need to use a third party compression for full backups, especially as a tool for encryption or to gain maximum compression ratios; a belief that the subscriber must be initialized from a full backup; and a requirement to limit subscribers to a subset of tables. All of these limitations are easy to handle with initialization from backup.

The 8-page whitepaper “Initializing Replication from Backup” by Kenneth Fisher and Robert L. Davis provides clear guidance on the perceived limitations and restrictions on initializing transactional replication subscribers from a backup. The whitepaper describes the trouble with snapshot initialization, which backups are suitable for initialization, moving beyond simple initialization from backup, and initializing from a filegroup backup.

Click here to read the whitepaper.

With SQL Safe Backup, automate SQL Server backup and recovery across the entire enterprise. Backup faster and save space via dynamic compression with encryption. Choose from multiple options for recovery. Ensure organizational compliance via policy-based management. Reduce failures due to temporary network problems. Receive alerts and create reports.

SQL Safe Backup

View the infographic "Why Use SQL Safe Backup", read a case study, view TechValidate survey results, watch the overview video, browse the datasheet, download a fully functioning 14-day trial, request a live demonstration, and request a price quote.