SQL Server DDL Statements does not replicated on SQL

5lwkijsr  于 2023-06-28  发布在  其他
关注(0)|答案(2)|浏览(162)

I don't have much experience on SQL replication(SQL Server 2014). My client have a replication process which was created by his previous contractor. It worked well and suddenly it stopped replicating DDL statements couple of days ago. We have not done any change related to replication. When I checked data, subscriber has received up to date data. Only DDL statements have the problem. It uses transactional replication.

When I searched on web it says that the "Replicate schema changes" option need to set as true on Publication Properties.In my case it was already set to true.

Is there anyway for me to fix this and again have DDL statements to replicate as earlier?

Thank you

qkf9rpyu

qkf9rpyu1#

SQL Server Replication does support schema changes, but not all of them. In your case, CREATE PROCEDURE is not a supported schema change. Why? It's not an article yet, and not marked for replication, thus it cannot be replicated - replication has no way of knowing whether or not you would want that object replicated.

However, if you create the stored proc, then create an article for it, then issue an ALTER PROCEDURE, you will see the change replicated.

Please see article Make Schema Changes on Publication Databases :
Replication supports a wide range of schema changes to published objects. When you make any of the following schema changes on the appropriate published object at a Microsoft SQL Server Publisher, that change is propagated by default to all SQL Server Subscribers:

ALTER TABLE

ALTER TABLE SET LOCK ESCALATION should not be used if schema change replication is enabled and a topology includes SQL Server 2005 or SQL Server Compact 3.5 Subscribers.

ALTER VIEW

ALTER PROCEDURE

ALTER FUNCTION

ALTER TRIGGER

ALTER TRIGGER can be used only for data manipulation language [DML] triggers because data definition language [DDL] triggers cannot be replicated.

Please ensure you read the whole article, to be fully aware of what can be replicated, and under what circumstances.

vddsk6oq

vddsk6oq2#

EXECUTE sp_helppublication  @publication ='<publicationame>';

and look for the replicate_ddl . If it is 0 , then no DDL commands will replicate. Setting it to a 1 will allow the create procedure to replicate.

Use sp_changepublication if changes are needed.

相关问题