CREATE OR ALTER is not fully supported in Transactional Replication DDL commands
We've been having trouble with some objects not replicating correctly after a DDL change.
When a published function or proc is altered, the following appears to happen:
A database trigger is called: tr_MStran_alterschemaonly
Which calls a proc called: sys.sp_MStran_ddlrepl - This proc will output debug IF MSrepl_debug_DDL exists (create anything called this!)
which calls a function: master.sys.fn_replgetparsedddlcmd, which is designed to return the ddl after the object's name, to be used for generating the command to be applied
at the subscribers.
SQL Server BOL states that ALTER is supported (https://docs.microsoft.com/en-us/sql/relational-databases/replication/publish/replicate-schema-changes?view=sql-server-2016)
However, this doesn't appear to be the case:
(13.0.4451.0): only works for 'Alter' because 'create or alter' is not supported.
(13.0.4466.4): Works fine as long as you don't have a comment before the 'create or alter'
(13.0.4522.0): Works fine as long as you don't have a comment before the 'create or alter'
So the functionality appears to have been included in a patch, trying with the latest patch level for SQL2016
(13.0.5377.0): Works fine as long as you don't have a comment before the 'create or alter'
It looks like Create or Alter works but only if it isn't preceded with a comment. The sys.sp_MStran_ddlrepl proc has 'Create or Alter' catered for on line 147.
Rod Edwards
shared this idea
Thanks for reaching out!
We are ware of this limitation; however, there is not current plan to address it (yet)
Thanks,
-Matteo and Li