The question for the week has to do with linked servers. We used to define linked servers as the name of the server they were connecting to as that made it easy to see where they were going and other users could reuse the links when needed. The first issue we had with this was the fact that we have different server names based upon environment. This meant that your code in development would have one type of server name, which meant your code in test would need to be changed, as well as prod.
The idea of maintaining three different versions of code was not pleasing to anyone, so we made a switch and started using linked servers defined with the name of a specific application. This meant we would be able to reuse code easily as long as the links were named the same in each environment. Of course, this has led to a proliferation in the number of links defined to any particular instance. So, if three different applications needed to link out to another server, we would have three different links defined.
So my question is: What is the standard for naming linked servers in your shop?
Thanks for the conversation! I realized it was easier to blog about my experience than try to include it in the comments here.
http://wendyverse.blogspot.com/2009/05/linked-server-naming.html
We went through both of the standards that you mentioned, before settling on a compromise. Since our envioronments were completely mirrored, and the only difference between the dev SQL servers and the Production SQL Servers was a D or P.
We ended up with a convention where the linked servers are the full server name with an X in place of the environment character.
We used the convention for linked servers for the very same environment reasons.
We also kept a database of global environment variables. One of the pieces was the name of the linked server.
Not saying it’s right or wrong, just something else I’ve encountered.
We have three main SQL Servers, call them A B and C. Each has linked servers defined for the other two, using the server name. So server A has link B pointing to server B, link C pointing to server C, etc.
Our test environment has three servers with a T prefix (or as substitute for a production P). The linked servers have the same name as the production links, but point to the test servers. So server TA has link B pointing to TB, link C pointing to TC, etc.
This way code can be moved between environments without editing.
thanks scott, that setup would seem to work very well