tag:blogger.com,1999:blog-4639656271502429319.post8989759280215036882..comments2023-07-27T03:49:29.762-07:00Comments on Matt Slocum's SQL Blog: The Distribution Agent for a Transactional Publication Will Ignore the MaxBCPThreads Setting by DefaultMatt Slocumhttp://www.blogger.com/profile/12250451871941307366noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-4639656271502429319.post-38946809178619645722013-01-18T11:53:11.681-08:002013-01-18T11:53:11.681-08:00Thank you John! I tested this on SQL 2005 appears ...Thank you John! I tested this on SQL 2005 appears to have the same behavior you describe.<br />I appreciate you adding this comment as I'm sure I'm not the only one it will help.<br />Matt Slocumhttps://www.blogger.com/profile/12250451871941307366noreply@blogger.comtag:blogger.com,1999:blog-4639656271502429319.post-48097184527535684592013-01-16T11:52:10.003-08:002013-01-16T11:52:10.003-08:00Oh, but one does have to drop and recreate the sub...Oh, but one does have to drop and recreate the subscription. Performing the above step will reset the publication properly, which will allow multiple threads for SNAPSHOT, but the import by the dist agent will still be single threaded. After dropping and re-adding the subscription, performance was greatly increased. Thanks for this article! It helped us go from 5 hour snapshot/resync times to 4 minutes!!!JohnNelsonNmbr2https://www.blogger.com/profile/15679869685153004135noreply@blogger.comtag:blogger.com,1999:blog-4639656271502429319.post-32020875546390800672013-01-16T11:25:27.155-08:002013-01-16T11:25:27.155-08:00FYI, according to this MS doc
http://msdn.microsof...FYI, according to this MS doc<br />http://msdn.microsoft.com/en-us/library/ms188413.aspx<br />In SQL 2012 you CAN change the concurrency property ("Sync_Method") on the fly, you just need to set @Force_Reinit_Subscription = 1 at the same time and re-run the snapshot.<br /><br />sp_changepublication<br /> @publication = 'Update_ComplianceStatus',<br /> @property = 'sync_method',<br /> @value = 'native',<br /> @force_reinit_subscription = 1;<br />GO<br />JohnNelsonNmbr2https://www.blogger.com/profile/15679869685153004135noreply@blogger.comtag:blogger.com,1999:blog-4639656271502429319.post-29529686107590968722012-10-18T20:20:35.177-07:002012-10-18T20:20:35.177-07:00Thanks Mr Slocum, this has helped our replication ...Thanks Mr Slocum, this has helped our replication performance. Have you tried your hand at sailing?Anonymoushttps://www.blogger.com/profile/18333265840567558364noreply@blogger.com