SQLServer分布式查询(2)-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

SQLServer分布式查询(2)

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 03:27 PM

sqlserverdistributedtroubleshootingInquirequestion

3问题排查与更多查询方式当我们在实际编程中进行访问远程数据时因为不同操作环境会引发各种各样的异常,如下我会提出一种常见的异常方式解决办法和关于远程数据操作更多查询方式. 3.1无法建立远程连接其实这个问题在做分布式查询时极其常见. 而引起这个问

　　问题排查与更多查询方式

　　当我们在实际编程中进行访问远程数据时因为不同操作环境会引发各种各样的异常,如下我会提出一种常见的异常方式解决办法和关于远程数据操作更多查询方式.

　　无法建立远程连接

　　其实这个问题在做分布式查询时极其常见. 而引起这个问题的因素过多. 我们一时无法判断真正引发这个异常地方. 只能通过逐个排查方式来进行设置:

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

SQLServer分布式查询(2)

　　提示是: 在进行远程连接时超时, 引起这个问题原因可能是远程服务器积极拒绝访问!

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

　　再次检查SQl2005外围配置DataBaseEngine允许远程连接:

SQLServer分布式查询(2)

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

SQLServer分布式查询(2)

　　在远程服务器上如果启用了防火墙则可能对目前SQl Server方位实例进行拦截. 所以在服务器端启用防火墙情况下要为SQl DAtaBase创建例外.防止客户端请求被拦截.

　　进程被其他用户占用

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

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

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

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

SQLServer分布式查询(2)

　　“Only use Process can be Kill ”在SQl2005 只有只有用户进程才能Kill掉.

　　更多的查询操作

　　往往我们在实际操作中需要对数据读写有更多要求. 例如从远程连接多个服务器进行数据读取或是把本地数据提交到服务器上. 为了提高效率和性能采用分布式事务来进行批量操作等等. 如下简单介绍在分布式查询中多中数据操作:

　　把远程数据导入本地:

<p><span>1</span><span>:  </span><span>--</span><span> 导入数据操作</span><span><br>   </span><span>2</span><span>:  </span><span>select</span><span>top</span><span>(</span><span>3</span><span>) </span><span>*</span><span>into</span><span> TestDB.dbo.CopyDb </span><span>from</span><span>[</span><span>192.168.10.76</span><span>]</span><span>.wl.dbo.Users<br></span></p>

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

　　本地数据导入远程:

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

更新本地表数据:<br>

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

　　当然还有更多方式来操作分布式查询操作.各位都可以尝试.

　　尾语

　　如上是我最近在项目中处理关于分布式查询涉及到方方面面. 从系统架构到分部是查询具体操作细节.基本都是一些非常基础运用.当然也参考不少资料.以及动手来验证整个过程出现问题原因所在. 篇幅有限写的有些仓促. 难免有纰漏地方还望各位指正.

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

MySQL: BLOB and other no-sql storage, what are the differences?May 13, 2025 am 12:14 AM

MySQL'sBLOBissuitableforstoringbinarydatawithinarelationaldatabase,whileNoSQLoptionslikeMongoDB,Redis,andCassandraofferflexible,scalablesolutionsforunstructureddata.BLOBissimplerbutcanslowdownperformancewithlargedata;NoSQLprovidesbetterscalabilityand

MySQL Add User: Syntax, Options, and Security Best PracticesMay 13, 2025 am 12:12 AM

ToaddauserinMySQL,use:CREATEUSER'username'@'host'IDENTIFIEDBY'password';Here'showtodoitsecurely:1)Choosethehostcarefullytocontrolaccess.2)SetresourcelimitswithoptionslikeMAX_QUERIES_PER_HOUR.3)Usestrong,uniquepasswords.4)EnforceSSL/TLSconnectionswith

MySQL: How to avoid String Data Types common mistakes?May 13, 2025 am 12:09 AM

ToavoidcommonmistakeswithstringdatatypesinMySQL,understandstringtypenuances,choosetherighttype,andmanageencodingandcollationsettingseffectively.1)UseCHARforfixed-lengthstrings,VARCHARforvariable-length,andTEXT/BLOBforlargerdata.2)Setcorrectcharacters

MySQL: String Data Types and ENUMs?May 13, 2025 am 12:05 AM

MySQloffersechar, Varchar, text, Anddenumforstringdata.usecharforfixed-Lengthstrings, VarcharerForvariable-Length, text forlarger text, AndenumforenforcingdataAntegritywithaetofvalues.

MySQL BLOB: how to optimize BLOBs requestsMay 13, 2025 am 12:03 AM

Optimizing MySQLBLOB requests can be done through the following strategies: 1. Reduce the frequency of BLOB query, use independent requests or delay loading; 2. Select the appropriate BLOB type (such as TINYBLOB); 3. Separate the BLOB data into separate tables; 4. Compress the BLOB data at the application layer; 5. Index the BLOB metadata. These methods can effectively improve performance by combining monitoring, caching and data sharding in actual applications.

Adding Users to MySQL: The Complete TutorialMay 12, 2025 am 12:14 AM

Mastering the method of adding MySQL users is crucial for database administrators and developers because it ensures the security and access control of the database. 1) Create a new user using the CREATEUSER command, 2) Assign permissions through the GRANT command, 3) Use FLUSHPRIVILEGES to ensure permissions take effect, 4) Regularly audit and clean user accounts to maintain performance and security.

Mastering MySQL String Data Types: VARCHAR vs. TEXT vs. CHARMay 12, 2025 am 12:12 AM

ChooseCHARforfixed-lengthdata,VARCHARforvariable-lengthdata,andTEXTforlargetextfields.1)CHARisefficientforconsistent-lengthdatalikecodes.2)VARCHARsuitsvariable-lengthdatalikenames,balancingflexibilityandperformance.3)TEXTisidealforlargetextslikeartic

MySQL: String Data Types and Indexing: Best PracticesMay 12, 2025 am 12:11 AM

Best practices for handling string data types and indexes in MySQL include: 1) Selecting the appropriate string type, such as CHAR for fixed length, VARCHAR for variable length, and TEXT for large text; 2) Be cautious in indexing, avoid over-indexing, and create indexes for common queries; 3) Use prefix indexes and full-text indexes to optimize long string searches; 4) Regularly monitor and optimize indexes to keep indexes small and efficient. Through these methods, we can balance read and write performance and improve database efficiency.

See all articles