I was helping out with a SQL Server upgrade recently, going from 2016 to 2019. We ran into a problem when trying to run replmerg.exe for a merge replication subscription. Specifically, we were getting error code 20084, which means that the replication process couldn’t connect to one of the instances. Interestingly, the process couldn’t connect to the local instance, and the failure was immediate—that is, within a couple of milliseconds. There was nothing in the management logs on either the distributor server or the subscriber server which indicated a problem. We were able to connect both sides together just fine—from the subscriber, we could connect to the distributor, and from the distributor, we could connect to the subscriber.
Common problems which would show agent message code 20084 include:
- The distribution login doesn’t have rights on the distributor. In my case, I knew this wasn’t it because there was another subscription for the same publication running successfully on a different server.
- The subscriber is inaccessible. We could definitely access both servers from both sides, so no networking or firewall chicanery.
- Some account settings are wrong. Again, no problem here. Also, the publisher/distributor and subscriber are both on SQL Server 2019.
- Some publication settings are wrong, especially that the distributor login needs to be a member of the Publication Access List. Again, that wasn’t the issue.
- You have a 32-bit alias but not a 64-bit alias. We didn’t have any aliases, so that wasn’t an issue.
When we looked back at a different subscriber which was running correctly in production (still in 2016), I noticed one minor difference in the output: the prod subscription used SQLNCLI (SQL Server Native Client 11.0) and the dev subscription was using MSSQLOLEDB (the new OLE DB driver). Turns out that the Native Client technically doesn’t support versions past 2014, though it did work in 2016. Nonetheless, that was an interesting red herring.
Add one more common solution to the list: make sure you have the latest Cumulative Update of SQL Server 2019. When we started troubleshooting, we only had 2019 RTM installed. After installing the latest CU, everything worked without a hitch, so it seems that somewhere between RTM and CU14, there was a bugfix which affected merge replication.