SQLServer分布式查詢

作者: chenkai 來源: 博客園發布時間: 2010-09-12 13:10 閱讀: 5292 次推薦: 0 原文鏈接 [收藏]

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

　　本篇將演示利用SQlExpress鏈接遠程SQlServer來獲取數據方式來詳細說明分布式查詢需要注意細節.先看一下系統架構數據查詢基本處理:

　　當然如果采用了分布式查詢我們系統采取數據DataBase也就可能在多個遠程[Remote Server]上訪問時:

　　如上截取系統架構中關于數據與緩存流向中涉及的分布式查詢業務, 當我們從客戶端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 訪問接口之間的連接如下圖:

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

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

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

　　其實這兩種方式在實際運用中是有區別的:

　　方式A:Add Link Server方式建立服務器之間關聯.創建一個鏈接的服務器，使其允許對分布式的、針對 OLE DB 數據源的異類查詢進行訪問. 一般適用于持久的數據操作對于數據量偏大服務器之間交付時間長特點.

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

　　<2>分布式查詢實現

　　在進行實現分布式查詢之前.本次測試Demo對應的SQL版本:

　　確定SQLServer版本后如下會演示兩種方式來實現分布式查詢,并對Distributed Query中詳細細節進行說明.

　　<2.1>鏈接服務器查詢

　　鏈接服務器配置使 SQL Server 可以對遠程服務器上的 OLE DB 數據源執行命令。鏈接服務器具有以下優點：

訪問遠程服務器。
能夠對企業內的異類數據源發出分布式查詢、更新、命令和事務。
能夠以相似的方式確定不同的數據源

　　下圖顯示了鏈接服務器配置的基礎:

　　現在利用鏈接服務器方式實現數據訪問遠程服務器數據庫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: select top 10 * from [192.168.10.104]. wl . 架構名 . 表名
5: -- 架構名 [采用默認架構名 ]
7: select top 10 * 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

　　查詢結果:

　　測試查詢成功.遠程數據成功獲取.

　　當測試完成后我們不需要這個連接服務器是即可利用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 * FROM OPENDATASOURCE(
26: 'SQLOLEDB',
27: 'Data Source=遠程ip;User ID=sa;Password=密碼'
28: ).庫名.dbo.表名
29: WHERE 條件
31: -- 需要開啟權限
32: -- 開啟權限提示[遠程的SqlServer不允許遠程連接]
34: select * from OPENDATASOURCE('SQLOLEDB','Data Source=192.168.10.67; User ID=sa; Password=chenkai').wl.dbo.Users

　　開啟權限后. 需要里利用ReConfig命令來確認.對目前分布式查詢權限的修改. 如果在使用完分布式查詢后注意關閉.最后查詢結果:

　　測試成功.

　　有些人說使用數據庫角色SysAdmin角色下的Sa用戶進行遠程數據傳輸和驗證. 不安全. 其實在使用過程中應該不難看出. 在從遠程服務器拉取數據庫過程中. 本地數據庫需要對權限，創建連接服務器都需要最大用戶權限來操作. 而服務器呢, 只需要能連接上同時對指定數據CustomerDB具有讀寫的權限即可. 當然你更多遠程操作可以把用戶賦予CustomerDB的OWner角色.

　　這時我們如何用非SA用戶來來連接遠程用戶?

　　我們現在遠程服務器上對連接創建一個用戶名為Test的用戶服務器角色設置Public即可:

　　在用戶角色設置中需要對指定訪問數據CustomerDB具有讀寫權限:

　　在遠程服務器創建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系統存儲過程用來創建鏈接服務器上遠程登錄之間的映射 . 即我們可以詳細設置本地與遠程服務器詳細的映射信息. 例如設置我們特定用戶訪問的用戶名和密碼.

　　查詢數據: