Linked Servers and Distributed Transactions

Updated : July 19, 2001

On This Page

Working with Linked Servers and Distributed Data
Managing Linked Servers
Working with Remote Servers

Networking environments are becoming more and more complex. Organizations that once may have had a single Microsoft SQL Server are now finding that they need additional servers or that they need to integrate their existing server with other heterogeneous data sources. SQL Server 2000 provides several features for integrating SQL Server databases with other SQL Server databases and with other data sources, including distributed data, linked servers, and replication. This chapter focuses on linked servers and distributed data. Distributed data includes support for distributed queries, distributed transactions, and remote stored procedure execution. These distributed data features are handled through linked servers, which can be SQL Servers or non-SQL Servers. Replication is covered in the next chapter.

Working with Linked Servers and Distributed Data

Before you use distributed data, you must configure the linked servers you want to use. Linked servers depend on OLE DB providers to communicate with one another. Through OLE DB, you can link instances of SQL Server to other instances of SQL Server as well as to other data sources.

You use linked servers to handle distributed queries, distributed transactions, remote stored procedure calls, and replication. Basically, queries and transactions are distributed when they make use of two or more database server instances. For example, if a client is connected to one server instance and starts a query that accesses a different server instance, the query is distributed. On the other hand, if the same client queries two different databases on the same server instance, the query is considered a local query and is handled internally.

Using Distributed Queries

When you execute a distributed query, SQL Server interprets the command and then breaks it down for the destination OLE DB provider using rowset requests. A rowset is a type of database object that enables OLE DB data providers to support data with a tabular format. As their name implies, rowset objects represent a set of rows and columns of data. After creating the rowset objects, the OLE DB provider calls the data source, opens the necessary files, and returns the requested information as rowsets. SQL Server then formats the rowsets as result sets and adds any applicable output parameters.

Note: With SQL-92, user connections must have the ANSI_NULLS and ANSI_WARNINGS options before they can execute distributed queries. Be sure to configure these options, if necessary. For more information, see the section of Chapter 2 entitled "Configuring User and Remote Connections."

You can create ad hoc distributed queries by creating your own rowsets. To do this, you use the Openrowset function. When you do this, you don't need to use linked servers and you can use the Openrowset function in place of a table in a query as long as you pass parameters that identify the OLE DB data source and provider.

You use the Openrowset function in the same way that you use virtual tables. Simply replace the virtual table reference with an Openrowset reference. Sample 8-1 shows the syntax and usage of Openrowset.

Sample 8-1 Openrowset Syntax and Usage

Syntax

OPENROWSET('provider_name',
{'datasource';'user_id';'password' | 'provider_string'},
{[catalog.][schema.]object | 'query'})

Usage

USE pubs
GO
SELECT a.*
FROM OPENROWSET('SQLOLEDB','Pluto';'netUser';'totem12',
'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname')
AS a
GO
SELECT o.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','C:\northwind.mdb';'Admin';", 'Orders')
AS o

Using Distributed Transactions

Distributed transactions are transactions that use distributed queries or remote procedure calls (RPCs). As you might expect, distributed transactions are a bit more involved than distributed queries. This is primarily because you need a mechanism that ensures that transactions are uniformly committed or rolled back on all the linked servers. For example, if you start a transaction that updates databases on three different server instances, you want to ensure that the transaction is committed when it's successfully completed or that the transaction is rolled back if an error occurs. In this way you ensure the integrity of the databases involved in the distributed transaction.

On SQL Server, three components are required for distributed transactions to be handled properly:

• Resource managers You must configure resource managers, which are the linked servers used in the distributed transactions. For details, see the section of this chapter entitled "Managing Linked Servers."

• Distributed Transaction Coordinator service The Distributed Transaction Coordinator service must be running on all servers handling distributed transactions. If it's not, distributed transactions won't work properly.

• Transaction manager The transaction manager coordinates and manages distributed transactions. The transaction manager on SQL Server is the Distributed Transaction Coordinator.

Note: Applications other than SQL Server can use the Distributed Transaction Coordinator. If you try to analyze Distributed Transaction Coordinator performance, you should note which applications besides SQL Server are using Distributed Transaction Coordinator.

Each server instance involved in a distributed transaction is known as a resource manager. Resource managers coordinate transactions through a transaction manager, such as the Microsoft Distributed Transaction Coordinator. You can use other transaction managers just as long as they support the X/Open XA specification for distributed transaction processing.

You handle distributed transactions in much the same manner as local transactions. Applications start distributed transactions in several ways:

• Explicitly, by using BEGIN DISTRIBUTED TRANSACTION

• Explicitly, by using OLE DB methods or ODBC functions to join a distributed transaction started by the application

• Implicitly, by executing a distributed query within a local transaction

• Implicitly, by calling a remote stored procedure within a local transaction (provided the REMOTE_PROC_TRANSACTIONS option is set ON)

At the end of the transaction the application requests that the transaction be either committed or rolled back. To ensure that the transaction is handled prop erly on all servers, even if problems occur during the transaction, the transaction manager uses a commit process with two phases:

• Phase 1: The prepare phase The transaction manager sends a prepare to commit request to all the resource managers involved in the transaction. Each resource manager performs any necessary preparatory tasks and then reports their success or failure to the transaction manager. If all the resource managers are ready to commit, the transaction manager broadcasts a commit message and the transaction enters phase 2, the commit phase.

• Phase 2: The commit phase The resource managers attempt to commit the transaction. Each resource manager then sends back a success or failure message. If all the resource managers report success, the transaction manager marks the transaction as completed and reports this to the application. If a resource manager fails in either phase, the transaction enters a pending state that must be resolved before the transaction can continue. Otherwise, the transaction is rolled back.

SQL Server applications manage distributed transactions either through Trans- act-SQL or through the SQL Server database application programming inter- face (API). SQL Server itself supports distributed transactions using the ITransactionLocal (local transaction) and ITransactionJoin (distributed transactions) OLE DB interfaces as well as the rowset objects discussed previously. If an OLE DB provider doesn't support ITransactionJoin, then only read-only procedures are allowed for that provider. Similarly, the types of queries you can execute on a linked server depend on the OLE DB provider you're using.

With distributed queries and transactions, you can use most data manipulation language (DML) commands, such as SELECT, INSERT, UPDATE, and DELETE. You can't, however, use data definition language (DDL) commands, such as CREATE, DROP, or ALTER. If you need to use DDL commands on linked servers, you may want to create stored procedures and then execute these stored procedures remotely, as necessary.

Running the Distributed Transaction Coordinator Service

The Distributed Transaction Coordinator service must run on each server that handles distributed transactions, and you'll usually want the service to start automatically when the system starts. This ensures that the distributed transactions are executed as expected. Using SQL Server Service Manager, you can control the Distributed Transaction Coordinator service just as you do other SQL Server-related services. For details, see the section of Chapter 3 entitled "Managing SQL Server Service with Service Manager."

You can also start and stop the Distributed Transaction Coordinator service in Enterprise Manager. To do this, complete the following steps:

1. Start Enterprise Manager and then access the server that you want to configure.

2. Click the plus sign (+) next to the server's Support Services folder and then right-click Distributed Transaction Coordinator.

3. To start the Distributed Transaction Coordinator service, from the shortcut menu, choose Start.

4. To stop the Distributed Transaction Coordinator service, from the shortcut menu, choose Stop.

Managing Linked Servers

To work properly, distributed queries and transactions depend on linked servers. You configure the linked servers you want to work with by registering their connection and data source information in SQL Server. Afterward, you can reference the linked server using a single logical name. If you no longer need to link to a server, you can remove the linked server connection.

Adding Linked Servers

If you want a server to be able to use distributed queries, distributed transactions, or RPCs (or all three), you must configure linked server connections to other servers. For example, if clients that access a server named Zeta make distributed queries to Pluto and Omega, you'll want to configure Pluto and Omega as linked servers on Zeta. If clients that connect to Pluto make distributed queries to Zeta and Omega, you'll want to configure Zeta and Omega as linked servers on Pluto. To add a linked server, complete the following steps:

1. Start Enterprise Manager and then access the server you want to configure.

2. Click the plus sign (+) next to the server's Security folder.

3. Right-click the Linked Servers entry and from the shortcut menu, choose New Linked Server. This opens the dialog box shown in Figure 8-1.

4. In the Linked Server field, type the name of the linked server to create.

5. If you're linking to a SQL Server, select the SQL Server option button.

   If you're linking to a different data source, select the Other Data Source option button and then configure the data source using the input fields provided. You can't configure unavailable fields for the selected provider. The available fields are summarized in Table 8-1, and you can use them as follows:

   • Provider Name Use the drop-down list box to select the name of the OLE DB provider to use when communicating with the specified linked server

   • Product Name Sets the server product name for the OLE DB data source

   • Data Source Sets the OLE DB data source, which is used to initialize the OLE DB provider

   • Provider String Sets a provider-specific connection string that identifies a unique data source

     

     Figure 8-1: In the Linked Server Properties dialog box, choose SQL Server or Other Data Source, and then set Linked Server options and security.

   • Location Sets the location of the database for the OLE DB provider

   • Catalog Sets the catalog to use when connecting to the OLE DB provider

   Real World The most commonly used option combinations are provider name and data source. For example, if you were configuring a linked server for a Microsoft Access database or a Microsoft Excel spreadsheet, you would select Microsoft Jet 4.0 OLE DB Provider and then set the data source name. With Oracle, you would select Microsoft OLE DB Provider For Oracle and then set the data source name.

6. If you're using a data source other than SQL Server, you can configure options for the OLE DB provider. Click Provider Options and then use the dialog box shown in Figure 8-2 to configure provider options.

   Configure server-specific settings using these fields from the Server Options tab:

   • Collation Compatible Set this option to enable SQL Server to send comparisons on character columns to the provider. Otherwise, SQL Server evaluates comparisons on character columns locally. Set this option only when the linked server has the same collation as the local server.

     

     Figure 8-2: Use the Provider Options dialog box to set options for OLE DB providers to configure or disable features, such as nested queries.

     Note: Collation compatible controls sort order settings. If you don't select this option, SQL Server uses the local sort order. This affects the order of result sets, and you should note it when you develop SQL Server applications or configure clients.

   • Data Access Set this option to enable the linked server for distributed query access.

   • RPC Set this option to enable remote procedure calls from the linked server.

   • RPC Out Set this option to enable RPC to the linked server.

   • Use Remote Collation Set this option to have SQL Server use the collation from the linked server's character columns. If you don't set this option, SQL Server interprets data from the linked server using the default collation of the local server instance. Note that only SQL Server databases take advantage of this option.

   • Collation Name Set this option to assign a specific collation for queries and transactions. Note that you must clear the Collation Compatible check box before you can set this option.

   • Connection Timeout Sets the timeout value for connections made to the remote server.

   • Query Timeout Sets the timeout for queries made to the remote server.

7. Click OK to create the linked server. Next, you'll need to configure security settings for the linked server. You may also need to configure Distributed Transaction Coordinator. For details, see the section of this chapter entitled "Running the Distributed Transaction Coordinator Service."

The corresponding Transact-SQL command for adding linked servers is sp_addlinkedserver. You use this stored procedure as shown in Sample 8-2.

Sample 8-2 sp_addlinkedserver Syntax and Usage

Syntax

sp_addlinkedserver [@server =] 'server'
   [, [@srvproduct =] 'product_name']
   [, [@provider =] 'provider_name']
   [, [@datasrc =] 'data_source']
   [, [@location =] 'location']
   [, [@provstr =] 'provider_string']
   [, [@catalog =] 'catalog']

Usage

EXEC sp_addlinkedserver @server='linkedservername',
   @svrproduct=", @provider='SQLOLEDB',       @datasrc='linkedservername'
GO

Table 8-1 provides a summary of parameter values you can use when configuring various OLE DB providers. The table also shows the sp_addlinkedserver parameter values to use for each OLE DB provider. Because some providers have different configurations, there may be more than one row for a particular data source type.

Table 8-1 Parameter Values for Configuring OLE DB Providers

OLE DB Provider	Product Name	Provider Name	Data Source	Other
Microsoft OLE DB Provider for SQL Server	SQL Server
Microsoft OLE DB Provider for SQL Server	SQL Server	SQLOLEDB	Network name SQL Server	Set Catalog to of Database name (optional)
Microsoft OLE DB Provider for Oracle	Any	MSDAORA	SQL*Net alias for Oracle database
Microsoft OLE DB Provider for Jet	Any	Microsoft.Jet. OLEDB.4.0	Full path name of Jet database file
Microsoft OLE DB for Jet	Any	Microsoft.Jet. OLEDB.4.0	Full path name of Excel file	Set provider string to Excel 5.0, 97-2000
Microsoft OLE DB Provider for ODBC	Any	MSDASQL	System DSN of ODBC data source
Microsoft OLE DB for ODBC	Any	MSDASQL		Set provider string to the ODBC connection string
Microsoft OLE DB for Indexing Service	Any	MSIDXS	Indexing Service catalog name

Configuring Security for Linked Servers

You use linked server security to control access and to determine how local logins are used. By default, new linked servers are set to have no security context when a user login isn't defined. This blocks access to all logins not explicitly mapped to the linked server.

To change the security settings for a linked server, complete the following steps:

1. Start Enterprise Manager and then access the local server that contains the linked server definitions you want to work with.

2. Click the plus sign (+) next to the server's Security folder, and then click the plus sign (+) next to the Linked Servers entry. You should now see an entry for each linked server you created on the currently selected server.

3. Right-click the icon for the linked server you want to configure and then choose Properties.

4. In the Linked Servers Properties dialog box, click the Security tab, as shown in Figure 8-3.

   Map local logins to remote logins, as necessary, using the following fields:

   • Local Login Sets the ID of a local login that can connect to the linked server.

   • Impersonate Select this check box to use the local login ID to connect to the linked server. The local login ID must exactly match a login ID on the linked server.

     

     Figure 8-3: Use the Security tab to control access to linked servers by mapping logins and by setting default security information for all other users.

     Note: If you select the Impersonate check box, you can't map the local login to a remote login.

   • Remote User Sets the remote user to which the local login ID maps on the linked server.

   • Remote Password Sets the password for the remote user. If it's not provided, the user may be prompted for a password.

   Use the option buttons and fields in the lower portion of the Security tab to set a default security context for all users who don't have a specific login setting for the linked server. These options are used as follows:

   • Not Be Made Users without logins aren't allowed access to the linked server.

   • Be Made Without Using A Security Context Blocks access to all logins not explicitly mapped to the linked server.

   • Be Made Using The Login's Current Security Context Logins not explicitly mapped to the linked server use their current login and password to connect to the linked server. Access is denied if the login and password don't exist on the linked server.

   • Be Made Using This Security Context Logins not explicitly mapped to the linked server will use the login and password provided in the Remote Login and With Password fields.

5. When you're finished configuring logins, click OK.

The related Transact-SQL command for configuring logins is sp_addlinkedsrvlogin. You can use this stored procedure as shown in Sample 8-3.

Sample 8-3 sp_addlinkedsrvlogin Syntax and Usage

Syntax

sp_addlinkedsrvlogin [@rmtsrvname =] 'rmtsrvname'
[,[@useself =] 'useself']
[,[@locallogin =] 'locallogin']
[,[@rmtuser =] 'rmtuser']
[,[@rmtpassword =] 'rmtpassword']

Usage

EXEC sp_addlinkedsrvlogin 'Pluto', 'true'
EXEC sp_addlinkedsrvlogin 'Pluto', 'false', NULL, 'remUser',    'tangent23'
EXEC sp_addlinkedsrvlogin 'Pluto', 'false', 'Domain\GIJOE',    'GEORGEJ', 'tango98'

Deleting Linked Servers

If you don't need a linked server anymore, you can delete it by completing the following steps:

1. Start Enterprise Manager and then access the local server that contains the linked server definition you want to delete.

2. Click the plus sign (+) next to the server's Security folder and then click the plus sign (+) next to the Linked Servers entry. You should now see an entry for each linked server you created on the local server.

3. Right-click the icon for the linked server you want to remove and then choose Delete. When prompted to confirm the action, choose Yes.

The Transact-SQL command to drop linked servers is sp_dropserver. The Transact-SQL command to drop linked server logins is sp_droplinkedsrvlogin. You can use these stored procedures as shown in Samples 8-4 and 8-5.

Sample 8-4 sp_dropserver Syntax and Usage

Syntax

sp_dropserver [@server =] 'server'
   [, [@droplogins =]{'droplogins' | NULL}]
(continued)

Usage

sp_dropserver 'Pluto', 'droplogins'

Sample 8-5 sp_droplinkedsrvlogin Syntax and Usage

Syntax

sp_droplinkedsrvlogin [@rmtsrvname =] 'rmtsrvname',
   [@locallogin =]'locallogin'

Usage

sp_droplinkedsrvlogin 'Pluto', 'username'

Working with Remote Servers

Through a remote server, a client connected to one server can run a stored procedure on another server without having to establish another connection. The originating server handles the client's request and passes it to the remote server. The remote server then executes the referenced stored procedure and returns any results to the originating server, which in turn passes those results to the client.

You can establish remote server connections only with other SQL Servers. You can't connect to other database servers, such as Oracle. If you have existing applications that use remote server connections, you can configure remote servers and use these remote servers as you have with previous versions of SQL Server. However, if you're creating new applications, you should use the new distributed data features of SQL Server 2000. You'll find that these new features are much more versatile.

Note: SQL Server 2000 supports remote servers for backward compatibility only. On SQL Server 2000, you should use distributed queries and EXECUTE statements that execute stored procedures on linked servers. Note also that previous versions of SQL Server used remote servers for replication as well. SQL Server 2000 doesn't handle replication in this way. Instead, it uses linked servers to set up replication.

Setting Up Remote Servers in Enterprise Manager

You configure remote servers in pairs. You must make each server recognize the other as a remote server, and you must explicitly permit remote connections to each server in the pair. After you've configured the necessary connections, users logging on to either server in the pair can execute stored procedures remotely.

Tip If you have an existing application that uses remote servers, you may need to check the data services gateway setup. The only Open Data Services gateways supported by SQL Server 2000 are those that ship with SQL Server 2000. If you aren't using this version, you'll need to recompile the Open Data Services with the gateway files that ship with SQL Server 2000. Trying to call SQL Server 2000 with an older version of the gateway usually results in one of the following errors:

• Error 7399, Level 16, State 1

• OLE DB provider 'SQLOLEDB' reported an error. A provider-specific error occurred.

• Error 7304, Level 16, State 1

• Could not create a new session on OLE DB provider 'SQLOLEDB.' Reported an error.

• Error 7303, Level 16, State 1

• Unable to initialize data source object of OLE DB provider 'SQLOLEDB.'

You must configure each server in the pair to accept remote connections. You configure this SQL Server property setting as explained in the section of Chapter 2 entitled "Configuring Remote Server Connections." Don't forget to stop and start the SQL Server service on each server instance.

Next, you must configure each server in the pair to accept the other as a remote server. To do this, complete the following steps:

1. Start Enterprise Manager and then access the first server in the pair.

2. Click the plus sign (+) next to the server's Security folder.

3. Right-click the Remote Servers entry and from the shortcut menu, choose New Remote Server. This opens the dialog box shown in Figure 8-4.

4. In the Name field, type the name of the second server in the pair.

5. To enable the remote server to execute stored procedures on the current server using RPCs, select the RPC check box.

   Determine how logins are used on the current server. You can configure logins in one of these ways:

   • To map remote logins to a local login with the same name, select Map All Remote Logins To and then select <Same Name> on the related drop-down list box.

     

     Figure 8-4: Configure each remote server in the pair through the Remote Server Properties dialog box.

   • To map all remote logins to a single SQL Server login ID, select Map All Remote Logins To and then use the related drop-down list box to select the login ID you want to use for remote connections.

   • To map each remote login to a separate SQL Server login ID, select Map Remote Logins To Different Local Logins. Then specify how the remote logins should be mapped. For example, to map the remote login genuser to the local login curruser, you would type webuser in the Remote Login Name field and curruser in the Local Login Name field. Repeat this process for each individual login you want to configure.

6. By default, all logins are trusted, which means that users aren't prompted for a password to access the remote server. If you want to prompt users for a password, select the Check Password check box for each type of login or login ID you want to verify.

7. Click OK and then repeat the process on the second server in the pair.

Setting Up Remote Servers Using Stored Procedures

You can also configure remote server pairs with sp_addserver and sp_configure. You use sp_addserver to add remote server connections and sp_configure to allow remote server connections. The syntax and usage for sp_addserver is shown in Sample 8-6.

Sample 8-6 sp_addserver Syntax and Usage

Syntax

sp_addserver [@server =] 'server'
   [,[@local =] 'local']
   [,[@duplicate_ok =] 'duplicate_OK']

Usage

EXEC sp_addserver Pluto, local
EXEC sp_addserver Zeta

The following example shows how you could configure the SQL Servers Zeta and Pluto as remote servers. Use this example as a starting point to configure your own server pair.

On Zeta, you would run the following SQL commands:

EXEC sp_addserver Zeta, local
EXEC sp_addserver Pluto
EXEC sp_configure 'remote access', 1
RECONFIGURE
GO

Then you would stop and restart the MSSQL Server service on Zeta. Afterward, you would configure Pluto for remote server connections to Zeta as follows:

EXEC sp_addserver Pluto, local
EXEC sp_addserver Zeta
EXEC sp_configure 'remote access', 1
RECONFIGURE
GO

Note: You drop remote servers using the sp_dropserver stored procedure. The syntax and usage for this procedure was shown previously in this chapter as Sample 8-4.

Then you would stop and restart the MSSQL Server service on Pluto. Now you can configure logins on the servers. You map remote logins to local logins using sp_addremotelogin. The syntax and usage for sp_addremotelogin is shown in Sample 8-7.

Sample 8-7 sp_addremotelogin Syntax and Usage

Syntax

sp_addremotelogin [@remoteserver =] 'remoteserver'
   [,[@loginame =] 'login']
   [,[@remotename =] 'remote_name']

Usage

EXEC sp_addremotelogin 'Pluto'
EXEC sp_addremotelogin 'Omega', 'BILLS', 'WRSTANEK'

You map remote logins to local logins with the same name by completing the following steps:

1. Execute this command on Zeta:

EXEC sp_addremotelogin 'Pluto'

2. Execute this command on Pluto:

EXEC sp_addremotelogin 'Zeta'

You map all users to a single user ID by completing the following steps:

1. Execute this command on Zeta:

EXEC sp_addremotelogin 'Pluto', 'remUser'

2. Execute this command on Pluto:

EXEC sp_addremotelogin 'Zeta', 'remUser'

You've just mapped all remote logins to the remUser login ID.

You can also map each remote login to a separate SQL Server login ID. For example, if you wanted to map the goteam login on Pluto to georgej on Zeta, you would use the following command:

EXEC sp_addremotelogin 'Pluto', 'GEORGEJ', 'GOTEAM'

By default, remote logins are configured to check passwords, and users may be prompted for a password. To set up a trusted login and to disable password checking, use the sp_remoteoption stored procedure after configuring the login. Sample 8-8 shows the syntax and usage of sp_remoteoption.

Sample 8-8 sp_remoteoption Syntax and Usage

Syntax

sp_remoteoption [[@remoteserver =] 'remoteserver']
   [,[@loginame =] 'loginame']
   [,[@remotename =] 'remotename']
(continued)
   [,[@optname =] 'optname']
   [,[@optvalue =] 'optvalue']

Usage

EXEC sp_remoteoption 'Pluto', 'GEORGEJ', 'GIJOE', trusted, true

Executing Remote Stored Procedures

You execute remote stored procedures in much the same way as any other stored procedure. The key difference is that you use a prefix to specify where the stored procedure is to be executed. For example, if you wanted to execute the totalRevenues stored procedure on a remote server's Customer database, you could use the following Transact-SQL command:

EXECUTE Pluto.Customer.dbo.totalRevenues 'December'

Or you could use

EXECUTE Pluto.Customer.totalRevenues 'December'

Here, Pluto is the name of the remote server, Customer is the database name, and totalRevenues is a user-defined stored procedure.