Linked servers can bring your SQL Server and Oracle databases together
Suppose you have a Microsoft .NET-enabled Internet ordering system but your warranty system is a legacy Oracle database application. You don't want to bill your commercial customers when they place orders for warranty replacement parts, so you need realtime answers from your Oracle database. Creating a linked server lets you use SQL Server to query your Oracle database in realtime to find out who your existing customers are.
When your data is distributed across several databases on multiple SQL Servers, linked servers let you run queries distributed across those servers. When all the servers are SQL Servers, configuration is easy, and SQL Server Books Online (BOL) covers everything you need to know. However, if some of your data is on an Oracle database, for example, configuring a linked server brings special challenges. You need to understand that even if you configure an Oracle linked server in SQL Server Enterprise Manager, your SQL Server is a client of your Oracle database. Therefore, you must successfully install and configure Oracle client software on your SQL Server. Oracle provides product support only for Oracle8 and later, so this article assumes that you're running Oracle8 or later. The Oracle Net8 library provides the client software that SQL Server needs.
In Oracle, a schema is the name for the entity SQL Server professionals know as a database. To connect to Oracle, you need to supply the schema name, password, and host string. An Oracle schema is owned by a specific Oracle username, so the schema name is also the username of the user who owns the schema. One Oracle username owns only one schema. You can find out more about the contents of a schema by querying the Oracle data dictionary, as I describe in the sidebar "System Procedures for Obtaining Linked-Server Metadata," page 26. The Oracle host string is also known as a service name or System Identifier (SID). What SQL Server calls an instance, Oracle calls a database. During an Oracle server installation, Oracle Universal Installer (a graphical interface similar to SQL Server's installation program) asks for a SID to use as the name of the Oracle database.
If you install your Oracle instance on a Windows machine and set your SID to Ora817, as in this article's example installation, you'll have a Windows 2000 service called OracleServiceORA817. This service is analogous to the MSSQLSERVER service for SQL Server 2000. For information about the architectural differences between the two products, see the Microsoft article "Migrating Oracle Databases to Microsoft SQL Server 7.0" (http://msdn.microsoft.com/library/techart/oracle2sql.htm).
Setting Up the Oracle Client on SQL Server
A SQL Server instance and its client exchange data through the application-level Tabular Data Stream (TDS) protocol by using a supported network protocol such as TCP/IP or IPX/SPX. Oracle servers and clients use Net8 to exchange data through three components: the Transparent Network Substrate (TNS), an Oracle protocol adapter, and a supported network library. Net8 uses a protocol adapter for translations between TNS and the network library. Net8, a replacement for SQL*Net, uses service names to find servers. The network libraries Net8 supports are TCP/IP, SPX, Named Pipes, Logical Unit Type 6.2 (LU6.2), and Bequeath. TCP/IP and SPX are the network libraries you can use to connect remote clients to Oracle servers. You use LU6.2 for compatibility with the IBM Advanced Program-to-Program Communications (APPC) architecture. Net8 uses the Bequeath network protocol for making connections when an Oracle client is logged in locally to the Oracle server.
For a Net8 client to make a successful connection to an Oracle server, the client must be able to find the service name for the server. Oracle clients can resolve service names by using a local client file called tnsnames.ora (which is analogous to a Windows HOSTS file), the DNS, or an Oracle Names Server. With Oracle9i, Lightweight Directory Access Protocol (LDAP) becomes another service-name resolution option. However, at press time, Microsoft didn't officially support Oracle9i as a linked server.
The Java-based Oracle Universal Installer helps you install Oracle server and client software on both Windows and UNIX systems. On a Windows system, inserting the Oracle installation CD-ROM autostarts Oracle Universal Installer. I recommend selecting a default installation of the Oracle8 client because in addition to installing Net8, the default installs the Net8 Assistant, the Net8 Configuration Assistant, and SQL*Plus and adds these options to your Start menu. SQL*Plus is a client tool comparable to SQL Server's osql.
Troubleshooting
Using SQL Server to validate a linked-server connection to Oracle can give misleading results. Until you execute a linked-server query, you can't be certain whether you configured the link correctly. Keep in mind that you can issue a linked-server query indirectly. In Enterprise Manager, if you select Tables or Views under your linked-server definition, you're indirectly querying the Oracle data dictionary. After I issued such a query, I received the error message in Figure 1. But when I executed Oracle's TNSPING command (similar to a TCP/IP PING, but specifically for testing connectivity to an Oracle database) from a command prompt, the results showed that Oracle client and networking components were installed, as Figure 2 shows.
Additionally, I had successfully used both the Net8 Assistant, as Figure 3 shows, and the Net8 Configuration Assistant to make actual connections to the example SCOTT schema, which is analogous to the SQL Server Pubs database. Using either of those network administration tools is sufficient for validating a database connection, so I knew that the problem lay elsewhere.