locked
will transnational replication made changes to the source tables? RRS feed

  • Question

  • hi all:

      Here is the situation: We want to replication a few big tables TabA, B, C from Server 1 to Server 2.  We are thinking about using Transactional Replication but have a few concerns about the schema changes it's gonna make on the articles. 

    My questions:

      1. Will transactional replication add new tracking columns to the source Tables TabA,B,C?  We can't tolerate this production schema changes. 

     Thanks

    Hui


    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

    Tuesday, February 16, 2016 8:10 PM

Answers

  • Yes if you have configured Transactional replication with updatable subscriptions.

    Why does replication add a column to replicated tables; will it be removed if the table isn't published?

    To track changes, merge replication and transactional replication with queued updating subscriptions must be able to uniquely identify every row in every published table. To accomplish this:

    • Merge replication adds the column rowguid to every table, unless the table already has a column of data type uniqueidentifier with the ROWGUIDCOL property set (in which case this column is used). If the table is dropped from the publication, the rowguid column is removed; if an existing column was used for tracking, the column is not removed.

    • If a transactional publication supports queued updating subscriptions, replication adds the column msrepl_tran_version to every table. If the table is dropped from the publication, the msrepl_tran_version column is not removed.

    • A filter must not include the rowguidcol used by replication to identify rows. By default this is the column added at the time you set up merge replication and is named rowguid.

    Source :https://msdn.microsoft.com/en-us/library/ms151740.aspx

    Hope this helps

    Thanks

    Bhanu


    • Edited by bhanu_nz Tuesday, February 16, 2016 9:17 PM
    • Proposed as answer by Ice Fan Wednesday, February 17, 2016 2:11 AM
    • Unproposed as answer by cat_ca Wednesday, February 17, 2016 7:27 PM
    • Marked as answer by cat_ca Wednesday, February 17, 2016 10:27 PM
    Tuesday, February 16, 2016 9:15 PM
  • No.  Transactional replication (without updatable subscriptions) does not change the source tables.

    Every published table must have a Primary Key.

    Please read:

    https://technet.microsoft.com/en-us/library/ms151706(v=sql.105).aspx

    and

    https://technet.microsoft.com/en-us/library/ms151254(v=sql.105).aspx

    • Marked as answer by cat_ca Wednesday, February 17, 2016 10:27 PM
    Wednesday, February 17, 2016 8:12 PM

All replies

  • Yes if you have configured Transactional replication with updatable subscriptions.

    Why does replication add a column to replicated tables; will it be removed if the table isn't published?

    To track changes, merge replication and transactional replication with queued updating subscriptions must be able to uniquely identify every row in every published table. To accomplish this:

    • Merge replication adds the column rowguid to every table, unless the table already has a column of data type uniqueidentifier with the ROWGUIDCOL property set (in which case this column is used). If the table is dropped from the publication, the rowguid column is removed; if an existing column was used for tracking, the column is not removed.

    • If a transactional publication supports queued updating subscriptions, replication adds the column msrepl_tran_version to every table. If the table is dropped from the publication, the msrepl_tran_version column is not removed.

    • A filter must not include the rowguidcol used by replication to identify rows. By default this is the column added at the time you set up merge replication and is named rowguid.

    Source :https://msdn.microsoft.com/en-us/library/ms151740.aspx

    Hope this helps

    Thanks

    Bhanu


    • Edited by bhanu_nz Tuesday, February 16, 2016 9:17 PM
    • Proposed as answer by Ice Fan Wednesday, February 17, 2016 2:11 AM
    • Unproposed as answer by cat_ca Wednesday, February 17, 2016 7:27 PM
    • Marked as answer by cat_ca Wednesday, February 17, 2016 10:27 PM
    Tuesday, February 16, 2016 9:15 PM
  • hi bhanu_nz, thanks, how about the normal the transaction replication ? There is another transactional replication type which is called  transactional replication with updatable subscription, but I am not using this one. 


    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

    Wednesday, February 17, 2016 7:27 PM
  • No.  Transactional replication (without updatable subscriptions) does not change the source tables.

    Every published table must have a Primary Key.

    Please read:

    https://technet.microsoft.com/en-us/library/ms151706(v=sql.105).aspx

    and

    https://technet.microsoft.com/en-us/library/ms151254(v=sql.105).aspx

    • Marked as answer by cat_ca Wednesday, February 17, 2016 10:27 PM
    Wednesday, February 17, 2016 8:12 PM
  • thanks tom and bhau_nz

    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

    Wednesday, February 17, 2016 10:27 PM
  • Yes if you have configured Transactional replication with updatable subscriptions.

    Why does replication add a column to replicated tables; will it be removed if the table isn't published?

    To track changes, merge replication and transactional replication with queued updating subscriptions must be able to uniquely identify every row in every published table. To accomplish this:

    • Merge replication adds the column rowguid to every table, unless the table already has a column of data type uniqueidentifier with the ROWGUIDCOL property set (in which case this column is used). If the table is dropped from the publication, the rowguid column is removed; if an existing column was used for tracking, the column is not removed.

    • If a transactional publication supports queued updating subscriptions, replication adds the column msrepl_tran_version to every table. If the table is dropped from the publication, the msrepl_tran_version column is not removed.

    • A filter must not include the rowguidcol used by replication to identify rows. By default this is the column added at the time you set up merge replication and is named rowguid.

    Source :https://msdn.microsoft.com/en-us/library/ms151740.aspx

    Hope this helps

    Thanks

    Bhanu


    Its incorrect.

    the question was about transactional replication.

    The content you posted is about merge replication.

    In transnational replication, additional Column is not added. only requirement is that the tables should have primary key.

    Thursday, February 18, 2016 10:39 AM
  • Hi Aniket,

    I content I posted is about transactional replication with updatable subscriptions which is different to merge replication that you  mentioned in your comment. I also mentioned that - it will be a YES only if you are using replication with updatable subscriptions which indirectly gives you an impression that NO is the answer if you are not using updatable subscriptions in your replication.

    See the difference between merge and updatable subscriptions below

    https://technet.microsoft.com/en-us/library/aa256086%28v=sql.80%29.aspx?f=255&MSPPError=-2147217396

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c8d5c02a-8569-42f8-9949-4e42b13a73f1/sql-server-merge-replication-vs-sql-server-transactional-with-updatable-subscribtion?forum=sqlreplication

    Hope this helps

    Thanks

    Bhanu

     
    • Edited by bhanu_nz Thursday, February 18, 2016 8:31 PM
    Thursday, February 18, 2016 8:30 PM