none
how to automatically update database structure changes to replicated databases automatically

    Question

  • i have set replication of my producton server database to another server which is running at Disaster Recovery site.

    but the changes in table structure and views which are being created on production server are not replication on aonther site.

    can any expert identify the reason why structural changes are not reflecting on DR site.

    Thursday, June 14, 2012 9:27 AM

Answers

  • Changes to schema objects should be replicated. In some cases changes to table structures are not replicated - for example from

    http://technet.microsoft.com/en-us/library/ms188738.aspx

    The @replicate_ddl parameter is ignored when a DDL statement alters or drops a column for the following reasons.
    • When a column is dropped, sysarticlecolumns must be updated to prevent new DML statements from including the dropped column which would cause the distribution agent to fail. The @replicate_ddl parameter is ignored because replication must always replicate the schema change.

    • When a column is altered, the source data type or nullability might have changed, causing DML statements to contain a value that may not be compatible with the table at the subscriber. Such DML statements might cause distribution agent to fail. The @replicate_ddl parameter is ignored because replication must always replicate the schema change.

    • When a DDL statement adds a new column, sysarticlecolumns does not include the new column. DML statements will not try to replicate data for the new column. The parameter is honored because either replicating or not replicating the DDL is acceptable.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Thursday, June 14, 2012 5:04 PM
    Moderator

All replies

  • What type of replication? Transactional? There is a column in syspublications (query this table in the published database) called replicate_ddl. Please check to ensure that this is set to 1. That will tell you whether replication is going to send ddl / schema changes to the subscribers. 

    Additionally, what types of changes are you not seeing replicated or is it all changes not being replicated. 


    David

    Thursday, June 14, 2012 12:58 PM
  • Changes to schema objects should be replicated. In some cases changes to table structures are not replicated - for example from

    http://technet.microsoft.com/en-us/library/ms188738.aspx

    The @replicate_ddl parameter is ignored when a DDL statement alters or drops a column for the following reasons.
    • When a column is dropped, sysarticlecolumns must be updated to prevent new DML statements from including the dropped column which would cause the distribution agent to fail. The @replicate_ddl parameter is ignored because replication must always replicate the schema change.

    • When a column is altered, the source data type or nullability might have changed, causing DML statements to contain a value that may not be compatible with the table at the subscriber. Such DML statements might cause distribution agent to fail. The @replicate_ddl parameter is ignored because replication must always replicate the schema change.

    • When a DDL statement adds a new column, sysarticlecolumns does not include the new column. DML statements will not try to replicate data for the new column. The parameter is honored because either replicating or not replicating the DDL is acceptable.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Thursday, June 14, 2012 5:04 PM
    Moderator