Sql-server – 1 big merge publication or N small ones

performancesql serverwindows

Scenario

We have two servers, each one with its own Microsoft SQL Server

  1. Serv_Home
  2. Serv_Web

In both servers, a local program works with its own local database and inserts/updates the tables. The data is shared between the 2 servers, and for this purpose we have several replicas. Serv_Home is the master server (the distributor of the publications).

A specific table must be copied from Serv_Home to Serv_Web (not viceversa), so the publication is a Snapshot.

For all the rest, a Merge publication is used. We decided to create 5 Merge publications, one for each group of tables which are more or less related. The reason why we have 5 instead of a big single one is simply convenience. Everything works fine.

Lately though, we are experiencing a high CPU usage in Serv_Home (like 50% sometimes) due to the sqlservr.exe process.

Question

Is it better to have a big single Merge publication instead of N small ones? Could this be a performance bottleneck?

I know that the best way is to try it myself, but we are in a production environment and it would be messy to redo it all. Some documentation or good reasoning would be appreciated.

Best Answer

I've been looking for an answer to no avail.

However, I've found a place where some advantages are mentioned:

  1. Easier to maintain.
  2. Multiple data streams going to the subscription database at once (hopefully).
  3. If the merge fails for a publication, only this publication has to be fixed and reinitialized.

So I think there would be no performance improvement having just 1 big one publication. On the contrary, if the data streams are parallel, we can even get a better performance when splitting the merge in N small publications.

Related Question