首页 / MSSQL / SQLServer分布式查询

SQLServer分布式查询

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了SQLServer分布式查询，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含8873字，纯文字阅读大概需要13分钟。

内容图文

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

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

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

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

SQLServer分布式查询 - 文章图片

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

　　<1>分布式查询方式

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

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

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

　　OLE DB 访问接口将在称为行集的表格格式对象中公开数据。SQL Server 允许在 Transact-SQL 语句中像引用 SQL Server 表一样引用 OLE DB 访问接口中的行集,[其实不用关心这个行集概念它的功能类似SQl中临时表不过它容积更大能容纳类型更多更丰富]

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

SQLServer分布式查询 - 文章图片

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

　　使用添加链接服务器方式(Add Link Server)

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

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

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

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

　　<2>分布式查询实现

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

SQLServer分布式查询 - 文章图片

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

　　<2.1>链接服务器查询

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

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

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

SQLServer分布式查询 - 文章图片

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

1: -- 建立连接服务器第一步建立连接 IP方式来控制
2:
3: EXEC sp_addlinkedserver '192.168.10.104' , 'SQL Server'
4:
5: -- 查看链接服务器信息 [测试连接成功]
6:
7: select 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来标识.第二个参数是要添加为链接服务器的 OLE DB 数据源的产品名称. 默认为Null,如果指定”SQlServer“则无需指定其他参数.

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

1:  -- 含架构名  查询数据两种模式 
   3:  selecttop10*from[192.168.10.104]. wl . 架构名 . 表名
   5:  -- 架构名 [采用默认架构名 ] 
   7:  selecttop10*from[192.168.10.104]. CustomerDB . dbo. Users

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

　　架构是形成单个命名空间的数据库实体的集合。命名空间是一个集合，其中每个元素的名称都是唯一的。例如，为了避免名称冲突，同一架构中不能有两个同名的表。两个表只有在位于不同的架构中时才可以同名例如本次Demo 在CustomerDB后对应DBO既是默认的架构名.

　　创建后.如果需要修改连接服务器属性可以通过sp_serveroption系统Proc来设置:

1:  -- 配置链接服务器属性 sp_serveroption为远程服务器和链接服务器设置服务器选项
   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.Users --[成功]
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

　　查询结果:
SQLServer分布式查询 - 文章图片

　　测试查询成功.远程数据成功获取.

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

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

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

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

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

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

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

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

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

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

2: -- 如果想使用分布式查询，必须先开通分布式查询 [外围配置这点是所有查询操作前提]
3: -- sp_configure--显示或更改当前服务器的全局配置设置
4: -- reconfigure 指定如果配置设置不需要服务器停止并重新启动，则更新当前运行的值
5: -- SQL2005默认是没有开启’Ad Hoc Distributed 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 options',0
19: reconfigure
20: go
23: -- 开启权限后另外一种查询方式
24: -- 查询格式
25: SELECT*FROMOPENDATASOURCE(
26: 'SQLOLEDB',
27: 'Data Source=远程ip;User ID=sa;Password=密码'
28: ).库名.dbo.表名
29: WHERE 条件
31: -- 需要开启权限
32: -- 开启权限提示[远程的SqlServer不允许远程连接]
34: select*fromOPENDATASOURCE('SQLOLEDB','Data Source=192.168.10.67; User ID=sa; Password=chenkai').wl.dbo.Users

　　开启权限后. 需要里利用ReConfig命令来确认.对目前分布式查询权限的修改. 如果在使用完分布式查询后注意关闭.最后查询结果:

SQLServer分布式查询 - 文章图片

　　测试成功.

　　有些人说使用数据库角色SysAdmin角色下的Sa用户进行远程数据传输和验证. 不安全. 其实在使用过程中应该不难看出. 在从远程服务器拉取数据库过程中. 本地数据库需要对权限，创建连接服务器都需要最大用户权限来操作. 而服务器呢, 只需要能连接上同时对指定数据CustomerDB具有读写的权限即可. 当然你更多远程操作可以把用户赋予CustomerDB的OWner角色.

　　这时我们如何用非SA用户来来连接远程用户?

　　我们现在远程服务器上对连接创建一个用户名为Test的用户服务器角色设置Public即可:

SQLServer分布式查询 - 文章图片

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

SQLServer分布式查询 - 文章图片

　　在远程服务器创建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='SQLOLEDB',
10: @datasrc='192.168.10.76'-- 要访问的服务器
12:
13: EXEC sp_addlinkedsrvlogin
14: 'demodb', -- 被访问的服务器别名
15: 'false',
16: NULL,
17: 'Test', -- 帐号
18: 'RemoteDB'-- 密码

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

　　查询数据:

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

　　查询结果:

SQLServer分布式查询 - 文章图片

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

内容总结

以上是互联网集市为您收集整理的SQLServer分布式查询全部内容，希望文章能够帮你解决SQLServer分布式查询所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/530917.html

来源：【匿名】

【上一篇】sqlserver2005只能用localhost表示本地？【下一篇】一个odbc连mssql分页的类

更多 ►

【SQLServer分布式查询】教程文章相关的互联网学习教程文章

.Net 性能优化--缓存--分布式缓存 --sqlserver缓存【图】

2、在Startup中注册服务，如下： 3、在HomeController中添加如下内容： 4、在Privacy.cshtml添加如下内容 5、创建一个sqlserver 数据库的表，这个表用来存储sqlserver 缓存的，可以手动去创建，也可以使用sql-cache工具去创建(建议使用工具，本文使用的sql-cache工具)，要使用sql-cache工具，就要先在电脑中安装了sql-cache工具，安装sql-cache工具命令如下： win+ R ，打开cmd 输入：dotnet tool install --global ...

基于SqlServer2008的分布式数据库的实践（五）_PHP教程

程序设计 ----------------------------------------------------------------------------------------------------------------- Index.php————选择界面，并且实现学生和老师的注册 sql_ini.php————SQL配置文件 <span ./student————学生的后台 <span ./admin————老师的后台 ----------------------------------------------------------------------------------------------------------------- ./student cook...

基于SqlServer2008的分布式数据库的实践（三）_PHP教程【图】

配置PHP 1.打开PHP配置文件，找到extension=php_mssql.dll，将前面的注释符号去掉2.找到mssql.secure_connection，将Off改为On3.找到com.allow_dcom = true，将前面的注释符号去掉4.下载正确版本的 ntwdblib.dll (2000.80.194.0)，覆盖Apache-20/bin/ntwdblib.dll、覆盖php-5.2.14-Win32/ntwdblib.dll并且复制ntwdblib.dll和php_mssql.dll到系统system32目录下，重启Apache 5.测试成功转载请注明出处：http://www.cnblogs.com/yydc...

SQL Server 创建水平分布式数据库尝试【代码】

TABLE [dbo].[Person] ([PersonID] [int] NOT NULL,[PersonType] [nchar](2) NOT NULL,[FirstName] [sysname] NOT NULL,[MiddleName] [sysname] NOT NULL,[LastName] [sysname] NOT NULL )step1，打开Win10 MSDTC 参考《Win10 打开MSDTC》，不再赘述 step2，分别在两台Server上创建数据库和表，数据库分别是DBtest1 和 DBTest2，将DBTest1作为Master DB，将DBTest2作为Slave DB。--default instance CREATE TABLE [dbo].[Person]([...

SQLServer跨库查询--分布式查询[转载]【代码】

用openrowset连接远程SQL或插入数据 --如果只是临时访问,可以直接用openrowset--查询示例select * from openrowset(‘SQLOLEDB‘,‘sql服务器名‘;‘用户名‘;‘密码‘,数据库名.dbo.表名)--导入示例select * into 表 from openrowset(‘SQLOLEDB‘,‘sql服务器名‘;‘用户名‘;‘密码‘,数据库名.dbo.表名)--创建链接服务器exec sp_addlinkedserver ‘srv_lnk‘,‘‘,‘SQLOLEDB‘,‘远程服务器名或ip地址‘exec sp_addlinkedsrv...

SQLServer跨库查询--分布式查询

出处：http://www.cnblogs.com/doosmile/archive/2012/03/16/2400646.html --用openrowset连接远程SQL或插入数据 --如果只是临时访问,可以直接用openrowset--查询示例select * from openrowset(‘SQLOLEDB‘,‘sql服务器名‘;‘用户名‘;‘密码‘,数据库名.dbo.表名)在跨库查询时需要启用Ad Hoc Distributed Queries启用Ad Hoc Distributed Queries：exec sp_configure ‘show advanced options‘,1reconfigureexec sp_configure ‘...

SqlServer+Topshelf+Quartznet做集群，定时任务分布式处理

USE 数据库名2 GO3 4 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N‘[dbo].[FK_QRTZ_TRIGGERS_QRTZ_JOB_DETAILS]‘) AND OBJECTPROPERTY(id, N‘ISFOREIGNKEY‘) = 1)5 ALTER TABLE [dbo].[QRTZ_TRIGGERS] DROP CONSTRAINT FK_QRTZ_TRIGGERS_QRTZ_JOB_DETAILS6 GO7 8 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N‘[dbo].[FK_QRTZ_CRON_TRIGGERS_QRTZ_TRIGGERS]‘) AND OBJECTPROPERTY(id...

SQL Server横向扩展：设计，实现与维护（2）- 分布式分区视图

到现在为止，也说了一些分布式分区视图的一些概念，朋友可能已经有些迫不及待的要知道如何实现，那么，我这里先缓缓。我们先来看几个与它相关的重要的内容。假设，现在分布式的视图已经建立好了，我们下一步要做的就是如何使用它。下面，我们就谈谈实现分布式视图的实现的原理和分布式查询相关的知识。分布式分区视图工作原理分布式查询和分布式分区视图中使用了OLE DB提供程序来实现数据库实例之间间信息沟通。访问远程数...

SQL Server 分布式事务与本地事务【代码】

SQL Server 分布式事务与本地事务 @(SQL Server) 背景：之前有项目中出现大量死锁，进行排查后最终发现很多死锁都是由于序列化隔离级别导致，开发针对业务和SQL进行优化后，死锁减少，但是没进行后续研究。最近又有很多项目出现死锁及超时，特别是工作流和待办这块，同样发现都是存在序列化，于是针对这一点进行相关资料查阅及解答。一. 为什么会出现serializable(序列化) 如果我们程序中定义事务类调用了分布式事务,那么事务的隔...

2019-04-29 EasyWeb下配置Atomikos+SQLServer分布式数据源【代码】【图】

spring.datasource.system.xa-properties.dataSourceURL=jdbc:sqlserver://XXX;DatabaseName=XXX 2 spring.datasource.system.xa-properties.username=sa 3 spring.datasource.system.xa-properties.password=XXX 4 spring.datasource.system.xa-data-source-class-name=com.alibaba.druid.pool.xa.DruidXADataSource 5 spring.datasource.system.unique-resource-name=systemDataSource 但是通过修改配置文件配置SQLServer发现：...

mssql数据库,数据库同步,分布式数据库,数据库集群,如何实现

由于项目需要,产品的部署必须考虑到安全和灾难的解决办法.由于之前一直做的的小项目,基本都是单服务器,单数据库结构,但是由于一次灾难,把这个问题提上了日程. 本人资历浅薄,很多东西还不是很熟悉,最近在网上百度了一大堆相关的东西,基本有了大概的思路, 思路就是, 1..用多个服务器做集群,做压力负载均衡,这样第一可以分流,减轻每个服务器的压力,提高稳定性,再者就是,一个服务器崩溃了,其他服务器可以继续运行.提供服务....

SqlServer分布式跨数据库查询Join【代码】

--step1 创建链接服务器 exec sp_addlinkedserver srv_lnk,,SQLOLEDB, ip地址 exec sp_addlinkedsrvlogin srv_lnk, false ,null, sql登陆名, 密码 --exec sp_addlinkedserver srv_lnk,,SQLOLEDB, 192.168.1.58 --exec sp_addlinkedsrvlogin srv_lnk, false ,null, sa, 123456 --step2 应用：查询 select * from srv_lnk.数据库名.dbo.表 --select * from srv_lnk.erp.dbo.t1192--step2 应用：导入 select * into 表 ...

Sqlserver分布式跨数据库查询、Join，以及分布式事务【代码】【图】

简言：这篇文章我要谈一谈SQL Server分布式跨服务器查询，多表Join，以及分布式事务的处理SqlServer跨服务器查询的方式以往自己才疏学浅，学习了一波之后，在这记录下来。1. 使用sp_addlinkedserver和sp_addlinkedsrvlogin存储过程添加linkserver和登录--别名 //ip地址 exec sp_addlinkedserver interlink,,SQLOLEDB, xxx.xxx.xx.xx --别名 --登录名 --密码 exec sp_addlinkedsrv...

SQL Server 开启DTC分布式事务

MSDTC(分布式交易bai协调器)，协调跨多个数据库、消du息队列、文件系统等zhi资源管理器的事务。该服务的dao进程名为Msdtc.exe。解决办法: 1. 在windows控制面版-->管理工具-->服务-->Distributed Transaction Coordinator-->属性-->启动 2. 在CMD下运行"net start msdtc"开启服务后正常。 3. 输入:msdtc -resetlog (注意运行此命令时,不要执行挂起的事务) 4. 最后输入:net s...SQL Server 开启DTC分布式事务标签：window 文件系...