Unable to replicate schema changes in transactional replication

Question

I have implemented a transactional replication on one of my dbs.

In the documents provided by Microsoftsql (here and here), I should be able to change the schema and that these changes will be replicated to the subscriber side as well. However, I haven't been able to do so.

Is there anything I am missing while setting up transactional replication?

I have used the following customized script in accordance to my requirements, basically replicating each and every column and everything associated(like a complete replica):

use [db_name]
exec sp_changearticle 
    @publication = N'db_PUB', 
    @article = N'table_name', 
    @property =schema_option, 
    @value= '0x000003454DFD7FDF', 
    @force_invalidate_snapshot =0, 
    @force_reinit_subscription =1
GO

The error I get while making changes is something like this:

Cannot perform action on the table because it is published for replication.

I am thinking of a way to get over this issue, by removing the article from publication, make changes to it and then add it back to publication.

But every time I do this I have to generate a new snapshot and reinitialize the subscription, which doesn't seem to be much efficient.

What can I do to resolve this issue? Any alternative?

exec sp_dropsubscription   
        @publication =  'tTestFNames' 
     ,  @article =  'tEmployees' 
     ,  @subscriber =  'RSCOMPUTER'
     ,  @destination_db =  'testrep' 

  exec sp_droparticle  
        @publication =  'tTestFNames'
     ,  @article =  'tEmployees'

  alter table tEmployees alter column Forename varchar(100) null

  exec sp_addarticle  
        @publication =  'tTestFNames' 
     ,  @article =  'tEmployees' 
     ,  @source_table =  'tEmployees' 

  exec sp_addsubscription  
        @publication =  'tTestFNames'
     ,  @article =  'tEmployees'
     ,  @subscriber =  'RSCOMPUTER' 
     ,  @destination_db =  'testrep' 

exec sp_startpublication_snapshot 
     @publication = N'tTestFNames';
Go

Marcello Miorelli · Accepted Answer · 2020-06-03T06:19:36.663

You cannot change the table name or change the primary key of a table that is involved in replication.

you can see the scripts here

You can replicate the schema changes if your publication has @replicate_ddl on. However

The ALTER TABLE ... DROP COLUMN statement is always replicated to all Subscribers whose subscription contains the columns being dropped, even if you disable the replication of schema changes.

You want to avoid running a full snapshot - you want a snapshot just with the affected object(s).

As Brandon Williams said on the comments: First you check your publication and make sure that immediate_sync and allow_anonymous are set to FALSE (0) to avoid the creation of the full snapshot and not just the snapshot of the article/s changed.

SELECT immediate_sync, allow_anonymous 
FROM Your_Published_DBName.dbo.syspublications

Then you can change these properties as described here.

when i change column name , what will be the effect on subscriber, do i have to change the name there as well? while adding subscription or will it be able to use the previous name as well

You will need to remove the table from the replication first. Then do your changes - like renaming a column or changing the primary key. then you need to re-add the table to the publication. Generate a new snapshot - will only have the new version of the table. When you apply this snapshot to the subscriber database - the table there will be overwritten.

You can specify the destination table on the subscriber.

Therefore if you need different permissions, triggers, indexes, stats, or anything else to this table, you need to script all these and re-apply them on the subscription after the snapshot has been applied.

how the changing table name works, if tableA was replicated and it resides at the subscriber, how will changes apply to it, after I change the name to tableB at the the publisher. will I have to remove it tableA from subscriber or will it be overwritten? or will new tableB will be crated at the subscriber?

tableA was part of publication1
you remove tableA from publication1
you rename tableA to tableB
you add tableB to publication1
you generate a snapshot of publication1 (hopefully with only tableB in it)
you add subscriber1 to publication1
you apply the publication1 snapshot on subscriber1
tableB is created on subscriber1

Unable to replicate schema changes in transactional replication

1 Answers1