Sql-server – Transactional replication altering tables and adding stored procedures

sql serversql-server-2008transactional-replication

I have a database that is replicated in several locations. It is setup as transactional replication. However, now I need to add more columns to the table. Add more stored procedures and etc. Hence, if I make the change in the publisher, will it replicate across the subscribers or will it negatively impact the replication?

Suggestions will be very helpful.

Best Answer

You should be able to make DDL changes as described in this article .

Make Schema Changes on Publication Databases

make sure you dont use SSMS. Use T-SQL only.

How to: Replicate Schema Changes (Replication Transact-SQL Programming) specify a value of replicate_ddl for @property and a value of 1 for @value.

EXEC sp_changepublication 
@publication = N'publication_name', 
@property = N'replicate_ddl', 
@value = 0;

EDIT : Below is what you need to take care, if you want to avoid reinitializing subscription(s).

@sync_method=N'character'
  1. Script out the replication and look for sp_addpublication --- @sync_method=N'character'
  2. If the sync_method is “character” and you add a column to the publication, it will generate a message and ask to reinitialize subscription(s).

  3. Change the sync_method to “native”.

    @replicate_ddl=0

  4. By default, the @replicate_ddl is set to 0 “false”, meaning no schema changes are replicated.

  5. Changing the value to 1 “True” will allow changes done on publisher to propogate to subscribers (as shown in below figure).

Using T-sql:

EXEC sp_changepublication @publication='kin_test_Trepl'
    ,@property='sync_method'
    ,@value='native'
-- sync_method = native ==> this will not reinitialize subscribers
-- sync_menthod = character ==> this will generate below message:
--DDL replication is forcing reinitialization because either publication'kin_test_Trepl' uses character mode bcp, or timestamp/identity column is being replicated as base type only for article 'numbers'.
--Reinitialized subscription(s).
GO

EXEC sp_changepublication @publication='kin_test_Trepl'
    ,@property='replicate_ddl'
    ,@value=1
-- 1 = allow ddl changes to be replicated to subscribers
-- 0 = DO NOT propogate ddl changes to subscribers
GO

Then we can add the column to table that is replicated as below:

e.g: ALTER TABLE numbers ADD comment varchar(200)

Note that the column is nullable. If it is not null then it will generate below error:

Msg 4901, Level 16, State 1, Line 1 ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'comment' cannot be added to non-empty table 'numbers' because it does not satisfy these conditions.

Related Question