An architecture question on replication

I have a large central server. I need to replicate the data to around 50
smaller sqlserver at various locations. The subscription is decided by a join
between two tables and the where clause is a specific location_id. The larger
table in central DB is around 200 million rows and the local DB is 200/50= 4
million rows/local DB.
Since i need to do a join two tables, i think i cannot use transaction
replication, need to go with merge replication. What i found is when i create
multiple publication(right now just 2) and all merge agents running on
publisher, it takes out too much resource. So i am worried about going with
an approach of 50 publications .
I tried the option of running merge agents at subscriber, a single
publication differentiate by HOST_NAME. I couldn't make it work.
Please give your thoughts, what could be my best approach for this problem.
(or i could go a .net solution using msmq or SSB)
You can do a join as part of a filter in transactional replication. Please
provide more info about the join and I can write a udf to do it.
For merge replication you need to limit the number of concurrent merge
agents, and possibly use a hierarchy.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"skv" <skv@.discussions.microsoft.com> wrote in message
news:89ADE585-E17C-4C9C-9A5A-6A49305AA398@.microsoft.com...
>I have a large central server. I need to replicate the data to around 50
> smaller sqlserver at various locations. The subscription is decided by a
> join
> between two tables and the where clause is a specific location_id. The
> larger
> table in central DB is around 200 million rows and the local DB is 200/50=
> 4
> million rows/local DB.
> Since i need to do a join two tables, i think i cannot use transaction
> replication, need to go with merge replication. What i found is when i
> create
> multiple publication(right now just 2) and all merge agents running on
> publisher, it takes out too much resource. So i am worried about going
> with
> an approach of 50 publications .
> I tried the option of running merge agents at subscriber, a single
> publication differentiate by HOST_NAME. I couldn't make it work.
> Please give your thoughts, what could be my best approach for this
> problem.
> (or i could go a .net solution using msmq or SSB)
>
|||Dynamic filtering is now pretty straightforward in SQL 2005. Please see this
article for some relevant info and screenshots:
http://www.replicationanswers.com/MergeChangesArticle1.asp
To minimise proc congestion, you could have the merge agents run on the
subscribers (pull), stagger the sync times and as Hilary mentions limit the
number of concurrent merge agents.
HTH,
Paul Ibison
|||query1
select *
from customer
where base_loc=1
query2
select B.*
from customer A,customer_preference B
where A.customer_id=B.customer_id
and A.base_loc=1
this select will return 4 million and customer_preference table will have
200 million rows . I need to create 50 transactional replication/or merge
replication to replicate to 50 locations. What are your thoughts about
performance.
Thanks
"Hilary Cotter" wrote:

> You can do a join as part of a filter in transactional replication. Please
> provide more info about the join and I can write a udf to do it.
> For merge replication you need to limit the number of concurrent merge
> agents, and possibly use a hierarchy.
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "skv" <skv@.discussions.microsoft.com> wrote in message
> news:89ADE585-E17C-4C9C-9A5A-6A49305AA398@.microsoft.com...
>
>
|||Given that you have a large number of records and a largish number of
subscribers I'd create 50 publications each with a static filter. Dynamic
filters are not as optimised and this route could end up being slow. 50
publications is not unmanageable and the processing could be offloaded by
having the subscriptions all set up as pull ones.
HTH,
Paul Ibison
|||So you are recommending me a merge replication with the merge agent running
on subscriber.
What are the advantages of merge over transactional replication for my
scenario. (still i have not figured out how to create a join for
transactional replication)
Thanks
"Paul Ibison" wrote:

> Given that you have a large number of records and a largish number of
> subscribers I'd create 50 publications each with a static filter. Dynamic
> filters are not as optimised and this route could end up being slow. 50
> publications is not unmanageable and the processing could be offloaded by
> having the subscriptions all set up as pull ones.
> HTH,
> Paul Ibison
>
|||I'm assuming that you have updating subscribers. For such a large number of
updating subscribers, merge is the preference. There are other advantages
over queued updating subscribers inc it handles updates to BLOBS, has more
conflict resolvers.
HTH,
Paul Ibison
|||thanks for the reply. i am surprised to see insert/update/delete triggers on
the table for merge replication. This can cause performance issue.
Can we use transactional replication with joins, which doesn't use triggers.
Your thoughts.
Thanks
"Paul Ibison" wrote:

> I'm assuming that you have updating subscribers. For such a large number of
> updating subscribers, merge is the preference. There are other advantages
> over queued updating subscribers inc it handles updates to BLOBS, has more
> conflict resolvers.
> HTH,
> Paul Ibison
|||But we are designing the sytem such a way that subscriber never will change
the data, so we can avoid merges. I like to see less overhead on publisher,
because it needs to handle 50 publication. Your thoughts.
Thanks
skv
"Paul Ibison" wrote:

> For either method (merge or transactional with updating subscribers) there
> will be an overhead at the subscriber end. Can I just confirm that the
> subscribers will be editing data?
> Cheers,
> Paul Ibison
>
>
|||OK - as far as I can tell from your explanations, you have 50 subscribers
which don't update data and which each have a different subset of data. In
this case I'd set up 50 Transactional publications, each having one
subscriber and each defined to take a different set of data.
HTH,
Paul Ibison