|Oracle Transparent Gateway Microsoft SQL Server Administrator's Guide
Release 8.1.6 for Windows NT
Part Number A82868-01
Oracle Transparent Gateway for Microsoft SQL Server allows Oracle client applications to access Microsoft SQL Server data through Structured Query Language (SQL). The gateway, with the Oracle database server, creates the appearance that all data resides on a local Oracle database server, even though data might be widely distributed. If data is moved from the Microsoft SQL Server database to an Oracle database, no changes in client application design or function are needed, because the gateway handles all differences in data types or SQL functions between the application and database.
This chapter contains the following sections:
The generic core of the Oracle Transparent Gateway for Microsoft SQL Server is incorporated into the database as the Heterogeneous Services facility. Heterogeneous Services (HS) extend the Oracle SQL engine to recognize the SQL and procedural capabilities of the remote non-Oracle system and the mappings required to obtain necessary data dictionary information. Additionally, since Heterogeneous Services is integrated with the Oracle database server, the transaction coordination of the server maintains the transaction coordination between Oracle and the remote non-Oracle system. For example, Heterogeneous Services provide the two-phase commit protocol to ensure distributed transaction integrity, even for non-Oracle systems that do not natively support two-phase commit.
Oracle Transparent Gateway for Microsoft SQL Server, together with the Heterogeneous Services component that is integrated in the Oracle8i server, provide information for and connectivity to Microsoft SQL Server.
Using Oracle SQL, Oracle client applications can access Microsoft SQL Server data as if the data were stored in an Oracle table. Data residing in Oracle and Microsoft SQL Server databases can be accessed by a single SQL statement, performing heterogeneous joins and subselects. This means you can develop one set of portable applications to use against Oracle and Microsoft SQL Server databases. You can continue to develop new information systems without losing your investment in existing data and applications.
Transactions updating Oracle and Microsoft SQL Server databases are automatically protected by the Oracle two-phase commit feature. Use of synonyms is another Oracle feature. By setting up synonyms in the Oracle database server that point to database links to Microsoft SQL Server tables, the physical location of the data is transparent to the client application. This allows future migration of data from Microsoft SQL Server to Oracle to be transparent to the client applications.
The gateway requires only the Oracle database server and Net8. All other Oracle products are optional. However, using other Oracle products with the gateway can greatly extend the gateway's capabilities.
The gateway is invoked by the listener. The gateway is not multi-threaded and cannot support shared database links. Each gateway session spawns a separate gateway process, and connections cannot be shared.
The gateway resides on the Microsoft Windows NT machine with the Microsoft SQL Server database or Microsoft SQL Server client (in which case the Microsoft SQL Server database can reside on a separate machine). The Oracle database server can reside on the same machine as the gateway or on another machine.
The gateway interacts with the Oracle database server to interface between client applications and Microsoft SQL Server, as shown in Figure 2-1.
The Oracle database server and the gateway work together to present the appearance of a single Oracle database to the client. All data accessed by the client appears to reside in a single Oracle database. The client application sends a request to the Oracle database server, and the Oracle database server sends the request to the gateway. For the first transaction in a session, the gateway logs into Microsoft SQL Server using a username and password that is valid in the Microsoft SQL Server database. The gateway converts the SQL statement to a native Microsoft SQL Server statement, and Microsoft SQL Server performs the request. The gateway converts the retrieved data to a format compatible with the Oracle database server and returns the results to the Oracle database server, which returns the results to the client application.
Figure 2-2 shows a typical gateway process flow. The steps explain the sequence of events that occurs when a client application queries the Microsoft SQL Server database through the gateway.
This section describes the following additional features provided by the Oracle Transparent Gateway for Microsoft SQL Server:
Applications can take advantage of Oracle client-server capability to connect to a remote server using Net8. The server can then connect to the gateway using a database link. You have more flexibility in locating your data because the Oracle architecture enables network connections between each of the components.
With remote access, you can move application development onto cost-efficient workstations or microcomputers. Without remote access, you are limited to the data available in the local environment. With remote access, your data sources are virtually unlimited. Remote access also enables you to choose the best environment for your users. For example, data might be located on a platform that supports only character-mode interfaces, but users can access the data from desktop platforms that support graphical user interfaces.
The gateway gives applications direct access to Microsoft SQL Server data, so the need for uploading and downloading large quantities of data to other locations is eliminated. You instead access the data where it is, when you want it, without having to move the data between machines and risk unsynchronized and inconsistent data. Avoiding data duplication reduces the disk storage requirements over all your systems.
The Oracle database server can accept a SQL statement that queries data stored in several different databases. The Oracle database server with heterogeneous services processes the SQL statement and passes the appropriate SQL directly to other Oracle databases and through gateways to non-Oracle databases. The Oracle database server then combines the results and returns them to the client. This enables a query to be processed that spans Microsoft SQL Server, other databases, and local and remote Oracle data.
Through the gateway, Oracle Corporation extends the range of application development and user tools you can use to access the databases. These tools increase application development and user productivity by reducing prototype, development, and maintenance time. Current Oracle users do not have to learn a new set of tools to access data stored in Microsoft SQL Server databases. Instead, they can access Oracle and Microsoft SQL Server data with one set of tools. These tools can run on remote machines connected through Net8 to the Oracle database server.
In a distributed database system, the network might fail during a distributed transaction. The Oracle transaction model uses a two-phase commit protocol to protect the databases during the period of committing data at sites participating in a distributed transaction. This feature ensures that all database servers participating in the transaction commit or roll back the transaction statements. The gateway supports this two-phase commit protocol. Only one Microsoft SQL Server database is allowed for each update transaction.
Whenever possible, the Oracle database server passes the entire query to the non-Oracle system to utilize the indexes and statistics of the non-Oracle system tables.
When a query that involves multiple databases is processed, the Oracle database server passes optimized statements to the remote servers and gateways involved in the query to minimize the amount of data returned across the network.
The gateway provides error mapping. It maps the Microsoft SQL Server error to an Oracle database server error message and adds all of the relevant error messages generated by Microsoft SQL Server. You can route messages to the client application, an operator console, an error log, or any combination of these destinations as needed. Error mapping provides database transparency for applications.
Using the procedural feature, the gateway can execute stored procedures defined in Microsoft SQL Server databases. There is no requirement to relink the gateway or define the procedure to the gateway, but the procedure's access privileges must permit access by the gateway.
Commands and statements specific to the Microsoft SQL Server database can be passed through the gateway for execution by Microsoft SQL Server.