Implementation interconnection between Oracle and SQLServer

The interconnection of different database platforms is generally called the heterogeneous service of databases. Currently, all major databases can achieve such Heterogeneous Interconnection, but the specific implementation technologies of different vendors are different, such: in SQL SERVER, it is called LINKED SERVER, and ODBC is used to interconnect with other databases.
　　
The technology for implementing heterogeneous services in Oracle is called the Transparent Gateway. Of course, ORACLE has also adopted the universal connection technology before. Currently, ORACLE uses transparent gateways to connect with SQL SERVER, SYBASE, DB2, and other databases.
　　
The architecture of transparent gateway is also very simple. The ORACLE and SQL server are interconnected using the ORACLE transparent gateway SERVER. The transparent gateway SERVER can be on the same host with ORACLE or SQL SERVER databases, it can also be on an independent host.
　　
The procedure is as follows:
　　
1. Create test accounts and tables on the SQL SERVER database
　　
Here I am using the PUBS database of 10.16.74.140, account cyx,
　　
Create table t (c char (10 ));
　　
2. The database used in the test is on the same machine as the transparent gateway. On my local machine: 10.16.98.16, the transparent gateway is not installed in oracle by default, so select this option if you want to use it.
　　
3. After installing the transparent gateway for SQL server software, you can view the tg4msql directory under $ ORACLE_HOME and 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 listener. ora on the transparent gateway server and add the following content to SID_LIST:
　　
(SID_NAME = tg4msql) # SID Name by yourself
　　
(ORACLE_HOME = c)
　　
(PROGRAM = tg4msql)
　　
5. Add tnsnames. ora on the oracle server to the tnsname of the transparent gateway. The content is as follows:
　　
Sql2k =
　　
(DESCRIPTION =
　　
(ADDRESS_LIST =
　　
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.16.98.16) (PORT = 1521) # Here, the host address of the transparent gateway SERVER is entered.
　　
)
　　
(CONNECT_DATA = (SID = tg4msql) # This SID should be the same as the SID set on the transparent gateway SERVER.
　　
(HS = OK)
　　
)

1 2 3 Next Page

[Content navigation]
Page 1st: Implementation and interconnection between Oracle and SQLServer	Page 2nd: Implementation and interconnection between Oracle and SQLServer
Page 3rd: Implementation and interconnection between Oracle and SQLServer