Adding new articles & reinitializing the existing SQL Server transactional replication

Previously, we had discussed how to repGenerally, a user tends to use a snapshot agent to add any new articles to the publication or reinitializing the subscriber, however, in this scenario, we have already configured the transactional replication using the database backup.

Introduction

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, we have already configured the transactional replication using the database backup.
The following are the detailed steps: 

1. Add new articles to the existing publication that was initialized with the backup.
2. Reinitialize the subscriber that was already initialized with the backup.

Scenario 1: How to Add new articles to the existing publication which was initialized with the backup

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

Process Steps Overview:

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.

1. Synchronize the data for new articles from publisher to subscriber using any popular import/export method.

 Add new tables to the publisher database and insert a few rows. 

   -     Script the create definition for new tables and execute on subscriber database.

   -     Use export\import method to sync the data between the publisher and subscriber. 

2. Stop the Log reader and Distributor agent jobs.

3. Add the new articles to the publication using GUI to complete the configuration.

4. Enable and start Log reader and distributor agent jobs.

5. Insert new data on publisher.

6. Validate the data on subscriber.

Step 1: Synchronize the data for new articles from publisher to subscriber using import/export method. 

Articles on the existing replication

• Add new tables to the publisher database and insert a few rows. 
• Script the create definition for new tables and execute on subscriber database.
• Use export\import method to sync the data between the publisher and subscriber. 

2.    Stop the Log reader and Distributor agent jobs.
3.    Add the new articles to the publication using GUI to complete the configuration.
4.    Enable and start Log reader and distributor agent jobs.
5.    Insert new data on publisher.
6.    Validate the data on subscriber

Step 1: Synchronize the data for new articles from publisher to subscriber using import/export method.

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's 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.

1. Stop the Log reader and the distributor agent jobs– 

Stalling the replication agents is the best recommended practice before doing any operations with the articles. 

2. Add the new articles to the publication using GUI to complete the configuration

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. 

Enable and start Log reader and distributor agent jobs –  

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. 

Insert new data on publisher. 

Now I will add data on both the publisher tables to add 5 more records on each table.

Validate the data on subscriber: 

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

Scenario 2: How to Reinitialize the subscriber that was already initialized with backup:

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.

1.    Stop the Log reader, distributor agent and distributor cleanup jobs. 
2.    Take the full backup for the publisher database and disable the T-Log backup job. 
3.    Restore the database on subscriber database with replace. 
4.    Validate the data. 
5.    Run sp_addsubscription
6.    Enable Log reader and distributor jobs, insert new records on the publisher database and validate the data on subscriber.

Stop the Log reader and distributor agent jobs. 

Stopping the replication agents is the best recommended practice before doing any operations with the articles.

Take full backup for the publisher database and disable the T-Log backup job. 

Trigger full backup for the database ABC_Pub on publisher server and disable the T-Log backup job if any. 

Restore the database on subscriber database with replace and norecovery 

Restore subscriber database ABC_Sub with Replace:

Validate the data between publisher and subscriber database.

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.
 

 

Health check for replication