Re: Replicating very large articles but need to avoid locks from s
- From: Cqlboy <Cqlboy@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 27 Apr 2007 16:42:01 -0700
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 snap-
- Prev by Date: Re: Monitor transactional replication.
- 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
|
