SQL数据库的分布式查询

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,独享型 2核4GB
简介:
1、启用Ad Hoc Distributed Queries
在使用openrowset/opendatasource前搜先要启用Ad Hoc Distributed Queries服务,因为这个服务不安全所以SqlServer默认是关闭的
启用Ad Hoc Distributed Queries的方法
SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset/OpenDatasource'
的访问,因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用
sp_configure 启用 'Ad Hoc Distributed Queries'。有关启用 'Ad Hoc Distributed Queries' 的详细
信息,请参阅 SQL Server 联机丛书中的 "外围应用配置器"。
启用Ad Hoc Distributed Queries的方法,执行下面的查询语句就可以了:
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
使用完毕后,记得一定要要关闭它,因为这是一个安全隐患,切记执行下面的SQL语句
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure 
 
2、使用示例
--创建链接服务器 
exec sp_addlinkedserver   'ITSV '' ''SQLOLEDB ''远程服务器名或ip地址 '  
exec sp_addlinkedsrvlogin 'ITSV ''false ',null'用户名 ''密码 '
 

--查询示例 

select * from  ITSV.数据库名.dbo.表名 

--导入示例 

select * into 表 from  ITSV.数据库名.dbo.表名 

--以后不再使用时删除链接服务器 

exec sp_dropserver  'ITSV ''droplogins '  

--连接远程/局域网数据(openrowset/openquery/opendatasource) 

--
1、openrowset 

--查询示例 
select * from openrowset'SQLOLEDB ''sql服务器名 ''用户名 ''密码 ' ,数据库名.dbo.表名) 

--生成本地表 

select * into 表 from openrowset'SQLOLEDB ''sql服务器名 ''用户名 ''密码 ' ,数据库名.dbo.表名) 

--把本地表导入远程表 

insert openrowset'SQLOLEDB ''sql服务器名 ''用户名 ''密码 ' ,数据库名.dbo.表名) 
select *from
 本地表 

--更新本地表 

update  b 
set b.列A=
a.列A 
from openrowset'SQLOLEDB ''sql服务器名 ''用户名 ''密码 ',数据库名.dbo.表名)as a inner join
 本地表 b 
on a.column1=
b.column1 

--openquery用法需要创建一个连接 


--首先创建一个连接创建链接服务器 
exec sp_addlinkedserver   'ITSV '' ''SQLOLEDB ''远程服务器名或ip地址 '  
--查询 

select *  
FROM openquery(ITSV,  'SELECT *  FROM 数据库.dbo.表名 '

--把本地表导入远程表 

insert openquery(ITSV,  'SELECT *  FROM 数据库.dbo.表名 '
select * from
 本地表 
--更新本地表 

update  b 
set b.列B=
a.列B 
FROM openquery(ITSV,  'SELECT * FROM 数据库.dbo.表名 'as
 a  
inner join 本地表 b on a.列A=
b.列A 

--3、opendatasource/openrowset 

SELECT   *  
FROM   opendatasource'SQLOLEDB ',  'Data Source=ip/ServerName;User ID=登陆名;Password=密码 '
 ).test.dbo.roy_ta 
--把本地表导入远程表 

insert opendatasource'SQLOLEDB ',  'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).数据库.dbo.表名 
select * from 本地表
3、自己写的例子
 
--openrowset使用OLEDB的一些例子
select * from openrowset('SQLOLEDB','Server=(local);PWD=***;UID=sa;','select * from TB.dbo.school'as  t
select * from openrowset('SQLOLEDB','Server=(local);PWD=***;UID=sa;',TB.dbo.school) as
 t
select * from openrowset('SQLOLEDB','Server=(local);Trusted_Connection=yes;',TB.dbo.school) as
 t
select * from openrowset('SQLOLEDB','(local)';'sa';'***','select * from TB.dbo.school'as
 t
select * from openrowset('SQLOLEDB','(local)';'sa';'***',TB.dbo.school) as
 t
select * from openrowset('SQLOLEDB','(local)';'sa';'***','select school.id as id1,people.id as id2 from TB.dbo.school inner join TB.dbo.people on school.id=people.id'as
 t

--openrowset使用SQLNCLI的一些例子(SQLNCLI在SqlServer2005以上才能使用)

select * from openrowset('SQLNCLI','(local)';'sa';'***','select * from TB.dbo.school'as  t
select * from openrowset('SQLNCLI','Server=(local);Trusted_Connection=yes;','select * from TB.dbo.school'as
 t
select * from openrowset('SQLNCLI','Server=(local);UID=sa;PWD=***;','select * from TB.dbo.school'as
 t
select * from openrowset('SQLNCLI','Server=(local);UID=sa;PWD=***;',TB.dbo.school) as
 t
select * from openrowset('SQLNCLI','Server=(local);UID=sa;PWD=***;DataBase=TB','select * from dbo.school'as
 t

--openrowset其他使用

insert openrowset('SQLNCLI','Server=(local);Trusted_Connection=yes;','select name from TB.dbo.school where id=1'values('ghjkl')/*要不要where都一样,插入一行*/
update openrowset('SQLNCLI','Server=(local);Trusted_Connection=yes;','select name from TB.dbo.school where id=1'set name='kkkkkk'
delete from openrowset('SQLNCLI','Server=(local);Trusted_Connection=yes;','select name from TB.dbo.school where id=1' )







--opendatasource使用SQLNCLI的一些例子

select * from opendatasource('SQLNCLI','Server=(local);UID=sa;PWD=***;').TB.dbo.school as  t
select * from opendatasource('SQLNCLI','Server=(local);UID=sa;PWD=***;DataBase=TB').TB.dbo.school as
 t

--opendatasource使用OLEDB的例子

select * from opendatasource('SQLOLEDB','Server=(local);Trusted_Connection=yes;').TB.dbo.school as  t

--opendatasource其他使用

insert opendatasource('SQLNCLI','Server=(local);Trusted_Connection=yes;').TB.dbo.school(name) values('ghjkl')/*要不要where都一样,插入一行*/
update opendatasource('SQLNCLI','Server=(local);Trusted_Connection=yes;').TB.dbo.school set name='kkkkkk'
delete from opendatasource('SQLNCLI','Server=(local);Trusted_Connection=yes;').TB.dbo.school where id=1







--openquery使用OLEDB的一些例子
exec sp_addlinkedserver   'ITSV''''SQLOLEDB','(local)'  
exec sp_addlinkedsrvlogin 'ITSV''false',null'sa''***'

select * FROM openquery(ITSV,  'SELECT *  FROM TB.dbo.school '

--openquery使用SQLNCLI的一些例子

exec sp_addlinkedserver   'ITSVA''''SQLNCLI','(local)'  
exec sp_addlinkedsrvlogin 'ITSVA''false',null'sa''***'

select * FROM openquery(ITSVA,  'SELECT *  FROM TB.dbo.school '

--openquery其他使用

insert openquery(ITSVA,'select name from TB.dbo.school where id=1'values('ghjkl')/*要不要where都一样,插入一行*/
update openquery(ITSVA,'select name from TB.dbo.school where id=1'set name='kkkkkk'
delete openquery(ITSVA,'select name from TB.dbo.school where id=1')
 
 
 
4、总结
可以看到SqlServer连接多服务器的方式有3种
其中我个人认为openrowset最好,使用简单而且支持在连接时制定查询语句使用很灵活
openquery也不错查询时也可以指定查询语句使用也很灵活,不过查询前要先用exec sp_addlinkedserver和exec sp_addlinkedsrvlogin建立服务器和服务器连接稍显麻烦
opendatasource稍显欠佳,他无法在连接时指定查询使用起来稍显笨拙
另外还可以连接到远程Analysis服务器做MDX查询,再用T-Sql做嵌套查询,可见T-SQL的远程查询非常强大



     本文转自fengyp 51CTO博客,原文链接:,http://blog.51cto.com/fengyp/368354 如需转载请自行联系原作者

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
2天前
|
SQL 机器学习/深度学习 分布式计算
MaxCompute产品使用合集之怎么使用SQL查询来获取ODPS中所有的表及字段信息
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
3天前
|
SQL 运维 分布式计算
DataWorks产品使用合集之ODPPS中如何使用SQL查询从表中获取值并将其赋值给临时变量以供后续使用
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
|
6天前
|
SQL Oracle 关系型数据库
|
2天前
|
SQL 关系型数据库 MySQL
乐观锁在分布式数据库中与事务隔离级别结合使用
乐观锁在分布式数据库中与事务隔离级别结合使用
|
1天前
|
SQL 分布式计算 DataWorks
MaxCompute操作报错合集之使用sql查询一个表的分区数据时遇到报错,该如何解决
MaxCompute是阿里云提供的大规模离线数据处理服务,用于大数据分析、挖掘和报表生成等场景。在使用MaxCompute进行数据处理时,可能会遇到各种操作报错。以下是一些常见的MaxCompute操作报错及其可能的原因与解决措施的合集。
|
4天前
|
负载均衡 数据管理
ClickHouse的分布式查询流程
ClickHouse的分布式查询流程
|
4天前
|
SQL 分布式计算 DataWorks
DataWorks操作报错合集之在执行SQL查询时遇到报错,代码为[XX000],该怎么解决
DataWorks是阿里云提供的一站式大数据开发与治理平台,支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中,可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
|
3天前
|
SQL DataWorks NoSQL
DataWorks操作报错合集之在执行带with语句的SQL查询时出现了错误,是什么原因
DataWorks是阿里云提供的一站式大数据开发与治理平台,支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中,可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
|
5天前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
24 3
|
10天前
|
SQL 监控 安全
数据库安全:SQL注入防御实践
【7月更文挑战第11天】SQL注入攻击作为一种常见的网络攻击手段,对数据库的安全性和业务稳定构成了严重威胁。为了有效防御SQL注入攻击,开发者和数据库管理员应采取一系列实践措施,包括输入验证与过滤、使用参数化查询、限制数据库用户权限、使用Web应用程序防火墙、定期更新和打补丁、实施实时监控和审计以及使用HTTPS协议等。通过这些措施的实施,可以显著提升数据库的安全性,降低遭受SQL注入攻击的风险。同时,开发者和数据库管理员应持续关注新的安全威胁和防御技术,不断提升自身的安全防护能力。