Re: Adding a nosync article to an existing transactional publication
- From: "Hilary Cotter" <hilary.cotter@xxxxxxxxx>
- Date: Wed, 13 Sep 2006 09:38:13 -0400
For what you are trying to do I think that bi-directional transactional
replication is a better fit. Queued updating is not scalable where a large
portion of the DML originates at the Subscriber. I take it you are using the
subscriber as a failover server, in this case the queued subscriber will not
work well for any significant load.
I haven't tried to repro your plan. I don' think it will work. Have you
tried it?
--
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
"John T" <JohnT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5D5995C9-EEEC-422B-8207-D3841B289991@xxxxxxxxxxxxxxxx
I am using transactional replication with one queued updating subscriber as
a
high-availability solution in a 24x7 environment. Several of the articles
combined total 100 gigabytes of data. When applying the snapshot, I
generally choose the option to drop the existing table and recreate it.
The
problem is, records in the articles of the tables in question do not
change
once written, and deploying replication to accommodate schema changes is
taking longer and longer. I am aware of several techniques to mitigate
this,
but each has their own problems, mainly because I want to ensure that I
have
no down time. Ultimately, my goal is to only replicate data that is on
the
publisher, not existing on the subscriber, WHILE keeping the publisher
online, accepting transactions. To this end, I know what data needs to be
replicated. One obvious solution is to use row filtering, however I
anticipate problems with the latency involved in filtering the rows. Here
is
a proposed solution that I am considering for evaluation:
1) create a transactional replication publication, allowing
queued updating WITHOUT the large table articles
2) add the push subscription allowing queued updating
3) fire the snapshot
4) validate subscription
5) stop the distribution agent
6) add queued updating large table articles with nosync
7) sp_addsubscription for the new large table articles,
allowing
queued updating, to the existing replicated publication
8) run sp_scriptpublicationcustomprocs on the publisher
9) run sp_script_synctran_commands on the publisher
10) identify the first newly inserted record on the publisher
for large table articles, stored by the logreader
11) bcp all data from the publisher, missing from the
subscriber
MINUS the records starting from the first identified entry in the
logreader
in msrepl_transactions.
12) run the output of sp_scriptpublicationcustomprocs on the
subscriber
13) run the output of sp_script_synctran_commands on the
subscriber
14) start the distribution agent
15) validate the subscription again
I know that I can use sp_browsereplcommands @article_id to identify queued
transactions. However I have not found a way to identify which
transactions
in msrepl_transactions have actually been applied to the subscriber. My
clustered index on these large tables is a sequential identity, so once I've
identified the first transaction in msrepl_transactions, I should know
what
data range to bcp.
My questions are:
a) Is this a viable strategy to minimize locking and I/O
overhead?
b) Am I guaranteed that since the distribution agent has not
run
since the addition of the large table articles, that the min(xact_seqno),
for
each article in question, is the first newly added transaction >not< to be
bcp'd in step 11?
c) If the answer to question b is 'NO', what technique do I use
to identify the newly inserted records for the large table articles in
step
10?
d) Am I missing any obvious steps, or creating other problems
unknown?
Thanks for any feedback.
.
- References:
- Prev by Date: Replication Alerts SQL2005
- Next by Date: Re: Peer-to-peer conflict resolution
- Previous by thread: Adding a nosync article to an existing transactional publication
- Next by thread: Re: Transactional replication question
- Index(es):
Relevant Pages
|
Loading
