How to choose a name for Linked Server object in SQL Server

At first glance, there is nothing to talk about. Linked server object name defined as a sysname datatype and it means that it is an Unicode string with no more than 128 characters. Hopefully, you won’t use even half of the maximum length. Shorter is better, but there are few tips that might be helpful:

  1. Don’t use generic names like ‘LINKEDSERVER’ or ‘APP’. The name has to give a basic idea where this linked server pointing to without going into details. Not every user (even developers) can see options of linked server objects and they’ll start bugging you or other DBAs with questions.
  2. Don’t use common words that can be found anywhere in the code. Sooner or later you’ll need to find all references to this linked server in a database and it will be painful to look for all occurrences of the same string, especially if it’s a part of the tables’ or columns’ names.
  3. Don’t use an instance name as a linked server name. When you start moving databases around you will quickly find out that the instance name is not relevant anymore. Even moving a copy of databases from production to development or test environments will become problematic.
  4. Create a new linked server for every new application even if an existing linked server object can serve the purpose. In many cases you will find that permissions required for one application are not exactly the same as for another one. As the result one application or another will have higher level of access that it’s required. Troubleshooting on another side of the database link also will be easier if you have separate users associated with different applications.
  5. Ideally a name should identify an application name of the database this linked server pointing to and a name of the application it’s going to be used by. In this case everybody will see where they are going to and which application it should be used in.
  6. Stick to a naming convention in all instances of SQL Server. So, if you use ‘DYNSAGE’ linked server everybody would now that it is used ‘from’ Microsoft Dynamics ‘to’ Sage ERP and not other way around.

If you have other tips please leave them in the comments section. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>