SQL Server Replication isn't a good way to handle a database upgrade. A much better approach would be to take a full backup and restore then do your testing. After completing your testing you do a full backup and restore, then perform log restores until you are ready to cut over the database. After you take the production system offline you finish the log restores then bring the database online.
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'
- Script out the replication and look for sp_addpublication --- @sync_method=N'character'
- 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).
Change the sync_method to “native”.
@replicate_ddl=0
By default, the @replicate_ddl is set to 0 “false”, meaning no schema changes are replicated.
- 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.
Best Answer
how SQL server knows to replicate only the stored proc execution (i.e. actual exec myupateproc t-sql command) instead of underlying table data that is being updated on the publisher (i.e. actually replicating 25 million update statement)?
SQL Server knows that when you set up article property as below :
You can explicitly set only to replicate SP defination, execution of SP or Execution in a serialized transaction of the SP while setting up Article property.
Refer here. When you select "serialized transaction of the SP", the entire execution is replicated as a single transaction there by reducing the Transactional Replication overhead i.e. With the replication of stored procedure execution, replication sends only the command to execute the stored procedure at the Subscriber, rather than writing all the updates to the distribution database and then sending them over the network to the Subscriber
Test it before hand as performing such a large update (25 million rows) will be way too much for replication to handle (depending on your replication topology - same distribution server as publisher, geographically separate publisher and subscribers, etc) and some caveats as described in BOL.
You can probably thing of another approach like --