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

Alternative to Transactional Replication

I have a customer who has about 80 SQL 2005 servers in remote offices
with various WAN links. The distribution of the SQL servers are as
follows:
- 15 site servers (publishers) must update to a single regional server
(central subscriber), there are about 5 regional servers, each with
their own set of publishers, for a total of about 75 publishers and 5
subscribers.
- There is a single SQL server in central HQ which must get updates
from all of the regional servers, for a complete picture of all the
data from the site servers.
- The amount of updates at each site are minimal and only occur once
or twice per day. However, when the update does occur, it should be
pushed out with minimal delay to the regional server as well as the
central HQ server.
- The data at the regional and HQ locations must be available at all
times for reporting purposes. No updates will be performed at the
regional or HQ locations.
- SQL Transactional Replication in a central subscriber model has been
investigated as a possible solution, but has been eliminated as a
possibility at this time for various reasons.
What am considering is a solution similar to the following.
Create a Trigger (http://msdn2.microsoft.com/en-us/library/
ms189799.aspx) that automatically updates a linked server upon update
of a table or view at the site server. Each site office would have a
linked server back to the SQL server in the regional office as well as
the SQL server in the central HQ office. This would ensure that each
time the data is updated, it is automatically updated on the remote
servers as well.
Any thoughts on my solution? Will it work?
Thanks for your input.
triggers work abysmally over a network, let alone a wan.
Hilary Cotter
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
"daveberm" <david.bermingham@.steeleye.com> wrote in message
news:1172767518.597593.64730@.8g2000cwh.googlegroup s.com...
>I have a customer who has about 80 SQL 2005 servers in remote offices
> with various WAN links. The distribution of the SQL servers are as
> follows:
> - 15 site servers (publishers) must update to a single regional server
> (central subscriber), there are about 5 regional servers, each with
> their own set of publishers, for a total of about 75 publishers and 5
> subscribers.
> - There is a single SQL server in central HQ which must get updates
> from all of the regional servers, for a complete picture of all the
> data from the site servers.
> - The amount of updates at each site are minimal and only occur once
> or twice per day. However, when the update does occur, it should be
> pushed out with minimal delay to the regional server as well as the
> central HQ server.
> - The data at the regional and HQ locations must be available at all
> times for reporting purposes. No updates will be performed at the
> regional or HQ locations.
> - SQL Transactional Replication in a central subscriber model has been
> investigated as a possible solution, but has been eliminated as a
> possibility at this time for various reasons.
> What am considering is a solution similar to the following.
> Create a Trigger (http://msdn2.microsoft.com/en-us/library/
> ms189799.aspx) that automatically updates a linked server upon update
> of a table or view at the site server. Each site office would have a
> linked server back to the SQL server in the regional office as well as
> the SQL server in the central HQ office. This would ensure that each
> time the data is updated, it is automatically updated on the remote
> servers as well.
> Any thoughts on my solution? Will it work?
> Thanks for your input.
>
|||On Mar 2, 9:59 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> triggers work abysmally over a network, let alone a wan.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "daveberm" <david.berming...@.steeleye.com> wrote in message
> news:1172767518.597593.64730@.8g2000cwh.googlegroup s.com...
>
>
>
>
>
>
> - Show quoted text -
Thanks Hilary. I'm in no way a Transact SQL guru, so please forgive
my ignorance. Here is the logic I was considering...
If a local table is updated, have a trigger that detects that update
and runs an INSERT query to update a linked table, which is on the
other side of a WAN.
So here are some more questions...
Is it the INSERT to the linked table that is abysmal? Assuming the
link is stable, is it just that it is slow or are there other issues?
I suppose any update to a linked table across a WAN is abysmal,
regardless of whether it is run by a trigger or by the application
itself, would you agree?
Does transactional replication address the performance issues of
updates over a WAN, or does it suffer from he same restraints?
What if it is just a very small transaction, say just an INSERT of a
single row that occures 2-3 times a day, do you still have
reservations about that kind of implementation over a WAN?
So given my situation, 15 sites with databases that need to update
small amounts of data 2-3 times a day to a master database (for
reporting purposes) across WAN links, what would you recommend?
And finally, are you available on a consulting basis? If so, I may
need your assistance if things get hairy.
David A. Bermingham, MCSE, MCSA:Messaging
Director of Product Management
www.steeleye.com
|||Hi Dave!
Basically the insert is wrapped in a transaction when you fire a trigger.
Any update done over a network incurs network hop which will add latency to
all transactions. For example there is a replication topology called
immediate updating where precisely what you are trying to do is implemented.
For a client of mine we found that transaction latency increased from 50 ms
(average) to 150 ms average. Over a WAN you can imagine the increase in
latency.
The real problem is that if there is any network interruption the calling
transaction (which could be the trigger) will hang for up to 20 s before
giving you an access denied error message and rolling back the trigger and
the precipitating trigger action.
So, triggers over the network simply aren't scalable and seriously degrade
performance.
HTH
Hilary Cotter
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
"daveberm" <david.bermingham@.steeleye.com> wrote in message
news:1172974417.463883.123990@.s48g2000cws.googlegr oups.com...
> On Mar 2, 9:59 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> Thanks Hilary. I'm in no way a Transact SQL guru, so please forgive
> my ignorance. Here is the logic I was considering...
> If a local table is updated, have a trigger that detects that update
> and runs an INSERT query to update a linked table, which is on the
> other side of a WAN.
> So here are some more questions...
> Is it the INSERT to the linked table that is abysmal? Assuming the
> link is stable, is it just that it is slow or are there other issues?
> I suppose any update to a linked table across a WAN is abysmal,
> regardless of whether it is run by a trigger or by the application
> itself, would you agree?
> Does transactional replication address the performance issues of
> updates over a WAN, or does it suffer from he same restraints?
> What if it is just a very small transaction, say just an INSERT of a
> single row that occures 2-3 times a day, do you still have
> reservations about that kind of implementation over a WAN?
> So given my situation, 15 sites with databases that need to update
> small amounts of data 2-3 times a day to a master database (for
> reporting purposes) across WAN links, what would you recommend?
> And finally, are you available on a consulting basis? If so, I may
> need your assistance if things get hairy.
> David A. Bermingham, MCSE, MCSA:Messaging
> Director of Product Management
> www.steeleye.com
>
|||On Mar 5, 8:59 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> Hi Dave!
> Basically the insert is wrapped in a transaction when you fire a trigger.
> Any update done over a network incurs network hop which will add latency to
> all transactions. For example there is a replication topology called
> immediate updating where precisely what you are trying to do is implemented.
> For a client of mine we found that transaction latency increased from 50 ms
> (average) to 150 ms average. Over a WAN you can imagine the increase in
> latency.
> The real problem is that if there is any network interruption the calling
> transaction (which could be the trigger) will hang for up to 20 s before
> giving you an access denied error message and rolling back the trigger and
> the precipitating trigger action.
> So, triggers over the network simply aren't scalable and seriously degrade
> performance.
> HTH
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "daveberm" <david.berming...@.steeleye.com> wrote in message
> news:1172974417.463883.123990@.s48g2000cws.googlegr oups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
Thanks Hilary.
How does Transactional Replication handle latency and instability of
WAN links? What happens to the replication if a link between
Publisger and Subscriber is down for a period of time? Is
Transactional Replication recommended when Publishers and Subscribers
are on the other side of WAN links?
In my case, data needs to be updated generally with 2-3 minutes of the
original update to the Publisher. Is Transaction Replication useful
in this case, considing WAN links are in place?
Thanks again for your insight.
Dave

Alternative to Microsoft?

Andreas,
there are axtiveX controls to control the execution of=20
the various agents. Have a look at this for more detail:
http://www.windowsitpro.com/Articles/Index.cfm?
ArticleID=3D39079&DisplayTab=3DArticle
As for general options, there are alternatives out there.=20
I recently looked at SQL DataCompare, which could be used=20
in a similar way. Some of these alternatives are also=20
significantly faster. However, consider that merge has a=20
huge amount of inbuilt versatility. Merge has automatic=20
range management, a rich set of conflict resolution=20
techniques and assign priorities, the option to=20
dynamically filter data and prefilter snapshots, the=20
ability to configure unidirectional replication (in=20
either direction), the ability to control the batch size.=20
Along with other replication methods, we have subscriber=20
timeouts and inbuild alerts. The list of extras is huge=20
and the price of this functionality is the increased=20
complexity behind the scenes.
is designed for high autonomy.
>--Original Message--
>I have to implement a simple Merge-Replication, using=20
the MSDE.=20
>That means, I can=B4t using Enterprice-Manager.=20
>I don=B4t want using SQLDMO because of redistribution-
problems.=20
>Using T-SQL, it is quite difficult to implement Merge-
Replication.=20
>Are the thrid-pary-products, ActiveX-Controls,=20
Libraries, assembly,
>etc. that manage SQL-Server-Replication very well?
>What do you think about writing your own merge-
replication tool? I
>just wrote a prototyp, and it seems to be very easy.
>pro:
> - no limitations on changing data strukturs while=20
publishing tables
>!!!
> - using a connection in ONE direction (no re-connect=20
from subscriber
>to distritubor necessary, ideal for replication into=20
internet !! )
> - high level of transparency, you can debug each step,=20
you can log
>each SQL-Statement
> - if you want to use a single-sided replication (like=20
transactional
>replication) the subscriber database need not implement=20
anything (no
>table, no trigger, nothing!!)
> - few overhead: just adding 3 triggers per table (like
>MS-merge-replication does) and 2 or 3 system-tables
> - very simple code to switch on replication (just=20
adding the 3
>triggers to each table, independent on data strukture)
>contra:
> - replication-process itself is not tested, each=20
possible event have
>to be implemented and well tested
>I would be happy if someone would like to diskuss this=20
with me, or if
>someone has just written a tool like this, or have=20
another good idea.
>Andreas Lauffer, Germany
>.
>
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<086e01c51813$bebd2800$a501280a@.phx.gbl>...

> there are axtiveX controls to control the execution of=20
> the various agents. Have a look at this for more detail:
> http://www.windowsitpro.com/Articles...yTab=3DArticle
OK, I tested these controls. There are not bad, but fare away from
beeing perfect. Sometimes the error messages arent detailed enough,
you dont know why replication doesnt work.
In addition, the controls covers only a part of the whole replication
process.

> As for general options, there are alternatives out there.=20
> I recently looked at SQL DataCompare, which could be used=20
> in a similar way. Some of these alternatives are also=20
> significantly faster.
SQL DataCompare looks very nice, but it seems so that it cannot merge
Datasets. You have to tell the tool, wheater to change the Database B
with the changes from A or vise versa.
Maybe there is a possibilty with C#, if you compare all the changes
from Database A and B and generate SQL-Scripts manually.

> However, consider that merge has a=20
> huge amount of inbuilt versatility. Merge has automatic=20
> range management, a rich set of conflict resolution=20
> techniques and assign priorities, the option to=20
> dynamically filter data and prefilter snapshots, the=20
> ability to configure unidirectional replication (in=20
> either direction), the ability to control the batch size.=20
> Along with other replication methods, we have subscriber=20
> timeouts and inbuild alerts. The list of extras is huge=20
> and the price of this functionality is the increased=20
> complexity behind the scenes.
I dont need range management, assign priorities, Data filters, etc.
Comflict management is even much much simpler and comfortable if you
implement this inside your code.
[vbcol=seagreen]
>
>
> is designed for high autonomy.
> the MSDE.=20
> problems.=20
> Replication.=20
> Libraries, assembly,
> replication tool? I
> publishing tables
> from subscriber
> internet !! )
> you can log
> transactional
> anything (no
> adding the 3
> possible event have
> with me, or if
> another good idea.