Distributed Data Processing

SQL Server provides features such as linked servers, remote stored procedure calls, and two-phase commit protocol that enable you to easily manage and use data in distributed environments. Microsoft Distributed Transaction Coordinator (MS DTC) was designed to be the vote collector and coordinator of transactions, and it allows many different types of systems to participate, laying the foundation for ACID transactions among heterogeneous systems.

A system participating in a transaction coordinated by MS DTC manages its own work and is called a resource manager . This resource manager system communicates with MS DTC, which coordinates all the resource managers participating in the transaction to implement the two-phase commit protocol. Distributed transactions honoring the ACID properties are supported as a whole: the entire distributed transaction at all sites either commits or aborts.

In the first phase of the two-phase commit protocol, all participating resource managers (that is, those that have enlisted in the transaction) prepare to commit . This means that they have acquired all the locksand resources they need to complete the transaction. MS DTC then acts as a vote collector. If it gets confirmation that all participants are prepared to commit, it signals to go ahead and commit.

The actual COMMIT is the second phase of the protocol. If one or more participants notify the system that it can't successfully prepare the transaction, MS DTC automatically sends a message to all participants indicating that they must abort the transaction. (In this case, an abort, rather than a commit, is the second phase of the protocol.) If one or more participants don't report back to MS DTC in phase one, the resource managers that have indicated they're prepared to commit (but haven't committed because they haven't received the instruction to do so yet) are said to be in doubt . Resource managers that have transactions in doubt indefinitely hold the locks and resources necessary to ultimately commit or roll back the transaction, preserving the ACID properties. (SQL Server provides a way to force in-doubt transactions to abort.)

Another important distributed capability allows SQL Server to issue a remote procedure call (RPC) to another server running SQL Server. Remote procedure calls are stored procedures that can be invoked from a remote server, allowing server-to-server communication. This communication can be accomplished transparently to the client application, because the client can execute a procedure on one server, and that procedure can then invoke a procedure located on a different server.

Using RPCs can easily extend the capacity of an application without the added cost of reengineering the client application. Remote procedure calls optionally can be coordinated by the MS DTC service to ensure that the transactions maintain their ACID properties. The default behavior is to not execute the RPC in the context of a transaction so that if the local transaction is rolled back, work done by the RPC will still be committed. This behavior can be overridden by using the command BEGIN DISTRIBUTED TRANSACTION, or by setting the configuration option remote proc trans to 1.

Distributed data can also be managed by defining and accessing linked servers . A linked server can be any data source for which an OLE DB provider exists. The most commonly used OLE DB providers are the SQL Server OLE DB provider; the Jet provider, which allows you to connect to Microsoft Access databases; and the Open Database Connectivity (ODBC) provider, which allows you to connect to any ODBC data source.

A linked server is a logical name , defined using the stored procedure sp_addlinkedserver . The information you provide when you define the linked server includes the name and location of the datasource to connect to. Once you've defined the name, you can use that name as the first part of a four-part name for any object, such as tables, views, or stored procedures. Objects on the linked server can be queried as well as modified, and they can be used in joins with local objects. The types of queries possible on the linked server depend on the capabilities of the particular OLE DB providers. Some providers, such as the one for SQL Server, allow all data modification operations and full transaction control across linked servers. Others, such as the OLE DB provider for text files, allow only querying of data.