SQL Server Linked Database Aliases

Question

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 though, 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?

Answer 1

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.