will transnational replication made changes to the source tables?

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 --

Answer 1

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

Answer 2

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 --

Answer 3

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

Answer 4

thanks tom and bhau_nz

---

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

Answer 5

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.

Answer 6

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