Post reply

Best practices - Linked server naming convention

  • November 6, 2015 at 8:29 pm

    #306417

    How do you name sql linked-servers in your company ?

    we have many legacy systems (random naming), but mostly newer systems follow this pattern

    <ENVIRONMENT><APPLICATION><FUNCTION><VIRTUAL><NUMBER>

    e.g.

    PRD-FAX-DB-V01, TST-PAY-WEB-P02 etc.

    We have link-servers all over the place. The ideal naming convention should satisfy following:

    - developers should not have to modify code when deploying between environments DEV->TEST->PROD. This rules out using actual server name as linked-name.

    - the name should easily identify actual server without much mental translation. This rules out relevant but generic names like FAXSERVER or PAYSERVER

    - also, if the name could state that it is a linked-server e.g starting with LINK_, it helps when reading the code.

    So, I was thinking the link-name should simply remove the environment :

    e.g LINK_FAX_DB_V01

    that way, on DEV box, the underlying sql data source could point to the dev server, while on test, it could point to test server etc. without having to change code, and also knowing which server it's pointing to by just adding a TST or PRD in front-of it.

    What do you think ? How are your linked-servers named ?

  • November 17, 2015 at 7:58 pm

    #1840518

    sqld-_-ba (11/6/2015)

    - developers should not have to modify code when deploying between environments DEV->TEST->PROD. This rules out using actual server name as linked-name.

    - the name should easily identify actual server without much mental translation. This rules out relevant but generic names like FAXSERVER or PAYSERVER

    I strongly disagree with the second notion above. If you even move something to another server, you'll find out why the hardway.

    I strongly recommend the first method. Pick a good generic name representing the function of the linked server and use it in all 3 environments pointing, of course, to the correct server for the given requirement. If you want to know which server a linked server is pointing to, either gen the code or simply look it up in the Object Explorer. You'll find that not many people will need to actually do that.

    Also, NEVER use 3 or 4 part naming in code. If you need your code to point to a linked server, 2 part named synonyms. That way, it doesn't really matter what you call your linked servers... just change the 2 part named synonym to point to the correct linked server depending on the environment you're in.

    The obvious exception to that is if you use OPENROWSET or similar in which case the first option is the most important.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply