基础知识介绍
以SQL Server的数据库管理工具SSMS(SQL Server Management Studio)为平台进行操作。
SQL Server Management Studio (SSMS) 是用于管理SQL Server 基础结构的集成环境。 使用 SSMS,可以访问、配置、管理和开发 SQL Server、Azure SQL 数据库和 SQL 数据仓库的所有组件。 SSMS 在一个综合实用工具中汇集了大量图形工具和丰富的脚本编辑器,为各种技能水平的开发者和数据库管理员提供对 SQL Server 的访问权限。
什么是跨服务器操作?
跨服务器操作就是可以在本地连接到远程服务器上的数据库,可以在对方的数据库上进行相关的数据库操作,比如增删改查。
为什么要进行跨服务器操作
随着数据量的增多,业务量的扩张,需要在不同的服务器安装不同的数据库,有时候因为业务需要,将不同的服务器中的数据进行整合,这时候就需要进行跨服务器操作了。
跨服务器操作的工具是什么?
DBLINK(数据库链接),顾名思义就是数据库的链接,就像电话线一样,是一个通道,当我们要跨本地数据库,访问另外一个数据库表中的数据时,本地数据库中就必须要创建远程数据库的dblink,通过dblink本地数据库可以像访问本地数据库一样访问远程数据库表中的数据。
方法一:用SSMS创建SQL Server远程链接服务器(LinkedServer)--简单链接到远程SqlServer
1. 打开SSMS -->登录到本地数据库 --> 服务器对象 --> 链接服务器(右键) --> 新建链接服务器,如下图:
2. 在弹出的对话框中输入相关信息
● 在【链接服务器】输入对方服务器的IP地址;
● 在【服务器类型】中选择【SQL Server】;
3. 点击左侧的【安全性】,出现如下页面,在第3步中输入对方数据库的账号密码即可。
点击确定按钮后,链接服务器(LinkedServer)就创建成功了。这时可以看到创建好的链接服务器:
查看链接服务器的代码: 在创建好的链接服务器上点右键,编写链接服务器脚本为 --> Create到 -->新查询编辑器窗口,即可打开刚刚创建的链接服务器的脚本。
1 2 3 4 |
--链接服务器(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名].[对方数据库名].[对方数据库下模式名].[对方数据库表名],表名前面的这些内容一个都不能少。
查询结果如下图:
方法二:SSMS创建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='########';
![复制代码](https://common.cnblogs.com/images/copycode.gif)
/****** 实际例子 系统生成的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='########'
![复制代码](https://common.cnblogs.com/images/copycode.gif)
其他方式: 提供程序换成其它的, 如本机SQL Server Native Client 11.0 (SQL Server Native Client 11.0 不支持连接到SQL Server 2000或更早的版本) 等
方法三:用SSMS创建SQLServer链接服务器(LinkedServer)--链接到非SqlServer的其它数据库
四、代码详解:方法一和方法二是通过SSMS直接操作的,下方直接使用sql脚本来创建链接服务器(LinkedServer)
A. SSMS链接到远程SQLServer数据库
(本地SQLServer数据库链接服务器(LinkedServer)到远程SQLServer数据库。)
![复制代码](https://common.cnblogs.com/images/copycode.gif)
-- 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='########';
![复制代码](https://common.cnblogs.com/images/copycode.gif)
B. SSMS链接到远程非SQLServer数据库
(本地SQLServer数据库链接服务器(LinkedServer)到远程非SQLServer的数据库。如远程的MySQL、Oracle等数据库。)
![复制代码](https://common.cnblogs.com/images/copycode.gif)
-- 链接到远程的非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='########';
![复制代码](https://common.cnblogs.com/images/copycode.gif)
实际例子-SQL Server通过Linkserver连接MySql
![复制代码](https://common.cnblogs.com/images/copycode.gif)
-- 通过SSMS链接到远程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';
![复制代码](https://common.cnblogs.com/images/copycode.gif)
实际例子-SQL Server通过Linkserver连接Oracle
![复制代码](https://common.cnblogs.com/images/copycode.gif)
-- 通过SSMS链接到远程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');
![复制代码](https://common.cnblogs.com/images/copycode.gif)
参考文章:https://www.cnblogs.com/RicCC/archive/2009/07/23/sql-server-linked-server.html
https://www.cnblogs.com/ljhdo/p/5171006.html
sp_addlinkedserver (Transact-SQL)
sp_addlinkedsrvlogin (Transact-SQL)
标签:--,数据库,Server,SQL,服务器,链接 From: https://www.cnblogs.com/lkj371/p/16723831.html