none
Replication without Overwriting

    Question

  • I would like to setup replication between production server and a development server.  At the same time, I would like to be able to add a row of data to the development server and not have that data overwritten when the replication takes place. 

    Is there a way to accomplish this?

    Friday, July 01, 2011 8:15 PM

Answers

  • I would like to setup replication between production server and a development server.  At the same time, I would like to be able to add a row of data to the development server and not have that data overwritten when the replication takes place. 

    Is there a way to accomplish this?


    Simple transactional replication with filtering should help you here .The other way is to create a new similar table in subscriber and periodically move the rows from replicated table to the new table using alter table switch partition command (it points the rows to the new table) which will empty the rows from the subscribed table .On the new tables you can add or remove rows as you wish ...
    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    • Proposed as answer by Peja Tao Monday, July 04, 2011 6:38 AM
    • Marked as answer by tomrippity Wednesday, July 06, 2011 2:16 PM
    Saturday, July 02, 2011 9:25 AM

All replies

  • SQL Server is using stored procedures to handle the data changes on the subscribers end. You can, during setup of replication, specify your own procedure. In there you could filter on a list of primary keys to exclude the development rows from replication.

    if you know the rows in question in advance you can filter them out by specifying a WHERE clause filter during creation of the publication.

    Friday, July 01, 2011 10:23 PM
  • I dont think that is possible using replication...
    Friday, July 01, 2011 10:25 PM
  • I would like to setup replication between production server and a development server.  At the same time, I would like to be able to add a row of data to the development server and not have that data overwritten when the replication takes place. 

    Is there a way to accomplish this?


    Simple transactional replication with filtering should help you here .The other way is to create a new similar table in subscriber and periodically move the rows from replicated table to the new table using alter table switch partition command (it points the rows to the new table) which will empty the rows from the subscribed table .On the new tables you can add or remove rows as you wish ...
    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    • Proposed as answer by Peja Tao Monday, July 04, 2011 6:38 AM
    • Marked as answer by tomrippity Wednesday, July 06, 2011 2:16 PM
    Saturday, July 02, 2011 9:25 AM
  • I would like to setup replication between production server and a development server.  At the same time, I would like to be able to add a row of data to the development server and not have that data overwritten when the replication takes place. 

    Is there a way to accomplish this?

    I understand I am late to the party on this question but it must be known that it appears the question was incorrectly interpreted by Abhay_78.  The question is listed in the quote above.  It appears that the user wants to insert replicated data and not overwrite data being used in the table of the development server, though it is true that using filters replication (transactional or merge) allow you to control the data that is being replicated to the subscriber, it must be understood that this data will still be overwritten once the transaction is commited on the subscriber database.  In order to have this environment, it is best to create a secondary table in the subscriber DB or even a separate secondary DB that will hold the data. Why is this best practice? Because you can then continue with the normal setup and administration of replication and just add triggers to the table to insert the replicated data in the secondary table or separate secondary DB, from there your application should be linked to that table.  Lastly, it is my suggestion that when you setup replication have you publisher replicate to a subscriber that has different table names but exact same table structure as your production environment, this will allow you to use the real tablename on your development table that will have the replicated date plus the new data you want to add as well.

    You are welcome :-)


    Sr. Sql Server Database Administrator

    • Proposed as answer by Frank Ivey Wednesday, July 24, 2013 7:38 PM
    Wednesday, July 24, 2013 7:38 PM