Re initializing replication after the changes in articles using script

Question

Re initializing replication after the changes in articles using script

Anitha John 1

Hi team,

I am using scripts to create Publication, articles and subscriptions using the procedures, sp_addpublication, sp_addpublication_snapshot, sp_addarticle, sp_addsubscription, sp_startpublication_snapshot, sp_addpullsubscription. It is working fine.

Now I should be able to add, remove or update the publication. What are the steps to follow using sql scripts.

Thanks & Regards,
Anitha

Seeya Xi-MSFT 16,586 Reputation points

2022-07-13T06:34:20.257+00:00

Hi @Anitha John ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,
Seeya
Anitha John 1 Reputation point

2022-08-04T15:44:34.38+00:00

Hi @Seeya Xi-MSFT

That was not the solution which I am looking for. I found out the steps to follow once after adding/dropping an article from the existing publication which is from the below link.
https://www.sqlshack.com/how-to-add-drop-articles-from-existing-publications-in-sql-server/
(Adding/dropping article(s) from an existing publication by invalidating the snapshot agent)

But there it is mentioned start Snapshot Agent using Replication monitor, I need a T-SQL for that as well, which I didnt get yet.
It would be great, if you can provide me the sql query for that.

3 answers

Your answer

Seeya Xi-MSFT 16,586 Reputation points

2022-07-13T06:34:20.257+00:00

Hi @Anitha John ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,
Seeya
Anitha John 1 Reputation point

2022-08-04T15:44:34.38+00:00

Hi @Seeya Xi-MSFT

That was not the solution which I am looking for. I found out the steps to follow once after adding/dropping an article from the existing publication which is from the below link.
https://www.sqlshack.com/how-to-add-drop-articles-from-existing-publications-in-sql-server/
(Adding/dropping article(s) from an existing publication by invalidating the snapshot agent)

But there it is mentioned start Snapshot Agent using Replication monitor, I need a T-SQL for that as well, which I didnt get yet.
It would be great, if you can provide me the sql query for that.

Answer 1

Bert Zhou-msft 3,436

Hi,@Anitha John

Your needs are vague and we don't know exactly what you want? Do you want to ask about the aspects that need to be paid attention to when using SP stored procedures ? Please read this document in detail.

Bert Zhou

Answer 2

Hi, @Bert Zhou-msft ,

I have created SQL Server replication transaction with out using SSMS. I used scripts for that.
My question was how can I re-initiate the subscription when the publication articles changes like adding an extra column in a table or removing table etc. without using SSMS. I tried using sp_reinitsubscription, but I didn't work.
What are the steps to follow to change an article and reflect that change in the subscriber. (Not using SSMS, but the scripts and steps for that)

Thanks & Regards,
Anitha

Answer 3

Seeya Xi-MSFT 16,586

Hi @Anitha John ,

Welcome to Microsoft Q&A!
Please refer to this official document: https://learn.microsoft.com/en-us/sql/relational-databases/replication/reinitialize-a-subscription?view=sql-server-ver16
This document contains the SSMS approach and the sql script approach.

I tried using sp_reinitsubscription, but I didn't work.

Only members of the sysadmin fixed server role, members of the db_owner fixed database role, or the creator of the subscription can execute sp_reinitsubscription. You can check it.

Best regards,
Seeya

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Anitha John 1 Reputation point

2022-07-15T15:59:47.093+00:00

Hi Seeya,

Can you please let me know what is the procedure to create a new snapshot?
After changing any article, I should be able to programmatically re-initialize on a new snapshot.

Thanks & Regards,
Anitha John
Anitha John 1 Reputation point

2022-07-20T13:28:41.633+00:00

@Seeya Xi-MSFT can you please let me know?
Seeya Xi-MSFT 16,586 Reputation points

2022-07-21T02:44:52.107+00:00
Hi @Anitha John ,

Hope everything goes well.
You can follow this flow:

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.

Stop the Log reader and Distributor agent jobs.

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

Enable and start Log reader and distributor agent jobs.

Insert new data on publisher.

Validate the data on subscriber.
For more information, please refer to this article which contains all screenshots of every step: https://docs.rackspace.com/blog/adding-new-articles-and-reinitializing-the-subscriber-for-already-initialized-sql-transactional-replication-with-backup/

Best regards,
Seeya
Seeya Xi-MSFT 16,586 Reputation points

2022-07-27T06:06:18.347+00:00

Hi @Anitha John ,

In order to benefit all community members who are having this similar issue, please choose an answer to accept or vote for the answer you think is useful to you. Your contribution is highly appreciated.

Best regards,
Seeya

Share via

Re initializing replication after the changes in articles using script

3 answers

Your answer