SQL Server – Pros and Cons of Multiple Transactional Publications

sql servertransactional-replication

I have two database servers: 

 1. DB1 
 2. DB2

Both are SQL Server 2012. DB1 – is a production server. DB2 – is a server which is used only for reporting. There is a transactional replication, which replicate data between DB1 (publisher) and DB2 (subscriber and distributor).

Recently database size has grown and if I need to add or delete any object it takes too much time to make a snapshot, send it to Distributor and apply all changes to Subscriber. Now it takes 3+ hours which is not reasonable for me.

So I wonder if I could configure multiple publications – one for the biggest tables which are not changed to often and one for all other objects.

Does it make sense?
What do you think about any cons of this solution?
Maybe you could provide any other solutions for this case?

Thank you.

Best Answer

Make sure both of these settings for your publication is set to False which is the default. It these are set to FALSE and you DO NOT reinitialize your subscription snapshot should only be generated for new articles.

If you are changing any portieres of the article you have to reinitialize and recreate full snapshot.

[ @immediate_sync=] 'immediate_synchronization' Specifies if the synchronization files for the publication are created each time the Snapshot Agent runs. immediate_synchronization is nvarchar(5), with a default of FALSE. If true, the synchronization files are created or re-created each time the Snapshot Agent runs. Subscribers are able to get the synchronization files immediately if the Snapshot Agent has completed before the subscription is created. New subscriptions get the newest synchronization files generated by the most recent execution of the Snapshot Agent. independent_agent must be true for immediate_synchronization to be true. If false, the synchronization files are created only if there are new subscriptions. You must call sp_addsubscription for each subscription when you incrementally add a new article to an existing publication. Subscribers cannot receive the synchronization files after the subscription until the Snapshot Agents are started and completed.

[ @allow_anonymous=] 'allow_anonymous' Specifies if anonymous subscriptions can be created for the given publication. allow_anonymous is nvarchar(5), with a default of FALSE. If true, immediate_synchronization must also be set to true. If false, anonymous subscriptions are not allowed on the publication.

My personal preference is to group articles into multiple publications. That way even you have to reinitialize subscriptions and generate new snapshot you can run multiple jobs at the same time. You will need to make a choice if you want shared or individual (per publication) distribution agents.

Related Question