1

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

  • Is your publication property @replicate_ddl set to true? Also, can you provide the DDL that you are executing to perform the schema changes please? – Brandon Williams Jun 2 at 15:57
  • @replicate_ddl = 1 this is set in by default, i tried to change table name using this exec sp_rename 'old_name','new_name'; – tsu90280 Jun 2 at 16:10
  • As mentioned in Replication Schema Changes (docs.microsoft.com/en-us/sql/relational-databases/replication/…), you can make the following schema changes to a published article, ALTER TABLE, ALTER VIEW, ALTER PROCEDURE, ALTER FUNCTION, ALTER TRIGGER. sp_rename is not one of the schema changes that are supported. – Brandon Williams Jun 2 at 16:18
  • You'll need to drop the article, rename, and add it back. As noted in (docs.microsoft.com/en-us/sql/relational-databases/replication/…), you must create a new snapshot for the publication. To avoid generating a snapshot for all articles when adding a new article, publication property immediate_sync must be set to 0. Call sp_addarticle, then sp_addsubscription. If subscriptions are pull you must also call sp_refreshsubscriptions.Then generate a snapshot and only a snapshot for the newly added article will be generated. – Brandon Williams Jun 2 at 16:19
  • ok, got it, but as in other dbs like oracle table and column names can be changed using alter table and are referred as schema changes, this was a confusion for me. Any changes you would propose in the above script for removing/adding articles. I am also re initializing the subscription, Is that ok? – tsu90280 Jun 2 at 16:26
1

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?

  1. tableA was part of publication1
  2. you remove tableA from publication1
  3. you rename tableA to tableB
  4. you add tableB to publication1
  5. you generate a snapshot of publication1 (hopefully with only tableB in it)
  6. you add subscriber1 to publication1
  7. you apply the publication1 snapshot on subscriber1
  8. tableB is created on subscriber1
| improve this answer | |
  • Ok, understood, but, one thing I am not getting is 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? – tsu90280 Jun 3 at 5:49
  • 1
    I added this to the answer for your clarity – Marcello Miorelli Jun 3 at 6:20
  • what about re-initialization? should i do that as well? means after starting applying new snapshot, is it necessary to execute the reinit sp? – tsu90280 Jun 3 at 6:38
  • yes the new snapshot has to be generated and then applied to the subscriber(s) - for that you mark the subscriber for reinit and use the new snapshot (the one that has tableB - which is the latest) – Marcello Miorelli Jun 3 at 6:42
  • after re initializing, I get stuck with this The initial snapshot for publication 'FMS_PUB' is not yet available. this is the issue that now haunts me, I mean why it always displays this message, every time i make changes to replication. I followed exactly the same steps. No changes to data are replicated to subscriber now. Can you help me with this? – tsu90280 Jun 3 at 7:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.