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
Best Answer
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. HoweverYou 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
andallow_anonymous
are set to FALSE (0) to avoid the creation of the full snapshot and not just the snapshot of the article/s changed.Then you can change these properties as described here.
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.