Apply snapshot regularly

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Wingman - 28 Dec 2005 04:03 GMT

I am planning to do a snapshot regularly once a month to make sure that the
publshing database and subscriber database are always in sycn. Our system
is 24x7 but night time and weekend are relatively quiet. Is the correct
process for this is to do the 'Reinitialize All subscriptions' in Replication
Monitor or just start the Snapshot Agents? Do I need to shut down the
Distribution Agent and/or Log Reader Agent first before I do anyone of these
options?

Also, will the tables in the publishing database that are being transferred
over to the subscribing server be locked by SQL server during the transfer?

wingman

Reply to this Message

Hilary Cotter - 29 Dec 2005 03:15 GMT

There is no point to do this unless you know you are out of sync. I would
run monthly or weekly validations to see if you are out of sync. Then if you
are reinitialize and then manually start the snapshot agent. The log reader
and distribution agents run fine when you are simultaneously generating a
snapshot.

For named subscriptions the snapshot will only be generated when you have a
new subscription, or when you have reinitialized a subscription or
subscriptions. For anonymous subscriptions a snapshot will be run each time
your snapshot agent runs.

While the snapshot agent is being run the published tables will be locked.
After the snapshot has finished the locks will be released, while the
distribution and log reader agents are running they will not hold any locks
on the published tables.

Signature

Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

>I am planning to do a snapshot regularly once a month to make sure that the
> publshing database and subscriber database are always in sycn. Our
[quoted text clipped - 13 lines]
>
> wingman

Reply to this Message

Wingman - 29 Dec 2005 03:54 GMT

Thanks for the great detail. I appreciate that a lot. Just one quick
follow-up question, say I only have one subscription, what is the difference
between 'reinitialize all subscriptions' and running the 'snapshot' again?

Wing

> There is no point to do this unless you know you are out of sync. I would
> run monthly or weekly validations to see if you are out of sync. Then if you
[quoted text clipped - 29 lines]
> >
> > wingman

Reply to this Message

Hilary Cotter - 29 Dec 2005 12:33 GMT

If you run the snapshot again the snapshot will only be generated if there
is a subscription that needs it or if you are using anonymous subscriptions.
Note that if you are using anonymous subscribers the snapshot will be
generated but not sent (unless a subscriber needs one which occurs in two
cases - it does not yet have a snapshot, or if you have reinitialized this
subscriptions or all subscriptions)

if you reinitialize all subscriptions, a snapshot will be generated the next
time your snapshot agent runs and be distributed to all of your subscribers.

So reinitialize is saying this subscriber(s) needs a new snapshot and the
snapshot will be generated the next time the snapshot agent runs, running
the snapshot agent merely will generate a snapshot if someone needs it for
named, or every time for anonymous.

Hope this makes sense - its a difficult concept to understand.

Signature

Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> Thanks for the great detail. I appreciate that a lot. Just one quick
> follow-up question, say I only have one subscription, what is the
[quoted text clipped - 44 lines]
>> >
>> > wingman

Reply to this Message

Wingman - 29 Dec 2005 13:42 GMT

Thanks!! I got it now.

> I am planning to do a snapshot regularly once a month to make sure that the
> publshing database and subscriber database are always in sycn. Our system
[quoted text clipped - 8 lines]
>
> wingman

Reply to this Message