Creating a Linked Server and Database Alias

Question

I have two servers Server1 and Server2

On both servers i have a Database called QAI

is there a way that i can create a linked server with an alias from Server2 to Server1 but instead of Server1.QAI.dbo.tbBlah

Have it aliased as QAI.dbo.tbBlah

This is needed due to the QAI DB on server2 failing and needing to fall back to the Server1 instance while it is fixed

Thanks

Did you ever find a good solution for this? – Niels Brinch May 23 '13 at 06:10 — Niels Brinch, May 23 '13 at 06:10

score 12 · Answer 1 · edited Apr 28 '15 at 14:45

This works. Tested on a SQL2012

http://alexpinsker.blogspot.se/2007/08/how-to-give-alias-to-sql-linked-server.html

There are no problems to add SQL linked server to the specific host running it. But what if you need to give it an alias, rather than use hostname as linked server name? Here is how to do it:

1) Step 1:
• In SQL Server Management Studio open Linked Servers and then 'New Linked Server'.
• Inside of appeared wizard – Select the General tab.
• Specify alias name in "Linked server" field.
• Select SQL Native Client as provider.
• Add sql_server in "Product Name" field (that's the magic).
• In "Data Source" – specify name of the host to be used as linked server.

2) Step 2:
• In Security tab – specify proper security options (e.g. security context)

3) Step 3:
• In Server Options tab – put "Data Access", RPC, "Rpc Out" and "Use Remote Collaboration" to be true.

4) Step 4:
• Enjoy.

Welcome to Stack Overflow! Whilst this may theoretically answer the question, [it would be preferable](//meta.stackoverflow.com/q/8259) to include the essential parts of the answer here, and provide the link for reference. — Sam Hanley, Apr 28 '15 at 13:44

score 4 · Answer 2 · answered Dec 07 '12 at 17:27

4

If you don't want to specify the database name when querying a table you can create synonym for it.

CREATE SYNONYM [dbo].[DimProduct]
FOR
[AdventureWorksDW].[dbo].[DimProduct]

this will create DimProduct in your Synonyms folder

answered Dec 07 '12 at 17:27

BraveNewMath

8,090
5
46
51

4

Note that synonyms have to point to low-level objects; you cannot create a synonym for a database or schema. If you have 42 different tables in Server1.QAI, you'll have to create 42 synonyms, and maintain them as objects are added. – Aug 06 '13 at 17:03

score 1 · Answer 3 · answered Jul 24 '12 at 09:44

I'm afraid it's not possible, you'll always have to refer to Server1 explicitly. But if your application is a on server and not on many clients, so there is a limited number of usage points, you can modify the connection string there, so the app would refer to server1 instead of server2 temporarily.

Creating a Linked Server and Database Alias

3 Answers3

Linked