链接服务器

核心提示:SQL
Server所谓的分布式查询是能够访问存放在同一部计算机或不同计算机上的SQL
Server或不同种类的数据源

MSSQlServer所谓的分布式查询(Distributed
Query)是能够访问存放在同一部计算机或不同计算机上的SQL
Server或不同种类的数据源, 从概念上来说分布式查询与普通查询区别
它需要连接多个MSSQL服务器也就是具有多了数据源.实现在服务器跨域或跨服务器访问.
而这些查询是否被使用完全看使用的需要.

创建链接服务器。链接服务器让用户可以对 OLE DB
数据源进行分布式异类查询。在使用 sp_addlinkedserver
创建链接服务器后,可对该服务器运行分布式查询。如果链接服务器定义为 SQL
Server 实例,则可执行远程存储过程。

Linked Server简介

我们日常使用SQL
Server数据库时,经常遇到需要在实例Instance01中跨实例访问Instance02中的数据。例如在做数据迁移时,如下语句:

insert into Instance01.DB01.dbo.Table01
   select * from Instance02.DB01.dbo.Table01

普通情况下,这样做是不允许的,因为SQL
Server默认不可以跨实例访问数据。解决方案是使用存储过程sp_addlinkedserver进行实例注册。

图片 1

  链接服务器让用户可以对 OLE DB
数据源进行分布式异类查询。在创建某一链接服务器后,可对该服务器运行分布式查询,并且查询可以联接来自多个数据源的表。如果链接服务器定义为
SQL Server 实例,则可执行远程存储过程。链接服务器具有以下优点:

  • 能够访问 SQL Server之外的数据。
  • 能够对企业内的异类数据源发出分布式查询、更新、命令和事务。
  • 能够以相似的方式确定不同的数据源。

SQL
Server所谓的分布式查询是能够访问存放在同一部计算机或不同计算机上的SQL
Server或不同种类的数据源, 从概念上来说分布式查询与普通查询区别
它需要连接多个MSSQL服务器也就是具有多了数据源。实现在服务器跨域或跨服务器访问。
而这些查询是否被使用完全看使用的需要。

 

图片 2 Transact-SQL
语法约定

Linked Server组件

链接服务器定义指定了下列对象:

  • OLE DB 访问接口
  • OLE DB 数据源

图片 3

  “OLE DB 访问接口” 是管理特定数据源并与其交互的 DLL。 “OLE DB 数据源”
标识可通过 OLE DB 访问的特定数据库。
虽然通过链接服务器定义查询的数据源通常是数据库,但 OLE DB
访问接口对各种文件和文件格式仍可用。
这些文件和文件格式包括文本文件、电子表格数据和全文内容搜索的结果。

Microsoft SQL Server本机客户端 OLE DB 访问接口 (PROGID: SQLNCLI11) 是
SQL Server 的正式 OLE DB 访问接口。

通常,链接服务器用于处理分布式查询。
当客户端应用程序通过链接服务器执行分布式查询时, SQL Server
将分析命令并向 OLE DB 发送请求。
为使数据源能通过链接服务器返回数据,该数据源的 OLE DB 访问接口 (DLL)
必须与 SQL Server 的实例位于同一服务器上

本篇将演示利用SQL ServerExpress链接远程SQL
Server来获取数据方式来详细说明分布式查询需要注意细节。先看一下系统架构数据查询基本处理:

本篇将演示利用SQlExpress链接远程SQlServer来获取数据方式来详细说明分布式查询需要注意细节.先看一下系统架构数据查询基本处理:

图片 4  语法

Linked Server的安全性

当用户登录到本地服务器并执行分布式查询,以访问链接服务器上的表时,本地服务器必须登录链接服务器上,代表该用户访问该表。使用存储过程sp_addlinkedsrvlogin来指定本地服务器用于登录链接服务器的登录凭据。
  例如,使用远程密码 8r4li034j7$ 为链接服务器 S1 建立了一个从本地登录名
U1 到远程登录名 U2 的映射(如在SQL
Server中使用sa账户登录到远程oracle服务器的sys账户)。在本地登录名 U1
执行访问链接服务器 S1 中存储的表的分布式查询时,如果 SQL Server
连接到链接服务器 S1,则将 U2 和 8r4li034j7$ 分别作为用户 ID
和密码进行传递。
  本地服务器上的所有登录和链接服务器上的远程登录之间的默认映射通过执行
sp_addlinkedserver
自动创建。默认映射表示,当代表本地登录连接到链接服务器时,SQL Server
使用本地登录的用户凭据。这相当于在链接服务器的 @useself 设置为 true
的情况下执行 sp_addlinkedsrvlogin,无需指定本地用户名。
  创建或更新 SQL Server
本地实例上的登录名与远程服务器中安全帐户之间的映射语法如下:

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname' 
     [ , [ @useself = ] 'TRUE' | 'FALSE' | NULL ] 
     [ , [ @locallogin = ] 'locallogin' ] 
     [ , [ @rmtuser = ] 'rmtuser' ] 
     [ , [ @rmtpassword = ] 'rmtpassword' ] 
  • @rmtsrvname:应用登录映射的链接服务器的名称。
  • @useself:确定是否通过模拟本地登录名或显式提交登录名和密码来连接到
    rmtsrvname。默认值为 TRUE。
      值为 TRUE
    指定登录名使用其自己的凭据来连接到rmtsrvname,rmtuser和rmtpassword自变量被忽略。
    FALSE指定rmtuser和rmtpassword参数用于连接到rmtsrvname。
    如果rmtuser和rmtpassword也是设置为
    NULL,任何登录名或密码用于连接到链接服务器。
  • @locallogin:指要本地哪个账户登录到远程服务器。如果不为 NULL,
    locallogin可以是一个SQL Server登录或 Windows NT用户。
  • @rmtuser:用于连接到远程服务器的登录名。
  • @rmtpassword:用于连接到远程服务器的密码。

【示例】

A. 使用各自的用户凭据将所有本地登录连接到链接服务器 **
以下示例将创建一个映射,以确保所有到本地服务器的登录都使用
其各自的**用户凭据连接到链接服务器
Accounts。

EXEC sp_addlinkedsrvlogin 'Accounts'
或
EXEC sp_addlinkedsrvlogin 'Accounts', 'true'

B. 使用不同的用户凭据将特定的登录连接到链接服务器
以下示例将创建一个映射,以确保 Windows 用户 DomainMary 使用登录名
MaryP 和密码 d89q3w4u 连接到链接服务器 Accounts。

EXEC sp_addlinkedsrvlogin
        'Accounts',
        'false',
        'DomainMary', 
        'MaryP', 
        'd89q3w4u'

C. 不使用任何用户凭据将所有本地登录连接到链接服务器
此示例创建一个映射,以确保所有到本地服务器的登录都连接到链接服务器
mydb,而不使用登录或密码(mydb 不需要登录或密码)。

EXEC sp_addlinkedsrvlogin 'mydb', 'false', NULL, NULL, NULL
or
EXEC sp_addlinkedsrvlogin 'mydb', 'false'

D. 将特定的登录连接到 Excel 电子表格(链接服务器)

EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   'c:MyDataDistExcl.xls',
   NULL,
   'Excel 5.0'
GO
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', 'sa', 'Admin', NULL

当然如果采用了分布式查询
我们系统采取数据DataBase也就可能在多个远程[Remote Server]上访问时:

 图片 5

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ] 
     [ , [ @provider= ] 'provider_name' ]
     [ , [ @datasrc= ] 'data_source' ] 
     [ , [ @location= ] 'location' ] 
     [ , [ @provstr= ] 'provider_string' ] 
     [ , [ @catalog= ] 'catalog' ] 

创建Linked Server

(1)在SSMS中创建链接服务器

  1. 在 SQL Server Management Studio 中,打开对象资源管理器,展开“Server
    Object”,右键单击“Linked Servers”,然后单击“New Linked Servers”。
![](https://upload-images.jianshu.io/upload_images/4444657-4f8bbfb8abd125a0.png)
  1. 在“常规”页上的“链接服务器”框中,键入您链接到的 SQL Server
    实例的名称。

如果该 SQL Server 实例是默认实例,则输入承载 SQL Server
实例的计算机的名称。如果该 SQL Server
是命名实例,则输入计算机名称和实例名称,例如 AccountingSQLExpress。

  1. 在“服务器类型”区域中,选择 SQL Server 以便指示该链接服务器是 SQL
    Server 的另一个实例。
  2. 在“安全性”页上,指定在原始 SQL Server
    连接到链接服务器时将使用的安全上下文。在通过使用其域登录名连接用户的域环境中,选择“使用登录名的当前安全上下文建立连接”通常是最佳选择。在用户通过使用
    SQL Server 登录名连接到原始 SQL Server
    时,最佳选择通常是选择“通过使用此安全上下文”,然后提供在链接服务器上进行身份验证时所必需的凭据。
  3. 单击“确定”。

(2)使用 Transact-SQL创建链接服务器

sp_addlinkedserver [@server=] 'server'
     [ , [ @srvproduct= ] 'product_name']
     [ , [ @provider= ] 'provider_name' ]  
     [ , [ @datasrc= ] 'data_source' ]   
     [ , [ @location= ] 'location' ]   
     [ , [ @provstr= ] 'provider_string' ]   
     [ , [ @catalog= ] 'catalog' ]   

参数说明

  • @server:链接服务器别名(习惯上直接使用目标服务器IP,或取个别名如:Test,反正想取什么名就取什么)
  • @srvproduct:要添加为链接服务器的 OLE DB 数据源的产品名称。
    如果为 SQL
    Server,则不必指定provider_name、data_source、location、provider_string
    和 catalog。
  • @provider:与此数据源对应的 OLE DB 访问接口的唯一编程标识符
    (PROGID)。但如果忽略 provider_name,则使用 SQLNCLI。
  • @datasrc:链接服务器名,也就是与指定链接服务器对应的OLE
    DB数据源属性。
  • @location:与指定链接服务器对应的OLE DB位置属性。
  • @catalog:与指定链接服务器对应的OLE DB目录属性。

@xxx这些名词在写的时候是可以省略的。

【注释】
  下表显示为能通过 OLE DB
访问数据源而建立链接服务器的方法。对于特定的数据源,可以使用多种方法为其设置链接服务器;该表中可能有多行适用于一种数据源类型。该表还显示了用于设置链接服务器的
sp_addlinkedserver 参数值。

图片 6

图片 7

  • “任何”指产品名称可以任意。
  • 如果未指定访问接口名称或将 SQL Server 指定为产品名称,则 Microsoft
    SQL Server Native Client OLE DB 访问接口将是用于 SQL Server
    的访问接口。即使指定了较早版本的访问接口名称
    SQLOLEDB,在保存到目录时该名称也将改为 SQLNCLI。

【示例】

A. 访问MSSQL

  1. 在查询编辑器中,输入以下 Transact-SQL 命令以便链接到名为
    SRVR002ACCTG 的 SQL Server 实例:

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver 
    @server = N'SRVR002ACCTG', 
    @srvproduct = N'SQL Server' ;
GO
  1. 执行以下代码,以便将链接服务器配置为使用正在使用链接服务器的登录名的域凭据。

EXEC master.dbo.sp_addlinkedsrvlogin 
    @rmtsrvname = N'SRVR002ACCTG', 
    @locallogin = NULL , 
    @useself = N'True' ;
GO
  1. 跟进:在创建链接服务器后采取的步骤

查询的数据库格式为:

[别名].[数据库名].[架构名].[表名]

插入数据库格式为:

insert [数据库名].[架构名].[表名] select * from [别名].[数据库名].[架构名].[表名]

select * into [数据库名].[架构名].[新表名] from [别名].[数据库名].[架构名].[表名]
  • 执行下面的代码,测试与链接服务器的连接。以下示例返回链接服务器上数据库的名称。

SELECT name FROM [SRVR002ACCTG].master.sys.sysdatabases;
  • 使用由四部分组成的名称引用链接服务器上的对象。执行以下代码,以便返回本地服务器上所有登录名的列表及其在链接服务器上的匹配登录名。

-- sys.server_principals可以查询登入名拥有的服务器角色
SELECT local.name AS LocalLogins, linked.name AS LinkedLogins
FROM master.sys.server_principals AS local
LEFT JOIN [SRVR002ACCTG].master.sys.server_principals AS linked
    ON local.name = linked.name;

B. 访问ACCESS数据库

  1. Microsoft.Jet.OLEDB.4.0 访问接口连接到使用 2002-2003 格式的
    Microsoft Access 数据库。 下面的示例将创建一个名为 SEATTLE Mktg
    的链接服务器。

EXEC sp_addlinkedserver   
   @server = N'SEATTLE Mktg',   
   @provider = N'Microsoft.Jet.OLEDB.4.0',   
   @srvproduct = N'OLE DB Provider for Jet',  
   @datasrc = N'C:MSOfficeAccessSamplesNorthwind.mdb';  
GO  

@datasrc=’ServiceName’,即数据源的格式如下:
(1)盘符:路径文件名
(2)网络名共享名文件名 (网络版本)

  1. 若要访问Access数据库中的数据,使用以下的查询格式:

select * from 别名...表名

C. 访问Oracle数据库

访问前需要SQL SERVER服务器中安装ORACLE客户端。

  1. 创建链接服务器

EXEC sp_addlinkedserver
        @server ='ora', -- 要创建的链接服务器别名
        @srvproduct='Oracle', --产品名称    
        @provider='MSDAORA', 
        @datasrc='oracle客户端设置的本地net服务名'   --数据源
GO

@datasrc可以在oracleora92networkadmintnsnames.ora文件中查看,文件中的SERVICE_NAME就是服务名。

  1. 若要访问Oracle数据库中的数据,使用以下的查询格式:

-- 四部分名称全部用大写,因为在Oracle的数据字典中为大写
SELECT * FROM 链接服务器别名..ORACLE用户名.表名(视图)
  1. 若要执行Oracle数据库中的存储过程,使用以下的查询:

SELECT * FROM OPENQUERY(别名, ’exec 用户名.存储过程名’)

D. 访问EXCEL

  1. 若要创建链接的服务器定义使用MicrosoftOLE DB Provider for Jet 访问
    Excel 电子表格中 1997年-2003年格式中,首先创建的命名的区域在 Excel
    中通过指定要选择的 Excel 工作表的行和列。
    这样,可以在分布式查询中将此范围的名称引用为表名称。

EXEC sp_addlinkedserver 'ExcelSource',  
   'Jet 4.0',  -- 产品名称
   'Microsoft.Jet.OLEDB.4.0',  -- provider
   'c:MyDataDistExcl.xls',  
   NULL,  
   'Excel 5.0';  
GO  

若要访问 Excel 电子表格中的数据,请将单元范围与名称相关联。
以下查询通过使用先前设置的链接服务器,将指定的命名范围 SalesData
作为表来访问。

SELECT * FROM ExcelSource...SalesData;  
  1. 如果 SQL Server 在可以访问远程共享的域帐户下运行,则可以使用 UNC
    路径来代替映射驱动器。

EXEC sp_addlinkedserver 'ExcelShare',  
   'Jet 4.0',  
   'Microsoft.Jet.OLEDB.4.0',  
   '\MyServerMyShareSpreadsheetsDistExcl.xls',  -- UNC 路径
   NULL,  
   'Excel 5.0'; 

如何在SSMS查看Linked Server?
  在SSMS的Server Object——>Linked
Servers
可以找到本数据库所有的数据库链接,如下图:

图片 8

  此外,通过执行**Programmability——>Store Procedures——>System
Stored Procedures——>sp_linkedservers
**这个存储过程也可以查询到当前数据库的所有linked server。如下图:

图片 9

如上截取系统架构中关于数据与缓存流向中涉及的分布式查询业务,
当我们从客户端Client发起请求数据时。 首先检查MemCache
Server缓存服务器是否有我们想要数据。 如果没有我需要查询数据库。
而此时数据要求查询多个远程服务器上多个数据库中表,
这时利用分布式查询。获得数据 然后更新我们在缓存服务器MemCache
Server上数据保持数据更新同步,
同时向客户端Client直接返回数据。那如何来执行这一系列动作中最为关键分布式查询?

 

图片 10  参数

OpenQuery

有时候我们希望在一个sqlserver下访问另一个sqlserver数据库上的数据,或者访问其他oracle数据库上的数据,要想完成这些操作,我们首要的是创建数据库链接(Linked
Server)。
数据库链接能够让本地的一个sqlserver登录用户映射到远程的一个数据库服务器上,并且像操作本地数据库一样。OpenQuery
就是SQL Server用来与其他Server交互的一种技术,其他数据库在OpenQuery
表达式中是以Linked Server 存在的。
  OPENQUERY 可以在查询的 FROM 子句中引用,就好象它是一个表名。OPENQUERY
也可以作为 INSERT、UPDATE 或 DELETE 语句的目标表进行引用。

语法

OPENQUERY ( linked_server ,'query' )  
  • linked_server :表示链接服务器名称的标识符。
  • query:在链接服务器中执行的查询字符串。 该字符串的最大长度为 8 KB。

示例
A. 执行 UPDATE 传递查询

UPDATE OPENQUERY(MSSQLSvr, 'SELECT * FROM tableName') SET role_id = 'ForTest' WHERE id = 3

可以简写为:

UPDATE MSSQLSvr.DataBaseName.dbo.tableName SET role_id = 'ForTest' WHERE id = 3;

B. 执行 INSERT 传递查询

INSERT INTO OPENQUERY(MSSQLSvr, 'SELECT * FROM tableName WHERE 1>1') VALUES(3,6,'ANDY');

可以简写为:

INSERT INTO MSSQLSvr.DataBaseName.dbo.tableName VALUES(3,6,'ANDY');

C. 执行 DELETE 传递查询

DELETE OPENQUERY(MSSQLSvr, 'SELECT * FROM tableName') WHERE id IN (1,3);

可以简写为:

DELETE MSSQLSvr.DataBaseName.dbo.tableName WHERE id IN (1,3);

D. 执行SELECT传递查询

SELECT 1 FROM OPENQUERY(OracleSvr,'SELECT * FROM stuMan.person WHERE id > 10');

E. 执行存储过程

SELECT EXECUTE testProcedure(isnull(name,' ')) 
   FROM OPENQUERY(OracleSvr,'SELECT name FROM stuMan.person')

备注
(1)OPENQUERY 不接受参数变量,也就是说:

DELETE OPENQUERY (linked_server, 'SELECT id FROM msgs WHERE id=' + @id);

是不正确的,但可以这样做:

DELETE OPENQUERY (linked_server, 'SELECT id FROMmsgs') WHERE id=@id); 。

(2)若没有参数变量,最好把条件表达式卸载OPENQUERY如里面,因为在linked_server执行完筛选才RETURN的,TABLE的数据量越大效果越明显。如下面的SQL语句:

select * from openquery( OracleSvr ,'select * from kxx_file  ') 
     where kxx07='2009-10-08'

该语句查询耗时18s;很明显RETURN的数据相当大,再进行筛选;而

select * from openquery( OracleSvr ,
  'select * from kxx_file where kxx07={ ts ''2009-10-08 00:00:00.000''} ')

该语句查询耗时3s。
(3)T-SQL 中,INSERT 可以省略 INTO,DELETE 也可以省略 FROM。
(4)我们在对表进行查询时,通常都会有传入的参数。在OpenQuery中使用参数和我们平时写的数据库语句会不会有所不同呢?对于Int类型的参数,没有什么不同。但是,如果是字符类型的参数,比如,你要在Openquery中执行如下的数据库语句:

select * from tbl_emp Where empName = 'leo'

那么我们就必须对这个参数进行转义,单变双实现字符转义。如下:

select * from OpenQuery([linkedServerName],'Select * from table1 where empName = ''leo''')

即我们必须对”进行转义,所以” 就变成了””。

《1》分布式查询方式

 

[ @server = ] ‘*server*’
要创建的链接服务器的名称。server 的数据类型为
sysname,没有默认值。

我们知道Microsoft微软公用的数据访问的API是OLE_DB, 而对数据库MSSQL
Server 2005的分布式查询支持也是OLE_DB方式.SQL Server
用户可以使用分布式查询访问以下内容:

 

A:存储在多个 SQL Server 实例中的分布式数据

 

[ @srvproduct = ] ‘*product_name*’
要添加为链接服务器的 OLE DB 数据源的产品名称。product_name
的数据类型为 nvarchar(128),默认值为 NULL。如果为 SQL
Server
,则不必指定
provider_namedata_sourcelocationprovider_string
catalog

B:存储在各种可以使用 OLE DB
访问接口访问的关系和非关系数据源中的异类数据

 

OLE DB 访问接口将在称为行集的表格格式对象中公开数据。SQL Server 允许在
Transact-SQL 语句中像引用 SQL Server 表一样引用

 

[ @provider = ] ‘*provider_name*’
与此数据源对应的 OLE DB 访问接口的唯一编程标识符
(PROGID)。对于当前计算机中安装的指定 OLE DB 访问接口,provider_name
必须唯一。provider_name 的数据类型为 nvarchar(128),默认值为
NULL;但如果忽略 provider_name,则使用 SQLNCLI。(使用 SQLNCLI 并且
SQL Server 将重定向到 SQL Server Native Client OLE DB
访问接口的最新版本。)OLE DB 访问接口应以指定的 PROGID 在注册表中注册。

OLE DB 访问接口中的行集,[其实不用关心这个行集概念 它的功能类似SQL
Server中临时表 不过它容积更大 能容纳类型更多 更丰富]

 

SQL Server 实例的客户机与 OLE DB 访问接口之间的连接 如下图:

 

[ @datasrc = ] ‘*data_source*’
由 OLE DB 访问接口解释的数据源的名称。data_source 的数据类型为
nvarchar(4000)data_source 作为 DBPROP_INIT_DATASOURCE
属性传递以初始化 OLE DB 访问接口。

发表评论

电子邮件地址不会被公开。 必填项已用*标注