详解SQL Server散布式查询-交流共享-唐山网站建设

详解SQL Server散布式查询

核心提示：SQL Server所谓的散布式查询（Distributed Query）是能够访问寄存在同1部计算机或不同计算机上的SQL Server或不同种类的数据源，从概念上来讲散布式查询与普通查询辨别它需要连接多个MSSQL服务器也就是具有多了数据源。实现在服务器跨域或跨服务器访问。而这些查询

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

本篇将演示利用SQL ServerExpress链接远程SQL Server来获得数据方式来具体说明散布式查询需要留意细节。先看1下系统架构数据查询基本处理：

固然假设采取了散布式查询我们系统采取数据DataBase也便可能在多个远程［Remote Server］上访问时：

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

《1》散布式查询方式

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

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

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

OLE DB 访问接口将在称为行集的表格格式对象中公然数据。SQL Server 答应在 Transact-SQL 语句中像援用 SQL Server 表1样援用

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

1 2 3 4 5 6 7 下1页

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

从上图可以看出。客户端借助OLEDB接口可以访问Oracle/MS Jet/MS SQL/ODBC/第3方等这些丰富数据源来我们散布式查询提供数据。说了这么多关于OLEDB底层支持。关于在MS SQL 2005中则支持两种方式来进行散布式查询：

使用添加链接服务器方式（Add Link Server）

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

实在这两种方式在实际应用中是有辨别的：

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

方式B： Add Host Name 利用域来唯1辨认数据库和数据库表对象。来实现跨服务器访问。这类方式1般比较简单主要适用于对数据需求临时性查询是使用偏多。不合适做大批量数据提取。有性能瓶颈。

《2》散布式查询实现

在进行实现散布式查询之前。本次测试Demo对应的SQL版本：

肯定SQL Server版本后以下会演示两种方式来实现散布式查询，并对Distributed Query中具体细节进行说明。

《2.1》链接服务器查询

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

访问远程服务器。

能够对企业内的异类数据源发出散布式查询、更新、命令和事务。

能够以类似的方式肯定不同的数据源

上1页 1 2 3 4 5 6 7 下1页

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

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

以下是代码片断：

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

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

假设你的本地装有多个数据库实例。第1个种方式就不适用。这是就需要用SQL Server2005架构来唯1标识：

-- 含架构名查询数据两种模式
select top 10 * from [192.168.10.104]. wl . 架构名 . 表名
-- 架构名 [采取默许架构名 ]
select top 10 * from [192.168.10.104]. CustomerDB . dbo. Users

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

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

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

上1页 1 2 3 4 5 6 7 下1页

以下是代码片断：

-- 履行前先删除已存在数据
Exec sp_droplinkedsrvlogin [192.168.10.76],Null
Exec sp_dropserver 'demodb'
-- 创建服务器连接
EXEC sp_addlinkedserver
@server='demodb',-- 被访问的服务器别名
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='192.168.10.76' -- 要访问的服务器
EXEC sp_addlinkedsrvlogin
'demodb', -- 被访问的服务器别名
'false',
NULL,
'Test', -- 帐号
'RemoteDB' -- 密码

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

查询数据

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

查询结果

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

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

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

下面说明这类方式特点。：

这类方式是最为简单直接的1种建立链接服务器方式。但是存在条件的。测试发现：

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

经过测试还发现1种情况：

利用Windows7访问XP（Sp2）系统时始终提示没法解析或拒尽连接SQL Server2005.这个题目我整了好久后来才到官方链接参数中发现。：假设你的XP系统没有打上SP4的补钉包这个题目会始终出现。需要特别留意。

《2.2》直接指定数据源散布式查询

实在相对第1种方式，直接指定方式在SQL Server架构中实在跳过本地与远程服务器建立映照关系的这1步。通过链接关系建立实在就是建立1种内部映照关系。假设没有映照关系则大部份设置需要手动控制。

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

上1页 1 2 3 4 5 6 7 下1页

以下是代码片断：

-- 假设想使用散布式查询，必须先开通散布式查询 [外围配置这点是所有查询操纵条件]
-- sp_configure--显示或更改当前服务器的全局配置设置
-- reconfigure 指定假设配置设置不需要服务器停止并重新启动，则更新当前运行的值
-- SQL2005默许是没有开启’Ad Hoc Distributed Queries’ 组件
-- 启用权限
exec sp_configure 'show advanced options',1 -- 显示高级配置
reconfigure -- 更新值
exec sp_configure 'Ad Hoc Distributed Queries',1 -- 启用散布式查询
reconfigure
go
-- 封闭散布式查询
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
go
-- 开启权限后另外1种查询方式
-- 查询格式
SELECT * FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=远程ip;User ID=sa;Password=密码'
).库名.dbo.表名
WHERE 条件
-- 需要开启权限
-- 开启权限提示[远程的SqlServer不答应远程连接]
select * from OPENDATASOURCE('SQLOLEDB','Data Source=192.168.10.67; User

查询数据

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

查询结果

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

《3》题目排查与更多查询方式

当我们在实际编程中进行访问远程数据时由于不同操纵环境会引发各种各样的异常，以下我会提出1种常见的异常方式解决办法和关于远程数据操纵更多查询方式。

《3.1》没法建立远程连接

实在这个题目在做散布式查询时极其常见。而引发这个题目的因素过量。我们1时没法判定真正引发这个异常地方。只能通过逐一排查方式来进行设置：

上1页 1 2 3 4 5 6 7 下1页

例如我们在建立关联关系后进行查询时会碰到：

提示是：在进行远程连接时超时，引发这个题目缘由多是远程服务器积极拒尽访问！

首先要在SQL Server Configuation Manager中保证你服务已运行且是开机自动运行。

再次检查SQL Server 2005外围配置DataBaseEngine答应远程连接：

设置完成后。我们还需要设置SQL Server Analysis Services分析服务也支持远程数据查询：

在远程服务器上假设启用了防火墙则可能对目前SQL Server Server方位实例进行拦截。所以在服务器端启用防火墙情况下要为SQL Server DAtaBase创建例外。避免客户端要求被拦截。

《3.2》进程被其他用户占用

当我们在远程散布式查询中有创建动作或是类似创建1个新的数据库。有时会提示 “该数据库没法操纵已别其他进程占用”异常。导致我们没法访问数据库。或是履行我们要做的创建操纵.

上1页 1 2 3 4 5 6 7 下1页

碰到这类情况我们可以利用SA权限查询到Master数据库对应数据库被占用的进程并杀掉Kill Process.查询：

以下是代码片断：

-- [sysprocesses 表中保存关于运行在 Microsoft® SQL Server™ 上的进程的信息。
-- 这些进程可以是客户端进程或系统进程。sysprocesses 只存储在 master 数据库中]
use Master
go
SELECT * FROM sysprocesses ,sysdatabases
WHERE sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='CustomerDB'
select * from sysprocesses
select * from sysdatabases
-- 杀死占用进程
kill 5

当我们对进程占用清除时有可能访问数据库被系统进程占用。则这时候用Sa没法杀死。这时候提示：

“Only use Process can be Kill ”在SQL Server2005 只有只有用户进程才能Kill掉。

《3.3》更多的查询操纵

常常我们在实际操纵中需要对数据读写有更多要求。例如从远程连接多个服务器进行数据读取或是把本地数据提交到服务器上。为了进步效率和性能采取散布式事务来进行批量操纵等等。以下简单先容在散布式查询中多中数据操纵：

把远程数据导进本地：

以下是代码片断：

-- 把本地表导进远程表 [openWset方式]
insert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) select *from 本地表
-- 把本地表导进远程表 [open Query方式]
insert openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')

导进时使用Into方式自动在本地创建CopyDB表完全复制远程服务器上Users表的数据结构。但是要留意在进行后的CopyDB将不包括原表的主键和索引束缚。固然能快构建但是主键和索引设置都会丢失。

本地数据导进远程：

-- 把本地表导进远程表 [openWset方式] insert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) select *from 本地表 -- 把本地表导进远程表 [open Query方式] insert openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')

更新本地表数据：

以下是代码片断：

-- 把本地表导进远程表 [opendataSource方式]
insert opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ').数据库.dbo.表名
-- 更新本地表 [openowset方式]
update b set b.列A=a.列A from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)
as a inner join 本地表 b on a.column1=b.column1

固然还有更多方式来操纵散布式查询操纵。各位都可以尝试。

《4》尾语

如上是我最近在项目中处理关于散布式查询触及到各个方面。从系统架构到分部是查询具体操纵细节。基本都是1些非常基础应用。固然也参考很多资料。和动手来验证全部进程出现题目缘由所在。篇幅有限写的有些仓促。难免有纰漏地方还看各位指正。

原文：http://database.ctocio.com.cn/243/11533743.shtml

上1页 1 2 3 4 5 6 7 http://www.fw8.net/
TAG:数据库,数据,服务器,方式,数据源

评论加载中...