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
The schema on the left is your own DDL statement. The schema on the right is a DDL statement generated by your schema comparison tool.
The statement on the right shows constraint names for your DEFAULT constraints. Yours does not have them but that does not mean SQL Server allows you to create a nameless constraint. When you do not provide a name explicitly, SQL Server picks one for you, because ultimately a constraint must have a name. The names on the right are the ones chosen by SQL Server.
So, the two sets of constraints most likely are the same in both databases. It is only the DDL statements that differ, because yours does not assign constraint names.
You can assign your own names if you use the syntax shown in the query on the right. That is, instead of declaring a default value like this:
use the following syntax:
That way the names will always be predictable and, when comparing the schemas, you will not run into this issue again.
The same applies to all types of constraints in SQL Server (defaults, primary keys, foreign keys, check constraints).