none
Distributor Publisher Consolidation, Multiple Linked Servers to same Subscriber

    Question

  • We are combining the distribution and publicaiton functions to a single server. Originally we had them on separate servers. Under the original scenario the publisher had a linked server to subscriber1 created thru replication, and the publisher had a linked server to subscriber1 one in order for the applicaitons to run stored procedures and functions on the subscriber both the original distributor and publisher had the server named subscriber1. The applications use a specific sql user to access procs and functions from the publisher. Now, when we combine the distributor and publisher, replication creates a linked server named subscriber1 so I can't create the linked server named subscriber1 now. What would be my options here, setting up a 2nd linked server to the same server subscriber1 on the new combined dist/pub since access has to be done via different accounts? Anyone aware of options or issues I will encounter here? Thanks
    Thursday, August 16, 2018 1:45 PM

Answers

  • Create another linked server using an IP address, wrap the IP address in square brackets ([]) and it will work.
    • Marked as answer by Abeljdang Thursday, August 16, 2018 4:31 PM
    Thursday, August 16, 2018 3:27 PM
    Moderator

All replies

  • If you move the distributor to the publisher on the original server you can use the existing linked server. If you use a new server as both publisher and distributor you can create the same name of existing linked server name as the one on the original one.

    A Fan of SSIS, SSRS and SSAS

    Thursday, August 16, 2018 2:01 PM
  • Thanks, but I have already set up the distributor and it has the subscriber1 linked server set up for replication. I need to address how to handle the applications that use the same named linked server (subscriber1) under a different security context as it was when the distributor and publisher were on 2 servers. When I script the linked servers I can see the sp_addlinkedsrvlogin has different values for the login used. the linked server set up via replication uses "be made using the login's current security context" and for the linked server set up for the application access  uses "be made using this security context" which is a specific sql login.
    Thursday, August 16, 2018 2:09 PM
  • Not sure that I understand your requirements. So both original publisher and distributor have the same names of the linked server to the subscriber?

    A Fan of SSIS, SSRS and SSAS

    Thursday, August 16, 2018 2:22 PM
  • Yes because they were on 2 separate servers so they could have the same name for a linked server. Now, the distrubutor and publisher will be the same server, so I can't use the same name for a linked server as I need a different security context/login for access from the applications that use the publisher AND the publisher will also now be the distributor AND replication is using the linked server named subscriber1  for its distribution function. Below is a picture of the situation. The name of the subscriber1 is Server3 in the diagram below.

    Thursday, August 16, 2018 2:49 PM
  • The server names are the same in the column "Subscription Existing"?

    A Fan of SSIS, SSRS and SSAS

    Thursday, August 16, 2018 2:59 PM
  • Yes because there are 2 separate servers accessing server3 so you can use the same linked server name. 

    Thursday, August 16, 2018 3:03 PM
  • Create another linked server using an IP address, wrap the IP address in square brackets ([]) and it will work.
    • Marked as answer by Abeljdang Thursday, August 16, 2018 4:31 PM
    Thursday, August 16, 2018 3:27 PM
    Moderator
  • So on the server with publisher/distributor you want to set up two linked servers with the same name but with the different security contexts? I am not sure if you can do that.

    A Fan of SSIS, SSRS and SSAS

    Thursday, August 16, 2018 3:36 PM