MSSQlServer所謂的分布式查詢(Distributed Query)是能夠訪問存放在同一部計(jì)算機(jī)或不同計(jì)算機(jī)上的SQL Server或不同種類的數(shù)據(jù)源, 從概念上來說分布式查詢與普通查詢區(qū)別 它需要連接多個(gè)MSSQL服務(wù)器也就是具有多了數(shù)據(jù)源.實(shí)現(xiàn)在服務(wù)器跨域或跨服務(wù)器訪問. 而這些查詢是否被使用完全看使用的需要.

　　本篇將演示利用SQlExpress鏈接遠(yuǎn)程SQlServer來獲取數(shù)據(jù)方式來詳細(xì)說明分布式查詢需要注意細(xì)節(jié).先看一下系統(tǒng)架構(gòu)數(shù)據(jù)查詢基本處理:

　　當(dāng)然如果采用了分布式查詢 我們系統(tǒng)采取數(shù)據(jù)DataBase也就可能在多個(gè)遠(yuǎn)程[Remote Server]上訪問時(shí):

　　如上截取系統(tǒng)架構(gòu)中關(guān)于數(shù)據(jù)與緩存流向中涉及的分布式查詢業(yè)務(wù), 當(dāng)我們從客戶端Client發(fā)起請(qǐng)求數(shù)據(jù)時(shí). 首先檢查MemCache Server緩存服務(wù)器是否有我們想要數(shù)據(jù). 如果沒有我需要查詢數(shù)據(jù)庫(kù).  而此時(shí)數(shù)據(jù)要求查詢多個(gè)遠(yuǎn)程服務(wù)器上多個(gè)數(shù)據(jù)庫(kù)中表, 這時(shí)利用分布式查詢.獲得數(shù)據(jù) 然后更新我們?cè)诰彺娣?wù)器MemCache Server上數(shù)據(jù)保持?jǐn)?shù)據(jù)更新同步, 同時(shí)向客戶端Client直接返回?cái)?shù)據(jù).那如何來執(zhí)行這一系列動(dòng)作中最為關(guān)鍵分布式查詢?

　　<1>分布式查詢方式

　　我們知道Microsoft微軟公用的數(shù)據(jù)訪問的API是OLE_DB, 而對(duì)數(shù)據(jù)庫(kù)MSSQlServer 2005的分布式查詢支持也是OLE_DB方式.SQL Server 用戶可以使用分布式查詢?cè)L問以下內(nèi)容：

　　A:存儲(chǔ)在多個(gè) SQL Server 實(shí)例中的分布式數(shù)據(jù)

　　B:存儲(chǔ)在各種可以使用 OLE DB 訪問接口訪問的關(guān)系和非關(guān)系數(shù)據(jù)源中的異類數(shù)據(jù)

　　OLE DB 訪問接口將在稱為行集的表格格式對(duì)象中公開數(shù)據(jù)。SQL Server 允許在 Transact-SQL 語(yǔ)句中像引用 SQL Server 表一樣引用 OLE DB 訪問接口中的行集,[其實(shí)不用關(guān)心這個(gè)行集概念 它的功能類似SQl中臨時(shí)表 不過它容積更大 能容納類型更多 更豐富]

　　SQL Server 實(shí)例的客戶機(jī)與 OLE DB 訪問接口之間的連接 如下圖:

　　從上圖可以看出.客戶端借助OLEDB接口可以訪問Oracle/MS Jet/MS SQL/ODBC/第三方等這些豐富數(shù)據(jù)源來我們分布式查詢提供數(shù)據(jù). 說了這么多關(guān)于OLEDB底層支持. 關(guān)于在MSSQL2005中則支持兩種方式來進(jìn)行分布式查詢:

　　<A>使用添加鏈接服務(wù)器方式(Add Link Server)

　　<B>使用特定名稱及特定數(shù)據(jù)源來直接指定(Add Host Names)

　　其實(shí)這兩種方式在實(shí)際運(yùn)用中是有區(qū)別的:

　　方式A:Add Link Server方式建立服務(wù)器之間關(guān)聯(lián).創(chuàng)建一個(gè)鏈接的服務(wù)器，使其允許對(duì)分布式的、針對(duì) OLE DB 數(shù)據(jù)源的異類查詢進(jìn)行訪問. 一般適用于持久的數(shù)據(jù)操作 對(duì)于數(shù)據(jù)量偏大 服務(wù)器之間交付時(shí)間長(zhǎng)特點(diǎn).

　　方式B: Add Host Name 利用域來唯一識(shí)別數(shù)據(jù)庫(kù)以及數(shù)據(jù)庫(kù)表對(duì)象. 來實(shí)現(xiàn)跨服務(wù)器訪問. 這種方式一般比較簡(jiǎn)單 主要適用于對(duì)數(shù)據(jù)需求臨時(shí)性查詢是使用偏多. 不適合做大批量數(shù)據(jù)提取. 有性能瓶頸.

　　<2>分布式查詢實(shí)現(xiàn)

　　在進(jìn)行實(shí)現(xiàn)分布式查詢之前.本次測(cè)試Demo對(duì)應(yīng)的SQL版本:

　　確定SQLServer版本后如下會(huì)演示兩種方式來實(shí)現(xiàn)分布式查詢,并對(duì)Distributed Query中詳細(xì)細(xì)節(jié)進(jìn)行說明.

　　<2.1>鏈接服務(wù)器查詢

　　鏈接服務(wù)器配置使 SQL Server 可以對(duì)遠(yuǎn)程服務(wù)器上的 OLE DB 數(shù)據(jù)源執(zhí)行命令。鏈接服務(wù)器具有以下優(yōu)點(diǎn)：

1. 訪問遠(yuǎn)程服務(wù)器。
2. 能夠?qū)ζ髽I(yè)內(nèi)的異類數(shù)據(jù)源發(fā)出分布式查詢、更新、命令和事務(wù)。
3. 能夠以相似的方式確定不同的數(shù)據(jù)源

　　下圖顯示了鏈接服務(wù)器配置的基礎(chǔ):

　　現(xiàn)在利用鏈接服務(wù)器方式實(shí)現(xiàn)數(shù)據(jù)訪問遠(yuǎn)程服務(wù)器數(shù)據(jù)庫(kù)CustomerDB中Users表數(shù)據(jù)先本地添加LinkServer:

   1:  -- 建立連接服務(wù)器  第一步建立連接  IP方式來控制 
   2:   
   3:  EXEC sp_addlinkedserver   '192.168.10.104' , 'SQL Server' 
   4:   
   5:  -- 查看鏈接服務(wù)器信息  [測(cè)試連接成功]
   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 

　　如上市建立連接服務(wù)器最簡(jiǎn)單方式.建立鏈接服務(wù)器過程其實(shí)調(diào)用了系統(tǒng)存儲(chǔ)過程Sp_addlinkedserver. 第一個(gè)參數(shù)為Name 其實(shí)用來唯一標(biāo)識(shí)鏈接服務(wù)器. 當(dāng)然可以其他任何有意義字符串來定義,但我個(gè)人建議使用遠(yuǎn)程服務(wù)器的IP來標(biāo)識(shí).第二個(gè)參數(shù)是要添加為鏈接服務(wù)器的 OLE DB 數(shù)據(jù)源的產(chǎn)品名稱. 默認(rèn)為Null,如果指定”SQlServer“則無需指定其他參數(shù).

　　如果你的本地裝有多個(gè)數(shù)據(jù)庫(kù)實(shí)例. 第一個(gè)種方式就不適用.這是就需要用SQl2005架構(gòu)來唯一標(biāo)識(shí):

   1:  -- 含架構(gòu)名  查詢數(shù)據(jù)兩種模式 
   3:  select top 10 * from [192.168.10.104]. wl . 架構(gòu)名 . 表名
   5:  -- 架構(gòu)名 [采用默認(rèn)架構(gòu)名 ] 
   7:  select top 10 * from [192.168.10.104]. CustomerDB . dbo. Users 

　　對(duì)于Sql2005架構(gòu)這個(gè)概念很多人比較陌生:

　　架構(gòu)是形成單個(gè)命名空間的數(shù)據(jù)庫(kù)實(shí)體的集合。命名空間是一個(gè)集合，其中每個(gè)元素的名稱都是唯一的。 例如，為了避免名稱沖突，同一架構(gòu)中不能有兩個(gè)同名的表。兩個(gè)表只有在位于不同的架構(gòu)中時(shí)才可以同名 例如本次Demo 在CustomerDB后對(duì)應(yīng)DBO既是默認(rèn)的架構(gòu)名.

　　創(chuàng)建后.如果需要修改連接服務(wù)器屬性可以通過sp_serveroption系統(tǒng)Proc來設(shè)置:

   1:  -- 配置鏈接服務(wù)器屬性 sp_serveroption為遠(yuǎn)程服務(wù)器和鏈接服務(wù)器設(shè)置服務(wù)器選項(xiàng)
   2:  -- 語(yǔ)法  sp_serveroption [@server =] 'server',[@optname =] 'option_name',[@optvalue =] 'option_value'
   4:  exec sp_serveroption '192.168.10.104','name','192.168.10.104' 
   6:  -- 查看連接服務(wù)器
   7:  select * from sys.servers

　　建立后我就可以直接來查詢遠(yuǎn)程服務(wù)器上數(shù)據(jù):

   1:  -- 查詢遠(yuǎn)程服務(wù)器數(shù)據(jù)
   3:  select * from [192.168.10.104].CustomerDB.dbo.Users   --[成功]
   5:  -- sp_droplinkedsrvlogin 刪除鏈接服務(wù)器登錄名映射 [刪除登錄映射]
   6:  -- 如果為 NULL，那么將會(huì)刪除由 sp_addlinkedserver 創(chuàng)建的默認(rèn)映射 [第二個(gè)參數(shù)]
   8:  exec sp_droplinkedsrvlogin '192.168.10.104' ,NULL 
  10:  -- 刪除鏈接服務(wù)器屬性 [刪除服務(wù)器]
  12:  exec sp_dropserver 'mytest' --[刪除成功 同時(shí)也刪除了Sys_Server信息]
  14:  -- 查看服務(wù)器詳細(xì)信息
  15:  EXEC sp_helpserver

　　查詢結(jié)果:

　　測(cè)試查詢成功.遠(yuǎn)程數(shù)據(jù)成功獲取.

　　當(dāng)測(cè)試完成后我們不需要這個(gè)連接服務(wù)器是即可利用SP_DroplinkServer刪除掉. 對(duì)應(yīng)參數(shù)為創(chuàng)建時(shí)Name唯一標(biāo)識(shí). 通過Sp_helpserver來查看連接服務(wù)器詳細(xì)信息.

　　注意如上創(chuàng)建連接服務(wù)器時(shí)設(shè)置srvproduct參數(shù)即OLED數(shù)據(jù)源名稱時(shí)我們采用了SQlServer方式.

　　下面說明這種方式特點(diǎn).：

　　這種方式是最為簡(jiǎn)單直接的一種建立鏈接服務(wù)器方式. 但是存在前提的. 測(cè)試發(fā)現(xiàn):

　　在所有數(shù)據(jù)庫(kù)的遠(yuǎn)程連接 dbo 的方式必須建立在 SA 密碼相同的基礎(chǔ)上 ，否則容易產(chǎn)生無法連接的情況 Sa用戶登錄失敗. 你也就明白這個(gè)SQlServer參數(shù)其實(shí)就是在本地?cái)?shù)據(jù)拷貝服務(wù)器角色SysAdmin下用戶SA.來對(duì)服務(wù)器進(jìn)行登錄. 如果你的本地Sa密碼與遠(yuǎn)程服務(wù)器上密碼不一致 則無法正常連接.

　　經(jīng)過測(cè)試還發(fā)現(xiàn)一種情況:

　　利用Windows7訪問XP(Sp2)系統(tǒng)時(shí)始終提示無法解析或拒絕連接SQlServer2005.這個(gè)問題我整了好久后來才到官方鏈接參數(shù)中發(fā)現(xiàn).:如果你的XP系統(tǒng)沒有打上SP4的補(bǔ)丁包 這個(gè)問題會(huì)始終出現(xiàn). 需要特別注意.

　　<2.2>直接指定數(shù)據(jù)源分布式查詢

　　其實(shí)相對(duì)第一種方式, 直接指定方式在SQlServer架構(gòu)中 其實(shí)跳過本地與遠(yuǎn)程服務(wù)器建立映射關(guān)系的這一步. 通過鏈接關(guān)系建立 其實(shí)就是建立一種內(nèi)部映射關(guān)系. 如果沒有映射關(guān)系則 大部分設(shè)置需要手動(dòng)控制.

　　直接指定數(shù)據(jù)源方式 需要開啟分布式查詢的基本權(quán)限 來進(jìn)行查詢：

   2:  -- 如果想使用分布式查詢，必須先開通分布式查詢 [外圍配置 這點(diǎn)是所有查詢操作前提]
   3:  -- sp_configure--顯示或更改當(dāng)前服務(wù)器的全局配置設(shè)置
   4:  -- reconfigure 指定如果配置設(shè)置不需要服務(wù)器停止并重新啟動(dòng)，則更新當(dāng)前運(yùn)行的值
   5:  -- SQL2005默認(rèn)是沒有開啟’Ad Hoc Distributed Queries’ 組件 
   6:   
   7:  -- 啟用權(quán)限
   8:  exec sp_configure 'show advanced options',1  -- 顯示高級(jí)配置
   9:  reconfigure -- 更新值
  10:  exec sp_configure 'Ad Hoc Distributed Queries',1 -- 啟用分布式查詢
  11:  reconfigure
  12:  go
  14:   
  15:  -- 關(guān)閉分布式查詢
  16:  exec sp_configure 'Ad Hoc Distributed Queries',0   
  17:  reconfigure
  18:  exec sp_configure 'show advanced options',0
  19:  reconfigure
  20:  go  
  23:  -- 開啟權(quán)限后 另外一種查詢方式
  24:  -- 查詢格式
  25:  SELECT * FROM OPENDATASOURCE(
  26:   'SQLOLEDB',
  27:   'Data Source=遠(yuǎn)程ip;User ID=sa;Password=密碼'
  28:   ).庫(kù)名.dbo.表名
  29:   WHERE 條件
  31:  -- 需要開啟權(quán)限 
  32:  -- 開啟權(quán)限 提示[遠(yuǎn)程的SqlServer不允許遠(yuǎn)程連接]
  34:  select * from OPENDATASOURCE('SQLOLEDB','Data Source=192.168.10.67; User ID=sa; Password=chenkai').wl.dbo.Users

　　開啟權(quán)限后. 需要里利用ReConfig命令來確認(rèn).對(duì)目前分布式查詢權(quán)限的修改. 如果在使用完分布式查詢后注意關(guān)閉.最后查詢結(jié)果:

　　測(cè)試成功.

　　有些人說使用數(shù)據(jù)庫(kù)角色SysAdmin角色下的Sa用戶進(jìn)行遠(yuǎn)程數(shù)據(jù)傳輸和驗(yàn)證. 不安全. 其實(shí)在使用過程中應(yīng)該不難看出. 在從遠(yuǎn)程服務(wù)器拉取數(shù)據(jù)庫(kù)過程中. 本地?cái)?shù)據(jù)庫(kù)需要對(duì)權(quán)限，創(chuàng)建連接服務(wù)器都需要最大用戶權(quán)限來操作. 而服務(wù)器呢, 只需要能連接上 同時(shí)對(duì)指定數(shù)據(jù)CustomerDB具有讀寫的權(quán)限即可. 當(dāng)然你更多遠(yuǎn)程操作可以把用戶賦予CustomerDB的OWner角色.

　　這時(shí)我們?nèi)绾斡梅荢A用戶來來連接遠(yuǎn)程用戶?

　　我們現(xiàn)在遠(yuǎn)程服務(wù)器上對(duì)連接創(chuàng)建一個(gè)用戶名為Test的用戶 服務(wù)器角色設(shè)置Public即可:

　　在用戶角色設(shè)置中需要對(duì)指定訪問數(shù)據(jù)CustomerDB具有讀寫權(quán)限:

　　在遠(yuǎn)程服務(wù)器創(chuàng)建TEst用戶時(shí)使用SQlServer身份驗(yàn)證方式登錄 這時(shí)設(shè)置密碼為RemoteDB.在使用非Sa用戶進(jìn)行遠(yuǎn)程：

   1:  -- 執(zhí)行前先刪除已經(jīng)存在數(shù)據(jù)
   2:  Exec sp_droplinkedsrvlogin [192.168.10.76],Null
   3:  Exec sp_dropserver 'demodb'
   4:   
   5:  -- 創(chuàng)建服務(wù)器連接
   6:  EXEC  sp_addlinkedserver
   7:        @server='demodb',-- 被訪問的服務(wù)器別名 
   8:        @srvproduct='',
   9:        @provider='SQLOLEDB',
  10:        @datasrc='192.168.10.76'   -- 要訪問的服務(wù)器
  12:   
  13:  EXEC sp_addlinkedsrvlogin 
  14:       'demodb', -- 被訪問的服務(wù)器別名
  15:       'false', 
  16:       NULL, 
  17:       'Test', -- 賬號(hào)
  18:       'RemoteDB' -- 密碼

　　如上我們首先清除已經(jīng)可能創(chuàng)建服務(wù)器數(shù)據(jù)記錄. 然后創(chuàng)建服務(wù)器連接.sp_addlinkedSrvlogin系統(tǒng)存儲(chǔ)過程用來創(chuàng)建鏈接服務(wù)器上遠(yuǎn)程登錄之間的映射 . 即我們可以詳細(xì)設(shè)置本地與遠(yuǎn)程服務(wù)器詳細(xì)的映射信息. 例如設(shè)置我們特定用戶訪問的用戶名和密碼.

　　查詢數(shù)據(jù):

   1:  -- 查詢指定用戶Test數(shù)據(jù)
   2:  select * from [demodb].CustomerDB.dbo.Users -- [如上測(cè)試成功]

　　查詢結(jié)果:

　　指定用戶Test對(duì)CustomerDB訪問數(shù)據(jù)方式測(cè)試成功.