IV.6.1. Data Everywhere: Remote Applications to the Rescue!

When running SQL Server, it's highly unlikely that you'll be running it on your desktop system. Instead, you'll run SQL Server 2008 on a server product (such as Windows Server 2003 or Windows Server 2008) and access your SQL Server 2008 database over the network.

Moreover, you're not limited to only the data held on a single SQL Server. A distributed query is used to access data from different data sources through SQL Server. In other words, the distributed query is executed against SQL Server, but pulls data from somewhere else.

By using distributed queries, you allow your applications to be remote applications. The applications can run from anywhere and can access data from any other data source as long as they can establish connectivity with a single SQL Server. Of course, the SQL Server must have access to the other data source; that is, there must be connectivity and permissions must be granted.

Figure 6-1 shows how a distributed query works. The client application queries SQL Server. SQL Server then uses an Object Linking and Embedding Database (OLE DB) provider to query data from another data source, such as another SQL Server, Oracle, or Microsoft Access database — any data source with an Open Database Connectivity (ODBC) driver. This is by no means a complete list. Just about any data source that enjoys some level of popularity has either an OLE DB or ODBC provider that can be used.

Figure IV.6-1. Querying data ...

Get Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies® now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.