Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

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.

share|improve this answer
    
Can you please explain the second part....I am a bit of a newbie to this. –  user24250 Jun 28 '13 at 21:40
    
If possible can you please also provide an example. –  user24250 Jun 28 '13 at 21:41
    
@user24250 Added with full explanation with steps. If it answers your question feel free to upvote / mark as answer. –  Kin Jun 28 '13 at 21:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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