|
|
You're in the
sql server replication
group:Replication Performance -- Many publishers, 1 subscriber
sql server replication:
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 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. [quoted text, click to view] "Mike Von Stein" <mvonstein@yahoo.com> wrote in message news:c92d1754.0405070936.fcab9ad@posting.google.com... > 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 [quoted text, click to view] "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,
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 [quoted text, click to view] mvonstein@yahoo.com (Mike Von Stein) wrote in message news:<c92d1754.0405070936.fcab9ad@posting.google.com>...
> 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. >
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. [quoted text, click to view] "Mike Von Stein" <mvonstein@yahoo.com> wrote in message news:c92d1754.0405080525.16417da3@posting.google.com... > 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.com>... > > 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,
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 [quoted text, click to view] "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,
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 [quoted text, click to view] "Hilary Cotter" <hilaryk@att.net> wrote in message news:<O1Ly#gXNEHA.2500@TK2MSFTNGP12.phx.gbl>...
> 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.com... > > 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.com>... > > > 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. > > >
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 which 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 talkin about Merge, but maybe I should look at reversing the terminology lik Paul in this thread suggested. Then maybe I could use transactiona with MSDE One concern, though, is the whole reason we are doing this is t 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, thi would be more like a 99.9% constant connection... So not sure what t think about that.. Thanks Mik [quoted text, click to view] "Hilary Cotter" <hilaryk@att.net> wrote in message news:<O1Ly#gXNEHA.2500@TK2MSFTNGP12.phx.gbl>..
> I meant "selected" in the sense of "chosen" >> MSDE can be a subscriber to a transactional publication, or a publisher fo > merge or snapshot publications. On further reflection I guess this is wh > you have selected merge >> I have worked on a topology that used over 60 merge subscribers to a singl > publisher, so it is highly scalable >> I have heard about people using merge with over 100 subscribers with no rea > problems >> Achieving real time replication with merge is a problem. Set you > pollinginterval to something low - perhaps 10s or so >> "Mike Von Stein" <mvonstein@yahoo.com> wrote in messag > news:c92d1754.0405080525.16417da3@posting.google.com.. >> Hilary >>>> Thanks for the info. Not sure exactly what you mean by "selecte >> merge"? Like horizontal selection? You're right. Actually, I don' >> need bi-directional replication in this case but, to my knowledge >> MSDE (which is running on the client) doesn't support transactiona >> and I thought snapshot would be inefficient >>>> Thanks again >> Mik >>>>>> mvonstein@yahoo.com (Mike Von Stein) wrote in messag > news:<c92d1754.0405070936.fcab9ad@posting.google.com>.. >>> I have a situation where I need about 50 client machines that ar >>> collecting data to synchronize with a network server on our local 10 >>> Mbps LAN. Am running SQL Server Enterprise 2000 on the back end >>>>>> Have experimented with using MSDE on the client machines and usin >>> merge replication with a push subscription to the server. Everythin >>> 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 5 >>> machines >>>>>> - Locking implications. Since this is an automated process, Th >>> ability to have real-time inserts needs to be always availabl >>> Typically happens 1 - 2 times per minute. (Updates can be handle >>> through standard error handling since a human operator is involved. >>>>>> - As time goes by and table sizes increase would this slow down, eve >>> if the primary activity on the table is the addition of records >>>>>> Thanks in advance >>>>>> Mik
Mike,
depends what type of schema changes - if it's adding/removing columns then it can be done using the usual setup without reinitializing (sp_repladdcolumn, sp_repldropcolumn). If you require a more radical change, then you can drop and readd the subscription from the publisher. HTH, Paul Ibison
Thanks, Paul. You've been very helpful.
Mike [quoted text, click to view] "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message news:<OIfzjErNEHA.1956@TK2MSFTNGP10.phx.gbl>...
> Mike, > depends what type of schema changes - if it's adding/removing columns then > it can be done using the usual setup without reinitializing > (sp_repladdcolumn, sp_repldropcolumn). If you require a more radical change, > then you can drop and readd the subscription from the publisher. > HTH,
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |