:::: MENU ::::

Concerns to keep in mind and analyze before going for linked servers

  • Sep 26 / 2013
  • 0
dbDigger, Linked Servers and remote data

Concerns to keep in mind and analyze before going for linked servers

Linked Servers are used in SQL Server to get connect and retrieve the data from outside your local server instance. We may link to other SQL Server as well as non SQL Server DBMS. It is a mature and proven technology. Rather than discussing the configuration method i will discuss some concerns that should be analyzed and take care while implementing the Linked Server.

  • Joining the data between local and linked server databases will result in poor performance. And performace may go worst if link is on WAN.
  • Execution of procedure on remote server will leak memory on local server for each call from it. It is due to return status of procedure even if it is not used by the caller. This leak is very minimal about 40 Bytes per call but may accumulate over the time.
  • SQL variant data types also cause minor memory leaks for local server. Large character strings of this type may accumulate considerable memory leaks over the time.
  • Performance – Optimizing is always a challenge and distributed queries are even more difficult. An extra complication with linked servers is that to get statistics from the remote server, OLE DB runs DBCC SHOW_STATISTICS. But this command requires ‘sysadmin’ permission or membership in ‘db_owner’ or ‘db_ddladmin’, which is not expected from a regular user to have, no matter he accesses the linked server with his own credentials, or through a proxy account defined.
  • There are a number of SQL Server features that are not supported with linked servers
    • XML data type
    • Table-valued parameters
    • CLR User-defined data types
    • Cannot call table-valued functions with four-part notation
    • MAX parameters cannot be OUTPUT
    • BULK INSERT into linked server is not supported. More details here
  • Error messages with linked servers and distributed transactions are often very obscure. Often it includes an OLE DB error, which is difficult to understand. Also, the useful part of the error message is often only raised with level 10, so you cannot catch it. A prime example is when a remote call times out.
  • Distributed transactions – The MSDTC has following problems:
    • Cannot use SAVE TRANSACTION in a distributed transaction
    • Some errors when a distributed transaction fails are not catchable
    • Once you have rolled back locally, there is very little you can do, for instance you cannot call a stored procedure
  •  Security and permissions for linked servers will require extra effort and skill.
Consult us to explore the Databases. Contact us