SQL Server Linked Server best practices

This post is one in a series of posts about SQL Server best practices. I've posted best practices about the following subjects, so far:

• General best practices
• Naming conventions
• Tables
• Columns
• Stored procedures.

This post is about linked servers. One note of appreciation for Arjan  Fraaij, who is a major contributor to this post. One of the serverobjects is linkedserver and is used for using data from a another server or storing data to another server. There are also other options available, like SSIS and therefore it's good to make a list of pros and cons of linked server. So when do you use a linked server? The main reason is that you need the information from another server immediately and in other cases i would suggest using SSIS. 

Below you can see diagram for a DTAP environment and databases A and B. On Server A a linked server is created to server B. This should be done in every environment : Development, Test, Acceptance and Production. It should not be allowed to create linked servers from a Test environment to a Acceptance environment.

Here are the Linked Server best practices:

• Naamconvention according to CamelCase.
• De namingconvention of a linked server is this::  ls<DatabaseName>
• eg. lsDatabaseB.
• A linked server should be related to a database (and not a server)
• A linked server name has never a servername incorporated.
• A linked server should only be created based on integrated security.
• A linked server can only point to a database in the same environment.
• If used in your organization , use a C-Name. This will enhance more flexibility in case migration scenarios.

Greetz,

Hennie