SQL Server Replication – Single vs Multiple Publications

replicationsql server

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.

Best Answer

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.

Related Solutions

Sql-server – sp_repladdcolumn – is it possible to supply a list of Publications to add this column on

sp_repladdcolumn is deprecated unless you have SQL2000 subscribers, you should use the Replicate Schema changes option on the publication. Assuming you have that set to No, I guess you could still use the sp_repladdcolumn, and if you look at the source for the proc, it looks like it has handling for accepting a comma separated list of publications, but using it is venturing into undocumented handling of a deprecated procedure...i.e. no guarantees.

sp_repladdcolumn  @source_object =  'fooTable',  
                  @column =  'fooNewColumn',  
                  @typetext =  'int' ,  
                  @publication_to_add =  '[pub1],[pub2]'

EDIT: Publication names need to be in [] for it to work as in the code of sp_repladdcolumns it has:

select p.name, a.name, a.pubid from dbo.sysmergepublications p, dbo.sysmergearticles a   
where p.pubid=a.pubid and a.objid=@objid and p.snapshot_ready=0 
     and (p.name like @publication_to_add 
         or charindex('[' + p.name + ']', @publication_to_add)>0)

Sql-server – Controlling the order in which merge articles are applied by snapshot at initialization

Yes, utilizing sp_changemergearticle to change the processing order will alleviate this issue.

Best Answer

Related Solutions

Sql-server – sp_repladdcolumn – is it possible to supply a list of Publications to add this column on

Sql-server – Controlling the order in which merge articles are applied by snapshot at initialization

Related Question