Replication Performance -- Many publishers, 1 subscriber

I have a situation where I need about 50 client machines that are
collecting data to synchronize with a network server on our local 100
Mbps LAN. Am running SQL Server Enterprise 2000 on the back end.
Have experimented with using MSDE on the client machines and using
merge replication with a push subscription to the server. Everything
seems to work good on a single client, but I'm wondering:
- How does this scale? Is it realistic to think I can do this with 50
machines?
- Locking implications. Since this is an automated process, The
ability to have real-time inserts needs to be always available
Typically happens 1 - 2 times per minute. (Updates can be handled
through standard error handling since a human operator is involved.)
- As time goes by and table sizes increase would this slow down, even
if the primary activity on the table is the addition of records?
Thanks in advance.
Mike
Mike,
I would say it is preferable to have a single publisher on the main server
and multiple push subscribers for this topology.
I'm not sure what your separation of inserts and updates is for - could you
explain a little further.
Inserts shouldn't cause any locking contention. For contention of resources,
you could stagger the synchronization, and limit the number of concurrent
merge processes.
Increasing table size for inserts should be no problem. You'll have a
gradual increase in metadata, which will be removed automatically though.
HTH,
Paul Ibison
|||do you need bi-directional replication? From what you describe you have
selected merge, where if you don't need bi-directional replication you can
get away with transactional which will offer better performance.
"Mike Von Stein" <mvonstein@.yahoo.com> wrote in message
news:c92d1754.0405070936.fcab9ad@.posting.google.co m...
> I have a situation where I need about 50 client machines that are
> collecting data to synchronize with a network server on our local 100
> Mbps LAN. Am running SQL Server Enterprise 2000 on the back end.
> Have experimented with using MSDE on the client machines and using
> merge replication with a push subscription to the server. Everything
> seems to work good on a single client, but I'm wondering:
> - How does this scale? Is it realistic to think I can do this with 50
> machines?
> - Locking implications. Since this is an automated process, The
> ability to have real-time inserts needs to be always available
> Typically happens 1 - 2 times per minute. (Updates can be handled
> through standard error handling since a human operator is involved.)
> - As time goes by and table sizes increase would this slow down, even
> if the primary activity on the table is the addition of records?
> Thanks in advance.
> Mike
|||Paul-
Thanks for the info. It was helpful. The reason I was pursuing this
topology is because the clients are the ones collecting the data.
Which need to be warehoused in a central place. (There is also
static setup data that is coming one way from the server, which I'm
doing exactly what you describe.)
The inserts and updates separation issue is because some of the data
comes automatically from the machine while another, different type of
data, data comes from the operator. It's stored in separate tables in
MSDE. The person's data can be corrected by the person to fix
mistakes. The machine data "never makes mistakes" (or at least it
doesn't know it does) so it's only an insert operation.
Thanks again,
Mike
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<#vLI3YGNEHA.1616@.TK2MSFTNGP12.phx.gbl>...
> Mike,
> I would say it is preferable to have a single publisher on the main server
> and multiple push subscribers for this topology.
> I'm not sure what your separation of inserts and updates is for - could you
> explain a little further.
> Inserts shouldn't cause any locking contention. For contention of resources,
> you could stagger the synchronization, and limit the number of concurrent
> merge processes.
> Increasing table size for inserts should be no problem. You'll have a
> gradual increase in metadata, which will be removed automatically though.
> HTH,
> Paul Ibison
|||Hilary-
Thanks for the info. Not sure exactly what you mean by "selected
merge"? Like horizontal selection? You're right. Actually, I don't
need bi-directional replication in this case but, to my knowledge,
MSDE (which is running on the client) doesn't support transactional
and I thought snapshot would be inefficient.
Thanks again,
Mike
mvonstein@.yahoo.com (Mike Von Stein) wrote in message news:<c92d1754.0405070936.fcab9ad@.posting.google.c om>...
> I have a situation where I need about 50 client machines that are
> collecting data to synchronize with a network server on our local 100
> Mbps LAN. Am running SQL Server Enterprise 2000 on the back end.
> Have experimented with using MSDE on the client machines and using
> merge replication with a push subscription to the server. Everything
> seems to work good on a single client, but I'm wondering:
> - How does this scale? Is it realistic to think I can do this with 50
> machines?
> - Locking implications. Since this is an automated process, The
> ability to have real-time inserts needs to be always available
> Typically happens 1 - 2 times per minute. (Updates can be handled
> through standard error handling since a human operator is involved.)
> - As time goes by and table sizes increase would this slow down, even
> if the primary activity on the table is the addition of records?
> Thanks in advance.
> Mike
|||I meant "selected" in the sense of "chosen".
MSDE can be a subscriber to a transactional publication, or a publisher for
merge or snapshot publications. On further reflection I guess this is why
you have selected merge.
I have worked on a topology that used over 60 merge subscribers to a single
publisher, so it is highly scalable.
I have heard about people using merge with over 100 subscribers with no real
problems.
Achieving real time replication with merge is a problem. Set your
pollinginterval to something low - perhaps 10s or so.
"Mike Von Stein" <mvonstein@.yahoo.com> wrote in message
news:c92d1754.0405080525.16417da3@.posting.google.c om...
> Hilary-
> Thanks for the info. Not sure exactly what you mean by "selected
> merge"? Like horizontal selection? You're right. Actually, I don't
> need bi-directional replication in this case but, to my knowledge,
> MSDE (which is running on the client) doesn't support transactional
> and I thought snapshot would be inefficient.
> Thanks again,
> Mike
>
> mvonstein@.yahoo.com (Mike Von Stein) wrote in message
news:<c92d1754.0405070936.fcab9ad@.posting.google.c om>...[vbcol=seagreen]
|||Mike,
as you know either way works, but I'd still choose to use use a central
publisher and multiple subscribers, as the metadata and alerts etc are then
in a central place and maintenance is simpler. For merge, the distinction
between publisher and subscriber is not too relevant if you don't have
conflicts.
Cheers,
Paul
|||Paul-
Okay, I guess I was thinking since the clients would be the primary
information generators, they would be the "Publishers", but I guess
the names shouldn't be taken literally in this case. I think I see
the benefits you are suggesting.
One question though. How does this impact future schema changes?
Would you have to drop all subscriptions remotely at the clients and
recreate? (That's a maintenance concern, since there are so many.)
We're kind of in a "formative" phase here and I'm expecting to have to
do some tweaks...
Thanks,
Mike
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<#of5O$cNEHA.3328@.TK2MSFTNGP10.phx.gbl>...
> Mike,
> as you know either way works, but I'd still choose to use use a central
> publisher and multiple subscribers, as the metadata and alerts etc are then
> in a central place and maintenance is simpler. For merge, the distinction
> between publisher and subscriber is not too relevant if you don't have
> conflicts.
> Cheers,
> Paul
|||Hilary-
The performance info is interesting. Yes, that is why I was talking
about Merge, but maybe I should look at reversing the terminology like
Paul in this thread suggested. Then maybe I could use transactional
with MSDE?
One concern, though, is the whole reason we are doing this is to
buffer the system to handle things such as planned/unplanned,
server/network downtime, etc. (The clients have to be available 24/7,
but not necessarily the server, if that makes sense...) Read in BOL,
that transactional was for a constant connection. In general, this
would be more like a 99.9% constant connection... So not sure what to
think about that...
Thanks,
Mike
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:<O1Ly#gXNEHA.2500@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> I meant "selected" in the sense of "chosen".
> MSDE can be a subscriber to a transactional publication, or a publisher for
> merge or snapshot publications. On further reflection I guess this is why
> you have selected merge.
> I have worked on a topology that used over 60 merge subscribers to a single
> publisher, so it is highly scalable.
> I have heard about people using merge with over 100 subscribers with no real
> problems.
> Achieving real time replication with merge is a problem. Set your
> pollinginterval to something low - perhaps 10s or so.
> "Mike Von Stein" <mvonstein@.yahoo.com> wrote in message
> news:c92d1754.0405080525.16417da3@.posting.google.c om...
> news:<c92d1754.0405070936.fcab9ad@.posting.google.c om>...
|||Transaction does not support bi-directional replication through the wizards. You can configure it for bi-directional replication using the replication stored procedures, or you could use queued replication (but it is tuned for less than 10 subscribers whi
ch rules it out in this case) or immediately updating susbcribers (only if your subscribers/publisher are always well connected).
Transactional does support the subscribers going offline, however you are updating the subscribers, so your best option in this case is merge.
Merge also supports having the publisher and susbcribers going offline.
The caveat in this is the more frequently your publisher/subscribers goes off line the greater the chance of having conflicts.
-- Mike Von Stein wrote: --
Hilary-
The performance info is interesting. Yes, that is why I was talking
about Merge, but maybe I should look at reversing the terminology like
Paul in this thread suggested. Then maybe I could use transactional
with MSDE?
One concern, though, is the whole reason we are doing this is to
buffer the system to handle things such as planned/unplanned,
server/network downtime, etc. (The clients have to be available 24/7,
but not necessarily the server, if that makes sense...) Read in BOL,
that transactional was for a constant connection. In general, this
would be more like a 99.9% constant connection... So not sure what to
think about that...
Thanks,
Mike
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:<O1Ly#gXNEHA.2500@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> I meant "selected" in the sense of "chosen".
> merge or snapshot publications. On further reflection I guess this is why
> you have selected merge.
> publisher, so it is highly scalable.
> problems.
> pollinginterval to something low - perhaps 10s or so.
> news:c92d1754.0405080525.16417da3@.posting.google.c om...
> news:<c92d1754.0405070936.fcab9ad@.posting.google.c om>...

Replication over WAN setup

Hello, I'm newbie in replication field. At the moment, I am able to setup replication on LAN (1 distributor to few subcribers). However, I found some difficulties on setting-up the replication on WAN. Is there any guidelines, setup documents or examples (like print screen) for me to refer to ? Hope able to get any assistance at here.

Best Regards,

Hans

Hello Hans,

What kind of difficulties did you see? Can you list them?

Thanks.

|||

are you replicating between two different domains? If so, please reference this support article: http://support.microsoft.com/kb/321822/en-us.

If not, please give us some more info, such as complete error number and error message.

|||

First define if the problem is the network connection (routing, firewall, e.t.c.)

Then if it is a security problem (different domains without trust, accounts with improper rights, wrong passwords, e.t.c.)

Lastly if it is a SQL config problem (e.g. are you running the SQL service with the SA account?)

Can you give more details?

|||

Sorry for the late reply guys, I guess I just figure out my problem. For WAN setup, wat I need is have to do some alias mapping for the server that I wanna connect (subscriber or publisher server). Once did the mapping , replication is able to run. :) Thx for the advice by the way.

Best Regards,

Hans

Replication over VPN

I think that the configuration for replication via VPN is the same as that
used on a LAN. But I can not register remote SQLserver by name and it does
not work by IP.
1. Does it work in case dynamic IP ?
2. Use alias name instead of IP address ?
3. Other recomendations .
Please help.
Thank you.
a vpn should be the same as a local lan. It will work by dynamic ip if it
can resolve the subscriber by host name. However, it sounds like you can't
do this. You need to work on the name resolution issue. It could be a
network connection problem. Tracert should help you to figure out where the
break is.
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
"Thang Long" <Thang Long@.discussions.microsoft.com> wrote in message
news:0F60A7B1-DF73-41F3-AF6F-9D3B64A3780E@.microsoft.com...
> I think that the configuration for replication via VPN is the same as that
> used on a LAN. But I can not register remote SQLserver by name and it does
> not work by IP.
> 1. Does it work in case dynamic IP ?
> 2. Use alias name instead of IP address ?
> 3. Other recomendations .
> Please help.
> Thank you.
>
>
|||Thank you for your reply.
The remote server named SERVER. Does it break naming rules ?
Many thanks.
Thang Long
"Hilary Cotter" wrote:

> a vpn should be the same as a local lan. It will work by dynamic ip if it
> can resolve the subscriber by host name. However, it sounds like you can't
> do this. You need to work on the name resolution issue. It could be a
> network connection problem. Tracert should help you to figure out where the
> break is.
> --
> 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
> "Thang Long" <Thang Long@.discussions.microsoft.com> wrote in message
> news:0F60A7B1-DF73-41F3-AF6F-9D3B64A3780E@.microsoft.com...
>
>
|||it might, I would try a fully qualified domain name in the alias section.
Also make sure there are no entries for it in
%windir%\system32\drivers\etc\hosts
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
"Thang Long" <ThangLong@.discussions.microsoft.com> wrote in message
news:61B0C5A5-B8AD-4003-A1E4-4EF2B64FA6C9@.microsoft.com...[vbcol=seagreen]
> Thank you for your reply.
> The remote server named SERVER. Does it break naming rules ?
> Many thanks.
> Thang Long
> "Hilary Cotter" wrote:
it[vbcol=seagreen]
can't[vbcol=seagreen]
the[vbcol=seagreen]
that[vbcol=seagreen]
does[vbcol=seagreen]
|||Doesn't break rules, try to the following.
ping SERVER
If you see the IP address in the lines follwoing this request and it's the
correct IP the DNS is funcitoning properly.
If the IP address is wrong or no IP address is given you may need the
assistance of the network administrator. I've seen it where a new remote
client VPN tunnel had an access-list misconfigured that then wouldn't let it
pull DHCP/DNS information accross the tunnel.
HTH,
GTM
"Thang Long" <ThangLong@.discussions.microsoft.com> wrote in message
news:61B0C5A5-B8AD-4003-A1E4-4EF2B64FA6C9@.microsoft.com...[vbcol=seagreen]
> Thank you for your reply.
> The remote server named SERVER. Does it break naming rules ?
> Many thanks.
> Thang Long
> "Hilary Cotter" wrote:
|||Where could I find step-by-step guide to setup sql replication between
non-trusted domains.
Thank you.
"Hilary Cotter" wrote:

> it might, I would try a fully qualified domain name in the alias section.
> Also make sure there are no entries for it in
> %windir%\system32\drivers\etc\hosts
> --
> 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
> "Thang Long" <ThangLong@.discussions.microsoft.com> wrote in message
> news:61B0C5A5-B8AD-4003-A1E4-4EF2B64FA6C9@.microsoft.com...
> it
> can't
> the
> that
> does
>
>
|||Paul, thank you. I try replication using VPN, but your article outlines what
to if a VPN is not available. Please tell me other articles.
Rgds,
"Paul Ibison" wrote:

> Thang,
> please take a look at this article:
> http://www.replicationanswers.com/InternetArticle.asp
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||If can not resolve the subscriber by host name. Could I use client network
utility to create alias name and cofigure replication ?
Thank you.
"Greg" wrote:

> Doesn't break rules, try to the following.
> ping SERVER
> If you see the IP address in the lines follwoing this request and it's the
> correct IP the DNS is funcitoning properly.
> If the IP address is wrong or no IP address is given you may need the
> assistance of the network administrator. I've seen it where a new remote
> client VPN tunnel had an access-list misconfigured that then wouldn't let it
> pull DHCP/DNS information accross the tunnel.
> HTH,
> GTM
>
> "Thang Long" <ThangLong@.discussions.microsoft.com> wrote in message
> news:61B0C5A5-B8AD-4003-A1E4-4EF2B64FA6C9@.microsoft.com...
>
>
|||Thang,
sorry - your question related to 'non-trusted domains' so I supposed you
were trying an alternative to the VPN setup. I don't know of any specialized
VPN articles.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Yes, use client network utility to create alias name and specify
alternate snapshot folder using IP adress.
This worked for me,
Adrian

Replication in LAN

Can Replication works in share network, I mean if i make a client as
publisher and server as distributer and subscriber willbe on remote location,
then will it work? if yes then how will i define the subscriber?
Manish,
if the networks are trusted then it is very much as per usual. if not, then
this article should help:
http://www.replicationanswers.com/InternetArticle.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)