i have a publisher agent that generates 61 articles for publication
without a problem.
however i'm having trouble understanding exactly how the distribution
agent executes. when executed manually once, it ran successfully, and
the subscriber got the data.
is it possible to run the distribution agent again to re-push/pull data
for the subscribers without running the publisher agent? when i try to
re-run the distributor agent, i get the success message "No replicated
transactions are available." have the snapshot files become
unavailable, or is it just something where the distribution agent knows
that they are older than the subscriber last obtained?
if it's possible to have a distribution agent push/pull data from a
single collection of publisher snapshot articles multiple times, i
would greatly appreciate knowing how that's done.
thanks in advance for any help,
jason
Jason,
to have the distribution agent pick up any new snapshot files, you need to
reinitialize this particular subscriber (sp_reinitsubscription). However if
this is a regular process, perhaps you should just use snapshot replication
instead? Or are you trying to avoid table locking?
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||paul,
sorry, i should have specified that this is a snapshot replication. but
because of the very reason you mentioned, the table locking, i was
hoping that i could tweak the distribution agent steps without having
to republish the data (avoiding further table locks).
does this information suggest any particular solutions as possible /
superior?
thanks for the response,
jason
|||Sounds like transactional would be more beneficial. Unless there are loads
of updates to the same rows, you'll notice an improvement in performance
versus regualr snapshots. Also you'll avoid locking issues by using the
concurrent snapshot generation option when initializing/reinitializing.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||i thought that might be the case. but doesn't transactional replication
come with a performance hit to the updates in the publisher database?
this replication is just to get "production-like" data from the
production database into the development and staging databases. we
don't want to perpetually diminish the performance of the production
database for a process we probably only need to run every couple of
weeks/months. but at the same time, the database is accessed 24-7, so
if we can do the replication without record locking, that's highly
preferable.
another option i'm looking into is a DTS task, which also seems like it
could be done without locking the tables. but i'm still sorting out the
details on DTS with regard to object copies vs. data transformations,
and how to handle identity columns, etc.
sorry for that long contextual tangent. so would you still recommend
transactional replication for a task that really only needs to
replicate data once or twice a month?
thanks,
jason
|||With no downtime of the production site allowed and representative data
required at fortnightly intervals, I'd restore full database backups - much
easier solution with no hit on the production database.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||that's an awesome idea. we use some sql-aware third party backup
software, so if i can figure out how to restore a single database file
from there in a way that sql can use, that's definitely the solution
i'll choose.
thanks for the suggestion!
Monday, March 19, 2012
another simple snapshot replication question
Labels:
agent,
articles,
database,
generates,
microsoft,
mysql,
oracle,
publicationwithout,
publisher,
replication,
server,
snapshot,
sql,
trouble,
understanding
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment