July 17, 2003 at 8:05 am
I'm kind of new to SQL replication so please bear with me if some of these questions sound very simple.
I'm trying to run snapshot replication between our offices in Houston and New York.
My first question is can Snapshot replication be run while people are still using the database?
What I tried doing to not tax the server was try to run the snapshot agent on a schedule that will kick it off at 3:00 in the morning. However the schedule doesn't seem to work. I have the enable checkbox checked but it doesn't seem to save my changes. The distribution agent starts and tries to push down the image but since there is no image it fails.
Any help will be greatly appreciated. Thank you.
I'm kind of new to this entire replication concept. And I keep running in to two problems.
July 17, 2003 at 8:53 am
Yes it can run while people are using it, but it does have to acquire an exclusive lock on each table so that it can get a clean copy BCP'ed out. Doing after hours is a good idea. To troubleshoot the snapshot part, does the job history show it running? Failing?
How much data are you moving and are you moving both directions? Merge or transactional might be better options depending on that and what you need to achieve.
Andy
July 17, 2003 at 9:04 am
The snapshot agent history says Failed. The error it give is: "Agent Suspect. No Response within last 10 min." I've increased the timout rate thing to 30 min. get the same error.
The data is going one way. I think transactional might be the best way because most of the tables will not change, the data in it is more or less static. Only a handfull of tables will have any changes made to them.
However i still can't seem to get the inital snapshot to replicate over.
July 17, 2003 at 10:22 am
Also think that transactional would be the best. You will only have to create a snapshot one time, and also with transactional replication, you can configure the snapshot agent not to lock the tables that is bcping out, because it uses the log reader.
Remember that transactional will only replicate the transactions made to the published tables.
If you still can't run the snapshot agent, I would suggest to check if the agent is being lock by a user connection. While you are running the snapshot agent, run a sp_who2 to see if it is locked?
Are you running the distribution server and the publisher on the same box?
July 18, 2003 at 2:07 am
Hello
Have you tried to decrease the BCPBatchSize in the distribution agent profile. I had the same problem and decrease the BCPBatchSize and the CommitBatchsize and after this it works fine.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply