how to replicate from multiple sources to one table appending rows w/o deleting the table?

Question

how to replicate from multiple sources to one table appending rows w/o deleting the table?

dzordz_parowkowy

Valued Member

Points: 59
More actions
April 22, 2009 at 2:51 am

#384414

Dear All,
I have following issue:
1. I have several db's with identical structure.
2. Data in each db has teoretically the same id's in the pk for the same tables.
3. I need to create database for reporting that will have the same set of tables with additional db_id field in each table.
4. I wanted to use transactional replication to have the data available in real time and to make it work fast without big overhead on the server.
The problem is that when I create more than one subscription then only the data from first subscription is replicated (I made tests with identical tables with identical data). Can I configure replication to do something like in the following example:
table in db test1 has 2 rows
db_id pk_value fld1_value
1 1 'xx'
1 2 'yy'
table in db test2 has 2 rows
db_id pk_value fld1_value
2 1 'zz'
2 2 'vv'
table in db report where the subscriptions are creted for publications from above 2 tables should have the following data:
db_id pk_value fld1_value
1 1 'xx'
1 2 'yy'
2 1 'zz'
2 2 'vv'
Thanks in advance,
Dzordz

Viewing 4 posts - 1 through 3 (of 3 total)

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

Mike_D Hall of Fame Points: 3295 More actions · Answer 1

Dzordz,

What you are doing is absolutely standard stuff. I have over 300 publishers publishing data to one subscriber. Without being able to see your set up it's difficult to give you any definitive answers. You haven't got a problem with a snapshot have you?

Mike

dzordz_parowkowy Valued Member Points: 59 More actions · Answer 2

Hi,

I wasn't clear enough in my description obove. Firt I was trying to replicate from 2 tables with 2 fields (id, value) where id field was autoincrement pk and the replication produced table with data from the first subscription only and in addition it was not propagating changes from the second one. Then I created views in the publication db's and added db_id column to differentiate the data from these tables + replicate these views + tables but this also was not working - I suspect that because the pk was still the id.

I thought that the article option "action if name is in use" determines that there can be only one subscription to the same object at a time since values for this option are only "delete object" or "truncate the data". There is also an option "keep existing object unchanged" but with this one it wasn't working for me. What options are you using in your replication? What type of replication are you using?

What information do you need to be able to help me with this issue?

Now I finally manage to make it work but I had to change the pk for the source tables by adding db_id column to it and I also changed "action if name is in use" option to "delete data. If article has a row filter, delete only data that matches the filter" + added filter for the db_id field. However this solution seams little bit too complicated to me.

Is this filter setting required to make it work? If not then what should be correct setting?

BR,

Dzordz

Mike_D Hall of Fame Points: 3295 More actions · Answer 3

The “action if name in use” option controls the behaviour of the snapshot agent. In my situation (transactional push subscriptions) I’m lucky because my publishers are simply logging servers situated around the world. The data is only queried at the subscriber so I am simply using replication as a means of transporting data to a central database. Thus, I’m not performing any snapshots.

The option you have chosen causes the snapshot agent to generate delete statements based on the primary key which is why your data from the second database table was disappearing. As you have concluded, adding the db_id to your primary key has solved that problem so what you have done is correct.

Having not done a lot with snapshots, I’m not an expert. So what would be interesting to know whether or not the row filter you have put on is actually required. Prior to SQL Server 2005 delete statements generated by the snapshot agent included by a “where” clause on the primary key. I’m sure that’s still the case with SQL Server 2005. So, if you have the time, take off the row filter and see what happens.

Another way around this, and this may not be a possible solution for you, is to ditch the db_id and id columns which currently form your primary key and replace them with a column of datatype “uniqueidentifier” and make that the primary key. Again, don’t create a row filter and see what happens.

Of course, your current solution makes it easy to view the subscriber’s table and see which of your publishers the data has come from. That’s often a bonus. I suppose another option is to have the uniqueidentifier and the db_id columns. It all depends on your environment.

Hope that helps.

Mike