Oracle and SQL Server interconnect

The interconnection of different database platform is commonly called the heterogeneous service of the database, now each big database can realize such heterogeneous interconnection, but the specific implementation technology of each vendor is not the same, such as: In SQL Server called linked server, through ODBC and other database interconnection.

Oracle's technology for heterogeneous services is called a transparent gateway (transparent gateway), and Oracle has used universal connection technology before. At present, Oracle uses transparent gateway to realize the interconnection of SQL SERVER, SYBASE, DB2 and other databases.

The architecture of transparent gateways is also simple, using Oracle transparent gateway servers to interconnect between Oracle and SQL Server, where transparent gateway servers can be on the same host as Oracle or SQL Server databases, or on a stand-alone host.

Below is the concrete step, if have the question Welcome to contact me, msn:gototop_ncn@hotmail.com.

1. Create test accounts and tables on SQL Server database

Here I use the 10.16.74.140 pubs database, the account cyx,

CREATE TABLE T (c char (10));

2, I test the database and transparent gateway is on the same machine, in my native: 10.16.98.16, transparent gateway in the Oracle default installation is not installed, so if you want to select this option.

3, after installing the transparent gateway for SQL Server software, you can see the Tg4msql directory under $oracle_home, edit the $oracle_home/tg4msql/admin/inittg4msql.sql file to confirm that this line is correct:

hs_fds_connect_info= "Server=10.16.74.140;database=pubs"

4. Modify the Listener.ora on the transparent gateway server and add the following in Sid_list:

(Sid_name = tg4msql) # SID named after himself

(Oracle_home = c:)

(program = tg4msql)

5, add the tnsname to the transparent gateway in the Tnsnames.ora on Oracle server, the contents are as follows:

　　sql2k =
　　 (DESCRIPTION =
　　 (ADDRESS_LIST =
　　 (ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.98.16)(PORT = 1521)) #此处HOST填的是透明网关SERVER的地址
　　 )
　　 (CONNECT_DATA = (SID = tg4msql) ) #此SID应和透明网关SERVER上设定的SID相同
　　 (HS=OK)
　　 )