Setting up a connection to Oracle Database
from SQL Server is fairly easy, but the opposite is not so true.
Steve Callan walks you through a complete example of how to access
SQL Server from Oracle Database using, named Database Gateways
(Oracle's new and improved method).
Setting up a connection
to Oracle Database from SQL Server is fairly easy, but the
opposite is not so true. Connecting Oracle Database to external
data sources was formerly handled using OHS, or Oracle
Heterogeneous Services. The new and improved method is named
Database Gateways, and the name reference is commonly seen as
DG4ODBC (or dg4odbc). The key part of this connection architecture
is based on ODBC.
A new (since Oracle 11gR1) piece of this setup involves
downloading and installing DG4ODBC resources into an existing
Oracle RDBMS installation. The product title for Oracle 11gR2 on
Windows (32-bit), as an example, is “Oracle Database Gateways 11g
Release 2 (11.2.0.1.0) for Microsoft Windows (32-bit)” and is
found under the download section for Databases at OTN. The
documentation for database gateways includes seven guides,
covering various flavors of UNIX, plus Windows. Of interest for us
in this article is the Database Gateway Installation and
Configuration Guide for Microsoft Windows guide, given that most
users are using Windows to learn Oracle.
What do you need for a test environment? Obviously Oracle
database, and although several ODBC data sources are covered in
the guide (Sybase et al), the external RDBMS here is SQL Server.
How do you get SQL Server? Download it from MSDN and sign up for a
180-day trial. Or, as covered before, spring for a copy of the
developer edition and get an Enterprise Edition installation for a
fraction of the commercial retail price. In addition to the SQL
Server installation, you’ll also need some SQL Server databases.
Once the database systems are in place (Oracle 11gR2 and SQL
Server 2008 for this example), it becomes a fairly simple matter
(well, almost) of following instructions regarding the DG4ODBC
installation and configuration. There are three sources of
information on how to configure Oracle to access SQL Server, and
you can pick and choose among them to get the final answer. The
sources include the instructions in the guide, plus two documents
on My Oracle Support:
"How to Setup DG4ODBC (Oracle Database Gateway for ODBC) on
Windows 32bit (Doc ID 466225.1)"
"How to Setup DG4MSQL (Database Gateway for MS SQL Server) on
Windows 32bit (Doc ID 466267.1)"
The differences include how to specify the HS_FDS_CONNECT_INFO
parameter in the initdg4msql.ora file, which is probably the
single most important parameter in the entire file. One document
shows use of a colon when specifying a port number, the other uses
a comma. Both of them use a sample select statement against
“systables” when the actual table is sys.tables. So, one thing
you’ll want to know ahead of time is a valid table in the SQL
Server database you’re trying to connect to.
You’ll need to collect the following information (for the OUI)
regarding the target database system:
SQL Server database server host name – if running Oracle
and MSSQL on the same server, this is simply your computer’s name.
SQL Server database server port number – just as Oracle
uses 1521 as a standard port, MSSQL uses 1433 as its typical port.
To find the port, run the following:
DECLARE @tcp_port nvarchar(5)
EXEC xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP',
@value_name = 'TcpPort',
@value = @tcp_port OUTPUT
select @tcp_port
Note: When
you get to the actual installation of Gateways, the OUI never asks
for the port number (despite what the guide shows), so what you do
need is the server name, instance name, and a database name. Also,
the installer never starts the Net Configuration Assistant (as
shown in the documentation).
SQL Server database name – either of the AdentureWorks or
AdventureWorksDW databases are suitable.
Download and unzip (not in your Oracle software installation) the
gateway file, and run setup.exe. Given that Oracle 11g is already
installed, it is highly likely the Oracle Universal Installer for
Gateways will start up with no problem. You’ll see the typical
welcome screen for 11g.
The documentation states that you don’t need to edit the Oracle
home value or path, but which Oracle home were the writers
referring to? Installation of this add-on is where having a CSI
for Oracle support comes in handy: the tech notes tell you that
installing in an existing Oracle home is okay. The caveat on this
is that if your Oracle installation is patched, then you will have
to re-patch the installation as the DG4ODBC install may overwrite
patched files.
I’ll use my existing Oracle home, going from this:
...to this:
We don’t need all of the gateways, so only the one for SQL
Server is selected.
Here’s where the
SQL Server information comes into play. My computer has SQL
Server 2005 installed as the default instance, and SQL Server
2008 installed as a named instance, so that’s why you see
WIN2003\SQL2008 (where SQL2008 is the named instance).
Confirm the
details of the Summary window and click Install.
The installation
process starts (mine took a few moments before the progress bar
started to show any progress) and overall, takes about three
minutes. The end of installation window shows that this was a
success.
A new folder
(dg4msql) was added to my 11gR2 installation.
-
Configure the Gateway Initialization
Parameter File
-
Configure Oracle Net for the Gateway
-
Configure the Oracle Database for Gateway
Access
-
Create Database Links
-
Configure Two-Phase Commit
-
Create SQL Server Views for Data
Dictionary Support
-
Encrypt Gateway Initialization Parameter
Values
-
Configure the Gateway to Access Multiple
SQL Server Databases
Shown below are
the steps to configure the gateway.
You can define
your own SID for a gateway, but it is easier to accept the
default SID Oracle creates for you: dg4msql. The benefit of
using dg4msql is that you get a pre-configured initialization
parameter file. If you have multiple connections, you’ll need
multiple init files. The contents of the default file are shown
below.
# This is a customized agent
init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server
#
# HS init parameters
#
HS_FDS_CONNECT_INFO=[WIN2003]/WIN2003\SQL2008/AdventureWorks
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
I added several other examples in my file, and wound up using
the comma-port format:
#HS_FDS_CONNECT_INFO=[WIN2003]/WIN2003\SQL2008/AdventureWorks
#HS_FDS_CONNECT_INFO=win2003:1433//AdventureWorks
#HS_FDS_CONNECT_INFO=://
# alternate connect format is hostname/serverinstance/databasename
#HS_FDS_CONNECT_INFO=win2003/sql2008/AdventureWorks
HS_FDS_CONNECT_INFO=win2003,1433/AdventureWorks
#HS_FDS_CONNECT_INFO=dg4msql
The next step is to configure the listener. No doubt, you
already have a listener.ora file on hand, so all that is
necessary here is to add an entry to the SID list, using the
format below:
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=gateway_sid)
(ORACLE_HOME=oracle_home_directory)
(PROGRAM=dg4msql)
)
)
As a bonus, Oracle creates a sample listener.ora (and
tnsnames.ora) file for you in the dg4msql\admin folder, so all
you need to do is cut and paste the list entry into your main
listener.ora file (and then reload the listener). The status
output in lsnrctl should show the new entry:
Service "dg4msql" has 1 instance(s).
Instance "dg4msql", status UNKNOWN, has 1 handler(s) for this service...
You have to edit the tnsnames.ora file manually, but again, you
can use the sample file in the dg4msql\admin folder. Just add
the sample entry (adjusting for port number if necessary) into
the main tnsnames.ora file:
dg4msql =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
(CONNECT_DATA=(SID=dg4msql))
(HS=OK)
)
Create a
database link, using double-quoted identifiers for the username
and password, and single quotes for the tnsnames value.
CREATE PUBLIC DATABASE LINK dg4 CONNECT TO
"Oracle" IDENTIFIED BY "oracle" USING 'dg4msql';
Skipping the
two-phase commit part, the next step includes running a script
that creates Oracle-like data dictionary views in MSSQL. Open a
New Query in SQL Server Management Studio (while connected as sa
or as an Administrator), open the file (which pastes it into the
query window) and run it. There will be several error messages
as the script contains drop statements against objects that
don’t exist (yet).
At this point, you should be able to query against SQL Server.
The output of select * from all_users@dg4 is shown below.
USERNAME
USER_ID CREATED
--------------------
---------- ---------
public
0
08-APR-03
dbo
1
08-APR-03
guest
2
08-APR-03
INFORMATION_SCHEMA
3
14-OCT-05
sys
4
14-OCT-05
Oracle
5
19-OCT-10
db_owner
16384 08-APR-03
db_accessadmin
16385 08-APR-03
db_securityadmin
16386 08-APR-03
db_ddladmin
16387 08-APR-03
db_backupoperator
16389 08-APR-03
db_datareader
16390 08-APR-03
db_datawriter
16391 08-APR-03
db_denydatareader
16392 08-APR-03
db_denydatawriter
16393 08-APR-03
A query against
a table in the AdventureWorks database:
select * from "AWBuildVersion"@dg4;
SystemInformationID Database Version VersionDa
ModifiedD
------------------- ---------------- ---------
---------
1 9.06.04.26.00 26-APR-06
26-APR-06
Note that these
two statements are not the same:
select * from "AWBuildVersion"@dg4;
select * from "awbuildversion"@dg4;
The
double-quoted identifier is important as the case matters when
selecting from Oracle into SQL Server. If the table is in a
namespace, such as HumanResources.Department, then double-quote
the namespace and table. For example:
select * from “HumanResources”.”Department”@dg4;
If selecting
columns by name, double-quote the column names as well.
SQL> select "DepartmentID", "Name", "GroupName"
2 from "HumanResources"."Department"@dg4;
DepartmentID Name
GroupName
------ ----------------
---------------------
1 Engineering
Research and Development
2 Tool Design
Research and Development
3 Sales
Sales and Marketing
4 Marketing
Sales and Marketing
5 Purchasing
Inventory Management
6 Research and Development
Research and Development
7 Production
Manufacturing
8 Production Control
Manufacturing
9 Human Resources
Executive General and Administration
10 Finance
Executive General and Administration
11 Information Services
Executive General and Administration
12 Document Control
Quality Assurance
13 Quality Assurance
Quality Assurance
14 Facilities and Maintenance
Executive General and Administration
15 Shipping and Receiving
Inventory Management
16 Executive
Executive General and Administration
The other
configuration options can be tested in your environment
(encryption and multiple databases) as needed. Now you have a
complete, from start to finish, example of how to access SQL
Server from Oracle.
and include the URL for the page.
