SQL Server Linked Database Aliases

Is it possible to have not only a LinkedServer, but linked database and server? My situation is that of having one environment with a setup of:

ServerX: DatabaseOne, DatabaseTwo

and another environment of:

ServerY: MyDatabaseOne, MyDatabaseTwo

Now, DatabaseOne and MyDatabaseOne are exactly the same, as are DatabaseTwo and MyDatabaseTwo. I have a stored procedure that does some basic table updates from one db to the other on a given server, and I want it to be able to work in both evironments. I have set up a linked server so my reference can be:

MyLinkedServer.DatabaseOne.dbo.MyTable or MyLinkedServer.MyDatabaseOne.dbo.MyTable

Even with this tho开发者_StackOverflowugh, I will need to change the SP when I switch environments to change the db names, is there any way of setting up a Database Alias, so that I can write:

SELECT * FROM MyLinkedServer.MyLinkedDatabase.dbo.MyTable

So that as long as LinkedServer and (if possible) LinkedDatabase are set up in each environment, the SP will work without changing when switching environments?

You can avoid dynamic sql by Aliasing the server by following these steps (credits to this post)

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 – set "Data Access", "RPC", "RPC Out" and "Use Remote Collation" to true.

4) Step 4:

• Enjoy

You can take it one step further and skip specifying the database name when querying a table you can create synonyms for each object like this:

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

this will create DimProduct Synonyms in your database. As far as I know, you can't create a synonym to a database.

I think the short answer is no. I don't believe database aliasing is supported yet.

Dynamic SQL could do it. You could inject the database name etc and execute your query.

Found the answer to my problems. It's slightly more complex then just creating a Linked Database, but Synonyms were the answer.

http://msdn.microsoft.com/en-us/library/ms190626.aspx

I know, its an old question, but (at least in SQL 2014, but I guess in older versions too) you can set up a linked server with an "alias" and remote queries (OPENQUERY) for the database part.

USE [master]
GO

EXEC master.dbo.sp_addlinkedserver @server = N'MyAliasServer', @srvproduct=N'SQLSERVER', 
             @provider=N'SQLNCLI', @datasrc=N'MyRealServer1', @catalog=N'database1'

Of course you have to set up the security etc. too. After that you could run

SELECT * FROM OPENQUERY(MyAliasServer, 'select * from dbo.MyTbl')

without specifying a database name (because it is already defined in the linked server connection).

If you script out the linked server (context menu) and replace the server name / database name you could do a switch over between the two servers just by dropping the current and create the other linked server.