Enterprise business applications are designed more frequently to run in a distributed computing environment. In addition to distributing applications across a network of workstations, servers, and legacy mainframe systems, organizations are distributing applications across geographic boundaries, time zones, and divisions. As organizations evolve and grow, they also often acquire a heterogeneous collection of computers, networks, and databases.
These organizations need access to information and data from diverse enterprise business applications. For example, an organization might need to access the data residing on a UNIX workstation or an Oracle database in a way that is transparent to the end users. Transparent access is essential to developing powerful distributed solutions that allow an organization to be responsive to the marketplace. The Microsoft solution is the Universal Data Access (UDA) architecture as shown in this illustration.
This chapter addresses the strategies and techniques for transparently accessing data in a heterogeneous environment. Microsoft SQL Server 7.0 is based on the UDA architecture, which is implemented by means of OLE DB. OLE DB is an interface specification that provides for distributed data access without regard to the data's source or format. Oracle, in contrast, takes a universal server approach, in which all data must exist in a single repository and must be accessed using a single access language.
The primary goal of SQL Server 7.0 is to take advantage of the capabilities offered through the UDA architecture, which allows data to exist in multiple formats and to be accessed using many different methods. With the release of SQL Server 7.0, Microsoft provides a more powerful relational database management system (RDBMS), and a mechanism for gathering disparate information stores and for presenting data in a consistent and useful manner without having to migrate or convert heterogeneous data to a single data store.
In addition to support for UDA, SQL Server 7.0 provides these new technologies for working with data in a heterogeneous environment:
SQL Server 7.0 allows the import, export, and transformation of data from heterogeneous data sources without any additional software investment. Any OLE DB provider can use DTS, including Oracle, Informix, and Microsoft Excel.
SQL Server 7.0 allows linking remote servers (using any OLE DB provider) and using data in queries that come from heterogeneous sources. This action is transparent to the client program, which sees the tables as if they were native SQL Server tables, and improves network traffic because the query engine tries to execute as much work as possible at the remote computer. In addition, data does not need to be moved; it remains in its native store.
Any Open Database Connectivity (ODBC) driver or OLE DB data provider can participate in SQL Server 7.0 replication.
Data warehouses or data marts can be created easily from a variety of relational databases, including SQL Server, Oracle, and Informix.
All this functionality comes as part of SQL Server 7.0 at no additional cost. In addition, wizards make it easier for the user to build heterogeneous solutions using SQL Server.
The following scenarios explore situations in which SQL Server and Oracle would need to coexist.