SQLSERVER与ORACLE互连_sqlserver链接oracle数据库-CSDN博客

不同数据库平台的互连一般称之为数据库的异构服务，现在各大数据库之间都可以实现这样的异构互连，只是各厂家的具体实现技术不一样，如：在SQLSERVER里面叫做LINKED SERVER,通过ODBC实现与其它数据库的互连。而ORACLE实现异构服务的技术叫做透明网关(Transparent Gateway),当然之前ORACLE还采用过通用连接技术。目前ORACLE利用透明网关可以实现和SQLSERVER,SYBASE,DB2等多种数据库的互连。

一、透明网关

透明网关的体系结构很简单，在ORACLE和SQL SERVER之间使用ORACLE透明网关服务器实现互连，其中透明网关服务器可以与ORACLE或SQLSERVER数据库在同一台主机上，也可以是在独立的一台主机上。

1、安装透明网关

ORACLE默认安装是不安装透明网关的，需要自定义选择安装。安装过程：运行oracle安装软件，安装类型选自定义，选择里面的安装oracle transport gateway,下面的oracle transparent gateway from Microsoft sql server,这里可以看到，还可以选择其他数据库，根据需要安装。剩下的按提示进行即可。确认透明网关安装成功。会出现X:/oracle/ora92/tg4msql目录，配置文件inittg4msql.ora。

2、配置

（1）、新增或修改已有配置文件

新增一个配置文件或者修改已有inittg4msql.ora.内容如下：

HS_FDS_CONNECT_INFO = "SERVER=172.16.0.106; DATABASE=hhcar"

HS_FDS_TRACE_LEVEL=OFF

HS_FDS_RECOVERY_ACCOUNT=RECOVER

HS_FDS_RECOVERY_PWD=RECOVER

说明：Server是指连接的Sql server所在主机

DATABASE是指要连接的数据库

新增的配置文件的名称必须如下：initXXX.ora;其中XXX是自定义，在下面配置listener文件时有用。

（2）、配置listener.ora

路径：/oracle/ora92/network/admin/下面

在SID_LIST_LISTENER里添加如下内容：

(SID_DESC =

(GLOBAL_DBNAME = tg4msql)

(PROGRAM = tg4msql)

(SID_NAME = sql2000)

(ORACLE_HOME = D:/oracle/ora92)

)

说明：SID_NAME:与1中添加配置文件的名称相同，这里的sql2000对应上面的initsql2000.ora

PROGRAM：为配置文件intisql2000所在文件夹，一般为tg4msql

GLOBAL_DBNAME可以随便起。

（3）、重启监听器

在命令行启动lsnrctl,执行下面命令：

lsnrctl>stop

lsnrctl>start

（4）、配置tnsnames.ora

路径：/oracle/ora92/network/admin/下面

加上如下内容：

sql =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.106)(PORT = 1521))

)

(CONNECT_DATA =

(SID = sql2000)

)

(HS = OK)

)

说明：HOST为透明网关的主机名或ip地址。

SID与listener.ora的SID_NAME对应。

服务器名自己起，在此为sql。

HS代表异构。

修改ORACLE SERVER的初始化参数，将global_names设为false,因为我们并不使用GLOABLE NAME,然后重启数据库。【这一步可能不需要。】

3、使用

删除数据库连接：drop dabase link db_sql;

建立数据库连接：

create public database link db_sql

connect to sa identified by "1"

using 'sql';

使用查询：

select a.userid,a.username,b.username

from

jxnf_user@db_sql a,

wsscar.jxnf_user b

where a.userid = b.userid;

说明：connect to 与 identified后面对应的分别为连接sql server数据库的访问用户名和密码。

4、扩展

如果需要配置多个对sqlserver数据库进行访问，可以在目录tg4msql中配置多个initXXX.ora文件，里面指定对应的数据库同时在listener.ora和tnsnames.ora进行配置，再建立相应的db links即可。

5、注意点

（1）、对远程数据库不允许进行ddl操作。

（2）、ORACLE FOR SQL SERVER的透明网关在UNIX下无法实现，目前只支持WIN；原因很简单，SQLSERVER本身不支持UNIX,所以ORACLE也无法直接在UNIX下访问SQLSERVER;

（3）、Oracle透明网关软件在Oracle8i时是需要花钱另买的，大约1万$；到Oracle9i时是作为数据库的一个组件免费发布的。安装时在组件中选择即可。

（4）、针对我们的实际应用，如果有需要实现Oracle到SQL SERVER的互连，我们需要另外用一台WIN下的Oracle9i做透明网关服务器，其它UNIX下的Oracle通过这个透明网关来访问SQLSERVER。

（5）、不同数据库间的数据处理需要使用标准SQL来实现。

（6）、各数据库中特殊的数据类型，需要在程序中实现转换，应尽量避免使用无法转换的数据类型。

6、啊

二、LINKED SERVER

用sp_addlinkedserver和sp_addlinkedsrvlogin两个存储过程实现跨数据库进行数据访问。

思路：

A、建立服务器连接

B、创建或更新SQL SERVER本地实例上的登录名与远程服务器中安全账户之间的映射。

1、建立服务器连接

使用sp_addlinkedserver系统存储过程。

连接SQLSERVER数据库

EXEC sp_addlinkedserver '远程服务器IP','SQL Server'

示例：EXEC sp_addlinkedserver '172.16.0.107','SQL Server'

说明：这是一种简化写法，当srvproduct是SQL SERVER，后面的字符串可以不指定。

连接SQLSERVER数据库2

EXEC sp_addlinkedserver

@server=’server’,--被访问的服务器的别名

@srvproduct=’product_name’,

@provide=’provider_name’,

@datasrc=’data_source’ –要访问的服务器

示例：

exec sp_addlinkedserver

@server='SQL107',

@srvproduct='',

@provider='SQLOLEDB',

@datasrc='172.16.0.107'

说明：这种写法可以使用别名。

连接ORACLE数据库

结构同上

示例：

EXEC sp_addlinkedserver

@server ='Oracle107',

@srvproduct='Oracle',

@provider='MSDAORA',

@datasrc="HTYD107"

语法

EXEC sp_addlinkedserver
@server = 'server', --链接服务器的本地名称。也允许使用实例名称，例如MYSERVER/SQL1
@srvproduct = 'product_name',--OLE DB数据源的产品名。对于SQL Server实例来说，product_name是'SQL Server'
@provider = 'provider_name', --这是OLE DB访问接口的唯一可编程标识。当没有指定它时，访问接口名称是 SQL Server数据源。SQL Server显式的provider_name是 SQLNCLI（Microsoft SQL Native Client OLE DB Provider）。Oracle的是 MSDAORA，Oracle 8或更高版本的是OraOLEDB.Oracle。MS Access和MS Excel的是 Microsoft.Jet.OLEDB.4.0。IBM DB2的是DB2OLEDB，以及ODBC数据源的是MSDASQL
@datasrc = 'data_source', --这是特定OLE DB访问接口解释的数据源。对于SQL Server，这是 SQL Server（servername或servername/instancename）的网络名称。对于Oracle，这是SQL*Net别名。对于 MS Access和MSExcel，这是文件的完整路径和名称。对于ODBC数据源，这是系统DSN名称
@location = 'location', --由特定OLE DB访问接口解释的位置
@provstr = 'provider_string',--OLE DB 访问接口特定的连接字符串。对于ODBC连接，这是ODBC连接字符串。对于MS Excel，这是Excel 5.0
@catalog = 'catalog' --catalog的定义变化基于OLE DB访问接口的实现。对于SQL Server，这是可选的数据库名称，对于DB2，这个目录是数据库的名称

2、创建连接服务器与远程间的映射

使用sp_addlinkedsrvlogin系统存储过程

方法一(SQL SERVER)

EXEC sp_addlinkedsrvlogin '远程服务器IP','false','sa','架构名','访问密码'

示例：sp_addlinkedsrclogin '172.16.0.107','false',null,'sa',''

说明：简化写法

exec sp_addlinkedsrvlogin

@rmtsrvname='SQL107',

@useself='false',

@locallogin=null,

@rmtuser='sa',

@rmtpassword=''

方法二（ORACLE）

EXEC sp_addlinkedsrvlogin

'Oracle107',

'false',

null,

'wsscar',

'1'

语法

EXEC sp_addlinkedsrvlogin
@rmtsrvname = '远程服务器IP', --要添加登录名映射的本地链接服务器
@useself = false, --当使用true值时，使用本地SQL或Windows登录名连接到远程服务器名。如果设为false，存储过程 sp_addlinkedsrvlogin的locallogin、rmtuser和rmtpassword参数将应用到新的映射中
@locallogin = NULL, --这是映射到远程登录名的SQL Server登录或Windows用户的名称。如果这个参数置为NULL，映射将应用SQL Server实例中的所有本地登录名
@rmtuser = '架构名', --用来连接到链接服务器的用户／登录名的名称
@rmtpassword = '访问密码' --用来连接到链接服务器的用户／登录名的密码

3、使用

查询数据SQLSERVER1

--简单点为远程机器设置远程链接服务器。
--假设远程机器IP为127.0.0.1
--建立连接服务器
EXEC sp_addlinkedserver '127.0.0.1','SQL Server'

--创建链接服务器上远程登录之间的映射

Exec sp_addlinkedsrvlogin '127.16.0.1','false','sa','sa','1'

--查询数据

select top 10 * from [127.0.0.1].pubs.dbo.sales;

查询数据SQLSERVER2

exec sp_addlinkedserver

@server='SQL107',

@srvproduct='',

@provider='SQLOLEDB',

@datasrc='172.16.0.107'

exec sp_addlinkedsrvlogin

@rmtsrvname='SQL107',

@useself='false',

@locallogin=null,

@rmtuser='sa',

@rmtpassword=''

select a.username

from SQL107.hhcar.dbo.jxnf_user as a

连接数据库ORACLE

EXEC sp_addlinkedserver

@server ='Oracle107',

@srvproduct='Oracle',

@provider='MSDAORA',

@datasrc="HTYD107"

EXEC sp_addlinkedsrvlogin

'Oracle107',

'false',

null,

'wsscar',

'1'

select * from Oracle107..WSSCAR.JXNF_USER

注意：用户名和表名一定要大写。用户名前面是2个点。

当前使用的用户，也就是使用企业管理器或查询分析器连接SQLSERVER的用户一定是

Sp_addlinkedsrvlogin中设置的用户，上面使用的是sa,如果是windows集成验证，将会报错

查看连接服务器信息

select name,product,provider,data_source,query_timeout,lazy_schema_validation,is_remote_login_enabled,is_rpc_out_enabled
from sys.servers
where is_linked=1

配置链接服务器属性

exec sp_serveroption '别名','name','远程服务器IP'
--标注存储
EXEC sp_serveroption
@server = 'server', --配置属性的链接服务器的名称
@optname = 'option_name', --要配置的选项
@optvalue = 'option_value' --选项的新值
--附参数
参数描述
collation compatible 如果你确认SQL Server实例与远程SQL Server拥有相同的排序规则就启用这个设置。由于SQL Server不再需要对数据源之间的字符列执行比较操作，把它们假定为相同的排序规则，这样做可以提升性能
collation name 如果启用了use remote collation并且是非SQL Server的数据源，则collation name指定远程服务器排序规则的名称。这个排序规则名称必须是SQL Server所支持的
connect timeout 指定在超时发生之前到链接服务器的连接会尝试多少秒。如果数值为“0”，sp_ configure的remote query timeout的服务器值用来当作默认值
data access 如果启用，就允许分布式查询访问
lazy schema validation 如果设为true，则架构不会在查询开始时去检测远程表。尽管这样会减少远程查询的负载，但是如果架构发生了变化并且你没有进行架构检测，比如说查询中引用的对象不能与查询命令进行通信，就会生成错误
query timeout 指定查询等待的超时值（秒数）。如果这个值为0，则query wait选项使用sp_configure值
rpc 启用从服务器进行远程过程调用
rpc out 启用远程过程调用到服务器
use remote collation 指定是使用远程服务器排序规则（true）还是本地服务器排序规则（false）

删除链接服务器属性

EXEC sp_dropserver 'Oracle107','droplogins'
--标注存储
EXEC sp_dropserver
@server = 'server' --从SQL Server实例中删除的链接服务器的名称
,@droplogins = 'droplogins' --如果指定droplogins，则在删除链接服务器之前要删除登录名映射

查看链接登录名

select s.name linkedServerName,s.data_source linkedserver_source,s.is_linked,s.modify_date,
ll.remote_name,ll.local_principal_id,ll.uses_self_credential,
p.name localLoginName
from sys.Linked_logins ll
inner join sys.servers s on s.server_id=ll.server_id
left join sys.server_principals p on p.principal_id=ll.local_principal_id
where s.is_linked = 1

删除链接服务器登录名映射

exec sp_droplinkedsrvlogin '远程服务器IP',NULL
--标注存储
EXEC sp_droplinkedsrvlogin
@rmtsrvname = '远程服务器IP' --登录名映射的链接服务器名称
@locallogin = NULL --这是从链接服务器删除的SQL Server登录或Windows用户映射的名称