Web Synchronization not working with SQL Express 2019 as subscriber

Question

Hello,

I have got an installation of a SQL Server Merge Replication system, with the option Web Synchronization over https.
The DB Server is SQL Server 2019 and the Web Server, Windows 2019, is IIS, hosting the replisapi.
DB and Web are different servers.
Everything is perfectly running and working, with about 100 subscribers, all of type SQL Express 2008 R2, that regularly synchronize.

In a new test client, I'm trying to make a new subscriber to replicate, but with SQL Express 2019. The purpose is to verify the proper functioning and then realize a full porting of the db subscribers from sql express 2008 to 2019.

But the synchronization ever fails. I detect that the SQL 2019 client ignores the Web Synchronization option, it never tries to reach the replisapi.dll. Instead, it attempts to run a merge replication directly against the server database, which is obviously impossible to be directly connected from the remote clients.

In the client, I installed the last available Cumulative Update for SQL, id est : SQLServer2019-KB5008996-x64.exe

Client side, I use a code very similar to this:
https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.replication.mergesynchronizationagent.synchronize?view=sqlserver-2016&viewFallbackFrom=sqlserver-2019

As I wrote, such code is perfectly working in the clients where the subscriber is SQL Express 2008 R2.

In the test client project for SQL 2019, realized with Visual Studio 2019, I use the following framework and libraries:

• MS .NET Framework 4.7.2
• nuget Microsoft.SQLServer.SQLManagementObjects version 150.18208.0
• Program Files\Microsoft SQL Server\150\SDK\Assemblies\Microsoft.SqlServer.Replication.dll
  version 15.0.4198.2
• Program Files (x86)\Microsoft SQL Server\150\SDK\Assemblies\Microsoft.SqlServer.Rmo.dll
  version 15.0.4198.2

I tried also with:
Program Files\Microsoft SQL Server\150\SDK\Assemblies\Microsoft.SqlServer.Replication.dll

Do you know, for the Web Synchronization option of Merge Replication, if there are any limitations or issues using a SQL 2019 subscriber ?

Many thanks in advance
Stefano Busolin

Answer 1

Hi, many thanks for your answer.
The certificate is properly installed. As I wrote in my question, in "my" replication system there are about 100 subscribers that are regularly replicating. But they are all SQL 2008 R2 Express subcribers.
I have such problem only in this new client environment, where i've installed SQL 2019 Express.

The exact error that the client receives is:

SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections

In my opinion, this is a proof that the client is ignoring the "Web Synchronization" option and that is trying a merge replication against the database server.
Besides, if i set a wrong and not-existing URL for the InternetUrl of the Subcription object (in my C# class), the error message doesn't change .... so the client is not trying to reach the web server, otherwise I should receive a different error.

Thanks
Stefano Busolin

Answer 2

Hi @Stefano Busolin ,

Your publisher cannot seem to local your subscriber, try to run the SQL Server browser on the subscriber so the publisher can locate the port/instance that the subscriber is running on.

Ensure use SQL authentication have necessary permission or use Windows authentication that has the same name and password at each node

Ensure the target server allows remote connections and the instance name was specified correctlym, if alias exists, create identical alias in both 32bit and 64bit

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @YufeiShao-msft ,
I think there's a misunderstanding.

This is a web synchronization, not a "normal" merge replication. The subscriber should not try to connect directly to the database server.
The error "A network-related or instance-specific error has occurred while establishing a connection to SQL Server" happens just because the subscriber is ignoring the "WebSynchronization" option and tries to connect to the DB Server, which is not exposed to the internet.

I found a similar problem for SQL 2016 in this forum (but my subscriber is 2019):

https://support.microsoft.com/it-it/topic/kb4014719-fix-0x80004002-no-such-interface-supported-error-when-you-use-rmo-to-run-web-synchronization-for-merge-replication-in-sql-server-2016-5b5aa728-e2a5-a8df-397a-bc0b46cd04b7

Thanks
Stefano

Answer 4

Did you ever figure out a solution? I am running into the exact issue with similar setup/scenario as you.