I have the same database on two servers and I am using merge replication for around 35 tables, and it is working fine.
But whenever I add 2-3 more tables with around 1 million records, the replication breaks after some time/days.
Can I do 2 replications on same database? Say 1 replication for those 35 tables and another one for 2-3 new tables?
Please suggest resolution for those bulk table data replication via merge method.
The answer to your question is yes. You can have 2 and more merge publications which synchronize different tables in the same database. However, you are misusing the term replication.
When you say:
Can i do 2 replication on same table database. Say 1 replication for those 35 tables and another one for 2-3 tables.
You probably wanted to say:
Can I do 2 publications on same table database. Say 1 publication for those 35 tables and another one for 2-3 tables.
So, what you need to do is to create a new merge publication on publisher and add those 2-3 big tables as articles. Then subscribe your subscriber DB to this new publication.
You can either create this new publication via SSMS UI OR generate scripts from existing merge publication then change name and articles list manually and run the script via SSMS.
If those 2-3 tables are not dependent on the other 35 I think there should not be issues. However, if those 2-3 tables depend somehow on the other 35 or you want to have Article Filters for those 2-3 tables which reference other tables some additional tweaking may be needed. For example, you may opt out to not copy Foreign Ket constraints, etc.
Also, it would be helpful if you post the actual error which you get when syncing those 2-3 tables. From my experience I can say that replication can be very fragile and you should prepare yourself for in-depth investigation of Merge Agent error reports/logs.
HTH.
