Linked Servers help

Question

Linked Servers help

matthewful3

SSC Veteran

Points: 265
More actions
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.

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

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

rvsc48 SSCertifiable Points: 7685 More actions · Answer 1

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.

matthewful3 SSC Veteran Points: 265 More actions · Answer 2

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.

rvsc48 SSCertifiable Points: 7685 More actions · Answer 3

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

crow1969 Hall of Fame Points: 3001 More actions · Answer 4

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.

matthewful3 SSC Veteran Points: 265 More actions · Answer 5

matthewful3

SSC Veteran

Points: 265

September 7, 2016 at 2:39 pm

#1899673

Thanks crow1969 that worked like a charm.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

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