Replicate to table with more columns?

Question

Replicate to table with more columns?

RustyT

Right there with Babe

Points: 758
More actions
September 24, 2008 at 2:03 pm

#194057

Is it possible to replicate to a table with more columns. I currently do a snapshot replication of a small table nightly. The issue now is that where that table is being replicated to they would like to add columns. Below is an example how they would like it to be. Is it possible to replicate the data from database 1, table A to database 2, table A where database 2 table A has additional columns? I quickly tried adding the columns to database 2, table A but the table was overwritten when the snapshot was applied. Thanks for any assistance
-rusty
Database 1 -Table A
Column1
Column2
Column3
Database 2 - Table A
Column1
Column2
Column3
Additional Column1
Additional Column2

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

steve smith-401573 SSChampion Points: 10889 More actions · Answer 1

There is a difference between requesting a 'what' and requesting a 'how'. This sounds like a 'what' request - they want additional columns to correlate with a snapshot-replicated table. Telling you to put that content in the SAME table is a 'how' request.

In short, you would have to script a job to add these new fields after every snapshot and restore the content. If you honor the 'what' but do it properly, then a table A' with identical keys but only the additional fields will force their developers to perform a little extra work (OR you can create a view which (inner) joins the two tables and use that instead).

Snapshots overwrite.

RustyT Right there with Babe Points: 758 More actions · Answer 2

I probably should have mentioned that I am not restricted to just snapshot replication. I chose to use it because the table being replicated doesn't change often and where it was being replicated to it is only used for reference.

In response to your post... I'm not sure I'm following you on the 'what' and 'how'. But based on your reply it sounds like the best options for me, if I continue snapshot replication, are to build a table that could hold the additional columns and have the developer relate these tables or build a view of the two tables and have him query the view.

thanks for your help

-rusty

happycat59 One Orange Chip Points: 29389 More actions · Answer 3

I am wondering why you would want to use snapshot replication to a table that has additional columns. The additional columns can't have any data in them - if something updates a record, that update will be lost when you apply a new snapshot. Seems like a waste of effort to me.

You may be able to get around this issue by creating a view on the target table. The view would have the same columns as in the source system. I am not able to test this at the moment so I am not sure whether there will be any problems but you may need

- to stop replication creating the schema on the target database (this is an option when you create the subscription)

- to check that replication is happy to use a view on the target. I don't think that it cares but a test with a few records will confirm this.

- confirm that any additional columns either accept NULLS or have appropriate default values.

RustyT Right there with Babe Points: 758 More actions · Answer 4

thanks for the reply.

i currently have snapshot replication setup because all that was needed (originally) was a refreshed copy of the table. Now they want to add additional columns to the subscribing table. I don't have to continue using snapshot replication and based on the test i did and the replies i received snapshot will just overwrite. I am going to look into transactional replication to see if the ability is there as well as your suggestions. I also thought about creating a job to run nightly to perform necessary insert, update, deletes.

thank you very much for your time.

steve smith-401573 SSChampion Points: 10889 More actions · Answer 5

Rusty -

If you use transactional replication, beware of the initial snapshot - it will overwrite your added fields. Also, each time you update a record in the subscriber, you will lose the added content on those fields.

Depending on how static / dynamic the additional columns are, you are best served to separate the new fields out into a separate table (I am anticipating that if you were to simply add fields to existing records, proper relationships exist for all the different fields within a single record, and only one additional table will be required). Then the only 'duplication' you need are the key fields which link the two tables together uniquely (1-1 relationship); some additional programming effort will be required to properly support this new relationship between tables.

Otherwise, you'll be driven into a merge replication where you copy back the subscriber's fields onto the publisher in order to preserve content - MUCH overhead for little gain.

Is there an architect involved in this design change? I'd suspect not, given your initial question. But having an architect to consult on this concept may be able to come up with an even better solution by looking at the bigger picture than what you've painted here. If not, then you really will need a separate table kept outside replication to properly preserve content **UNLESS** every time a record in the subscriber's table is replicated, the additional fields also need to change. But it doesn't sound like that's the scenario you're dealing with.

RustyT Right there with Babe Points: 758 More actions · Answer 6

Thanks for you response.

Unfortunately I do not have an architect to consult but in future design changes I agree that it is probably best that we consult one. Thanks again and I will be using your suggestion.