SQL Server replication: Need to remove articles from the publication

0 votes

1 answer

307 views

sql-server replication transactional-replication

                          I am replicating the data from the Database1 on Server1 to respectively, Database2 on Server2.

Now, the requirement is to remove certain tables form being replicated - so I will remove them from the Publication.
I have many articles, so am doing it using script and cursor .. but for simplicity, I will use example of one:

So, I am following this routine, but have a question:
**Questions are:**  Is there anywhere in the below, before or after where I need to 
- Stop / start snapshot agent?
- Stop / start log reader agent?
- Stop / start distribution agent?

If I do have to start / stop respective agent jobs, do I do it for every article or I can do it before (stop) and after (start) the cursor loop where each article is individually dropped?

The routine followed is

 

    USE Database 1
     SELECT @publicationName = name
     FROM dbo.syspublications sp


    -- Change the allow_anonymous property of the publication to FALSE
       EXEC sp_changepublication
        @publication = @publicationName,
        @property = N'allow_anonymous',
        @value = 'FALSE'

		-- Next, disable Change immediate_sync
         EXEC sp_changepublication
         @publication = @publicationName,
         @property = N'immediate_sync',
         @value = 'FALSE'

Assuming, the following is used in the cursor-loop, and there is a variable for @articleName

    SELECT articleName 
	FROM (
		SELECT sp.pubid, sp.name as 'publicationName', sp.description, sa.artid, 
      sa.name as 'articleName', sa.dest_table, sa.schema_option
		FROM dbo.syspublications sp
		INNER JOIN dbo.sysarticles sa
		ON sp.pubid=sa.pubid
	) arts
	WHERE articleName = '' --In reality it will be 'like '%%'

        -- Must drop subscription first
		EXEC sp_dropsubscription 
			@publication = @publicationName, 
			@article = @articleName,-- obtained from above
			@subscriber = 'Server 2'

		-- Drop the transactional article.
		EXEC sp_droparticle 
		  @publication = @publicationName, 
		  @article = @articleName,
		  @force_invalidate_snapshot = 1;
    
    -- Next, Enable Change immediate_sync
    EXEC sp_changepublication
    @publication = @publicationName,
    @property = N'immediate_sync',
    @value = 'TRUE'

    -- Change the allow_anonymous property of the publication to TRUE
    EXEC sp_changepublication
    @publication = @publicationName,
    @property = N'allow_anonymous',
    @value = 'TRUE'
                        

Asked by Dmitriy Ryabin (111 rep)

Feb 14, 2024, 03:49 PM
Last activity: May 28, 2025, 01:10 AM

SQL Server replication: Need to remove articles from the publication

Related Questions