Sometimes, the time it takes and the size of the snapshot is too large for easily configuring transactional replication. To deal with this situation, SQL Server offers the ability to initialize the subscriber from the backup of the database at the publisher. Using a backup for the snapshot requires extra steps and settings in the process of configuring transactional replication. Below is a step by step to walk you through a successful setup.
Process Overview
- Create the publication(s) on the publisher
- Change settings to allow initialization from backup and immediate sync options to true
- Take a backup of the database on the publisher.
- Restore the backup to the target subscriber
- Create Subscriptions using T-SQL (the GUI does not support initializing from backup)
Process Details
-
- Create the publication(s) wanted on the database
The first step is to create the publications. This can be skipped if they are already in place to support replication to other subscribers. If it is a brand new configuration, the publication must be in place before moving forward. - Once the publication(s) are created, verify the settings for the following options.
- Create the publication(s) wanted on the database
NOTE: Both of these must be set to true otherwise the initialization will fail.
a. [php]allow_initialize_from_backup[/php]
b. [php]immediate_sync[/php]
To check these settings, execute the following. If the field value = 0 then it is set to false.
EXEC sp_helppublication @publication = 'NAME of PUBLICATION'
If the values are false run the following commands to update to True
EXEC sp_changepublication @publication = ' NAME of PUBLICATION''
, @property ='immediate_sync'
, @value = 'true'
EXEC sp_changepublication @publication = ' NAME of PUBLICATION''
, @property ='Allow_initialize_from_Backup'
, @value = 'true'
-
- Create a full backup of the publication database to be replicated
Once the publication is in place, you will need to create a backup of the database to be replicated. It is important to create the backup after the publication(s) have created because the initialization will use the LSN numbers to sync the 2 databases.
-
- Copy the full back up to the subscriber server.
Once the backup has completed, you will need to copy the file to a location the subscriber can access.
To ensure you do not remove the backup from a location the publication server can access, you will need to access the file later in the process from the publisher.
-
- Restore the full backup on the subscriber database.
Complete a restore and recovery of the backup file on the subscriber database server.
-
- On the PUBLISHER server, issue the command to create the subscription.
NOTE: this must be done on the PUBLISHER!
EXEC sp_addsubscription
@publication = 'NAME of PUBLICATION',
@subscriber = 'NAME of SUBSCRIBER Server’,
@destination_db = 'NAME OF THE SUBSCRIPTION database',
@sync_type = 'initialize with backup',
@backupdevicetype = 'disk',
@backupdevicename = 'LOCATION on the PUBLICATION Server of the backup’
- Launch Replication monitor and validate that data is syncing between the publisher and subscriber instances.
Also, be sure to check out this SQL Server tip!