Re: replication latency
- From: "XNMB" <ChristianBautista@xxxxxxxxx>
- Date: 6 Sep 2006 03:24:34 -0700
Thanks for your input Hilary.
Your problem is that you should be using merge replication. Queued updating
does not scale well when you have a significant portion of your DML
occurring on the subscriber.
I dont think merge would be a good option since we need changes to be
applied to and from Publisher/Subscribers as close to real time as
possible. The setup is more of a server-to-server environment rather
than server-to-client. Also, given the sheer number of
transactions/commands executed/applied in any of the servers,
probability of conflict will be very high using merge.
I am also wondering why you have the mix of Queued and Immediate.
We need transactions committed as quickly as possible in Manila (Server
C). 2PC (used in immediate updates) does not meet this requirement.
This means applications would need to wait for the subscriber to
connect to the publisher before transactions are committed, defeating
the purpose of using a local DB server (to speed up performance).
Server B uses immediate since it's connected to the same local network
of Server A hence a 2PC doesn't have much impact in performance.
Updates are delivered to the publisher almost instantaneously, which is
not the case if Server C (Manila) were configured using immediate
updates.
Also are you sure you need bi-directional functionality?
Yes, of course. The crux of this setup is to distribute different sites
and applications among these 3 DB servers (in our effort to balance the
load). All data are required by all sites/applications running in any
of the servers, as close to real time as possible.
Any more suggestions?
Thanks you very much to everyone for your help.
xnmb
Hilary Cotter wrote:
Your problem is that you should be using merge replication. Queued updating
does not scale well when you have a significant portion of your DML
occurring on the subscriber. I am also wondering why you have the mix of
Queued and Immediate.
Also are you sure you need bi-directional functionality?
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"XNMB" <ChristianBautista@xxxxxxxxx> wrote in message
news:1157512485.658280.266700@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hello, the current setup is as follows:
Paris DB Server A [Publisher/Distributor/1Gbps LAN link/8Mbps WAN link]
Paris DB Server B [Subscriber/Same LAN as Server A/1Gbps LAN link]
Manila DB Server C [Subscriber/4Mbps WAN link (shared by entire company
~100-150 workstations)]
Server B has 2 Publications:
Publication FULL: Transactional/Immediate updating subscriptions
[Subscriber: Server B]
Publication MANILA:Transactional/Queued updating subscriptions
[Subscriber: Server C]
Scenario:
1) Server A is primary production DB. ~10 sites/applications using DB.
HEAVY on writes/reads
2) Server B is secondary production DB. ~2-5 sites/applications using
DB. 60% read/40% write
3) Server C is intended for use by Manila users. ~1-5
sites/applications
4) Server C is CURRENTLY UNUSED by any application. Only activity at
this time is receiving replicated commands from DIstributor (Server A)
5) No latency issues with Server A-->Server B and vice versa.
6) Here's what I typically get on peak hours [Publication
MANILA/Subscriber Server C]
Undistributed Commands Tab [Subscription Details]
# of commands in Distribution (undelivered to subscriber): 1744838
Estimated time to apply: 06:08:55
Tracer Tokens:
Publisher to Distributor: 00:00:05 (I think this is default
-PollingInterval value)
Distributor to Subscriber: Taking too long to display
6) I have tweaked and experimented with the following distrib.exe
switches with no luck:
-BcpBatchsize
-CommitBatchSize
-CommitBatchThreshold
-PollingInterval
PLEAE HELP!
THANK YOU VERY MUCH!
xnmb
Hilary Cotter wrote:
Queued is slower than transactional. I am curious as to which is your
publisher, which is your subscriber. And how is data flowing?
In which direction is it slow.
I would also advise you to look at using a multiple publications with the
independent agent option and replicating the execution of stored
procedures
as well.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Louie Lubangco" <llubangco@xxxxxxxxxxxxxxxxxx> wrote in message
news:e3BMBEL0GHA.1536@xxxxxxxxxxxxxxxxxxxxxxx
Hi all!
i'll go directly to my problem.
Our main db server is in Paris,France being replicated here in the
Philippines. during peak hours ,which is around 9AM-3PM(PARIS TIME),
replication takes more than an hour to finish. i know this is
intolerable
that's why im here to seek advice,tips from you guys.
Thanks in advance,
Louie from PH
.
- Follow-Ups:
- Re: replication latency
- From: Hilary Cotter
- Re: replication latency
- References:
- replication latency
- From: Louie Lubangco
- Re: replication latency
- From: Hilary Cotter
- Re: replication latency
- From: XNMB
- Re: replication latency
- From: Hilary Cotter
- replication latency
- Prev by Date: Re: Help in setting up SQL Server 2005 Replication
- Next by Date: Re: replication latency
- Previous by thread: Re: replication latency
- Next by thread: Re: replication latency
- Index(es):
Relevant Pages
|
