Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Add new article to existing publication for SQL Server Transactional Replication


By:   |   Last Updated: 2018-10-15   |   Comments (3)   |   Related Tips: More > Replication

Problem

SQL Server Replication is a great tool to transfer data between different instances of SQL Server as well as other non-SQL Server databases. When setting up replication, you never know what new database changes will occur, so there is often the need to add additional objects to the publication. In this tip we will look at how to add a new article (database object) to an existing SQL Server publication.

Solution

In this tip, I am going to demonstrate how to add an article (database object) to an existing replication publication.

I have set up transactional replication as follows:

  1. Replication Type: Transactional Replication
  2. Publisher database: AdventureWorks2014 on instance TTI412-VM\SQL2017
  3. Subscriber database: Customer_DB on instance TTI412-VM

I have a publication named Customer_Publication on the publisher database. The publication has one article which is a table named Customers. Replication has already been setup with the subscriber, so on the subscriber instance the same table, Customers, exists in the database Customer_DB.

According to the concept of transactional replication, row and data changes made on the Customers table at the publisher, replicate to the subscriber table Customers.

The following image illustrates the set-up:

replication stream

To review the publisher information open SQL Server Management Studio, expand the SQL Instance, expand Replication then expand Local Publications, as shown below. We can see we have a publication called Customer_Publication.

ssms databases and publications

To review the subscriber information open SQL Server Management Studio, expand the SQL Instance, expand Replication and expand Local Subscriptions as shown below:

ssms databases and subscriptions

Alternatively, you can view the replication settings by executing T-SQL queries. You can review the scripts in this article. The following output has been generated after executing the T-SQL scripts.

The Output of Publisher Server:

output of publications on publisher

The Output of Subscriber Server:

output of publications on subscriber

Add Article in Replication using SQL Server Management Studio

You can add articles to an existing publication from the Properties window on the publisher server. To open the Properties window, in SQL Server Management Studio, expand Replication, expand Local Publications and right click on the Customer_Publication and select Properties as shown below.

publication properties

A publication Properties dialog box opens. In the dialog box, select the Articles page. It populates the list of objects which are already part of the publication. We can see we only have the Customers table as part of this publication.

publication properties

To get a list of all database objects, un-check the checkbox for Show only checked articles in the list as shown below. Then select the list of database objects to add to the existing publication. Below I have highlighted the five tables I will add to the publication. After selecting the objects, click OK to close the dialog box.

publication properties

After adding the articles to the existing publication, we must re-initialize the subscriptions with a new snapshot. To do that, right click on the publication and select Reinitialize All Subscriptions as shown below.

reinitialize all subscriptions

A dialog box Reinitialize Subscription(s) opens. Select Use a new snapshot and then click the Mark For Reinitialization button.

reinitialize all subscriptions

We must create a new snapshot after adding the new articles. The new snapshot can be generated using Replication Monitor. To open Replication Monitor, right click on the Customer_Publication and select Launch Replication Monitor as shown below.

launch replication monitor

When Replication Monitor opens, click on the Agents tab. In the dropdown select Snapshot Agent from the Agent types. From the list of snapshot agents, right click on [AdventureWorks2014].[Customer_Publication] and select Start Agent as shown below.

replication monitor

Once the Snapshot Agent runs, you can view the process in the Last Action.

replication monitor

Alternatively, you can run the snapshot job from the View Snapshot Agent Status dialog box. To open it, expand Local publications and right click on Customer_Publication and select View Snapshot Agent Status. In the dialog box, click the Start button. You can see the status of a snapshot process in Status text box.

snapshot agent status

Once the snapshot job completes, the new articles will be added to the subscribers. To verify, connect to the subscriber database and execute the following query.

use Customer_DBgo
select name,create_date,type_desc 
from sys.tables 
where name not like '%MS%'

Following is the output of the above query. This shows all of the tables that now exist in the Customer_DB database on the subscriber.

query results

Let's check the status of the publisher and subscriber. To do that, re-execute the queries we used above.

The Output of Publisher Server:

replication articles

Summary

In this tip, I have explained how to add an article to existing transactional replication publication.

Next Steps

  • Read more tips about replication here.
  • Read interview questions related to replication here.


Last Updated: 2018-10-15


get scripts

next tip button



About the author
MSSQLTips author Nisarg Upadhyay Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional with more than 5 years of experience.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, December 24, 2018 - 10:41:06 PM - Sanjeev Sinha Back To Top

If I am going to add Article in Snapshot Replication, please guide me...


Wednesday, October 24, 2018 - 3:30:32 AM - Manu Back To Top

As Raja Reddy highlighted you can eliminate the snapshot generation for all objects.

 

http://www.sqlservercentral.com/articles/Replication/119650/

To avoid generating a snapshot for all articles when adding a new article, publication property immediate_sync must be set to 0. Call sp_addarticle, then sp_addsubscription. If subscriptions are pull you must also call sp_refreshsubscriptions. Then generate a snapshot and only a snapshot for the newly added article will be generated.


Thursday, October 18, 2018 - 9:19:08 AM - Rajasekhar Reddy Back To Top

No need to run newsnapshop for all objects for the just adding of 1 or 2 tables. We have a process to generate snapshot for only newly added articles.


Learn more about SQL Server tools