SQL Server™ 2005 Bible

Chapter 15. Working with Distributed Queries

In This Chapter

• Understanding distributed queries

• Making the connection with remote data sources

• T-SQL distributed queries

• Pass-through queries

• Two-phase commits and distributed transactions

Data is seldom in one place. In today's distributed world, most new projects enhance or at least connect to existing data. That's not a problem. SQL Server can read and write data to most other data sources. Heterogeneous joins can even merge SQL Server data with an Excel spreadsheet.

SQL Server offers several methods of accessing data external to the current database. From simply referencing another local database to executing pass-through queries that engage another client/server database, SQL Server can handle it.

Note

While SQL Server can handle the technical problems of querying external data, if the two systems are in fact two separate applications, then directly accessing an external data store will likely violate the principle of encapsulation; and coupling the two data stores reduces the flexibility of the architecture. In many IT shops this practice would not be approved. Instead, the two systems should communicate using XML, SOAP, and SOA, as described in Chapter 1, "The Information Architecture Principle," and Chapter 32, "Building an SOA Data Store with Web Services."

Distributed Query Concepts

Linking to an external data source is nothing more than configuring the name of the linked server, along with the necessary location and login information, ...