| Groups | Blog | Home |
sql server replication : sp_changepublication
Is there anyway to run this to set the sync_method = 'native' without having to also having to set the force_invalidate_snapshot = 1 ? Here's what I'm trying to do: SQL2000EE (SP4) on Win2000AS (SP4) Transactional Replication -Single Publisher/Distributor with one Publication (RMLP) -Multiple Subscribers receiving PUSH Subscriptions from the Publisher The initial setup worked beautifully, and has been running in production for the past few months, now I need to add 3 additional tables to the existing Publication so that they replicate over to the Subscribers. I only want the Snapshot Agent to generate scripts and bcp data for the NEW tables created on the Published DB instead of the entire Publication's articles. I know when the Dist Agent runs it will only pick up the newly added articles, but I've learned that certain factors appear to control the Snapshot Agent's script article generation behavior (@article param) so I set out to modify the requisite Publication properties to work the way I want. The initial Publication had been setup with allow_anonymous = true, and immediate_sync = true, which I changed using the following: exec sp_changepublication @publication = 'RMLP', @property = 'allow_anonymous', @value = 'FALSE' GO exec sp_changepublication @publication = 'RMLP', @property = 'immediate_sync', @value = 'FALSE' GO Now I was able to run this next script to add the new article, this was not possible before I changed the properties above as it kept insisting that the @article param = 'ALL' exec sp_addarticle @publication = 'RMLP' , @article = 'tbl_Content' , @source_table = 'tbl_Content' go Now when I went to run this next script to add the article to one of the subscriptions, it returns the message again that for concurrent snaphsot generation (do not lock tables) it requires @article param = 'ALL' again which I know will cause the Snapshot Agent to generate all articles again and not what I desire. exec sp_addsubscription @publication = 'RMLP' , @article = 'tbl_Content' , @subscriber = 'MLPDB2' , @destination_db = 'RMLP' go So, I'm figuring I can just run the following and again modify the properties of the Publication to change the sync_method to 'native' thereby allowing the above to run without any error ? exec sp_changepublication @publication = 'RMLP', @property = 'sync_method', @value = 'native' GO Of course when I run the above, once again I get the message that in order to apply this change I have to force_invalidate_snapshot = 1 It's been a bear of a learning experience attempting to mimic the exact same setup as the PROD config in a test environment to iron out all these snafus...but it would be disheartening to learn after all but achieving my desired Snapshot Agent behavior, it might be all for naught if I can't get past the last sync_method property change and have to let it regenerate the entire 20 GB of articles again just for these few new tables. Sorry for the long winded description, just wanted to provide as much info as possible on what I've tried thus far. Thanks in advance. Frank PS> Hilary, bought your book prior to setting up the inititial production environment; it helped tremendously and is an excellent resource and reference!! I thought I remember a paragraph somewhere in there that listed a publication's property changes that require the force_invalidate_snapshot to be set but couldn't locate it.
Unfortunately not when your subscriptions are anonymous. Is it possible to
have a separate publication for these guys? -- 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] "Frank" <frank@frank.none> wrote in message news:EY2bh.41$3k4.14@newsfe10.lga... > Good day all, > > Is there anyway to run this to set the sync_method = 'native' without > having to also having to set the force_invalidate_snapshot = 1 ? > > Here's what I'm trying to do: > > SQL2000EE (SP4) on Win2000AS (SP4) > Transactional Replication > -Single Publisher/Distributor with one Publication (RMLP) > -Multiple Subscribers receiving PUSH Subscriptions from the Publisher > > The initial setup worked beautifully, and has been running in production > for the past few months, now I need to add 3 additional tables to the > existing Publication so that they replicate over to the Subscribers. I > only want the Snapshot Agent to generate scripts and bcp data for the NEW > tables created on the Published DB instead of the entire Publication's > articles. > > I know when the Dist Agent runs it will only pick up the newly added > articles, but I've learned that certain factors appear to control the > Snapshot Agent's script article generation behavior (@article param) so I > set out to modify the requisite Publication properties to work the way I > want. > > The initial Publication had been setup with allow_anonymous = true, and > immediate_sync = true, which I changed using the following: > > exec sp_changepublication @publication = 'RMLP', > @property = 'allow_anonymous', @value = 'FALSE' > GO > exec sp_changepublication @publication = 'RMLP', > @property = 'immediate_sync', @value = 'FALSE' > GO > > Now I was able to run this next script to add the new article, this was > not possible before I changed the properties above as it kept insisting > that the @article param = 'ALL' > > exec sp_addarticle @publication = 'RMLP' > , @article = 'tbl_Content' > , @source_table = 'tbl_Content' > go > > Now when I went to run this next script to add the article to one of the > subscriptions, it returns the message again that for concurrent snaphsot > generation (do not lock tables) it requires @article param = 'ALL' again > which I know will cause the Snapshot Agent to generate all articles again > and not what I desire. > > exec sp_addsubscription @publication = 'RMLP' > , @article = 'tbl_Content' > , @subscriber = 'MLPDB2' > , @destination_db = 'RMLP' > go > > So, I'm figuring I can just run the following and again modify the > properties of the Publication to change the sync_method to 'native' > thereby allowing the above to run without any error ? > > exec sp_changepublication @publication = 'RMLP', > @property = 'sync_method', @value = 'native' > GO > > Of course when I run the above, once again I get the message that in order > to apply this change I have to force_invalidate_snapshot = 1 > > It's been a bear of a learning experience attempting to mimic the exact > same setup as the PROD config in a test environment to iron out all these > snafus...but it would be disheartening to learn after all but achieving my > desired Snapshot Agent behavior, it might be all for naught if I can't get > past the last sync_method property change and have to let it regenerate > the entire 20 GB of articles again just for these few new tables. > > Sorry for the long winded description, just wanted to provide as much info > as possible on what I've tried thus far. > > Thanks in advance. > > Frank > > PS> Hilary, bought your book prior to setting up the inititial production > environment; it helped tremendously and is an excellent resource and > reference!! I thought I remember a paragraph somewhere in there that > listed a publication's property changes that require the > force_invalidate_snapshot to be set but couldn't locate it. > > >
Hi Hilary,
Thanks for the speedy reply. I got past that 'allow_anonymous' issue, by running these first two scripts from my original post. This worked because when I view the Publication's property's in the EM Publication Property dialog, the "allow anonymous" checkbox is no longer checked. [quoted text, click to view] >> exec sp_changepublication @publication = 'RMLP', >> @property = 'allow_anonymous', @value = 'FALSE' >> GO >> exec sp_changepublication @publication = 'RMLP', >> @property = 'immediate_sync', @value = 'FALSE' >> GO Where I'm getting stuck is trying to run the following without also passing in the @force_invalidate_snapshot = 1 property. [quoted text, click to view] >> exec sp_changepublication @publication = 'RMLP', >> @property = 'sync_method', @value = 'native' >> GO When I try to add the specific @article = 'mynewtablename' using sp_addsubscription, it returns an error message that says because my publication is set for sync_method = concurrent in the snapshot properties I have to set @article = 'all'. This is why I'm trying to change the sync_method property using sp_changepublication. I hope this helps clarify ? Many thanks for your time and input. Regards, Frank [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:OeHC711EHHA.1224@TK2MSFTNGP04.phx.gbl... > Unfortunately not when your subscriptions are anonymous. Is it possible to > have a separate publication for these guys? > > -- > 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 > > > > "Frank" <frank@frank.none> wrote in message > news:EY2bh.41$3k4.14@newsfe10.lga... >> Good day all, >> >> Is there anyway to run this to set the sync_method = 'native' without >> having to also having to set the force_invalidate_snapshot = 1 ? >> >> Here's what I'm trying to do: >> >> SQL2000EE (SP4) on Win2000AS (SP4) >> Transactional Replication >> -Single Publisher/Distributor with one Publication (RMLP) >> -Multiple Subscribers receiving PUSH Subscriptions from the Publisher >> >> The initial setup worked beautifully, and has been running in production >> for the past few months, now I need to add 3 additional tables to the >> existing Publication so that they replicate over to the Subscribers. I >> only want the Snapshot Agent to generate scripts and bcp data for the NEW >> tables created on the Published DB instead of the entire Publication's >> articles. >> >> I know when the Dist Agent runs it will only pick up the newly added >> articles, but I've learned that certain factors appear to control the >> Snapshot Agent's script article generation behavior (@article param) so I >> set out to modify the requisite Publication properties to work the way I >> want. >> >> The initial Publication had been setup with allow_anonymous = true, and >> immediate_sync = true, which I changed using the following: >> >> exec sp_changepublication @publication = 'RMLP', >> @property = 'allow_anonymous', @value = 'FALSE' >> GO >> exec sp_changepublication @publication = 'RMLP', >> @property = 'immediate_sync', @value = 'FALSE' >> GO >> >> Now I was able to run this next script to add the new article, this was >> not possible before I changed the properties above as it kept insisting >> that the @article param = 'ALL' >> >> exec sp_addarticle @publication = 'RMLP' >> , @article = 'tbl_Content' >> , @source_table = 'tbl_Content' >> go >> >> Now when I went to run this next script to add the article to one of the >> subscriptions, it returns the message again that for concurrent snaphsot >> generation (do not lock tables) it requires @article param = 'ALL' again >> which I know will cause the Snapshot Agent to generate all articles again >> and not what I desire. >> >> exec sp_addsubscription @publication = 'RMLP' >> , @article = 'tbl_Content' >> , @subscriber = 'MLPDB2' >> , @destination_db = 'RMLP' >> go >> >> So, I'm figuring I can just run the following and again modify the >> properties of the Publication to change the sync_method to 'native' >> thereby allowing the above to run without any error ? >> >> exec sp_changepublication @publication = 'RMLP', >> @property = 'sync_method', @value = 'native' >> GO >> >> Of course when I run the above, once again I get the message that in >> order to apply this change I have to force_invalidate_snapshot = 1 >> >> It's been a bear of a learning experience attempting to mimic the exact >> same setup as the PROD config in a test environment to iron out all these >> snafus...but it would be disheartening to learn after all but achieving >> my desired Snapshot Agent behavior, it might be all for naught if I can't >> get past the last sync_method property change and have to let it >> regenerate the entire 20 GB of articles again just for these few new >> tables. >> >> Sorry for the long winded description, just wanted to provide as much >> info as possible on what I've tried thus far. >> >> Thanks in advance. >> >> Frank >> >> PS> Hilary, bought your book prior to setting up the inititial production >> environment; it helped tremendously and is an excellent resource and >> reference!! I thought I remember a paragraph somewhere in there that >> listed a publication's property changes that require the >> force_invalidate_snapshot to be set but couldn't locate it. >> >> >> > >
Frank,
There are 2 unofficial workarounds which have been mentioned on this group (which I haven't yet tested!): (a) you can circumvent the check by specifying @reserve = 'internal' when you add the subscription for the new article and the snapshot agent should generate snapshot for the new article after that and (b) you could change the immediate_sync property in syspublications to 0 (see sp_changepublication). Other more official workarounds including changing the sync_method from 'concurrent' to either 'database snapshot' (enterprise edition only) and 'native' (which locks table during snapshot generation). Change the sync_method will force a reinitialization of all your subscriptions at this point. Alternatively you could create another publication and use this instead. Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com .
Hi Paul,
I'd seen that @internal = 'internal' workaround from Ray in this NG; but given his caveat emptor, I didn't try it. I'm ok with locking the tables during the snapshot as the website this database serves will be down for maintenance whenever we need to add table(s) to the publication. Originally, we ticked the "Concurrent" snapshot in the properties dialog, then decided it wasn't necessary and when we tried to uncheck it, the force invalidate/re-init warning dialog popped up so we left it as is and it's been that way since. I will get back to testing this weekend on a lab environment and use the @internal workaround and report back to the NG the results. Thank you for your time and suggestions. Regards, Frank [quoted text, click to view] "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message news:e9KcVu5EHHA.2268@TK2MSFTNGP06.phx.gbl... > Frank, > > There are 2 unofficial workarounds which have been mentioned on this group > (which I haven't yet tested!): (a) you can circumvent the check by > specifying @reserve = 'internal' when you add the subscription for the new > article and the snapshot agent should generate snapshot for the new > article after that and (b) you could change the immediate_sync property in > syspublications to 0 (see sp_changepublication). > Other more official workarounds including changing the sync_method from > 'concurrent' to either 'database snapshot' (enterprise edition only) and > 'native' (which locks table during snapshot generation). Change the > sync_method will force a reinitialization of all your subscriptions at > this point. Alternatively you could create another publication and use > this instead. > > > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com . > >
Hi every one
Where is the line on databases/log size for the database mirroring. Does it make sense to set up high availability for database of size 600 GB with a transaction log of about 200 GB a day across a WAN of 1 GB pipe. There are 5000 users with a peak concurrency of about 50%. When do we resort in favour of clustering or other HA options. Bhas
Hi Paul,
@reserved = 'internal' did the trick in my test environment ! I was able to successfully run sp_addsubscription whilst the publication is still configured for sync_method = 'concurrent' and not receive any error. When I ran the snapshot agent after issuing the sp_addsubscription commands, it only created the snapshot for the new articles. -Frank [quoted text, click to view] "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message news:e9KcVu5EHHA.2268@TK2MSFTNGP06.phx.gbl... > Frank, > > There are 2 unofficial workarounds which have been mentioned on this group > (which I haven't yet tested!): (a) you can circumvent the check by > specifying @reserve = 'internal' when you add the subscription for the new > article and the snapshot agent should generate snapshot for the new > article after that and (b) you could change the immediate_sync property in > syspublications to 0 (see sp_changepublication). > Other more official workarounds including changing the sync_method from > 'concurrent' to either 'database snapshot' (enterprise edition only) and > 'native' (which locks table during snapshot generation). Change the > sync_method will force a reinitialization of all your subscriptions at > this point. Alternatively you could create another publication and use > this instead. > > > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com . > >
Thanks for the update Frank. Why not write a short article or blog about it?
Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com .
Hi Paul,
I'm flattered by the offer, however I'm not really the blogging type I hate to say and I'd defer any article writing to the experts out there. Hopefully this NG thread will be useful to someone else in the same predicament I was in. Again, many thanks for the assistance. Regards, Frank [quoted text, click to view] "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message news:%23sVYJ1SFHHA.4712@TK2MSFTNGP04.phx.gbl... > Thanks for the update Frank. Why not write a short article or blog about > it? > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com . > > > > >
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
| home · blog · groups | Questions? Comments? Contact the dn |