使用特定名称及特定数据源来直接指定(Add Host Names)

其实这两种方式在实际运用中是有区别的:

方式A:Add Link Server方式建立服务器之间关联.创建一个链接的服务器，使其允许对分布式的、针对 OLE DB 数据源的异类查询进行访问. 一般适用于持久的数据操作 对于数据量偏大 服务器之间交付时间长特点.

方式B: Add Host Name 利用域来唯一识别数据库以及数据库表对象. 来实现跨服务器访问. 这种方式一般比较简单 主要适用于对数据需求临时性查询是使用偏多. 不适合做大批量数据提取. 有性能瓶颈.

<2>分布式查询实现

在进行实现分布式查询之前.本次测试Demo对应的SQL版本:

确定SQL Server版本后如下会演示两种方式来实现分布式查询,并对Distributed Query中详细细节进行说明.

<2.1>链接服务器查询

链接服务器配置使 SQL Server 可以对远程服务器上的 OLE DB 数据源执行命令。链接服务器具有以下优点：

1. 访问远程服务器。
2. 能够对企业内的异类数据源发出分布式查询、更新、命令和事务。
3. 能够以相似的方式确定不同的数据源

下图显示了链接服务器配置的基础:

现在利用链接服务器方式实现数据访问远程服务器数据库CustomerDB中Users表数据先本地添加LinkServer: 

1. -- 建立连接服务器  第一步建立连接  IP方式来控制     
2. EXEC sp_addlinkedserver   '192.168.10.104' , 'SQL Server'     
3. -- 查看链接服务器信息  [测试连接成功]    
4. select name , product, provider, data_source, query_timeout, lazy_schema_validation, is_remote_login_enabled, is_rpc_out_enabled    
5. from sys.servers    
6. where is_linked= 1  

如上市建立连接服务器最简单方式.建立链接服务器过程其实调用了系统存储过程Sp_addlinkedserver. 第一个参数为Name 其实用来唯一标识链接服务器. 当然可以其他任何有意义字符串来定义,但我个人建议使用远程服务器的IP来标识.第二个参数是要添加为链接服务器的 OLE DB 数据源的产品名称. 默认为Null,如果指定”SQL Server“则无需指定其他参数.

如果你的本地装有多个数据库实例. 第一个种方式就不适用.这是就需要用SQL Server2005架构来唯一标识:

1. -- 含架构名  查询数据两种模式  
2. select top 10 * from [192.168.10.104]. wl . 架构名 . 表名     
3. -- 架构名 [采用默认架构名 ]    
4. select top 10 * from [192.168.10.104]. CustomerDB . dbo. Users  

对于SQL Server 2005架构这个概念很多人比较陌生:

在用户角色设置中需要对指定访问数据CustomerDB具有读写权限:

在远程服务器创建TEst用户时使用SQL Server身份验证方式登录 这时设置密码为RemoteDB.在使用非Sa用户进行远程：

1. -- 执行前先删除已经存在数据    
2. Exec sp_droplinkedsrvlogin [192.168.10.76],Null   
3. Exec sp_dropserver 'demodb'    
4. -- 创建服务器连接    
5. EXEC  sp_addlinkedserver    
6.  @server='demodb',-- 被访问的服务器别名   
7.         @srvproduct='',   
8.       @provider='SQLOLEDB',   
9. @datasrc='192.168.10.76'   -- 要访问的服务器  
10.   EXEC sp_addlinkedsrvlogin    
11.   'demodb', -- 被访问的服务器别名   
12.     'false',   
13.      NULL,     
14.   'Test', -- 帐号    
15.   'RemoteDB' -- 密码 

如上我们首先清除已经可能创建服务器数据记录. 然后创建服务器连接.sp_addlinkedSrvlogin系统存储过程用来创建链接服务器上远程登录之间的映射 . 即我们可以详细设置本地与远程服务器详细的映射信息. 例如设置我们特定用户访问的用户名和密码.

查询数据

1. -- 查询指定用户Test数据   
2. select * from [demodb].CustomerDB.dbo.Users -- [如上测试成功] 

查询结果

指定用户Test对CustomerDB访问数据方式测试成功.

当测试完成后我们不需要这个连接服务器是即可利用SP_DroplinkServer删除掉. 对应参数为创建时Name唯一标识. 通过Sp_helpserver来查看连接服务器详细信息.

注意如上创建连接服务器时设置srvproduct参数即OLED数据源名称时我们采用了SQlServer方式.

下面说明这种方式特点.：

这种方式是最为简单直接的一种建立链接服务器方式. 但是存在前提的. 测试发现:

在所有数据库的远程连接 dbo 的方式必须建立在SA 密码相同的基础上 ，否则容易产生无法连接的情况 Sa用户登录失败. 你也就明白这个SQlServer参数其实就是在本地数据拷贝服务器角色SysAdmin下用户SA.来对服务器进行登录. 如果你的本地Sa密码与远程服务器上密码不一致 则无法正常连接.

经过测试还发现一种情况:

利用Windows7访问XP(Sp2)系统时始终提示无法解析或拒绝连接SQL Server2005.这个问题我整了好久后来才到官方链接参数中发现.:如果你的XP系统没有打上SP4的补丁包 这个问题会始终出现. 需要特别注意.

<2.2>直接指定数据源分布式查询

其实相对第一种方式, 直接指定方式在SQL Server架构中 其实跳过本地与远程服务器建立映射关系的这一步. 通过链接关系建立 其实就是建立一种内部映射关系. 如果没有映射关系则 大部分设置需要手动控制.

直接指定数据源方式 需要开启分布式查询的基本权限 来进行查询：

1. -- 如果想使用分布式查询，必须先开通分布式查询 [外围配置 这点是所有查询操作前提]     
2. -- sp_configure--显示或更改当前服务器的全局配置设置    
3. -- reconfigure 指定如果配置设置不需要服务器停止并重新启动，则更新当前运行的值    
4. -- SQL2005默认是没有开启’Ad Hoc Distributed Queries’ 组件     
5. -- 启用权限     
6. exec sp_configure 'show advanced options',1  -- 显示高级配置    
7. reconfigure -- 更新值    
8. exec sp_configure 'Ad Hoc Distributed Queries',1 -- 启用分布式查询    
9. reconfigure   
10. go    
11.  -- 关闭分布式查询   
12. exec sp_configure 'Ad Hoc Distributed Queries',0       
13. reconfigure    
14. exec sp_configure 'show advanced options',0   
15.  reconfigure   
16.   go    
17.   -- 开启权限后 另外一种查询方式   
18. -- 查询格式    
19.  SELECT * FROM OPENDATASOURCE(    
20.   'SQLOLEDB',    
21.  'Data Source=远程ip;User ID=sa;Password=密码'    
22.    ).库名.dbo.表名   
23.   WHERE 条件   
24.   -- 需要开启权限   
25.  -- 开启权限 提示[远程的SqlServer不允许远程连接]   
26.   select * from OPENDATASOURCE('SQLOLEDB','Data Source=192.168.10.67; User ID=sa; Password=chenkai').wl.dbo.Users 

如上我们首先清除已经可能创建服务器数据记录. 然后创建服务器连接.sp_addlinkedSrvlogin系统存储过程用来创建链接服务器上远程登录之间的映射 . 即我们可以详细设置本地与远程服务器详细的映射信息. 例如设置我们特定用户访问的用户名和密码.

查询数据

1. -- 查询指定用户Test数据     
2. select * from [demodb].CustomerDB.dbo.Users -- [如上测试成功] 

查询结果

指定用户Test对CustomerDB访问数据方式测试成功.