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

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.

Answer 2

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