SQL Server replication: Need to remove articles from the publication

50 Views Asked by At

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 = '<TheArticleName>' --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'
0

There are 0 best solutions below