How to turn OFF snapshot agent - 2005

[icemel]

Hi,

Does anyone know where I can turn off the snapshot agent in SQL Server 2005?? Couldn't find it anywhere...

Thanks!

 

[mrdenny]

By snapshot agent I assume you are talking about for Replication?

It should be a job. It can be running on either the publisher or the distributor.

I can't be more specific as I don't have replication setup in SQL 2005.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

Why are you trying to turn off snapshot agent without turning off the rest of replication? You do realize you might break it if you do this, right?

Go to the Distributor, look under Replication -> Publications. The agents are usually listed under each publication (right window) when you highlight the publication in question.

Or, on the publisher, right click the publication and go to the status tab. I know you can re-run the snapshot from there. Not sure if you can stop the agent from there.



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"

 

[icemel]

This is point of confusion for me. We are not using snapshot replication. We are using transactional replication. I don't understand why we need the snapshot agent running if we don't use snapshots.

However, I do recall reading somewhere that transactional replication uses snapshots - that is, it applies a snapshot on occasion. Why?

I guess the question is, do we or don't we need to run the snapshot agent when using transaction replication only.

 

[Catadmin]

You do need the snapshot agent running because it gives the subscriber the initial "full picture" of the data and is used to reinitialize subscriptions as needed.

Snapshot agent is used in nearly every type replication. Unless something drastic has changed in 2k5 (which I don't believe it has), you can't shut it off without breaking replication.

Sorry.



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"

 

[icemel]

I guess I need to read up on this some more, thanks

 

[icemel]

Well, upon further investigation, I discovered that the snapshot agent is NOT running on any of our publications.

So effectively, it is turned off. We never turned it on to begin with, when we created the publications.

It worked without it because the tables on the subscriber did not need to be initialized. They were already up-to-date with the latest changes from the publisher.

Replication has been running like clockwork since last week, which seems to indicate that we do not need snapshot agent running... unless there is something that it will need to do in the future, but what?

That was what I wasn't sure about. Because I read somewhere that the snapshot agent needs to run "occasionally" even with transactional replication, I guess if it needs to "reinitialize" the tables.

But WHY do we ever need to reinitialize the tables? That's the real question that no one has answered.

 

[mrdenny]

The snapshot agent is scheduled to run every couple of weeks to create a new snapshot so that SQL doesn't need to keep all the logs around in the event that you want to repush out to a subscriber. If you add a new subscriber than you would also need a snapshot (unless the tables were already up to date).

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

icemel,

Just because Snapshot Agent isn't running doesn't mean it's turned off. It just means it has nothing to do at the moment. All the agents work that way.

As far as why you might need to re-initialize, I've had to do so on several occasions for things like data structure changes (additions of columns into replicated tables) or when the network connection between the publisher and distributor/subscriber goes down. And sometimes, you want to re-initialize just because someone is complaining something is different between the subscriber & publisher (whether or not it actually is different) and you want to get them off your back. @=)

Manual re-initialization can actually solve a lot of replication problems. Not all, but many of them. And automatic reinitialization might happen in SQL thinks the databases have gotten out of sync.

Does that help you out?



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"

 

[icemel]

Hi Catadmin,

Yes, that helps me out sort of - I guess I am having difficulty understanding the difference between INITIALIZING and SYNCHRONIZING - and when to do one or the other.

Can anyone explain that - because BOL does not, and I've read just about everything on replication. I guess I will have to dig into my books further.

Also, I am pretty sure the snaphost agent is turned off, because it is not enabled.

Thanks

 

[Catadmin]

Initializing essentially clears (without deleting) all data / references / pointers. It prepares the subscriber to receive all new information and, when it receives that new info, to trash the old stuff and any references to it within SQL Server & replication.

It's like heating up your griddle with a little bit of oil. You're preparing it for the pancakes you'll be cooking in a few minutes.

Let me know if that doesn't help.



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"

 

[icemel]

Not sure -

my mythical understanding was that initializing drops and recreates tables...

but you say it doesn't even delete the data...

so that can't be it... I think I need a more technical explanation before I am comfortable with it

but can't find that technical explanation