Have you ever noticed that when you add an article to a transactional publication and run the snapshot agent it does a complete snapshot of all tables? This can be a real pain for big publications. Fear not – it is configurable. The setting is visible in the Replication Monitor. Actually 2 settings – shown below. We need both to be False but the observant DBA will notice that the main one of these is greyed out!
A little scripting solves it though. We just run the script below before adding a table and all is well – the snapshot agent then just creates the new article.
EXEC sp_changepublication
@publication = ‘pubTestTransactional’,
@property = ‘allow_anonymous’,
@value = ‘false’
GO
EXEC sp_changepublication
@publication = ‘pubTestTransactional’,
@property = ‘immediate_sync’,
@value = ‘false’
GO
But this creates a bigger distribution db. I suggest making them false only when you are going to be adding new articles and true the rest of the time. This saves i/o’s and keeps the distribution db smaller.
Thanks Gene – I never noticed the effect on the distribution database – I’m going to have a play with this and then then reply properly.
And also thanks for being my 1st ever commenter 🙂