In my earlier blog, I had discussed how to Initialize replication for a large database using the database backup. I will now be discussing how to add new articles and reinitialize the existing SQL Server transactional replication configured through backup.
Generally, a user tends to use a snapshot agent to add any new articles to the publication or reinitializing the subscriber, however, in this scenario, I have already configured the transactional replication using the database backup. The following are the detailed steps:
In our scenario, transactional replication has already been configured using the backup file as follows:
-Publisher + Distributor: Node1 -Subscriber: Node2 -Publisher Database: ABC_Pub -Subscriber Database: ABC_Sub -Publication: ABC_Pub_Bkp -Subscription: ABC_Sub_Bkp -Articles: Cars, bikes
Adding new articles to the existing publication can be done by manually synchronizing the data (which we will discuss with the detailed steps) between the publisher and the subscriber.
In the existing replication, there are two articles, and I will be creating two more tables for the publisher database and later add them to the replication.
Articles on the existing replication:
I have created two new tables in the publisher database and added a few rows to each table. Now script the create definition for both tables and execute on subscriber database.
Two new tables were created on the publisher database:
Created tables on subscriber using the definition from publisher:
Now let sync the data between the publisher and subscriber using the import\export method:
Right click on the publisher database -> Select Export data -> Follow the wizard to pass the required information.
Select the source server (Publisher) and Database Name:
In the next step, Select Destination server (Subscriber) and database name:
Select the table names to transfer the data and click next:
In the next step, Export was successful:
Validating the data: As you can see, the data has synced between the publisher and subscriber.
Stalling the replication agents is the best recommended practice before doing any operations with the articles.
Right click on the publication under the replication folder and choose properties - > Navigate to Articles page -> uncheck Show only checked articles in the list -> Select the two new articles under the objects to publish i.e. Country and Customer in our case and click OK to finish the configuration.
Once the agent has been enabled and started, the newly added records should be captured by log reader and then replicate to the subscriber by distributor agent. In the next step, data will be validated.
Now I will add data on both the publisher tables to add 5 more records on each table.
Post validating the data, you will now see that 5 new records have been added (with total 10 rows) on both the tables. The same have been replicated to the subscriber.
Replication health status:
Moving to the second scenario where sometimes we are left with the last option to fix any synchronization issues with the replication by Reinitializing the subscriber.
In our case, we have initialized the replication through backup and the following is the process steps to reinitialize the subscriber.
Stopping the replication agents is the best recommended practice before doing any operations with the articles.
Trigger full backup for the database ABC_Pub on publisher server and disable the T-Log backup job if any.
Restore subscriber database ABC_Sub with Replace:
Data has been validated and as shown in the following snapshot, both Cars and Bikes table have 5 records each and Country and customer table has 10 records each.
Now run the following stored procedure with the required parameters to reinitialize the subscriber.
Enable replication agent jobs and start:
Insert additional 5 records on the following two replicated tables:
Validate the data now on subscriber database:
Validation is now complete and 5 newly added records have been replaced to subscriber and all the tables now have 10 records each.
Health check for replication:
I hope you find the above discussed two scenarios useful for adding new articles and reinitializing the subscriber which was already initialized through backup.
Use the Feedback tab to make any comments or ask questions. You can also start a conversation with us.