Post reply

Linked Servers help

  • September 7, 2016 at 10:41 am

    #329944

    Hello all,

    I am working on migrating from 2012 to 2014 and I know we have 3 linked servers.

    Let me give some background. I have 3 instances 1 is already on 2014 and the other two are going to be upgraded to 2014 soon, they are all linked together. I am assuming all linked server will break after the upgrade so this needs to be corrected.

    Now I have never used linked servers but I have tested creating a linked server where the linked server name is the name of the instance but looking at how the original ones were setup it looks like it is using SQL Server as the Server type but it is using a different name (CONFIRM) but connects to a named instance (instance1) So I am wondering how this is connecting to the other instance.

    I hope this isn't to confusing but to clarify I do not have a named instance called CONFIRM just instance1, instance2, instance3. And when I look at the properties under CONFIRM it shows it is using SQL Server as the Server type.

  • September 7, 2016 at 12:02 pm

    #1899642

    Hi, can you script out the Linked Server definition on the existing 2012 instance and paste the results? From Mang Studio>ServerObjects>LinkedServers>Highlite Linked Server> rt click>ScriptLinkedServerAs.

  • September 7, 2016 at 12:20 pm

    #1899644

    Yes, I can do this but I get an error when I try to test the connection but I think I found my issue. I need to setup alias which is new to me so any advice would be helpful.

  • September 7, 2016 at 12:27 pm

    #1899646

    I would simply follow the instructions in this link: https://www.mssqltips.com/sqlservertip/1620/how-to-setup-and-use-a-sql-server-alias/

  • September 7, 2016 at 12:54 pm

    #1899653

    You don't need an alias at the OS level in order to do this (although, if you are using SSIS packages on the server, it may help). sp_addlinkedserver will be a friend in this. to just create the server (and no security around it) you just need:

    declare @servername varchar(50)

    declare @remoteserver varchar(200)

    set @servername = 'CONFIRM'

    set @remoteserver = 'instance1\instancename'

    exec sp_addlinkedserver

    @server = @servername,

    @srvproduct = '',

    @provider = 'SQLNCLI',

    @datasrc = @remoteserver

    Setting up the security is another matter, but can also be scripted.

  • September 7, 2016 at 2:39 pm

    #1899673

    Thanks crow1969 that worked like a charm.

  • September 8, 2016 at 6:10 am

    #1899766

    farhan.appxtech (9/8/2016)

    From very long time I was looking for this topic on linked servers. Kepp updating such relevant posts.

    Reported as spam.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 7 posts - 1 through 6 (of 6 total)

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