查询.docx
《查询.docx》由会员分享，可在线阅读，更多相关《查询.docx（13页珍藏版）》请在冰豆网上搜索。
查询
Mssql技巧:
详解SQLServer分布式查询
[来源：
mssql教程|作者：
mssql数据库|时间：
2010-9-13|去论坛]
-
-
SQLServer所谓的分布式查询（DistributedQuery）是能够访问存放在同一部计算机或不同计算机上的SQLServer或不同种类的数据源，从概念上来说分布式查询与普通查询区别它需要连接多个MSSQL服务器也就是具有多了数据源。
实现在服务器跨域或跨服务器访问。
而这些查询是否被使用完全看使用的需要。
本篇将演示利用SQLServerExpress链接远程SQLServer来获取数据方式来详细说明分布式查询需要注意细节。
先看一下系统架构数据查询基本处理：
当然如果采用了分布式查询我们系统采取数据DataBase也就可能在多个远程［RemoteServer］上访问时：
如上截取系统架构中关于数据与缓存流向中涉及的分布式查询业务，当我们从客户端Client发起请求数据时。
首先检查MemCacheServer缓存服务器是否有我们想要数据。
如果没有我需要查询数据库。
而此时数据要求查询多个远程服务器上多个数据库中表，这时利用分布式查询。
获得数据然后更新我们在缓存服务器MemCacheServer上数据保持数据更新同步，同时向客户端Client直接返回数据。
那如何来执行这一系列动作中最为关键分布式查询？
《1》分布式查询方式
我们知道Microsoft微软公用的数据访问的API是OLE_DB，而对数据库MSSQLServer2005的分布式查询支持也是OLE_DB方式.SQLServer用户可以使用分布式查询访问以下内容：
A：
存储在多个SQLServer实例中的分布式数据
B：
存储在各种可以使用OLEDB访问接口访问的关系和非关系数据源中的异类数据
OLEDB访问接口将在称为行集的表格格式对象中公开数据。
SQLServer允许在Transact-SQL语句中像引用SQLServer表一样引用
OLEDB访问接口中的行集，［其实不用关心这个行集概念它的功能类似SQLServer中临时表不过它容积更大能容纳类型更多更丰富］
SQLServer实例的客户机与OLEDB访问接口之间的连接如下图：
从上图可以看出。
客户端借助OLEDB接口可以访问Oracle/MSJet/MSSQL/ODBC/第三方等这些丰富数据源来我们分布式查询提供数据。
说了这么多关于OLEDB底层支持。
关于在MSSQL2005中则支持两种方式来进行分布式查询：
使用添加链接服务器方式（AddLinkServer）
使用特定名称及特定数据源来直接指定（AddHostNames）
其实这两种方式在实际运用中是有区别的：
方式A:
AddLinkServer方式建立服务器之间关联。
创建一个链接的服务器，使其允许对分布式的、针对OLEDB数据源的异类查询进行访问。
一般适用于持久的数据操作对于数据量偏大服务器之间交付时间长特点。
方式B：
AddHostName利用域来唯一识别数据库以及数据库表对象。
来实现跨服务器访问。
这种方式一般比较简单主要适用于对数据需求临时性查询是使用偏多。
不适合做大批量数据提取。
有性能瓶颈。
《2》分布式查询实现
在进行实现分布式查询之前。
本次测试Demo对应的SQL版本：
确定SQLServer版本后如下会演示两种方式来实现分布式查询，并对DistributedQuery中详细细节进行说明。
《2.1》链接服务器查询
链接服务器配置使SQLServer可以对远程服务器上的OLEDB数据源执行命令。
链接服务器具有以下优点：
访问远程服务器。
能够对企业内的异类数据源发出分布式查询、更新、命令和事务。
能够以相似的方式确定不同的数据源
下图显示了链接服务器配置的基础：
现在利用链接服务器方式实现数据访问远程服务器数据库CustomerDB中Users表数据先本地添加LinkServer：
以下是代码片段：
--建立连接服务器第一步建立连接IP方式来控制
EXECsp_addlinkedserver'192.168.10.104','SQLServer'
--查看链接服务器信息[测试连接成功]
selectname,product,provider,data_source,query_timeout,lazy_schema_validation,is_remote_login_enabled,is_rpc_out_enabled
fromsys.servers
whereis_linked=1
如上市建立连接服务器最简单方式。
建立链接服务器过程其实调用了系统存储过程Sp_addlinkedserver.第一个参数为Name其实用来唯一标识链接服务器。
当然可以其他任何有意义字符串来定义，但我个人建议使用远程服务器的IP来标识。
第二个参数是要添加为链接服务器的OLEDB数据源的产品名称。
默认为Null，如果指定”SQLServer“则无需指定其他参数。
如果你的本地装有多个数据库实例。
第一个种方式就不适用。
这是就需要用SQLServer2005架构来唯一标识：
--含架构名查询数据两种模式
selecttop10*from[192.168.10.104].wl.架构名.表名
--架构名[采用默认架构名]
selecttop10*from[192.168.10.104].CustomerDB.dbo.Users
对于SQLServer2005架构这个概念很多人比较陌生：
在用户角色设置中需要对指定访问数据CustomerDB具有读写权限：
在远程服务器创建TEst用户时使用SQLServer身份验证方式登录这时设置密码为RemoteDB.在使用非Sa用户进行远程：
以下是代码片段：
--执行前先删除已经存在数据
Execsp_droplinkedsrvlogin[192.168.10.76],Null
Execsp_dropserver'demodb'
--创建服务器连接
EXECsp_addlinkedserver
@server='demodb',--被访问的服务器别名
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='192.168.10.76'--要访问的服务器
EXECsp_addlinkedsrvlogin
'demodb',--被访问的服务器别名
'false',
NULL,
'Test',--帐号
'RemoteDB'--密码
如上我们首先清除已经可能创建服务器数据记录.然后创建服务器连接.sp_addlinkedSrvlogin系统存储过程用来创建链接服务器上远程登录之间的映射.即我们可以详细设置本地与远程服务器详细的映射信息.例如设置我们特定用户访问的用户名和密码.
查询数据
--查询指定用户Test数据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）系统时始终提示无法解析或拒绝连接SQLServer2005.这个问题我整了好久后来才到官方链接参数中发现。
：
如果你的XP系统没有打上SP4的补丁包这个问题会始终出现。
需要特别注意。
《2.2》直接指定数据源分布式查询
其实相对第一种方式，直接指定方式在SQLServer架构中其实跳过本地与远程服务器建立映射关系的这一步。
通过链接关系建立其实就是建立一种内部映射关系。
如果没有映射关系则大部分设置需要手动控制。
直接指定数据源方式需要开启分布式查询的基本权限来进行查询：
以下是代码片段：
--如果想使用分布式查询，必须先开通分布式查询[外围配置这点是所有查询操作前提]
--sp_configure--显示或更改当前服务器的全局配置设置
--reconfigure指定如果配置设置不需要服务器停止并重新启动，则更新当前运行的值
--SQL2005默认是没有开启’AdHocDistributedQueries’组件
--启用权限
execsp_configure'showadvancedoptions',1--显示高级配置
reconfigure--更新值
execsp_configure'AdHocDistributedQueries',1--启用分布式查询
reconfigure
go
--关闭分布式查询
execsp_configure'AdHocDistributedQueries',0
reconfigure
execsp_configure'showadvancedoptions',0
reconfigure
go
--开启权限后另外一种查询方式
--查询格式
SELECT*FROMOPENDATASOURCE（
'SQLOLEDB',
'DataSource=远程ip;UserID=sa;Password=密码'
）.库名.dbo.表名
WHERE条件
--需要开启权限
--开启权限提示[远程的SqlServer不允许远程连接]
select*fromOPENDATASOURCE（'SQLOLEDB','DataSource=192.168.10.67;User
如上我们首先清除已经可能创建服务器数据记录.然后创建服务器连接.sp_addlinkedSrvlogin系统存储过程用来创建链接服务器上远程登录之间的映射.即我们可以详细设置本地与远程服务器详细的映射信息.例如设置我们特定用户访问的用户名和密码.
查询数据
--查询指定用户Test数据select*from[demodb].CustomerDB.dbo.Users--[如上测试成功]
查询结果
指定用户Test对CustomerDB访问数据方式测试成功。
《3》问题排查与更多查询方式
当我们在实际编程中进行访问远程数据时因为不同操作环境会引发各种各样的异常，如下我会提出一种常见的异常方式解决办法和关于远程数据操作更多查询方式。
《3.1》无法建立远程连接
其实这个问题在做分布式查询时极其常见。
而引起这个问题的因素过多。
我们一时无法判断真正引发这个异常地方。
只能通过逐个排查方式来进行设置：
例如我们在建立关联关系后进行查询时会遇到：
提示是：
在进行远程连接时超时，引起这个问题原因可能是远程服务器积极拒绝访问！
首先要在SQLServerConfiguationManager中保证你服务已经运行且是开机自动运行。
再次检查SQLServer2005外围配置DataBaseEngine允许远程连接：
设置完成后。
我们还需要设置SQLServerAnalysisServices分析服务也支持远程数据查询：
在远程服务器上如果启用了防火墙则可能对目前SQLServerServer方位实例进行拦截。
所以在服务器端启用防火墙情况下要为SQLServerDAtaBase创建例外。
防止客户端请求被拦截。
《3.2》进程被其他用户占用
当我们在远程分布式查询中有创建动作或是类似创建一个新的数据库。
有时会提示“该数据库无法操作已经别其他进程占用”异常。
导致我们无法访问数据库。
或是执行我们要做的创建操作.
遇到这种情况我们可以利用SA权限查询到Master数据库对应数据库被占用的进程并杀掉KillProcess.查询：
以下是代码片段：
--[sysprocesses表中保存关于运行在Microsoft®SQLServer™上的进程的信息。
--这些进程可以是客户端进程或系统进程。
sysprocesses只存储在master数据库中]
useMaster
go
SELECT*FROMsysprocesses,sysdatabases
WHEREsysprocesses.dbid=sysdatabases.dbidANDsysdatabases.Name='CustomerDB'
select*fromsysprocesses
select*fromsysdatabases
--杀死占用进程
kill5
当我们对进程占用清除时有可能访问数据库被系统进程占用。
则这时用Sa无法杀死。
这时提示：
“OnlyuseProcesscanbeKill”在SQLServer2005只有只有用户进程才能Kill掉。
《3.3》更多的查询操作
往往我们在实际操作中需要对数据读写有更多要求。
例如从远程连接多个服务器进行数据读取或是把本地数据提交到服务器上。
为了提高效率和性能采用分布式事务来进行批量操作等等。
如下简单介绍在分布式查询中多中数据操作：
把远程数据导入本地：
以下是代码片段：
--把本地表导入远程表[openWset方式]
insertopenrowset（'SQLOLEDB','sql服务器名';'用户名';'密码',数据库名.dbo.表名）select*from本地表
--把本地表导入远程表[openQuery方式]
insertopenquery（ITSV,'SELECT*FROM数据库.dbo.表名'）
导入时使用Into方式自动在本地创建CopyDB表完全复制远程服务器上Users表的数据结构。
但是要注意在进行后的CopyDB将不包含原表的主键和索引约束。
虽然能快构建但是主键和索引设置都会丢失。
本地数据导入远程：
--把本地表导入远程表[openWset方式]insertopenrowset（'SQLOLEDB','sql服务器名';'用户名';'密码',数据库名.dbo.表名）select*from本地表--把本地表导入远程表[openQuery方式]insertopenquery（ITSV,'SELECT*FROM数据库.dbo.表名'）
更新本地表数据：
以下是代码片段：
--把本地表导入远程表[opendataSource方式]
insertopendatasource（'SQLOLEDB','DataSource=ip/ServerName;UserID=登陆名;Password=密码'）.数据库.dbo.表名
--更新本地表[openowset方式]
updatebsetb.列A=a.列Afromopenrowset（'SQLOLEDB','sql服务器名';'用户名';'密码',数据库名.dbo.表名）
asainnerjoin本地表bona.column1=b.column1
当然还有更多方式来操作分布式查询操作。
各位都可以尝试。
《4》尾语
如上是我最近在项目中处理关于分布式查询涉及到方方面面。
从系统架构到分部是查询具体操作细节。
基本都是一些非常基础运用。
当然也参考不少资料。
以及动手来验证整个过程出现问题原因所在。
篇幅有限写的有些仓促。
难免有纰漏地方还望各位指正。
原文：
升级会员 