| Groups | Blog | Home |
sql server replication : Apply snapshot regularly
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?
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 [quoted text, click to view] "Hilary Cotter" wrote:
> 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. > > > > -- > 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 > > "Wingman" <Wingman@discussions.microsoft.com> wrote in message > news:B03834F3-CA1F-4CC8-9A79-E59593C3CD33@microsoft.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 > > 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 > >
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. -- 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 [quoted text, click to view] "Wingman" <Wingman@discussions.microsoft.com> wrote in message news:B03834F3-CA1F-4CC8-9A79-E59593C3CD33@microsoft.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 > 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
Thanks!! I got it now.
[quoted text, click to view] "Wingman" wrote:
> 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? >
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. -- 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 [quoted text, click to view] "Wingman" <Wingman@discussions.microsoft.com> wrote in message news:9E592EC9-CB21-41CD-A274-4FEE79AA940A@microsoft.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 > difference > between 'reinitialize all subscriptions' and running the 'snapshot' again? > > Wing > > "Hilary Cotter" wrote: > >> 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. >> >> >> >> -- >> 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 >> >> "Wingman" <Wingman@discussions.microsoft.com> wrote in message >> news:B03834F3-CA1F-4CC8-9A79-E59593C3CD33@microsoft.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 >> > 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 >> >> >>
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
October 2008
| home · blog · groups | Questions? Comments? Contact the dn |