We have two servers, each one with its own Microsoft SQL Server
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.
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.