October 22, 2008 at 12:19 am
Hi All,
I have created a transactional replication with publisher in server1 and subscriber in server2. My requirement is to create 1 publisher and multiple subscribers for that publisher. Assume that there is one Sales Office-Regional and multiple sales office at different locations. So, i resembled this with 1 publisher and multiple subsribers. Now after the publisher and subscriber , i have created the snapshot as welll.
Once i initialize the subscription i would like to insert only data which belongs to the sales_org_Id already defined i the subscriber. I wouldnt like to insert data that doesnt belong to that sales org id. HOW DO I DO THIS?. Because during initialization only BCP commands are fired and im not able to filter based on the sales org id. Could anybody help me on this issue?
Chrs
Balaji
October 22, 2008 at 9:06 am
I seem to remember there is a filter option you can use when you create your publication. I did you use this?
Matt
October 22, 2008 at 9:10 am
yes when you are selecting tables in the replication plan it asks you if you want to setup a filter
October 22, 2008 at 5:02 pm
Thanks for the help, but if i put a filter in the publication(source DB), i would be able to take data only for one of the subscribers right. That is, if i place the filter of a particular sales org id then i would not be able to intialize all the subscribers with their respective data. Because a filter in publication will be pertaining to only 1 sales Org Id.Could you pls tell me is there any other way?
October 22, 2008 at 5:04 pm
Thanks for the help, but if i put a filter in the publication(source DB), i would be able to take data only for one of the subscribers right. That is, if i place the filter of a particular sales org id then i would not be able to intialize all the subscribers with their respective sales org id. Because a filter in publication will be pertaining to only 1 sales Org Id.Could you pls tell me is there any other way?
October 23, 2008 at 8:44 am
Horizontal filters (row level filtering) are applied to an article at the publication level; you cannot apply a horizontal filter on a subscriber-by-subscriber basis.
You have two options:
1) Set up multiple publications where each publication has a filter that is specifc to the subscriber you will push the data to.
2) Stick with the single publication, no filtering, and put instead-of triggers on each subscriber which take care of inserting the data into the table based on whatever criteria you put into the trigger. To initially populate each subscriber l'd either use a data synchronization tool do not initialize them via replication, or you could apply the snapshot and delete the data you don't care about.
I'd go with option #1 - it's easier for someone new to come in an understand what's going on and cleaner if you ever have to resynchronize a subscriber.
October 23, 2008 at 8:51 am
Have to agree with Kendal on option 1 as this will also keep the amount of data replicated down to that which is truly pertinent for the subscriber. Saves on bandwidth. Always a good thing and you can stay friends with the network folks and the financial people. 🙂
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply