Job to restart Replication jobs?

[katgirl]

Hi,

In the event that the server that hosts replication goes down and then comes back up - do the replication jobs start automatically? Or say, the SQL Server Agent is stopped and started for any reason? Do the replication jobs re-start?

I can't remember.

So, my question is, do I need to create a job to restart the replication jobs in the event of "Agent restart"?

Thanks much!

 

[katgirl]

I forgot to mention, we are running

SQL Server 2005

 

[Catadmin]

Any jobs should restart on their own, assuming SQL Agent comes back up fine. You could run into corruption issues, though, that could stop the jobs from restarting.

You'll want to check the first time or two to make sure it does start up properly, though.

Usually it's not the jobs I have problems with (in any replication). Usually I have to re-initialize the subscriptions and manually re-run the snapshots because things get out of sync so bad, there's no other choice. of course, this was all on SQL 2000. Haven't done Rep on SQL 2005 yet.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[katgirl]

Hi Catadmin,

I've never used snapshots myself b/c I don't like the idea of dropping and recreating tables - I think that's how the snapshots work (I could be wrong though).

We have this tool called AdeptSQL that allows us to sync by hand when things occasionally go out of sync. Usually it's just a few rows. Seems simpler than the snapshots.

However, I have noticed in the past that when the agent would go down, the repl jobs did not restart. But in 2005, there's all kinds of jobs running - one of which checks for a "heartbeat" - so I don't want to add jobs that I don't need.

Since this is a production server, I can't test to see what the effect of stopping the agent will be. So, I guess I will find out the next time it stops for whatever reason, unless someone out there knows.

Thanks

 

[Catadmin]

Do you not have rep set up on some test servers? If you have the test servers available, I'd set up rep just like on Production so you can get the answers to questions like this before you actually need them.

Replication always has been a pain in the butt to manage if only because it's hard to tell what it's going to do.

BTW, I could be wrong, but I don't believe Snapshots drop & recreate the tables. Truncate them, maybe, but I don't think it does a drop & create.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"