Creating SQL Server Transactional Replication from a Transaction Log backup

There are situations where creating a transactional replication by using the most preferable snapshot method is not acceptable in terms of the performance impact or the long duration caused by the snapshot process. So there are alternatives to create transaction replications by just using a full database backup and creating the subscriber using the transaction log backup.

If we use a native SQL backup to create the replication, then the process is very straight forward. However, if you use any third party backup solution, they sometimes can’t be used to create transactional replications as the SQL Replication Command “sp_addsubscription” may be incompatible with them. In this case, we only need a “SQL Native Transaction Log Backup” before creating the subscriber. So whether we have a native SQL Backup or third party backup solution put in place, the fundamental process is the same.

0.0: Steps summary:

In summary, we need to follow the same basic principles while creating the replication from backup (database and log).

1.      Take a full database backup (using SQL Native or third party).

2.      Start restoring the full backup on the subscriber(s).

3.      Create the publication on the publisher server while backup is being restored on the subscriber.

4.      Restore all the t-log backups on the subscriber (SQL native or third-party).

5.      Take the last SQL Native t-log backup on the publisher and use this log to bring the subscriber database online.

6.      Use the “sp_addsubscription” to create the subscriber from the t-log.

The guidelines are equally applicable when creating transactional replication regardless if a SQL Native Backup or a third party backup has been implemented.

1.0: Snapshot and performance Impact:

As the backup will be used instead of snapshot method to create and to sync the subscriber, there will be no performance impact such as blocking, network traffic and high I/O as the snapshot method is not involved with the subscriber synchronization process.

1.1: Third Party backup and SQL Replication:

1.      The native SQL Database backup and SQL Transaction Replication command are fully compatible.

2.      The internal SQL replication command can’t read the header of third-party backup files and is incompatible. Therefore the backups (database or log) which are taken using third party-tools can’t be used to create transactional replications. A native SQL transactional log backup is required to sync the subscriber.

1.3: Adding a new or broken Subscriber:

1.      If one or more subscriber is broken, then we can rebuild the broken subscriber.

2.      We can add additional subscribers whenever it is required.

1.4: Tips to use third party backup (database and log):

1.      Restore all database and log backups on the subscriber servers which are taken with the third-party tool.  The SQL Server native log-shipping method or using a third party tool such as “Idera SQL Safe” can be used to perform this task.

2.      Before attaching a subscriber to the replication process, perform a Native SQL Transaction log backup and apply this latest T-Log backup to each subscriber with the RECOVERY option.

3.      Use the SQL replication command (sp_addsubscription) with the native T-Log backup (usually TRN) to create one or more subscriptions.

2.0: Step-by-step: Steps to create a brand new transactional replication from a database backup and the log:

1.      Create a network share folder for snapshot so that the SQL Server account can be accessed from anywhere (Although it will not be used).

2.      Configure the distributor server.

3.      Add the publisher server to the distributor server.

4.      Disable (or comment out the job script) the SQL Agent Job “Distribution clean up: distribution” on the distribution server. Watch for the distribution database size.

5.      Take a Full database backup of the publisher database.

6.      Start restoring the FULL DATABASE BACKUP to the subscriber servers with the “NORECOVERY” option.

7.      Restore one or more log backups after the last full backup.

8.      Create the publication on the publisher server.

9.      Change the subscription initialization option by using GUI (figure#1) or by using the following command on the publishing server.

--We need to set option 'allow Initialization from backup' on the Publication

EXEC sp_changepublication

     @publication = 'TestDBPub', 

     @property = 'allow_initialize_from_backup',

     @value = 'true'

10.  Stop any log backups on the publisher database and take a Last SQL Native Transaction Log backup of the publisher database.

11.  Restore the last t-log backup on the subscriber server with “WITH RECOVERY” option.

RESTORE LOG TestDB 
FROM DISK='X:\DBBackups\reptestdb_010.TRN' WITH RECOVERY

12.  On the publisher server, execute the following command to add the subscriber:

-- Add subscription specifying the “SQL Native LAST T-LOG Backup file name” to use for initializing.

-- To add “SRV123” as a subscriber execute the same script by changing the @subscriber parameter.

EXEC sys.sp_addsubscription

     @publication = 'TestDBPub',

     @subscriber = 'SRV123',

     @destination_db = 'TestDB',

     @subscription_type = 'Push',

     @sync_type = 'initialize with backup',

     @backupdevicetype = 'Disk',

     @backupdevicename = 'X:\DBBackups\reptestdb_010.TRN'

13.  Once all the subscribers are added and the replication is up and running, enable the “Distribution clean up: distribution” job.

14.  Restart the log backup if it was stopped earlier.

figure#1: Publication properties

2.1: Re-establish a broken subscriber:

1.      Cleanup the broken subscription (one or more).

2.      Disable the “Distribution clean up: distribution” SQL Agent Job.

3.      Use the last full backup of the published database to restore on the subscriber server.

4.      Perform the steps from #10 to #14.

2.2: Steps add a new subscriber:

1.      Disable the “Distribution clean up: distribution” SQL Agent Job.

2.      Use the full backup of the published database to restore on the subscriber server.

3.      Perform the steps from #10 to #14.

3.0: Re-establish a Third party Log-shipping if any:

If a native SQL transaction log backup is performed in between, then that log needs to be applied manually on the log-shipped server. Because once a native t-log backup is taken, the third-party log-shipping process, such as log-shipping process of Idera, will start failing.

1.      Restore one or more t-logs which was taken through the native SQL backup method on all log-shipped servers. Syntax for log-shipped server:

      RESTORE DATABASE abcDB

        FROM DISK = 'X:\DBBackups\reptestdb_010.TRN'

        WITH STANDBY = 'X:\DBBackups\my_undo_file_name.uno'  

2.      Re-establish the third-party log-shipping process.

4.0: VLF and replication:

Excessive number of VLF slows down the log reader agent and the replication performance, and therefore it is recommended to resize the number of VLF before creating the transactional replication. Kimberly Tripp has a great article; read it here “Transaction Log VLFs – too many or too few?” - http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/