使用特定名稱及特定數(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í)間長特點(diǎn).

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

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

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

確定SQL Server版本后如下會(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ù)庫CustomerDB中Users表數(shù)據(jù)先本地添加LinkServer: 

1. -- 建立連接服務(wù)器  第一步建立連接  IP方式來控制     
2. EXEC sp_addlinkedserver   '192.168.10.104' , 'SQL Server'     
3. -- 查看鏈接服務(wù)器信息  [測試連接成功]    
4. select name , product, provider, data_source, query_timeout, lazy_schema_validation, is_remote_login_enabled, is_rpc_out_enabled    
5. from sys.servers    
6. where is_linked= 1  

如上市建立連接服務(wù)器最簡單方式.建立鏈接服務(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,如果指定”SQL Server“則無需指定其他參數(shù).

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

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

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

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

在遠(yuǎn)程服務(wù)器創(chuàng)建TEst用戶時(shí)使用SQL Server身份驗(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. -- 創(chuàng)建服務(wù)器連接    
5. EXEC  sp_addlinkedserver    
6.  @server='demodb',-- 被訪問的服務(wù)器別名   
7.         @srvproduct='',   
8.       @provider='SQLOLEDB',   
9. @datasrc='192.168.10.76'   -- 要訪問的服務(wù)器  
10.   EXEC sp_addlinkedsrvlogin    
11.   'demodb', -- 被訪問的服務(wù)器別名   
12.     'false',   
13.      NULL,     
14.   'Test', -- 帳號(hào)    
15.   '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 -- [如上測試成功] 

查詢結(jié)果

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

當(dāng)測試完成后我們不需要這個(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).：

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

在所有數(shù)據(jù)庫的遠(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)過測試還發(fā)現(xiàn)一種情況:

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

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

其實(shí)相對(duì)第一種方式, 直接指定方式在SQL Server架構(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)行查詢：

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

如上我們首先清除已經(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 -- [如上測試成功] 

查詢結(jié)果

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