Jeff Moden (12/29/2014)
Phil Factor (12/29/2014)
We use only the 2 part name and let the synonyms do the rest of the work. Do you know of a way to do a SELECT from a LinkedServer directly without using 4 part names in code other than through the use of a Synonym?
This seems a rather better interview question to me than the original.
I reckon you've added a SQL Code Smell here 'Accessing a linked server directly via a four-part name rather than abstracting this via a Synonym'.
Agreed. The fly in the ointment would be any of the 4 parts of the name. Scott's absolutely correct that if only the server you want to point to changes, that it wouldn't be a problem because, ostensibly, ALL of the code would be 4 part named to the same linked server. Just change where the linked server points to and keep it named the same (takes a drop and recreate IIRC).
I'm also saying to get out of the mindset of the linked server name being a physical server. Instead, name it by application or function. Only force of habit causes people to directly tie a linked server name to a specific physical server. So just don't do it.
The bugger is that there's no guarantee that any of the other 3 parts will remain the same on the remote server. For example, I live in one of those freakishly over cautious worlds (not my idea but I enforce it for the boss) where every database in the Development environment is suffixed with "_Devl", the DBs in Staging environment are suffixed with _Staging, the QA environment with "_QA", and the Production environment with "_Prod". Having a consistent name for the Linked Server will certainly work but not for any of the rest of the stuff. I have to have synonyms that allow the 2 part naming convention in the code to stay the same no matter what.
Anybody know how to alias the names of databases, schemas, and tables without using a synonym or pass-through view?
Hmm, yeah, you've created a real mess for yourself there. So I guess all your cross-database references must be by synonym only, even for utility/shared dbs that just contain common functions, etc.?! That sounds like a royal pain.
Agreed. I don't use physical names for linked servers. That would be insane.
Heh.... and, no... I didn't make the mess of the insane Dev/QA/Stage/Prod naming convention. It was here when I got here and I've been fighting against it ever since. A couple of previous jobs had the same insanity and I was able to convince them otherwise. Not sure why they have such a problem making the leap here.
To make matters worse, we have special copies of the same databases in the same servers with different suffixes. Like you said, quite the mess.
They've also hog tied me a bit when it comes to utilities. I'm not allowed to have a util DB in staging or production. All functions, etc, must be in the database they're called from so that the databases are "not dependent on other databases". Yeah... I know. Doesn't make any sense in light of the synonyms.
The good part is, they let me lock down the production databases/server (couldn't have done that without full management support). Developers cannot promote their own code and all code is peer reviewed by me (not a huge shop). If I'm not available (which is rare), there are two other people that have been trained up to do the reviews. With that in mind, I don't complain so much about the Dev/QA/Staging environments.