Unable to create Linked Server via GUI or sp_addDistributor

  • I'm trying to get replication up and running in an AG environment.  As part of the process I'm running sp_addDistributor on the publisher instances and using the distributor listener name for the @distributor param.  On node A the procedure call fails with a generic "login timeout expired" error.  On node B the procedure runs to success.  After further testing I've found that I can create a linked server on node A, using the GUI, if I use the explicit server\instance name, but I cannot do so if I point to a listener.  Further testing revealed that if I hit the "Script action to new window" button after the GUI fails I can successfully run the script it creates and the linked server is successfully created.  It seems that sp_addDistributor and the New Linked Server GUI are doing something different behinds the scenes but I cannot narrow down what might be causing the failures.  Any thoughts on why one node would have no issues at all with the GUI and one node fails every time I try to use it pointing at a listener?

     

    Thanks!

  • Update... If I add the default port number to the end of the Data source field on node A I can get the linked server to work.  Port number is not needed on node B so I'm not sure yet what is allowing B to communicate properly and A not to allow it.

  • Figured out the default port for the SQL Native Client was incorrectly set.  Case closed.

  • I would check to make sure all the servers in the mix are 2017 with CU6. And I'm not sure which port you are referencing with the port number but it could be related to how you set up the listener with the port number - just as referenced in step 4 in the example of the documentation to set this up:

    Set up replication distribution database in Always On availability group

    In terms of the differences with SSMS and t-sql who knows. It really depends on what version of SSMS. It's not worth trying to figure those SSMS issues out other than to try the current version of SSMS and hope it doesn't break anything else or remove functionality you were used to having available. You could try to run an extended events session or a trace to try to see how the two are done differently.

    Sue

     

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply