Sql-server – Replicating different set of tables to subscribers

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.

If you change a user's permissions to a particular table BEFORE a Subscriber has been initialized or reinitialized, if the article property Copy permissions is set to true, the permissions will be copied to the Subscriber when the snapshot is applied.

If you change a user's permissions to a particular table AFTER a Subscriber has been initialized, the permissions will not be replicated on the fly, unless you generate a new snapshot and mark the Subscriber for reinitialization. Note that the article property Copy permissions must be set to true.

What is typically done is if you need to change a user's permissions to a particular table AFTER a Subscriber has been initialized, and you need the permissions to be present at the Subscriber without reinitializing the Subscriber, the script to grant permissions are posted to the Subscriber using sp_addscriptexec.

I have a post detailing sp_addscriptexec located here at Executing scripts with sp_addscriptexec.