Linked Server Considerations in a Clustered SQL Server
When linked servers are configured in a clustered SQL Server against OLE DB providers that are not shipped with SQL Server 2005, make sure that the OLE DB providers are installed in all nodes of the cluster. Also, any properties that define the linked server should be location transparent; they should not contain information that assumes SQL Server is always running on a specific node of the cluster.
The following example defines a linked server against a server running SQL Server and references one of the remote tables using a four-part name in a
sp_addlinkedserver @server = N'LinkServer', @srvproduct = N' ', @provider = N'SQLNCLI', @datasrc = N'ServerNetName', @catalog = N'AdventureWorks' GO SELECT * FROM LinkServer.AdventureWorks.HumanResources.Employee GO
Linked servers can be defined to point back (loop back) to the server on which they are defined. Loopback servers are most useful when testing an application that uses distributed queries on a single server network.
For example, executing the following
sp_addlinkedserver stored procedure on a server named
MyServer defines a loopback linked server:
sp_addlinkedserver @server = N'MyLink', @srvproduct = N' ', @provider = N'SQLNCLI', @datasrc = N'MyServer', @catalog = N'AdventureWorks' GO
Transact-SQL statements that use
MyLink as the server name loop through the
SQLNCLI provider and back to the local server.
Loopback linked servers cannot be used in a distributed transaction. Trying a distributed query against a loopback linked server from within a distributed transaction causes an error, such as error 3910: "[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in use by another session."