2022年MSSQL分布式查询 .pdf
《2022年MSSQL分布式查询 .pdf》由会员分享,可在线阅读,更多相关《2022年MSSQL分布式查询 .pdf(19页珍藏版)》请在得力文库 - 分享文档赚钱的网站上搜索。
1、MSSQL 分布式查询MSSQlServer所谓的分布式查询(Distributed Query)是能够访问存放在同一部计算机或不同计算机上的SQL Server 或不同种类的数据源, 从概念上来说分布式查询与普通查询区别它需要连接多个MSSQL服务器也就是具有多了数据源.实现在服务器跨域或跨服务器访问. 而这些查询是否被使用完全看使用的需要. 本篇将演示利用SQlExpress链接远程SQlServer来获取数据方式来详细说明分布式查询需要注意细节 .先看一下系统架构数据查询基本处理: 当然如果采用了分布式查询我们系统采取数据DataBase 也就可能在多个远程Remote 名师资料总结 -
2、 - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页,共 19 页 - - - - - - - - - Server上访问时 : 如上截取系统架构中关于数据与缓存流向中涉及的分布式查询业务, 当我们从客户端Client发起请求数据时. 首先检查MemCache Server 缓存服务器是否有我们想要数据. 如果没有我需要查询数据库. 而此时数据要求查询多个远程服务器上多个数据库中表, 这时利用分布式查询 .获得数据然后更新我们在缓存服务器MemCache Server 上数据保持数据更新同步, 同
3、时向客户端Client 直接返回数据.那如何来执行这一系列动作中最为关键分布式查询? 分布式查询方式我们知道Microsoft 微软公用的数据访问的API是 OLE_DB, 而对数据库MSSQlServer 2005的分布式查询支持也是OLE_DB方式 .SQL Server 用户可以使用分布式查询访问以下内容:A:存储在多个SQL Server 实例中的分布式数据B:存储在各种可以使用OLE DB 访问接口访问的关系和非关系数据源中的异类数据OLE DB 访 问 接 口 将 在 称 为 行 集 的 表 格 格 式 对 象 中 公 开 数 据 。 SQL Server 允 许 在名师资料总结
4、- - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页,共 19 页 - - - - - - - - - Transact-SQL 语句中像引用SQL Server 表一样引用OLE DB 访问接口中的行集,其实不用关心这个行集概念它的功能类似SQl中临时表不过它容积更大能容纳类型更多更丰富 SQL Server 实例的客户机与OLE DB 访问接口之间的连接如下图 : 从上图可以看出.客户端借助OLEDB接口可以访问Oracle/MS Jet/MS SQL/ODBC/第三方等这些丰富数据源来我们
5、分布式查询提供数据. 说了这么多关于OLEDB 底层支持. 关于在MSSQL2005中则支持两种方式来进行分布式查询: 使用添加链接服务器方式(Add Link Server) 使用特定名称及特定数据源来直接指定(Add Host Names) 其实这两种方式在实际运用中是有区别的: 方式 A:Add Link Server 方式建立服务器之间关联.创建一个链接的服务器,使其允许对分布式的、针对OLE DB 数据源的异类查询进行访问. 一般适用于持久的数据操作对于数据量偏大 服务器之间交付时间长特点. 方式 B: Add Host Name 利用域来唯一识别数据库以及数据库表对象. 来实现跨服
6、务器访问. 这种方式一般比较简单主要适用于对数据需求临时性查询是使用偏多. 不适合做大批量数据提取 . 有性能瓶颈 . 分布式查询实现在进行实现分布式查询之前.本次测试Demo 对应的 SQL版本 : 确定SQLServer 版本后名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页,共 19 页 - - - - - - - - - 如下会演示两种方式来实现分布式查询,并对 Distributed Query 中详细细节进行说明. 链接服务器查询链接服务器配置使SQL Server
7、 可以对远程服务器上的OLE DB 数据源执行命令。链接服务器具有以下优点:访问远程服务器。能够对企业内的异类数据源发出分布式查询、更新、命令和事务。能够以相似的方式确定不同的数据源下图显示了链接服务器配置的基础: 现在利用链接服务器方式实现数据访问远程 服 务 器 数 据 库CustomerDB 中Users表 数 据 先 本 地 添 加LinkServer: 1: - 建立连接服务器第一步建立连接IP方式来控制2: 3: EXEC sp_addlinkedserver 192.168.10.104 , SQL Server 4: 5: - 查看链接服务器信息测试连接成功 6: 7: sel
8、ect name , product, provider, data_source, query_timeout, lazy_schema_validation, is_remote_login_enabled, is_rpc_out_enabled 8: from sys.servers 9: where is_linked= 1 如 上市建立连接服务器最 简单方式.建 立链接服务器过程其实调用了系统 存储过程Sp_addlinkedserver. 第一个参数为Name 其实用来唯一标识链接服务器. 当然可以其他任何有意义字符串来定义,但我个人建议使用远程服务器的IP来标识 .第二个参数是要
9、添加为链接服务器的OLE DB 数据源的产品名称. 默认为Null,如果指定 ”SQlServer“则无需指定其他参数. 如果你的本地装有多个数据库实例. 第一个种方式就不适用.这是就需要用SQl2005 架构来唯一标识 : 1: - 含架构名查询数据两种模式名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页,共 19 页 - - - - - - - - - 3: select top 10 * from 192.168.10.104. wl . 架构名. 表名5: - 架构名采
10、用默认架构名 7: select top 10 * from 192.168.10.104. CustomerDB . dbo. Users 对于 Sql2005 架构这个概念很多人比较陌生: 架构是形成单个命名空间的数据库实体的集合。命名空间是一个集合,其中每个元素的名称都是唯一的。例如,为了避免名称冲突,同一架构中不能有两个同名的表。两个表只有在位于不同的架构中时才可以同名例如本次Demo 在 CustomerDB 后对应 DBO既是默认的架构名 . 创建后 .如果需要修改连接服务器属性可以通过sp_serveroption 系统 Proc 来设置 : 1: - 配置链接服务器属性sp_s
11、erveroption为远程服务器和链接服务器设置服务器选项2: - 语法sp_serveroption server = server,optname = option_name,optvalue = option_value 4: exec sp_serveroption 192.168.10.104,name,192.168.10.104 6: - 查看连接服务器7: select * from sys.servers 建立后我就可以直接来查询远程服务器上数据: 1: - 查询远程服务器数据3: select * from 192.168.10.104.CustomerDB.dbo.Us
12、ers -成功 5: - sp_droplinkedsrvlogin 删除链接服务器登录名映射删除登录映射 6: - 如果为NULL, 那么将会删除由sp_addlinkedserver 创建的默认映射第二个参数 8: exec sp_droplinkedsrvlogin 192.168.10.104 ,NULL 10: - 删除链接服务器属性删除服务器 12: exec sp_dropserver mytest - 删除成功同时也删除了Sys_Server信息 14: - 查看服务器详细信息15: EXEC sp_helpserver 查询结果 : 名师资料总结 - - -精品资料欢迎下载
13、- - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页,共 19 页 - - - - - - - - - 测试查询成功 .远程数据成功获取. 当测试完成后我们不需要这个连接服务器是即可利用SP_DroplinkServer删除掉 . 对应参数为创建时 Name 唯一标识 . 通过 Sp_helpserver 来查看连接服务器详细信息. 注意如上创建连接服务器时设置srvproduct 参数即 OLED数据源名称时我们采用了SQlServer方式 . 下面说明这种方式特点.:这种方式是最为简单直接的一种建立链接服务器方式. 但
14、是存在前提的. 测试发现 : 在所有数据库的远程连接dbo 的方式必须建立在SA 密码相同的基础上,否则容易产生无法连接的情况Sa用户登录失败 . 你也就明白这个SQlServer参数其实就是在本地数据拷贝服务器角色SysAdmin 下用户 SA.来对服务器进行登录. 如果你的本地Sa 密码与远程服务器上密码不一致则无法正常连接. 经过测试还发现一种情况: 利用 Windows7 访问 XP(Sp2) 系统时始终提示无法解析或拒绝连接SQlServer2005.这个问题我整了好久后来才到官方链接参数中发现.:如果你的XP系统没有打上SP4的补丁包这个问题会始终出现 . 需要特别注意. 直接指定
15、数据源分布式查询其实相对第一种方式, 直接指定方式在SQlServer架构中其实跳过本地与远程服务器建立映射关系的这一步. 通过链接关系建立其实就是建立一种内部映射关系. 如果没有映射关系则 大部分设置需要手动控制. 直接指定数据源方式需要开启分布式查询的基本权限来进行查询:2: - 如果想使用分布式查询,必须先开通分布式查询外围配置这点是所有查询操作前提 3: - sp_configure- 显示或更改当前服务器的全局配置设置4: - reconfigure 指定如果配置设置不需要服务器停止并重新启动,则更新当前运行的值5: - SQL2005默认是没有开启Ad Hoc Distribute
16、d Queries 组件6: 7: - 启用权限8: exec sp_configure show advanced options,1 - 显示高级配置9: reconfigure - 更新值10: exec sp_configure Ad Hoc Distributed Queries,1 - 启用分布式查询11: reconfigure 12: go 14: 15: - 关闭分布式查询16: exec sp_configure Ad Hoc Distributed Queries,0 17: reconfigure 18: exec sp_configure show advanced
17、options,0 19: reconfigure 20: go 23: - 开启权限后另外一种查询方式24: - 查询格式25: SELECT * FROM OPENDATASOURCE( 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页,共 19 页 - - - - - - - - - 26: SQLOLEDB, 27: Data Source=远程 ip;User ID=sa;Password=密码 28: ).库名 .dbo.表名29: WHERE 条件31: - 需要
18、开启权限32: - 开启权限提示 远程的 SqlServer 不允许远程连接 34: select * from OPENDATASOURCE(SQLOLEDB,Data Source=192.168.10.67; User ID=sa; Password=chenkai).wl.dbo.Users 开启权限后 . 需要里利用ReConfig 命令来确认 .对目前分布式查询权限的修改. 如果在使用完分布式查询后注意关闭.最后查询结果 : 测试成功 . 有些人说使用数据库角色SysAdmin 角色下的Sa用户进行远程数据传输和验证. 不安全 . 其实在使用过程中应该不难看出. 在从远程服务器拉取
19、数据库过程中. 本地数据库需要对权限,创建连接服务器都需要最大用户权限来操作. 而服务器呢 , 只需要能连接上同时对指定数据 CustomerDB 具有读写的权限即可. 当然你更多远程操作可以把用户赋予CustomerDB 的OWner 角色 . 这时我们如何用非SA用户来来连接远程用户? 我们现在远程服务器上对连接创建一个用户名为Test的用户服务器角色设置Public 即可 : 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页,共 19 页 - - - - - - - -
20、- 在用户角色设置中需要对指定访问数据 CustomerDB具有读写权限 : 在远程服务器创建TEst用户时使用SQlServer身份验证方式登录这时设置密码为RemoteDB.在使用非Sa用户进行远程:1: - 执行前先删除已经存在数据2: Exec sp_droplinkedsrvlogin 192.168.10.76,Null 3: Exec sp_dropserver demodb 4: 5: - 创建服务器连接6: EXEC sp_addlinkedserver 7: server=demodb,- 被访问的服务器别名8: srvproduct=, 9: provider=SQLOL
21、EDB, 10: datasrc=192.168.10.76 - 要访问的服务器12: 13: EXEC sp_addlinkedsrvlogin 14: demodb, - 被访问的服务器别名15: false, 16: NULL, 17: Test, - 帐号18: RemoteDB - 密码如 上 我 们 首 先 清 除 已 经 可 能 创 建 服 务 器 数 据 记 录 . 然 后 创 建 服 务 器 连接.sp_addlinkedSrvlogin 系统存储过程用来创建链接服务器上远程登录之间的映射. 即我们可以详细设置本地与远程服务器详细的映射信息. 例如设置我们特定用户访问的用户名
22、和密码 . 查询数据 : 1: - 查询指定用户Test数据2: select * from demodb.CustomerDB.dbo.Users - 如上测试成功 查询结果 : 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页,共 19 页 - - - - - - - - - 指定用户Test对 CustomerDB 访问数据方式测试成功. 问题排查与更多查询方式当我们在实际编程中进行访问远程数据时因为不同操作环境会引发各种各样的异常,如下我会提出一种常见的异常方式解决办法
23、和关于远程数据操作更多查询方式. 无法建立远程连接其实这个问题在做分布式查询时极其常见. 而引起这个问题的因素过多. 我们一时无法判断真正引发这个异常地方. 只能通过逐个排查方式来进行设置: 例如我们在建立关联关系后进行查询时会遇到: 提示是 : 在进行远程连接时超时, 引起这个问题原因可能是远程服务器积极拒绝访问! 首先要在Sql Server Configuation Manager 中保证你服务已经运行且是开机自动运行. 再次检查SQl2005外围配置DataBaseEngine允许远程连接 : 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - -
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 2022年MSSQL分布式查询 2022 MSSQL 分布式 查询
限制150内