April 3, 2003 at 1:27 pm
Hi to all:
All my subscribers are suscribed to all articles in my publication. But sometimes happens that some inserts in a table are for only one subscribers, not all.
Of course, the change it is pulled to all my subscribers instead of the one that I need.
I managed this by using for that table a custom replication stored procedure, that only inserts the record in the subscriber that the insert was for.
But this check, it is made at the subscriber and the insert replicates to all subscriber, event it actually inserts or not. My question...Is it possible to filter somehow the publication and the publisher (Locally, without riching the subscriber), to avoid sending all my subscribers some inserts?
thanks to all
April 3, 2003 at 6:02 pm
Look up sp_articlefilter and sp_articleview in BOL......
April 4, 2003 at 5:36 am
I don't need to filter the article's records, only the subscribers.
April 4, 2003 at 6:48 am
I think that if you need that, you need merge, has a richer set of filtering options. Letting the inserts go through and dealing with them on the subscriber isn't the worst plan in the world, though I'll grant not very elegant.
Andy
April 4, 2003 at 7:03 am
Yes I know. Like it is now it is working fine. But in fact the articles that would need to filter are almost 10, and now I have 74 subscribers, but the company is especting to have almost 300 all over the country.
I was thinking of adding a view of the table for every subscriber, and then publish the view. But that would mean a lot of views.
Anyone is the only way I can think of right now, and I will test it to see if it works, and If I see any improves.
April 4, 2003 at 7:53 am
If you use the filter options I referred to and used push replication, the filter would be applied at the publisher. Also, a good approach if you do not intend for all subscribers to use it would to be to use multiple publications, one filtered and one not, etc....
Your origional question "My question...Is it possible to filter somehow the publication and the publisher (Locally, without riching the subscriber), to avoid sending all my subscribers some inserts?" is answered by this method.
You state "I was thinking of adding a view of the table for every subscriber, and then publish the view. But that would mean a lot of views." That's exactly what the sp_articleview provides, if done correctly, but without the management of all the views....
Why do you believe that this wouldn't work for you? Maybe I'll learn something....
Edited by - scorpion_66 on 04/04/2003 07:53:27 AM
April 4, 2003 at 8:10 am
OK, maybe I din't tell my situation. The subcribers pull the subscription that I need to filter. So I subscribe them to my central publication and every subscriber manage the time to pull the transactions.
But even I could use a push subscription I don't understand how I could create a filter in the article.
The thing is like this. There is a column that it says to whom the article is applied to. If it say All, must be deliverd to all of my subscribers, and if it says the server code, then it must apply to only that server.
I can't create a filter on that, or I don't understand how. If I could create multiple articles from the same table i would be able to create differents filter for the article, and let the subscribers to subscribe to only the article that they need.
I think that this could be acchieve creating as many views and subscriber I had. And each view, will have only the filtered data. For example, ViewServer58 will have the data for Server 58. And every subscriber will subscribe to the corresponding view with all the rest of the articles of course.
If I sound rude before, I didn't meant to and I apologize.
April 4, 2003 at 7:15 pm
You were not rude, and I certianly didn't take it that way. I honestly wanted to know why it wouldn't work for you, as I felt I might learn somrthing. No matter how long you work with something, there will always be a different viewpoint, so I ask.....braving the flames and all....but you will never understand if you do not seek out the answer....
No-one knows it all, and I'm certianly part of no-one, so my question was in earnest. thank you much for explaining. If your not using push replication, and cannot change over to it, then your correct, it will not be aplicable to your situation. I wish I had a better answer for you. You can have multiple publications from a single table, but in order to partition your data it should be done from the publisher, not the subscriber. Please post your end solution as it would be very useful to the rest of us....
Edited by - scorpion_66 on 04/04/2003 7:15:58 PM
April 7, 2003 at 6:46 am
Ok, when I have time, I will try creating the views. I don't know if it is the best solution, so I will have to test it. Is has some probems, like when I have to add a new subscriber to the publication, I will have to re create it and reinitialize all subscriptions. But I will try and see...
April 7, 2003 at 7:41 am
If you replicate a view, SQL Server only replicates the definition. If you create an indexed view, SQL Server will replicate the data like you describe.
I think your best bet is to create a new publication with a filter for each subscriber. The real cost here is that you will have 300 distribution agents running off your box. Make sure to size your memory correctly, i.e. leave enough for the agents to run efficiently.
April 7, 2003 at 8:12 am
Of course my views are indexed to replicate the data in them. The problem about creating new publications in that I already have 3 publications created in my republisher topology. I made 3 to separate and divide the times in wich every subscriber replicate, because the distributor agent are running from my master server in differents schedulles. And I don't have more spaces of time for new publications. Any way let me see if my idea is better and brings me any benefits and then I will tell.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply