Running multiple database platforms is a way of life for many businesses, and SQL Server and Oracle are without a doubt the two most common enterprise database platforms. For most organizations, each database platform supports its own applications. However, you sometimes need to integrate the two databases and perform lookups, queries, and other operations on one database that incorporates data from the other database. If your SQL Server database needs ad-hoc database access to Oracle, the answer is linked servers. SQL Server’s linked servers functionality lets you seamlessly integrate your SQL Server applications with tables, indexes, and views stored in Oracle. In this article, I’ll show you how to configure and use a SQL Server 2008 linked server connection to an Oracle 11g database.
Installing the Oracle Client Software
Before you can create a SQL Server 2008 linked server to Oracle, you need to install the Oracle client software on the SQL Server 2008 system. The Oracle client software provides the network libraries that are required to establish connectivity to the Oracle database. In this case, the Oracle 11g database server is already installed and functional. Installing the Oracle client software on the SQL Server system enables it to connect to the existing Oracle server.
You can download the Oracle client software from technet.oracle.com. When selecting the Oracle client software you need to be sure to get the appropriate software for your SQL Server system’s processor architecture. If your SQL Server system is 64-bit, you need to download the Oracle x64 client. If your SQL Server system is 32-bit, you need to download the Oracle x86 client. The 64-bit Oracle client software is in the 11107_w2k8_x64_production_client.zip file. The 32-bit Oracle client software is in the win321_11gR1_client.zip file. After selecting the correct client software, install the Oracle client by running the setup.exe program on your SQL Server system. The first Oracle client setup screen is the Oracle Universal Installer Welcome dialog box, which lets you install and uninstall Oracle products. Then click Next to display the Select Installation Type dialog box, which you can use to determine what type of installation you’ll be performing, as shown in Figure 1.
You can choose to install the InstantClient, the Administrator, or the Runtime components. To create an Oracle linked server, select the Runtime option that’s shown in Figure 1. This option installs Oracle’s OLE DB Provider and ODBC driver, as well as SQL Plus, the Configuration Assistant, and the Net Manager. Note that the Runtime option requires a total of 483MB of hard disk space. Then click Next to display the Install Location dialog box.
The Install Location dialog box prompts you for the directory where you want to install the Oracle client software. I chose to install to the C:\ORACLE directory, which makes it easier for me to find the Oracle configuration files in case I need to check them later. Click Next to display the Summary dialog box in which you can confirm your choices. Clicking Install installs the Oracle client software.
Configuring the Oracle Client Software
Once the installation process is complete you need to tell the Oracle client software how to connect to the Oracle server. To configure the Oracle client, run the Oracle Net Configuration Assistant by selecting the Start, All Programs, Oracle – OraClient11g_home, Configuration and Migration Tools, Net Configuration Assistant menu option. The first screen displayed by the Oracle Net Configuration Assistant is the Welcome dialog box, shown in Figure 2.
Click Next to display a second Welcome dialog box where you can select the type of client configuration that you want to perform.
To configure the Oracle client for a linked server connection, select the Local Net Service Name configuration option where you can enter the Oracle server’s host and service names Clicking Next displays the Net Service Configuration dialog box. The Net Service Name Configuration dialog box lets you add a new Oracle Net Service configuration, as well as edit delete, or test an existing Net Service Name configuration. To create a new configuration, select Add and click Next. Doing so will display the Net Service Name Configuration dialog box, which is shown in Figure 3.
In the configuration dialog box (shown in Figure 3), you can enter the Service Name for the Oracle database. This name is created during the Oracle 11g server installation. The default value is ORCL, but this name can be changed. It’s essentially analogous to a SQL Server instance name. If you don’t know the Oracle service name, you can find it by going to the Oracle server and selecting the Start, All Programs, Oracle – OraDb11g_home1, Configuration and Migration Tools, Administration Assistant for Windows option. When the Administration Assistant for Windows starts, expand the Computers node, then the local host node, and click the Databases node. The Oracle database service names will be displayed in the right pane as Figure 4 shows.
