Replication nightmare!
-
Replication. Done all the reading and decided to have a play. Keep it simple, just a replication of one database table from one database to another on the same server. Followed all the wizards, didn't meddle with the advanced options.
Won't run. The snapshot and replication agents all say 'Never started'. When I right click and select start agent it hangs for a couple of seconds then nothing.
Been all over the internet searching. Found some advice about checking the user the job is created to run under. This is set to the domain administrator so should be able to do anything.
All the info. on this site suggests I should be fine! Really starting to wind me up...
So pretty please...have I failed to de-select the 'don't ever work' box somewhere?
Cheers
Will
-
Have you refreshed? The agents are like SQL Server Agent Jobs (actually they are) so they are updated as they run. The Snapshot Agent needs to be the first one you run, but it will only run when you have a Subscriber to the replication. If it's a large database, you should be able to see its progress. You should see something like "A Snapshot of X article(s) was generated." if it succeeds, or a reason for it to fail.
Asides from that, check the folder on disk that you selected during setup to hold the distribution files. There should be some snapshot files in there, and when the subscriber is up to date, transactions/ merge will be in this folder.
Give those things a go, and let us know what you get.
Julian Kuiters
juliankuiters.id.au -
Just got into work this morning (UK) and it looks like it might have worked! Very bizarre...
I have the 'snapshot was generated' message and the new table has appeared in my subscriber database.
The agents are all still showing red dots (stopped) but could this be because I have set up a schedule to run the jobs? i.e. if the job isn't currently active the red dot shows?
Thanks for your help on this - very useful, and quick!
Will
-
Not quite there yet
I have set up the two agents (snapshot and replication) to run on a schedule. The snapshot kicks off every 5 minutes, every day starting at 00.00.00. The replication agent does the same but starting at 00.02.00. The idea being that by the time the replication agent does it's thing, the snapshot is ready and waiting (it's a very short job - takes less than a second to run).
Looking at the agent history, the replication agent is running like clockwork every 5 minutes, but keeps finding nothing to replicate. Looking at the snapshot agent history, this isn't running to the schedule at all. Double checked everything and there seems no reason for the snapshot agent not to play ball.
So near and yet so far. This replication tool will be absolutely marvellous when it works!
Will
-
Ouch! Snapshot doesn't need to be run that often. Snapshot takes an ENTIRE copy of your table, where as replication only copies over the changes. Really you only need a snapshot when you first add a subscriber, or if for some reason your subscriber gets totally outta sync. My reps have Snapshot set to once a month for important low volume change tables > 10mb. Less for other tables.
Transaction Replication will see the changes made to the data moved over to the other table. I usually have Trep set to about every 5 to 10 mins, with push subscriptions, so that only if a change is made does it bother to send it.
With both you snapshot and transaction rep set so high, trep will almost never occur becuase you will always be syncing snapshots. Try turning your snapshot frequency down, and doing some transactions against the table (insert/update/delete). You should then see the Transaction Replication agent actually do something.
Julian
Julian Kuiters
juliankuiters.id.au -
Ahhhh...I may be overdoing things then!
So it might be better to set the snapshot agent to run once a week and then the distribution agent to run every 30 minutes or so? I have set up a snapshot publication rather than a transactional publication though. If I don't need changes to be propogated to the subscribers in real-time is this better? Or would a transactional approach be better?
Basically I have a database server that is populated from a web application via a web server. We need to run some heavy queries on the data so I thought it better to replicate the tables needed by the queries to a different server. The idea being not to clam up the web system whilst the queries are running.
All useful input - thanks!
Will
-
It depends on the size of your tables and the amount of change that occurs.
If you have tables that are rarely updated, snapshot is fine.
If you have tables that are updated frequently or regularly, I'd use queued transaction replication.
One of the main databases we have replicated here, has a number of tables which might be updated once a month or less. They are snapshot replicated monthly, push transaction published every day. (so the maximum time they are out of sync is 24 hours). For the tables which are constantly updated through out the day, we snapshot once a month, and push transaction publish every 10 mins. (so the max time they are out of sync is 10 mins).
Remember that the snapshot takes a copy of the articles at that point in time. Distributing it every 30 minutes will not replicate changes that occur AFTER the snapshot is taken. It would just apply the snapshot and then do nothing until the next snapshot is created.
How much data do you have to move? and how often do you run your heavy queries on the subscriber?
Julian Kuiters
juliankuiters.id.au -
by the way install SP3a .. it solved many Replication problems
Alamir Mohamed
Alamir_mohamed@yahoo.com -
Interesting. There are a number of changes to replication in SP3a. I wouldn't have thought they'd be the cause of you problems, but as always, having the latest service pack helps.
SQL Server version 2000 Service Pack 3a Readme.htm
INF: SQL Server 2000 Service Pack 3a Readme.htm Additions
http://support.microsoft.com/default.aspx?scid=kb;en-us;816502
Julian Kuiters
juliankuiters.id.au
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply