Running multiple database platforms is a way of life for most businesses because most companies implement a variety of applications that are tightly bundled with a particular back-end database system. Two of the most common database platforms found in today's enterprises are SQL Server and Oracle, so many companies are running SQL Server and Oracle at the same time. More often than not, these different database platforms are either totally independent islands of computing or the interaction between them consists of little more than batch data imports and exports. However, that doesn't have to be the case. SQL Server's linked servers feature lets you seamlessly integrate your SQL Server applications with the tables in Oracle databases. But first, you have to know how to set up and use a linked-server connection from SQL Server 2000 to an Oracle database.
Installing the Oracle Client Software
Before creating a linked server from SQL Server 2000 to an Oracle 9i database server, you need to install the Oracle client software on the SQL Server system. The Oracle client software provides the necessary network libraries to establish connectivity to the Oracle database. Because the SQL Server system performs the processing required for the linked-server functionality, the Oracle client software must be present on the SQL Server system. Figure 1 shows an overview of the SQL Server—to—Oracle linked-server connectivity layers.
At first glance, this configuration might seem a little different from the requirements for creating a linked server to another SQL Server system because you need to install a separate client component. But the configurations are actually similar because they both require client software for connecting to the database server. The installation process for SQL Server installs Microsoft Data Access Components (MDAC) on the server system. MDAC essentially provides the same functionality for SQL Server as the Oracle client software does for an Oracle server—they both provide the networking libraries required to connect to the database server.
To begin configuring your linked server connection to Oracle, you can install the Oracle client from the Oracle 9i Client Installation CD-ROM or download it from http://technet.oracle.com. After running the setup.exe program from either the CD-ROM or the download, you'll see the Oracle Universal Installer Welcome dialog box. Click Next. The resulting Oracle Universal Installer File Locations dialog box lets you enter the location of the Oracle products.jar file, which contains the software to be installed, and select the location on your system where you want to install the Oracle software. The Oracle Universal Installer looks for the product.jar file in the \stage directory on the same drive where you ran the setup program. By default, the Oracle software installation path for Oracle 9i is c:\oracle\ora92, but you can change this to any valid local path.
The next screen determines what type of installation you want to perform. From here, you can choose to install either the Oracle Administrator software or the Oracle Runtime software. The Oracle Administrator option is a superset of the Oracle Runtime option. In addition to the basic Oracle Client components, the Administrator option installs the Oracle Management Console and Management Tools and utilities. The Runtime option installs only the basic Oracle Client files, which include the essential Oracle Call Interface (OCI) and networking support as well as SQL Plus, the Configuration Assistant, and the Net Manager. To install the Oracle Client software, select the Runtime option. The Oracle Client runtime files require 161MB of hard disk space.
After the installation process has copied the Oracle Client software to the target directory, the Oracle Universal Installer prompts you to run the Oracle Net Configuration Assistant to configure the client connection. Don't be confused by the name; this Net isn't referring to the Microsoft .NET Framework. Instead, it means the Oracle client networking software.
Configuring the Oracle Client Software
You can start the Oracle Net Configuration Assistant immediately after you install the Oracle Runtime components. Or you can run it any time after the installation is complete by using the Start, Programs, Oracle, OraHome92 menu option from your Windows XP, Windows 2000, or Windows NT desktop. The Oracle Net Configuration Assistant: Welcome screen, which Figure 2 shows, lets you select the type of name service that you're using to connect to the Oracle database server.
The Welcome dialog box first asks what type of naming system the client will be using to locate the Oracle server. If you're using a directory service such as Active Directory (AD) or Sun's Network Information Service (NIS), you can select the first option. In this example, let's assume that no naming service exists. To step through the manual net-service naming process, select the option No, I will create net service names myself. The Assistant will help me create one now. Selecting the No option and clicking Next displays the Net Service Name Configuration, Database Version dialog box that Figure 3 shows.