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:
