1

I have a large database with several transactional replication publications. Each publication has 15-20 articles. Some subscriptions are push, so each time a new article is added to the publication, a new large snapshot must be created. In order to avoid this, I'm thinking to create a single publication per article (not adding the arcticle to an existing one), thus reducing the need for a large snapshot. I'm wondering if this approach has any downsize.

1

My last position we had dozens of publications. We would group the articles by size where the largest tables with millions or tens of millions of rows would have their own publication and others would be grouped together. In some cases publications were grouped by tables that were needed for a single application. Some critical tables were also in their own publications so that if we needed to reinitialize it during the day, it wouldn't be because of some minor table.

But this also depends on your hardware, applications, etc. Some publications we would reinitialize during the day and no one complained. Others could only be done after hours.

We probably had over 1000 tables that we published so one table per article would be a no go. There is no hard answer on this.

|improve this answer|||||
0

You are able to add an article without creating a FULL snapshot each time. See (http://www.sqlservercentral.com/articles/Replication/119650/)

I have never created one publication per article, I would think managing that would be a huge headache. One downside I could see is if you ever wanted to recreate a "group" of articles it would take quite a bit more time to create so many publications.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.