April 20, 2007 at 2:51 pm
Hi we have 8 publications on 4 different database on a single server .Its a transactional replication. The subscriber is another server with same databases where data is pushed. So my question is can i generate a snapshot on the first server in parallel for all publications simultaneously. Does it slows down the performance.Because there is only single snapshot agent running on one server.
May 3, 2007 at 4:29 pm
You will find that there is a different Snapshot job - one for each pub.
Also one LogReader job per db
but you have the option to set @independent_agent=false if you WANT to share one DistribAgent for pub-sub pair.
In your case there will be 4 x LR, 8 x Snap, and 1-8 x DA depending.
For Transactional repl the Snapshot is typically only needed at outset and when any new sub is added, but the GUI fires every hour just in case [immediately detects not needed and quits]. I normally change schedule to once per day at 05:00
It would also be normal to init the pub(s) and leave running, so you are really only concerned with that initial Snapshot generation. When you create pub you specify whether to keep the snaphot files or generate on demand [on new sub, and then delete files when delivered]. For 8 pubs that may be relevant [storage capacity].
It is possible to fire [all 8] Snapshot jobs at same time so they compete for CPU, I/O resources, or you could stagger their start schedules to have minimal overlap. If you have best hardware [raid array, multiple datapaths, caching] overlap is OK [and may reduce total elpased time. But since you are only doing once (?) per day it hardly matters.
There is another issue on starting SQLAgent jobs that the system needs to validate the job owner etc, entailing chat to a AD/DC for authentication. If you have a slow network [or hit rush hour with backup activity] I have found this security check can timeout [so job fails to even start].
Because of this I try to minimise scheduling multiple jobs to kick in together [all SQL Agent jobs not just the repl ones]. As such I use schedules that spread starttimes [even 15 second gaps are OK], hence starts of 00:00:15, 00:00:30 ...
Final thought on the distribution db [on Distributor, but this is commonly the Pub] ..
make sure you have decent AutoGrow increments [eg 100MB and not the silly 1MB default] and realistic ceiling for restricted sizes. And ensure you have sensible maintplans to conduct optimisation periodically [eg reindex+shrink every Sunday] else fragmentation will hurt performance for changes [the agents are busy doing I/U/D operations and can make a patchwork quilt in no time]
HTH
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply