After much testing and soul searching I built a methodology that provided a basis for all server classes code to work in all environments unchanged.

From SQL 2005 on we had the option of using synonyms which are effectively references they turn:

LinkedServer.Database.Schema.Object into Schema.Object

I only use them for border references, no database code ever references a user defined database directly, they ALWAYS occur through a synonym, only system databases tempdb, master, and msdb can be referenced directly and only for MS provided code.

What this means is that the code doesn't have to change if a database name changes for even if a database is moved to another server only the border references change.

In previous versions of SQL my linked servers on each server were the same names, defining a connection to a different server class. I differentiated them on each server by using server aliases that pointed at the correct server, ie: Linked server name ProcessMaster existed on all servers that filled the server class of Process Front-End, but an alias at the machine level made sure that on dev machines that ProcessMaster always pointed at the DEV Process Master and on QC machines pointed at QC Process Master machines. With the use of synonyms this isn't required but I still kind of like it.. Mileage may vary..

My goal is for the code itself to NEVER change in any of the environments, what works in Dev works in QC works in Demo works in Prod.

I try to leave the number of potential changes at an absolute minimum since every change is a potential risk. Also once environments are setup unless you make a process change the border objects are not likely to change..

I hope this provides some insight, I am unsure what the MS best practices folks would think about this, I just know that it has served me well and I have been able to deliver fairly large upgrades with little or no issue. As I said before, mileage may vary..

CEWII