Sdílet prostřednictvím

  • Článek

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, 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 SELECT statement. (Use SQLNCLI and SQL Server will redirect to the latest version of SQL Server Native Client OLE DB Provider.)

sp_addlinkedserver @server = N'LinkServer',
    @srvproduct = N' ',
    @provider = N'SQLNCLI', 
    @datasrc = N'ServerNetName', 
    @catalog = N'AdventureWorks'
GO
SELECT *
FROM LinkServer.AdventureWorks.HumanResources.Employee
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 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." This restriction does not apply when an INSERT...EXECUTE statement, issued by a connection that does not have multiple active result sets (MARS) enabled, executes against a loopback linked server. Note that the restriction still applies when MARS is enabled on a connection.