locked
How to configure Peer To Peer Topology after adding new table column to a database schema RRS feed

  • Question

  • Greetings,

    Our project is using SQL Server 2008 R2 running on Windows Server 2008 R2.
    We are currently trying to set up replication between two SQL Server Database.

    We are using Transactional Replication with Peer-To-Peer-Topology configured.
    During the setup, no error message is prompted and we were able to configured Publication in both server successfully.
    However, when the replication process started, we always received an error message saying that 'Column A' does not exist in 'Table A'. Although this column is there in both databases.

    Column A was recently added into the schema. Is there any step that I missed here, if I want to set-up replication right after adding new column?

    Wednesday, July 2, 2014 2:47 AM

Answers

  • Did you add the article once the replication was configured? I mean after the databases were restored on the other nodes? If yes, Could you please take a look at this link and confirm if you did add the article the same way

    http://msdn.microsoft.com/en-us/library/ms146867(v=sql.105).aspx


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com


    • Edited by Ashwin Menon Wednesday, July 2, 2014 10:36 AM
    • Marked as answer by Sofiya Li Wednesday, July 9, 2014 7:01 AM
    Wednesday, July 2, 2014 10:33 AM
  • Hi Hendra,

    Just to add to what Ashwin said, please verify that you added the column using ALTER TABLE ADD syntax.  The schema change should eventually be received by all nodes in the peer-to-peer topology.  Note that if you do not quiesce the system that there may be data changes that need to be applied before the schema change is applied.

    Also, please verify that publication property @replicate_ddl is set to true.


    Brandon Williams (blog | linkedin)

    • Proposed as answer by Sofiya Li Thursday, July 3, 2014 9:18 AM
    • Marked as answer by Sofiya Li Wednesday, July 9, 2014 7:00 AM
    Wednesday, July 2, 2014 4:47 PM
  • Hi Hendra,

    According to your description, replication supports a wide range of schema changes to published objects. When you make any of the schema changes on the appropriate published object at a Microsoft SQL Server Publisher, that change is propagated by default to all SQL Server Subscribers, such as ALTER TABLE, ALTER PROCEDURE and so on. Whenever you add a column to a transactional publication, it is appropriate that use sp_repladdcolumn or sp_repldropcolumn statement if the table is republished at the Subscriber, it will be propagated and run at the Subscribers to complete the schema changes at the subscription databases. For more information, see: http://msdn.microsoft.com/en-us/library/ms151870.aspx

     In addition, I recommend you replicate Schema Changes via Transact-SQL Programming , for example, specify a value of replicate_ddl for @property and a value of 1 for @value. There is a similar issue about altering tables in transactional replication.

    http://dba.stackexchange.com/questions/45442/transactional-replication-altering-tables-and-adding-stored-procedures

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    • Marked as answer by Sofiya Li Wednesday, July 9, 2014 7:00 AM
    Thursday, July 3, 2014 9:17 AM

All replies

  • Did you add the article once the replication was configured? I mean after the databases were restored on the other nodes? If yes, Could you please take a look at this link and confirm if you did add the article the same way

    http://msdn.microsoft.com/en-us/library/ms146867(v=sql.105).aspx


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com


    • Edited by Ashwin Menon Wednesday, July 2, 2014 10:36 AM
    • Marked as answer by Sofiya Li Wednesday, July 9, 2014 7:01 AM
    Wednesday, July 2, 2014 10:33 AM
  • Hi Hendra,

    Just to add to what Ashwin said, please verify that you added the column using ALTER TABLE ADD syntax.  The schema change should eventually be received by all nodes in the peer-to-peer topology.  Note that if you do not quiesce the system that there may be data changes that need to be applied before the schema change is applied.

    Also, please verify that publication property @replicate_ddl is set to true.


    Brandon Williams (blog | linkedin)

    • Proposed as answer by Sofiya Li Thursday, July 3, 2014 9:18 AM
    • Marked as answer by Sofiya Li Wednesday, July 9, 2014 7:00 AM
    Wednesday, July 2, 2014 4:47 PM
  • Hi Hendra,

    According to your description, replication supports a wide range of schema changes to published objects. When you make any of the schema changes on the appropriate published object at a Microsoft SQL Server Publisher, that change is propagated by default to all SQL Server Subscribers, such as ALTER TABLE, ALTER PROCEDURE and so on. Whenever you add a column to a transactional publication, it is appropriate that use sp_repladdcolumn or sp_repldropcolumn statement if the table is republished at the Subscriber, it will be propagated and run at the Subscribers to complete the schema changes at the subscription databases. For more information, see: http://msdn.microsoft.com/en-us/library/ms151870.aspx

     In addition, I recommend you replicate Schema Changes via Transact-SQL Programming , for example, specify a value of replicate_ddl for @property and a value of 1 for @value. There is a similar issue about altering tables in transactional replication.

    http://dba.stackexchange.com/questions/45442/transactional-replication-altering-tables-and-adding-stored-procedures

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    • Marked as answer by Sofiya Li Wednesday, July 9, 2014 7:00 AM
    Thursday, July 3, 2014 9:17 AM