sql?server跨服务器操作数据库的图文方法(linkedserver)-尊龙凯时

sql server management studio (s**s) 是用于管理sql server 基础结构的集成环境。 使用 s**s，可以访问、配置、管理和开发 sql server、azure sql 数据库和 sql 数据仓库的所有组件。 s**s 在一个综合实用工具中汇集了大量图形工具和丰富的脚本编辑器，为各种技能水平的开发者和数据库管理员提供对 sql server 的访问权限。

什么是跨服务器操作？

跨服务器操作就是可以在本地连接到远程服务器上的数据库，可以在对方的数据库上进行相关的数据库操作，比如增删改查。

为什么要进行跨服务器操作

随着数据量的增多，业务量的扩张，需要在不同的服务器安装不同的数据库，有时候因为业务需要，将不同的服务器中的数据进行整合，这时候就需要进行跨服务器操作了。

跨服务器操作的工具是什么？

dblink(数据库链接)，顾名思义就是数据库的链接，就像电话线一样，是一个通道，当我们要跨本地数据库，访问另外一个数据库表中的数据时，本地数据库中就必须要创建远程数据库的dblink,通过dblink本地数据库可以像访问本地数据库一样访问远程数据库表中的数据。

方法一：用s**s创建sql server远程链接服务器(linkedserver)--简单链接到远程sqlserver

1. 打开s**s -->登录到本地数据库 --> 服务器对象 --> 链接服务器(右键) --> 新建链接服务器，如下图：

2. 在弹出的对话框中输入相关信息

● 在【链接服务器】输入对方服务器的ip地址；

● 在【服务器类型】中选择【sql server】；

3. 点击左侧的【安全性】，出现如下页面，在第3步中输入对方数据库的账号密码即可。

点击确定按钮后，链接服务器(linkedserver)就创建成功了。这时可以看到创建好的链接服务器：

查看链接服务器的代码： 在创建好的链接服务器上点右键，编写链接服务器脚本为 --> create到 -->新查询编辑器窗口，即可打开刚刚创建的链接服务器的脚本。

--链接服务器(linkedserver)创建完成后会自动生成相关代码 —— 链接到远程sqlserver数据库：

exec master.dbo.sp_addlinkedserver @server = n'192.168.110.189,1433',@srvproduct=n'sql server';
-- @rmtsrvname
exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname=n'192.168.110.189',@useself=n'false',@locallogin=null,@rmtuser=n'sa',@rmtpassword='########';

注意： 这里有一个弊端，那就是链接的是整个远程sqlserver中的所有数据库(一般只需要一个特定的数据库)，而且链接服务器的名称是个ip且无法自定义！ 所以，最好的方式还是通过代码直接创建链接数据库(见“三、代码详解”)。

链接服务器(linkedserver)就创建成功后，我们就可以用创建好的dblink链接到远程的linked服务器了。下面我们用创建好的试着查询对方服务器上的表来验证一下。

--查询链接服务器(linkedserver)中数据的方法： [dblink名].[对方数据库名].[对方数据库下模式名].[对方数据库表名]

select * from [192.168.110.189].[erp25new].[dbo].[fee_data]

上面from字段后面依此是[dblink名].[对方数据库名].[对方数据库下模式名].[对方数据库表名]，表名前面的这些内容一个都不能少。

查询结果如下图：

方法二：s**s创建sqlserver链接服务器(linkedserver)--自定义链接到sqlserver的其它数据库

1. 【常规】选择页：

2.【安全性】选择页：

自定义链接数据库到sqlserver【新建链接服务器】对话框中需输入的相关信息说明：

1.【常规】页

● 在【链接服务器】中，输入 自定义的链接服务器别名，如：dblink_to_testdb

● 在【服务器类型】中选择【其他数据源】；

?[提供程序]中选择 第一个microsoft ole db provider for sql server

?[产品名称]中，可以空白不填，也可以填写sql server { 注意提供程序是ole db provider for sql server时产品名称这里必须为空白！}

?[数据源]中 远程数据库的地址,端口\实例名 ，如 10.10.0.73,1433\mssqlserver

?[访问接口字符串]中，可以空着不填； 也可以填下方的：(注意######是密码,请换成自己的密码)

provider=sqloledb;data source=10.10.0.73,1433\mssqlserver;initial catalog=testdb;user id=apps;password=#####;

?[目录]就是数据库名称，这里填上我们需要远程连上的数据库 testdb (可以换成自己实际的)。

2.【安全性】页

● 选择【使用此安全上下文建立连接(m)】

?[远程登录]： 远程数据库的连接账号

?[使用密码]： 远程数据库连接账号的密码

--链接服务器(linkedserver)创建完成后会自动生成相关代码 —— 链接到远程的sqlserver数据库(自定义)：
exec master.dbo.sp_addlinkedserver @server = n'dblink_to_testdb',@srvproduct=n'',@provider=n'sqlncli', @datasrc=n'10.10.0.73';exec master.dbo.sp_addlinkedsrvlogin@rmtsrvname=n'dblink_to_testdb',@useself=n'false',@locallogin=null,@rmtuser=n'apps',@rmtpassword='########';
/****** 实际例子 系统生成的object: linkedserver [dblink_to_testdb] ******/
use [master]
go
exec master.dbo.sp_addlinkedserver @server = n'dblink_to_testdb', @srvproduct=n'', @provider=n'sqlncli', @datasrc=n'10.10.0.73,1433\mssqlserver', @catalog=n'testdb'
/*for security reasons the linked server remote logins password is changed with ########*/
exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname=n'test',@useself=n'false',@locallogin=null,@rmtuser=n'apps',@rmtpassword='########'

其他方式： 提供程序换成其它的， 如本机sql server native client 11.0 (sql server native client 11.0 不支持连接到sql server 2000或更早的版本) 等

方法三：用s**s创建sqlserver链接服务器(linkedserver)--链接到非sqlserver的其它数据库

四、代码详解：方法一和方法二是通过s**s直接操作的,下方直接使用sql脚本来创建链接服务器(linkedserver)

a. s**s链接到远程sqlserver数据库

(本地sqlserver数据库链接服务器(linkedserver)到远程sqlserver数据库。)

--linkedserver链接到远程sqlserver数据库：

--1. 声明将要链接的‘链接名称(自定义)’,远程数据库产品名(或别名),(提供商,数据库服务器地址及实例名)

exec master.dbo.sp_addlinkedserver @server = n'dblink_to_testdb',@srvproduct=n'sql server';

--2. 声明‘链接名称(自定义)’,@useself=n'false',@locallogin=null,将要链接的数据库服务器的账号和密码

exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname=n'192.168.110.189',@useself=n'false',@locallogin=null,@rmtuser=n'sa',@rmtpassword='########';

b. s**s链接到远程非sqlserver数据库

(本地sqlserver数据库链接服务器(linkedserver)到远程非sqlserver的数据库。如远程的mysql、oracle等数据库。)

--链接到远程的非sqlserverd数据库(如链接到远程mysql、oracle等数据库)：

--1. 声明‘自定义的链接名称’,远程数据库产品名(或别名),提供商,数据库服务器地址及实例名

exec master.dbo.sp_addlinkedserver @server = n'test_sql_server',@srvproduct=n'test',@provider=n'sqlncli11', @datasrc=n'192.168.110.189';-

-2. 声明登录信息 ‘自定义的链接名称’,@useself=n'false',@locallogin=null,远程数据库的账号和密码

exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname=n'test_sql_server',@useself=n'false',@locallogin=null,@rmtuser=n'sa',@rmtpassword='########';

实际例子-sql server通过linkserver连接mysql

--通过s**s链接到远程mysql数据库(sql server连接mysql)--使用的访问接口为：mysql provider for ole db--
exec master.dbo.sp_addlinkedserver @server = n'dblink_to_mysqltestdb', @srvproduct = n'mysql', @provider = n'msdasql', @provstr = n'driver={mysql odbc 5.1 driver};server=10.167.69.6,3306/sytv;database=testdb;user=root;password=root;option=3';--
exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname = n'dblink_to_mysqltestdb', @useself = n'false', @locallogin = n'10.167.69.6,3306/sytv', @rmtuser = n'root', @rmtpassword = n'root';

实际例子-sql server通过linkserver连接oracle

--通过s**s链接到远程oracle数据库(sql server连接oracle)
--使用的访问接口为：oracle provider for ole db
use [master]
go
--declare oracle oledb 'oraoledb.oracle':
exec master.dbo.sp_msset_oledb_prop n'oraoledb.oracle', n'allowinprocess', 1;--create the linked server to the ect database in oracle:
exec sp_addlinkedserver 'dblink_to_oratestdb', 'oracle', 'oraoledb.oracle', '10.167.69.6/prt';--exec master.dbo.sp_addlinkedserver @server = n'dblink_to_oratestdb', @srvproduct=n'oracle', @provider=n'oraoledb.oracle', @datasrc=n'10.167.69.6/orcl'
--create the remote login for the oracle linked server:
exec sp_addlinkedsrvlogin @rmtsrvname=n'dblink_to_oratestdb',@useself=n'false',@locallogin=n'apps',@rmtuser=n'system',@rmtpassword='######'; 
--最后可以测试一下是否连接成功 --select * from openquery(dblink_to_oratestdb,'select * from system.help');