How to add new articles to existing transactional replication?

How to add new articles to existing transactional replication?

Select the article which you want to add in the publication. In our case, I am going to add the article “Foresttbl2” in the existing publication “ForestPub” Follow the link Capture Important Parameters of the SQL Server Replication to all the columns definitions. Now, run the “Snapshot Agent” to initialize the newly added article.

When to use SQL 2008 R2 transactional replication with pull subscribers?

Using SQL 2008 R2 transactional replication with pull subscribers, when we add an article, I’d like to avoid having to create an entire snapshot (the db is ~80 GB, so this takes hours). From this article, I’ve seen how to do this with a partial snapshot by setting immediate_sync off, but that didn’t work for us.

How to add Article to transactional publication without?

Open up Replication Monitor, select the publication, go to Agents, right-click Log Reader Agent, click Stop. Set the publication to not allow-anonymous & not immediate-sync, using sp_changePublication – yes, as @cody_konior points out, this is under-documented, but it did work fine in my case. YMMV

How to reinitialize a subscription in replication monitor?

A dialog box Reinitialize Subscription (s) opens. Select Use a new snapshot and then click the Mark For Reinitialization button. We must create a new snapshot after adding the new articles. The new snapshot can be generated using Replication Monitor.

Is the snapshot agent used in transactional replication?

The procedures by which the Snapshot Agent implements the initial snapshot in transactional replication are the same procedures used in snapshot replication (except as outlined above with regard to concurrent snapshot processing).

Do you have to be read only in transactional replication?

By default, Subscribers to transactional publications should be treated as read-only, because changes are not propagated back to the Publisher. However, transactional replication does offer options that allow updates at the Subscriber.

Can a Azure SQL managed instance do transactional replication?

However, transactional replication does offer options that allow updates at the Subscriber. Azure SQL Managed Instance can be a publisher, distributor, and subscriber for snapshot and transactional replication. Databases in Azure SQL Database can only be push subscribers for snapshot and transactional replication.

How to add an article to an existing subscription?

Let us see the steps on how to add an article to an existing Transactional Subscription initialized through backup Adding new articles to a publisher and a subscriber can be addressed in two ways The setup instruction is not discussed in this article and it is out of scope for the discussion.

When to add a new article to an existing SQL Server publication?

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.