連結的伺服器和分散式查詢

連結的伺服器為SQL Server提供了從遠端資料源訪問資料的能力。使用連結的伺服器，你可以執行查詢、實施資料修改以及執行遠端程序呼叫。遠端資料源可以是同類（意思是資料來源是另一個SQL Server執行個體）或異類的（來自其他關係型資料庫產品和資料來源，比如DB2、Access、Oracle、Excel和文字檔）。跨越多個平台連接或檢索資料的查詢是跨平台查詢。使用跨平台查詢，可以訪問遺留資料庫系統，而不需要對既有的資料來源進行合并和遷移。

通過OLE DB提供者串連遠端資料來源。OLE DB由微軟開發，是用來提供到各種不同的資料來源的一致性訪問的一組COM（component object model，元件物件模型）介面。為了建立從SQL Server 2005執行個體到另一資料來源的訪問，需要選擇適當的OLE DB提供者。OLE DB提供者的設計方式決定在分散式查詢（SELECT、INSERT、UPDATE或DELETE預存程序執行）中實現何種分散式查詢操作。

因此概括的說，連結的伺服器意思是建立到遠端資料源的串連的途徑。依賴用來設定連結的伺服器的OLE DB驅動，可以執行分散式查詢來檢索資料，或在遠端資料源中執行操作。

分散式查詢也可以不通過定義連結的伺服器來運行，例如通過使用T-SQL函數OPENROWSET。除了不通過連結的伺服器查詢遠端資料源，在SQL Server 2005增強了OPENROWSET，允許BULK讀取ASCII、Unicode以及二進位檔案。使用OPENROWSET和BULK，可以從文字檔中讀取表狀資料，或使用它將ASCII、Unicode或二進位類型檔案匯入到單個的大資料類型列及單個行中（比如varchar(max)、nvarchar(max)或varbinary(max)）。

也是SQL Server 2005中的新特性，微軟引入了SYNONYM對象，它允許你通過較短的名稱引用長名稱的對象。通常在使用長標識符時這是有用的，但在引用了4部分組成的連結的伺服器名稱的分散式查詢中，使用較短的名稱代替資料來源特別有用。

本章包括這些技巧：建立連結的伺服器、執行分散式查詢、使用OPENROWSET和BULK從文字檔中讀取以及使用新的SYNONYM對象。

27.1  連結的伺服器基礎
下面一組技巧將展示如何使用連結的伺服器。特別地，我將展示如何完成下列操作：

l 建立連結的伺服器串連到另一個SQL Server 2005執行個體。

l 配置連結的伺服器的屬性。

l 查看SQL Server執行個體上的配置連結的伺服器的資訊。

l 刪除連結的伺服器。

我將先討論如何使用系統預存程序sp_addlinkedserver來建立新連結的伺服器。

27.1.1  為另一SQL Server執行個體建立連結的伺服器
連結的伺服器允許從SQL Server執行個體中查詢外部資料源。外部資料源可以是不同的SQL Server執行個體，也可以是非SQL Server資料來源，比如說Oracle、MS Access、DB2或MS Excel。

使用系統預存程序sp_addlinkedserver來建立連結的伺服器。文法如下：

表27-1描述了這個系統預存程序的參數。

表27-1  sp_addlinkedserver參數

參    數
 描    述
 
server
 連結的伺服器的本地名稱。也允許使用執行個體名稱，例如MYSERVER\SQL1
 
product_name
 OLE DB資料來源的產品名。對於SQL Server執行個體來說，product_name是'SQL Server'
 
provider_name
 這是OLE DB提供者的唯一可程式化標識。當沒有指定它時，提供者名稱是SQL Server資料來源。SQL Server顯式的provider_name是SQLNCLI（Microsoft SQL Native Client OLE DB Provider）。Oracler的是MSDAORA，Oracle 8或更高版本的是OraOLEDB.Oracle。MS Access和MS Excel的是Microsoft.Jet.OLEDB.4.0。IBM DB2的是DB2OLEDB，以及ODBC資料來源的是MSDASQL
 
data_source
 這是特定OLE DB提供者解釋的資料來源。對於SQL Server，這是SQL Server（servername或servername\instancename）的網路名稱。對於Oracle，這是SQL*Net別名。對於MS Access和MSExcel，這是檔案的完整路徑和名稱。對於ODBC資料來源，這是系統DSN名稱
 
location
 由特定OLE DB提供者解釋的位置
 
provider_string
 OLE DB 提供者特定的連接字串。對於ODBC串連，這是ODBC連接字串。對於MS Excel，這是Excel 5.0
 
catalog
 catalog的定義變化基於OLE DB提供者的實現。對於SQL Server，這是可選的資料庫名稱，對於DB2，這個目錄是資料庫的名稱
 

在多SQL Server執行個體的網路環境中，連結的伺服器提供了方便的方式共用SQL Server資料，而不是必須要通過物理地推送或拉回資料和複製架構的方式來實現共用。

提示     在本章中，我講解了在SQL Server執行個體之間通訊的樣本。對於異類的資料來源，比如DB2、Access以及Oracle，參數會有很多變化。如果需要更全面的sp_addlinkedserver選項的講解，請看SQL Server聯機叢書中的主題“sp_addlinkedserver (Transact-SQL)”。

串連到異質資料源使用的配置會基於OLE DB提供者而發生變化。如果你只是串連到不同的SQL Server執行個體，微軟把它變得很簡單。在這個示

例中，我展示了建立串連到另一SQL Server執行個體的連結的伺服器： 

也可以建立串連到SQL Server具名執行個體的連結的伺服器，例如：

解析

添加串連到外部資料源的連結的伺服器允許執行分散式查詢（分散式查詢將在本章稍後介紹）。當添加SQL Server連結的伺服器到一個SQL Server執行個體時，不管它是預設的還是命名的執行個體，微軟都把它變得很簡單，只需要server和product_name值。

串連到SQL Server執行個體的安全性方法是怎樣的呢？當建立新的連結的伺服器時，使用目前使用者的登入名稱安全憑證（SQL或Windows）來連結到連結的伺服器。也可以建立顯示的連結的伺服器遠程登入名稱映射，稍後會討論該內容。

27.1.2  配置連結的伺服器屬性
在建立連結的伺服器之後有許多不同的設定可以用來配置它。表27-2描述了這些設定。

表27-2  連結的伺服器屬性

參    數
 描    述
 
collation compatible
 如果你確認SQL Server執行個體與遠程SQL Server擁有相同的定序就啟用這個設定。由於SQL Server不再需要對資料來源之間的字元列執行比較操作，把它們假定為相同的定序，這樣做可以提升效能
 
collation name
 如果啟用了use remote collation並且是非SQL Server的資料來源，則collation name指定遠程伺服器定序的名稱。這個定序名稱必須是SQL Server所支援的
 
connect timeout
 指定在逾時發生之前到連結的伺服器的串連會嘗試多少秒。如果數值為“0”，sp_ configure的remote query timeout的伺服器值用來當作預設值
 
data access
 如果啟用，就允許分散式查詢訪問
 
lazy schema validation
 如果設為true，則架構不會在查詢開始時去檢測遠端資料表。儘管這樣會減少遠程查詢的負載，但是如果架構發生了變化並且你沒有進行架構檢測，比如說查詢中引用的對象不能與查詢命令進行通訊，就會建置錯誤
 
query timeout
 指定查詢等待的逾時值（秒數）。如果這個值為0，則query wait選項使用sp_configure值
 
rpc
 啟用從伺服器進行遠端程序呼叫
 
rpc out
 啟用遠端程序呼叫到伺服器
 
use remote collation
 指定是使用遠程伺服器定序（true）還是本機伺服器定序（false）
 

使用系統預存程序sp_serveroption來修改連結的伺服器屬性。文法如下：

表27-3描述了這個系統預存程序的參數。

表27-3  sp_serveroption參數

參    數
 描    述
 
server
 配置屬性的連結的伺服器的名稱
 
option_name
 要配置的選項
 
option_value
 選項的新值
 

在這個技巧中，連結的伺服器JOEPROD\NODE2的查詢逾時設定將修改為60秒：

解析

在這個技巧中，連結的伺服器JOEPROD\NODE2的查詢逾時限制被修改為60秒。稱為server的第一個參數指定了連結的伺服器的名稱。第二個參數option_name指定了要配置的選項，第三個參數option_value配置了新值。

27.1.3  查看連結的伺服器資訊 
可以使用系統目錄檢視sysservers查看SQL Server執行個體中定義的連結的伺服器。例如：

它返回：

解析

系統目錄檢視sysservers可以用來檢索關於SQL Server執行個體中定義的連結的伺服器的資訊。可以從sys.servers查看到的其他選項包括：product、provider、data_source、location、provider_string、catalog、is_linked、is_remote_login_enabled、is_rpc_out_enabled、is_data_access_enabled、is_collation_compatible、use_remote_collation和collation_name。is_linked列在查詢中限定只返回連結的伺服器（不包括本地SQL Server執行個體設定）。

27.1.4  刪除連結的伺服器
系統預存程序sp_dropserver用來刪除連結的伺服器。sp_dropserver的文法如下：

表27-4描述了這個系統預存程序的參數。

表27-4  sp_dropserver的參數

參    數
 描    述
 
server
 從SQL Server執行個體中刪除的連結的伺服器的名稱
 
droplogins
 如果指定droplogins，則在刪除連結的伺服器之前要刪除登入名稱映射（在本章稍後描述）
 

這個技巧展示了刪除連結的伺服器：

解析

這個技巧展示了使用系統預存程序sp_dropserver從你的SQL Server執行個體中刪除連結的伺服器。在第二個參數中指定的droplogins選項將在刪除連結的伺服器之前刪除既有的登入名稱映射（我將在下面幾個技巧中介紹連結的伺服器登入）。如果在刪除登入之前嘗試刪除連結的伺服器，你將得到如下訊息：

仍有對伺服器'JOEPROD'的遠程登入或連結登入。