none
Merge Replication - Cannot Connect to Subscriber RRS feed

  • Question

  • I have two SQL Express databases with Merge Replication provided by SQL Server SE. Today we moved the SQL Express computers to a new location and DHCP assigned new IP addresses to them. At first both subscriptions failed and I eventually realized this was because the the SQL Server SE was not on the same domain and I had solved this originally by adding entries into the Hosts file on the SQL Server SE computer. After that was corrected both SQL Express computers could be pinged by name. Also both can be connected to from  SSMS on the SQL Server SE, by name and using the same SQL login and password.

    The puzzling thing is that one of the subscriptions now works and the other still fails,with the error "The process could not connect to Subscriber 'xxxxxxxxx\sqlexpress'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20084)"

    I have read through all the info that I can find on this error and all the suggested fixes are already implemented, and of course, they were both working before the computers were moved. I have tried deleting subscriptions and using the wizard to recreate them with only the the server names being different, but result is the sane, one works and one fails.

    If anyone has any suggestions as to what I need to check on the failing SQL Express, or its subscription, I would appreciate it. As I have said, I have already checked the usual suspects such as the SQL Express Network Configurations, Firewall rules etc.


    R Campbell

    Friday, January 19, 2018 12:36 PM

Answers

  • It turns out that I had used an Alias (in SQL Server Configuration) for one of the SQL Express servers as part of my early attempts to get merge replication working across domains. It seems that the Alias takes precedence over the Hosts file (for replication at least) and this was causing the old IP address to be used for the SQL Express that could not be replicated. After deleting the Alias, it all started to work again. A lesson learned the hard way.

    My question now is whether or not Aliases or the Hosts file should be used under such circumstances? I must admit that I haven't actually tested whether or not Aliases would do the same job, I am just relieved to see it all working again, at the moment.


    R Campbell


    Sunday, January 21, 2018 9:57 AM

All replies

  • This looks like a name resolution problem. Can you connect to the problem subscriber from the publisher using SSMS?

    Friday, January 19, 2018 3:59 PM
    Moderator
  • This looks like a name resolution problem. Can you connect to the problem subscriber from the publisher using SSMS?

    That's the most puzzling part of this. I can ping both SQL Express computers (by name) and I can connect to both of them (again by name) using SSMS with the same SQL login and password. That login has dbowner rights on both databases.

    Both computers have the same SQL Server Network Configuration for TCP/IP (port 1433 etc), SQL Server Browser is enabled and ports 1433 and 1434 are enabled in both Firewalls. One odd thing is that TCP 1, on both computers, has an IP address that I don't recognize. I did try changing this to the SQL Server SE IP on the SQL Express that was failing but that made no difference.

    I would have thought also, that a correctly configured Hosts file would solve any name resolution problems. beyond doubt.

    Correction: I changed the TCP 1 IP on teh SQL Express that was failing to be that of the SQL Express. I have since change it back to original.


    R Campbell





    • Edited by Dick Campbell Saturday, January 20, 2018 1:39 AM
    • Marked as answer by Dick Campbell Sunday, January 21, 2018 9:45 AM
    • Unmarked as answer by Dick Campbell Sunday, January 21, 2018 9:45 AM
    Friday, January 19, 2018 8:02 PM
  • It turns out that I had used an Alias (in SQL Server Configuration) for one of the SQL Express servers as part of my early attempts to get merge replication working across domains. It seems that the Alias takes precedence over the Hosts file (for replication at least) and this was causing the old IP address to be used for the SQL Express that could not be replicated. After deleting the Alias, it all started to work again. A lesson learned the hard way.

    My question now is whether or not Aliases or the Hosts file should be used under such circumstances? I must admit that I haven't actually tested whether or not Aliases would do the same job, I am just relieved to see it all working again, at the moment.


    R Campbell


    Sunday, January 21, 2018 9:57 AM