Distributed queries

Distributed queries

This article contains a beautiful collection related to distributed query. It is a small contains, hope you like.
Distributed queries access data from multiple heterogeneous data sources. Microsoft SQL Server supports distributed queries by using OLE DB.

Distributed query can be accessed by the:

·         From multiple instances of SQL Server.

·         Heterogeneous data stored in various relational and no relational data sources accessed by using an OLE DB provider.

OLE DB providers expose data in tabular objects called rowsets. 
The linked server allows us to create OLE DB provider.

Reference row seats from the OLE DB data sources as tables in Transact-SQL statements. Pass commands to the OLE DB data sources and include the resulting row sets as tables in Transact-SQL statements.

OLE DB defines optional Interface:

·         ITransactionLocal 
Supports local transactions in the OLE DB data source.

·         ITransactionJoin 
Lets the provider join a distributed transaction that includes other resource managers.

Note that any provider that supports ITransactionJoin also supports ITransactionLocal.

Only read operations are allowed against providers that do not support ITransactionLocal.All update operations are allowed against any providers that support ITransactionLocal.
Only read operations are allowed against providers that do not support ITransactionJoin.Providers that do not support any transactions or only support ITransactionLocal cannot participate in update operations.
If SET XACT_ABORT is ON, all update operations are allowed against any providers that support ITransactionJoin. The controlling instance of the Database Engine automatically calls ITransactionJoin in each linked server participating in an update operation to enroll it in the distributed transaction. Microsoft Distributed Transaction Coordinator (MS DTC) then either commits them or rolls them back when the controlling server indicates that the transaction is either committed or rolled back.
If SET XACT_ABORT is OFF, the linked server must also support nested transactions before update operations are allowed. Nested transactions are supported if the provider supports calling ITransactionLocal::StartTransaction when there is already an existing transaction for the session. This allows SQL Server to roll back individual statements in distributed queries without rolling back the entire transaction.