Re: Replicating very large articles but need to avoid locks from s
- From: "Raymond Mak [MSFT]" <rmak@xxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 27 Apr 2007 17:28:55 -0700
Blake, I am probably not reading you previous post (the one before you
latest response) correctly but it seems to me that doing a (full table) bulk
update on the pk or uq will incur rather significant resource\locking
contention at the publisher database, and the logreader\distribution agent
will need to transfer almost twice the amount of data (delete\insert +
command formatting overhead) thereby putting further strain on the network
and the cleanup agent. That seems to be a bit of an extreme measure to avoid
replication snapshot processing. And as I have mentioned before, locking
overhead for snapshot generation is much reduced in SQL2005 by default and
you can use sync_method = 'database snapshot' to reduce that further. Paul
Ibison also has a nice article up on his website
(http://www.replicationanswers.com/BCPPartitioning.asp) talking about an
enhancement in SQL2005 that allows a large table to be bulk-copied by the
snapshot agent in parallel. That said, it is not my intention to be pushy
about this and there are indeed cases where all the enhancement in SQL2005
snapshot processing will be inadequate to scale (it is not a pretty sight
but I have seen people managed 100GBs snapshot on SQL2000). I am merely ...
intrigued... by your comments and I very much appreciate your (extremely
detailed) feedback.
-Ryamond
"Cqlboy" <Cqlboy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:51FEF6A7-F5E5-4D54-BB9E-542EE39D5010@xxxxxxxxxxxxxxxx
Raymond, thank you for the response and tip regarding snapshot
processing.
I need to investigate/experiment more with this. I am admittingly not
familiar yet with all of SQL 2005's offerings. Also, let me add, I'm not
at
all averse to replication and used SQL 2000 Transactional replication
extensively at my former employer, Experian Corp.
Key things noted from my replication experience is this:
1) Taking an initial snap-shot against several large and important
order-related tables incurring heavy insert/update/delete activity
effectively took the company down until the snap-shot completed. This
could
be hours, something upper management would never tolerate. Heaven forbid
if
we lost sync' !
2) Publications containing hot articles presented significant loads to
the
disk IO subsystem, something that very few DBA's are aware of or
understand.
For example, if your publication contained articles responsible for much
of
the server IO you need to be aware that this resource drain will
effectively
double via the Distributor when replicated. This was a surprize to me
when I
first discovered it but it makes perfect sense in hindsight.
3) The last issue came from the Distributor clean up job. Whenever this
executed, it exercised a massive IO spike against its raid group, thus
affecting everything associated with those disks. Yep, I tinkered with
the
Clean-Up job schedule and tweaked the agent profiles but could never seem
to
alleviate these massive spikes. I never made it this far, but I was
seriously contemplating customizing the code in the Distributor Clean-up
proc' to break up and spread the IO demand over a longer interval,
anything
to avoid or reduce this sharp massive hits against the disks.
I learned and became more sensitized to these issues when tasked to
re-architect SQL Server on our EMC Clariion CX500 and CX700 SAN. I
captured
file level IO stats using the little understood/known SQL Server function
fn_virtualstats as a source, sampling @ 1 minute intervals. Querying
against
one weeks data was very enlightening.
I am aware of moving the Distributor to its own dedicated box/disks but
could never convince management to do so. BTW... it was a heavy bang
against
the SAN cache and it seemed we had evidence that the Distributor Clean-Up
job
cause global issues via the SAN. Evidence we out grew our SAN, I guess.
I apoligize for this lengthy response but the key answers I am looking for
is how to initialize replication for very large and critical,
order-related
articles without taking the company down. Secondly, is their a practical
means of determing the additional burden to the TempDB ? Log ? ... and
the
underlying disk subsystem. i.e. can I handle the load? I actually have a
rough idea on this last point but if you have anything to share I'd love
to
hear it.
Thanks again for taking the time to respond.
Blake Colson a.k.a CqlBoy
Lead DBA of Operations
RealtyTrac, Inc.
Irvine, CA
"Raymond Mak [MSFT]" wrote:
Hi Blake, being the person responsible for most of snapshot processing in
transactional\snapshot replication, I must admit that it is a bit
difficult
for me to read your posts. That said, in the interest of product
improvement, I must ask what sort of problems you have experienced in
SQL2000 (I can tell locking being one of them) that causes you to develop
such an intense aversion against using replication snapshot processing at
all.
As Hilary had mentioned, locking during snapshot generation for
transactional replication is much reduced by default in SQL2005, and you
can
further reduce it by using the new 'database snapshot' sync_method if you
are running Enterprise Edition. In addition, I had also put in quite a
few
performance improvements for snapshot processing in SQL2005 which should
hopefully make replication snapshot processing less painful to use. As
such,
I would really appreciate if you can give replication snapshot processing
a
try in SQL2005.
Thanks much,
-Raymond
"Cqlboy" <Cqlboy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CC9DE155-8328-4CBF-8BF5-EE68900A59B1@xxxxxxxxxxxxxxxx
Is it also possible to force at the Publisher like in SQL 2000 to send
a
pair
of DELETE/INSERT statements for an entire row by performing an UPDATE
on a
primary key or column that was unique? This was an issue in SQL Server
2000
when the PK was an identiy column, I couldn't update the PK and force
the
DELETE/INSERT but their was a trace flag that would trigger this
behavior
on
any column, thus making it possible to sync' an entire row.
Thanks.
-Blake
"Hilary Cotter" wrote:
The locking which occurs in SQL 2005 is much less than the default
locking
which would occur in SQL 2000.
In SQL 2005 you have the no-sync option as well. To do this create
your
publication and then create your subscription as per normal.
Then when you get to the initialize Subscriptions dialog uncheck
initialize.
"Cqlboy" <Cqlboy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:47464A31-3AD8-4A5A-9558-3009CE84A498@xxxxxxxxxxxxxxxx
I have a very large and very active table which I want to replicate
to
another server. How can I set-up a Publication for this table and
avoid
the
locking during the initial snap-shot in SQL 2005? SQL 2000 allowed
configuring replication without generating a snap-shot - assumed
data
was
already present. I used this technique and would update unique
columns
on
the source or primary key to send over complete row delete & inserts
to
finally achieve sync'. I'm looking for a similiar work-around in
SQL
2005
but don't see it. I'm aware of initializing from a backup but that
seems
extreme for just a ~5 of 400 tables I want to replicate. Any ideas
how
to
skin this cat would be appreciated. Thanks. -CqlBoy
.
- Follow-Ups:
- Re: Replicating very large articles but need to avoid locks from s
- From: Raymond Mak [MSFT]
- Re: Replicating very large articles but need to avoid locks from s
- References:
- Re: Replicating very large articles but need to avoid locks from snap-
- From: Hilary Cotter
- Re: Replicating very large articles but need to avoid locks from s
- From: Raymond Mak [MSFT]
- Re: Replicating very large articles but need to avoid locks from s
- From: Cqlboy
- Re: Replicating very large articles but need to avoid locks from snap-
- Prev by Date: Re: Replicating very large articles but need to avoid locks from s
- Next by Date: Re: Replicating very large articles but need to avoid locks from s
- Previous by thread: Re: Replicating very large articles but need to avoid locks from s
- Next by thread: Re: Replicating very large articles but need to avoid locks from s
- Index(es):
Relevant Pages
|
