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.
On the Database Version screen, you specify the version of the Oracle database server that you want to connect to. You can choose to connect to an Oracle 8i or later database or to an Oracle 8.0 database. Your choice here determines the subsequent dialog boxes the Net Configuration Assistant displays. Choosing Oracle 8i or later causes the assistant to prompt you for a Service name; if you choose Oracle 8.0, the Net Configuration Assistant prompts you for an Oracle System Identifier (SID). To connect to an Oracle 9i database, you need to select the 8i or later option.
Clicking Next displays the Net Service Name Configuration, Service Name dialog box that Figure 4 shows. On this dialog box, you enter the Service Name for the Oracle database. The Service Name consists of the database and domain names; you define it during the Oracle server installation process. The Service Name, a logical representation of a database, determines the way the server presents a database to clients. An Oracle database can be presented as multiple services, and a service can be implemented as multiple database instances. In this case, I used the service name tecao.teca.com to identify the Oracle database server.
Clicking Next displays the Net Service Name Configuration, Select Protocols screen that Figure 5 shows. This dialog box lets you select the network protocol that you'll use to connect the client to the Oracle server. In Figure 5, I selected the TCP network protocol. Clicking Next displays the dialog box that Figure 6 shows.
The Net Service Name Configuration, TCP/IP Protocol dialog box lets you enter the specific network protocol configuration information required to connect to the Oracle database. In the Host name field, you need to enter either the TCP/IP host name for the Oracle server or its TCP/IP address. In Figure 6, I entered the value teca5, the DNS name that the DNS server uses to identify the Oracle system. Next, you specify the TCP/IP port number. This value must match the port that the Oracle Database Listener is using. In Figure 6, I used the default port 1521. If you want to use a different TCP/IP port value, you can specify that value by clicking the Use another port number radio button and entering the value in the text box.
After you enter all the database server and network protocol information, the next dialog boxes let you test your connection to the Oracle database server. If all your Net Service configuration parameters are correct, you'll see a Connecting... Test successful message. If the test fails, you can click the Back button to step your way back through the Net Configuration Assistant dialog boxes, checking and changing any incorrect values. See the sidebar "Solving Common Connection Problems" if you have trouble getting the connection to work.
After you test the configuration values, the final dialog box, which Figure 7 shows, lets you enter the Net Service Name for the Oracle database. The Net Service Name is the name that this configuration will be known as on the local computer and can be anything you choose. In many ways, the Oracle Net Service Name is analogous to the ODBC Data Source Name (DSN): It's a user-defined value that identifies a target data source. SQL Server will use this name later in the linked-server configuration. In Figure 7, I used the service name tecao to identify the Oracle database server.
Clicking Next ends the Oracle Net Configuration Assistant's Net Service Name configuration. At this point, you can enter another local Net Service Name (for example, if you want to set up another Net Service Name that points to a different database or a different Oracle server), or you can exit the Net Configuration Assistant by clicking Cancel. Be aware that before using the Oracle client, you must reboot your system to register the Oracle client DLLs. Otherwise, you'll get the error message Unable to load DLL (oci.dll).
Creating the Linked Server
After installing and configuring the Oracle client software, you're ready to use the SQL Server tools to create your linked server. You can create a linked server either by using Enterprise Manager or by executing T-SQL statements. I prefer using T-SQL because it documents the exact values that SQL Server uses in the creation of the linked server, which makes it easier to create similar objects later by copying and changing your T-SQL linked-server creation script.
The following code is an example of creating a linked server to the Oracle tecao system that I defined in the Oracle Net Configuration Assistant:
exec sp_addlinkedserver 'TecaOracle', 'Oracle', 'MSDAORA', 'tecao'
In this example, SQL Server will refer to the linked server as TecaOracle. The second parameter is the OLE DB product name; you can leave this value as NULL or change it to any other value. In this instance, I used the word Oracle to describe the linked server. The third parameter specifies the OLE DB provider that the linked server will use. MSDAORA is the name of the Microsoft OLE DB Provider for Oracle. This provider is installed by default as part of the SQL Server 2000 installation. The final required parameter of the sp_addlinkedserver stored procedure is the DSN. For the Oracle linked server, this is the Net Service Name tecao that I created earlier by using the Oracle Net Configuration Assistant.
Creating the linked server is the first step to configuring an Oracle linked server. The next step is mapping your SQL Server login to an Oracle account.
Mapping Logins
The linked server essentially defines the connection to the Oracle server from the SQL Server system. However, just like with SQL Server, to access any of the databases, you need to authenticate your linked-server connection to the Oracle database server. The sp_addlinkedsrvlogin stored procedure lets you map your SQL Server login to a remote user's account on the linked-server system. The following code shows how to use sp_addlinkedsrvlogin to map the SQL Server login to the sample Scott account on the Oracle server:
exec sp_addlinkedsrvlogin 'TecaOracle', false, NULL, 'Scott', 'tiger'
The first parameter specifies the name of the linked server system. This name must match the name of the linked server that you created with sp_addlinkedserver. This example uses the value TecaOracle. The second parameter determines the name of the login SQL Server will use when the linked server attempts to connect to the remote system. A value of true indicates that SQL Server will use the current SQL Server login credentials to connect to the linked server. You can use this option only if the logins of the two database servers match. A value of false indicates that you'll supply the remote login credentials as part of the sp_addlinkedsrvlogin procedure. The third parameter specifies the local login that SQL Server will use to make this connection to the remote server. If you use a value in this parameter, a matching SQL Server login must exist. Using the default value of NULL indicates that this remote login will be used for all SQL Server logins for all connections to the linked Oracle server. The fourth and fifth parameters supply login and password values that are valid on the linked server.
In this example, SQL Server will use the Oracle sample username Scott to connect to the Oracle database server. Figure 8 shows the completed Oracle linked server as Enterprise Manager displays it.
You can use three handy T-SQL commands to list information about the linked servers that are available on your system. The EXEC sp_linkedservers command lists the linked servers and their providers; the EXEC sp_helpserver command lists the remote servers, their network names, and their timeout values. And SELECT * FROM sysservers lists the servers that SQL Server can access as OLE DB data sources.
Running Queries Against the Oracle Linked Server
Now that the connection is configured, you're ready to start running SQL Server statements against the linked Oracle server. Queries to linked servers use the following four-part naming syntax: linked_server_name.catalog_name.schema_name.table_name. As Figure 8 shows, the Oracle SCOTT schema has five user tables. To select all the rows from the EMP table, you'd enter the following SQL statement:
SELECT * FROM TecaOracle..SCOTT.EMP
The value TecaOracle identifies the linked server. The Catalog value is always blank because the Oracle database supports only one catalog. SCOTT is the name of the Oracle schema associated with the username Scott that we mapped earlier using the sp_addlinkedsrvlogin stored procedure. And finally, EMP is the name of the table in the schema you want to access. The right pane of Figure 8 shows the values that your linked server queries need to use in each of these name parts.
After you set up the linked server, SQL Server automatically begins to log statistics for the table on the linked server, attempting to optimize the type of queries that will be sent to the linked server. One important point about linked servers is that the SQL Server distributed query processor—not the linked server—is responsible for optimizing the queries that SQL Server sends to the linked server to retrieve data.
SQL Server first queries the linked server to determine the level of SQL dialect it supports, such as the full SQL Server dialect, the SQL-92 dialect, the ODBC core dialect, or the Jet dialect, then attempts to push operations such as joins, unions, sorts, and GROUP BYs to the remote server. However, the distributed query processor doesn't have the same type of information about remote tables as it does about local tables, so the steps it takes to retrieve data aren't always the most optimal.
SQL Server Query Analyzer's ability to display the execution plan is a good way to get some insight into the way SQL Server's distributed query processor will process the remote query. One possible workaround for queries that don't perform as expected is to use the OPENQUERY() function. OPENQUERY() essentially bypasses the distributed query processor and sends the query directly to the remote server for processing.
Overcoming the Great Database Divide
Using heterogeneous databases is a way of life for most corporations. SQL Server's linked-server feature lets you create a nearly seamless connection between your SQL Server system and other OLE DB—compliant databases including Oracle, Access, and IBM DB2. SQL Server's linked-server feature can provide the interoperability bridge that you need to connect the different database islands that exist in your organization. This connection helps you make the most of your investment in SQL Server as well as in Oracle and other OLE DB—compliant databases that SQL Server's linked-server capability supports.
