Re: replication latency

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






.

Relevant Pages

  • Re: SQLEXPRESS Replication?
    ... The way the Technet SQL BOL is arranged it is difficult to know just how to setup the required permissions to do replication which is the major stumbling block. ... You can replication from SQL Server 2005 Workgroup edition and above ... to a SQL Server Express subscriber using transactional replication. ...
    (microsoft.public.sqlserver.replication)
  • Re: replicated commands not executed on the subscriber side?
    ... My current environment is the most complex sql server environments in my 10+ ... Unlike the replication procedure when changes made at Subscriber, ...
    (microsoft.public.sqlserver.replication)
  • RE: Synchronisation failing on SQL CE Database
    ... There is also an error on the publication in the Replication monitor. ... Server SERVER, Level 0, State 1, Procedure sp_MSprepare_mergearticle, Line 78 ... I am having a problem setting up merge replication between SQL 2005 and SQL ...
    (microsoft.public.sqlserver.replication)
  • Re: transactional replication problem
    ... If the distribution agen can't connect to the subscriber, ... Looking for a SQL Server replication book? ... > server B. Problem is with only one database. ...
    (microsoft.public.sqlserver.replication)
  • SQL Server merge replication security
    ... Microsoft SQL Server replication. ... need to have the sysadmin role on their local database to initiate the ... The AppAdmin table cannot be removed from the publication as then the ...
    (Focus-Microsoft)
Loading