Linked Server Considerations in a Clustered SQL Server

Linked Server Considerations in a Clustered SQL Server

When linked servers are configured in a clustered SQL Server against OLE DB providers not shipped with Microsoft® SQL Server™ 2000, make sure that the OLE DB providers are installed in all nodes of the cluster. In addition, 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 given node of the cluster.

This 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 SELECT statement.

sp_addlinkedserver @server = N'LinkServer',
    @srvproduct = N' ',
    @provider = N'SQLOLEDB', 
    @datasrc = N'ServerNetName', 
    @catalog = N'Northwind'
GO
SELECT *
FROM LinkServer.Northwind.dbo.Shippers
GO

Loopback Linked Servers

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 sp_addlinkedserver stored procedure on a server named MyServer defines a loopback linked server:

sp_addlinkedserver @server = N'MyLink',
    @srvproduct = N' ',
    @provider = N'SQLOLEDB', 
    @datasrc = N'MyServer', 
    @catalog = N'Northwind'
GO

Transact-SQL statements that use MyLink as the server name loop through the SQLOLEDB provider and back to the local server.

Loopback linked servers cannot be used in a distributed transaction. Attempting a distributed query against a loopback linked server from within a distributed transaction causes an error:

Msg: 3910 Level: 16 State: 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in use by another session.