dinsdag 10 mei 2011

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:
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

Geen opmerkingen:

Een reactie plaatsen