Adding new articles & reinitializing the existing SQL Server transactional replication

by Rackspace Technology Staff

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

new articles on DB Picture1
  • 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:

articles on DB

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: 

articles on DB Picture 2

Created tables on subscriber using the definition from publisher: 

articles on DB Picture 3

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:

articles on DB Picture 4

In the next step, Select Destination server (Subscriber) and database name:

Select the table names to transfer the data and click next

articles on BD Picture 6

 

articles on DB Picture 6

 In the next step, Export was successful:

articles on DBPicture 7

Validating the data:

As you can see, the data has synced between the publisher and subscriber.

articles on DB Picture 9

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. 

Article on DB Picture 9

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. 

Articles on DB Picture 10

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. 

replication on DB Picture 11

Insert new data on publisher. 

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

Articles on DB Picture 12

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

articles on DB Picture 14

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.

articles on DB 15

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. 

replication on DB Picture 16

Restore the database on subscriber database with replace and norecovery 

Restore subscriber database ABC_Sub with Replace:

Article on DB Picture 17

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.
 

Article on DB Picture 22

 

Health check for replication

Add articles to DB

 

Learn How Rackspace Can Improve Your Databases' Capabilities