sql server數(shù)據(jù)庫技術(shù)第16章跨實(shí)例鏈接

16通過SQLServer2008不僅可以本機(jī)實(shí)例的數(shù)據(jù)庫，還可以通過跨實(shí)例的方 SQLServer提供了服務(wù)器用于數(shù)據(jù)庫對象。通過在服務(wù)器中配置訪問接口，使用不同的接口不同的數(shù)據(jù)庫類型，能夠?qū)ζ髽I(yè)內(nèi)的異類數(shù)據(jù)源發(fā)出分布式 OLEDB數(shù)據(jù)源，標(biāo)識可通過OLEDB的特定數(shù)據(jù)庫雖然通過服務(wù)器定義查詢的數(shù)據(jù)源通常是數(shù)據(jù)庫，但OLEDB接口對各種文服務(wù)器主要用于處理分布式查詢。例如當(dāng)客戶端應(yīng)用程序通過服務(wù)器執(zhí)行分布式查詢時(shí)，SQLServer將分析命令，然后根據(jù)服務(wù)器的具體數(shù)據(jù)庫生成請求命令，再向OLEDB發(fā)送請求。行集請求的形式可以是對該接口執(zhí)行查詢或從該接口打開基表。其查詢數(shù)據(jù)的結(jié)構(gòu)如圖16.1所示。為使數(shù)據(jù)源能通過服務(wù)器返回?cái)?shù)據(jù)，該數(shù)據(jù)源的OLEDB接口（DLL）必須與SQLServer的實(shí)例位于同一服務(wù)器上。使用第OLEDB接口時(shí)，運(yùn)行SQLServer服務(wù)的賬戶必須具有對安裝接口的及其所有子的和執(zhí)行權(quán)限。微軟默認(rèn)為SQLServer提供了多個(gè)數(shù)據(jù)庫的接口，AccessExcel或其他SQLServer實(shí)例時(shí)不需要再使用第的OLEDB接口。但是對于Sybase、Oracle等數(shù)據(jù)使用T-SQL創(chuàng)建服務(wù)

圖16.1服務(wù)器數(shù)據(jù)的結(jié)16.1sp_addlinkedserver[,[@provider=]'provider_name'][,[@datasrc=]'data_source'][,[@location=]'location'][,[@provstr=]'provider_string'][,[@catalog=]'catalog'][@srvproduct=]'product_name'為要添加為服務(wù)器的OLEDB數(shù)據(jù)源的產(chǎn)品名稱。如果為SQLServer，則不必指定provider_name、data_source、location、provider_string和catalog。[@provider=]'provider_name'為與此數(shù)據(jù)源對應(yīng)的OLEDB接口的唯一編程標(biāo)[@datasrc=]'data_source'為由OLEDB接口解釋的數(shù)據(jù)源的名稱。data_source作為DBPROP_INIT_DATASOURCE屬性傳遞以初始化OLEDB接口。[@location=]'location'為由OLEDB接口解釋的數(shù)據(jù)庫的位置。location作[@provstr=]'provider_string'指定OLEDB接口特定的連接字符串，它可標(biāo)識唯STRING屬性以初始化OLEDB接口。[@catalog=]'catalog'為與OLEDB接口建立連接時(shí)所使用的。catalog作為DBPROP_INIT_CATALOG屬性傳遞以初始化OLEDB接口。在針對SQLServer實(shí)例定義服務(wù)器時(shí)，指向服務(wù)器映射到的默認(rèn)數(shù)據(jù)庫。該表還顯示了用于設(shè)置服務(wù)器的sp_addlinkedserver參數(shù)值。16.1OLEDBOLEDBOLEDB接SQLSQLServerNativeClientOLEDB接口SQL認(rèn)值SQLSQLServerNativeClientOLEDB接SQLServer的網(wǎng)絡(luò)名稱（用SQLSQLServerNativeClientOLEDB接OLEDBProviderfor用 Oracle數(shù)據(jù)庫的SQL*NetOracle，版OracleProviderforOLEDBOracleOLEDBProviderforJetOLEDBProviderforODBCOLEDBProviderforOLEDB IndexingService OLEDBProviderforExcelIBMDB2OLEDBProviderfor代碼代碼16.2創(chuàng) EXECmaster.dbo.sp_addlinkedserver--新 服務(wù)@server=代碼代碼16.3創(chuàng)建Access數(shù)據(jù)庫 @server='ACC',-- @provider= @srvproduct='OLEDBProviderforJet',--@srvproduct='OLEDBProviderforJet',--驅(qū)動@datasrc'D:\Access1.mdbAccess數(shù)據(jù)庫文件地址儲過程為服務(wù)器添加用戶認(rèn)證信息，其語法如代碼16.4所示。16.4sp_addlinkedsrvloginsp_addlinkedsrvloginsp_addlinkedsrvlogin[@rmtsrvname=]'rmtsrvname'[,[@useself=]'TRUE'|'FALSE'|'NULL'][,[@locallogin=]'locallogin'][,[@rmtuser=]'rmtuser'][,[@rmtpassword=]'rmtpassword'[@rmtsrvname=]'rmtsrvname'為應(yīng)用登錄映射的服務(wù)器的名稱[@useself=]'TRUE'|'FALSE'|'NULL'確定是否通過模擬本地登錄名或顯式提交登錄名和來連接到rmtsrvname。值為TRUE指定登錄名使用自己的憑據(jù)連接到rmtsrvnamermtuserrmtpassword參數(shù)。值FALSErmtuser和rmtpassword參數(shù)用于連接到指定locallogin的rmtsrvname。如果rmtuser和rmtpassword也設(shè)置為NULL，則不使用登錄名或來連接服務(wù)器。[@locallogin=]'locallogin'為本地服務(wù)器上的登錄。NULL指定此項(xiàng)應(yīng)用于連接到錄或Windows登錄。對于Windows登錄來說，必須以直接的方式或通過已被的Windows組成員授予其SQLServer的權(quán)限[@rmtuser=]'rmtuser'為當(dāng)@useself為FALSE時(shí)，用于連接到rmtsrvname的登錄名。當(dāng)服務(wù)器不使用Windows驗(yàn)證的SQLServer實(shí)例時(shí)，rmtuser是SQLServer登錄名。rmtuser的數(shù)據(jù)類型為sysname，默認(rèn)值為NULL。[@rmtpassword=]'rmtpassword'為與rmtuser關(guān)聯(lián)的rmtpassword的數(shù)據(jù)類型sysname，默認(rèn)值為NULL碼16.5所示。代碼代碼16.5添 EXECmaster.dbo.sp_addlinkedsrvlogin--添加 @rmtsrvname=N'192.168.100.100',-- @useself=N'False',--不模擬本地用戶登錄@locallogin=NULL本地服務(wù)器登錄@rmtuser=N'sa',--用戶名@rmtpassword='p@ssw0rd'--當(dāng)下列所有條件都存在時(shí)，SQLServerWindows使用SSMS創(chuàng)建服務(wù)器”窗口，如圖16.2所示。圖16.2“新建服務(wù)器”窗在“服務(wù)器”文本框中輸入要新建的服務(wù)器的名稱，在“接口”下拉列表框中選擇需要使用的接口，然后分別在“產(chǎn)品名稱”、“數(shù)據(jù)源”、“接口字SQLServer支持為每個(gè)不同的登錄用戶映射不同的服務(wù)器認(rèn)證?？梢詥螕簟疤砑印卑粹o將本地登錄的用戶與用戶映射起來。對于沒有映射的用戶，SQLServer提供了4種方案： 圖16.3服務(wù)器安全性配圖16.4服務(wù)器選然使用sp_addlinkedsrvlogin過程。對于本地用戶映射用戶的修改，則需要使用sp_droplinkedsrvlogin過程來刪除原有用戶映射。sp_droplinkedsrvloginsp_droplinkedsrvlogin[@rmtsrvname=]'rmtsrvname',[@locallogin=]'locallogin'其中的參數(shù)[@rmtsrvname=]'rmtsrvname'是應(yīng)用SQLServer登錄映射的服務(wù)器名稱。rmtsrvname的數(shù)據(jù)類型為sysname，無默認(rèn)值。rmtsrvname必須已經(jīng)存在。[@locallogin=]'locallogin'表示當(dāng)前服務(wù)器上映射到服務(wù)器的SQLServer登錄，它具有到服務(wù)器rmtsrvname的映射。locallogin的數(shù)據(jù)類型為sysname，無默認(rèn)值。localloginrmtsrvnameNULLsp_addlinkedserver例如將原有的sa用戶映射刪除，而使用新的登錄名sa2來進(jìn)行用戶映射，對應(yīng)的如代碼16.6所示。16.6@rmtsrvname=N'192.168.100.100',@locallogin=N'sa'@rmtsrvname=N'192.168.100.100',@locallogin=N'sa2',--新的登錄名@useself=N'False',@rmtuser=N'',@rmtpassword=N'123'而修改服務(wù)器屬性需要使用系統(tǒng)過程sp_serveroption。該過程的語法如代碼16.7所示。16.7sp_serveroptionsp_serveroptionsp_serveroption[@server=],[@optname=],[@optvalue=]'option_value'其中的參數(shù)[@server=]'server'是要為其設(shè)置選項(xiàng)的服務(wù)器的名稱。 的數(shù)據(jù)類型sysname，沒有默認(rèn)值。[@optname=]'option_name為指定的服務(wù)器設(shè)置的選項(xiàng)。option_value可以是用于connecttimeout和querytimeoutcollationname選項(xiàng)，option_value可以是排序規(guī)則名稱或NULL。代碼代碼16.8修 過程調(diào)sp_dropserversp_dropserver[@server=][,[@droplogins=]{'droplogins'|NULL}其中的參數(shù)[@server=]'server'是要刪除的服務(wù)器。serversysname，無默認(rèn)值。server必須存在。[@droplogins=]'droplogins'|NULL為如果指定了droplogins，那么server，還必須刪除相關(guān)的服務(wù)器和服務(wù)器登錄名。@droplogins的數(shù)據(jù)類型為char(10)，默認(rèn)值為NULL。例如要刪除前面創(chuàng)建的服務(wù)器，對應(yīng)的如代碼16.9所示。代碼代碼16.9刪 EXECmaster.dbo.sp_dropserver@server=N'192.168.100.100',--刪除指定 關(guān)于使用SSMS修改和刪除服務(wù)器的操作與修改和刪除其他數(shù)據(jù)庫對象的操作并使用服務(wù)以前面創(chuàng)建的到Access數(shù)據(jù)庫的服務(wù)器ACC為例。現(xiàn)在Access數(shù)據(jù)庫中創(chuàng)建一個(gè)表Tb1和添加一些數(shù)據(jù)，然后在SSMS的對象資源管理器中可以展開ACC服務(wù)器節(jié)點(diǎn)，直到看見其中的表Tb1，如圖16.5所示。圖16.5查看服務(wù)器中的到”|“新查詢編輯器窗口”命令，系統(tǒng)將為該表的查詢編寫如代碼16.10所示。SELECTSELECT FROM--ACC 服務(wù)器代碼16.10查 [服務(wù)器名].[數(shù)據(jù)庫名].[架構(gòu)名].[對象名 代碼代碼16.11 INSERTINTO[ACC]...[Tb1]--插入數(shù)據(jù) 服務(wù) VALUES 同義 為可以存在于本地或服務(wù)器上的其他數(shù)據(jù)庫對象（稱為基對象）提供備用名稱。列數(shù)據(jù)庫對象創(chuàng)建同義詞：CLR過程、CLR表值函數(shù)、CLR標(biāo)量函數(shù)、CLR聚合函數(shù)、篩選過程、擴(kuò)展過程、SQL標(biāo)量函數(shù)、SQL表值函數(shù)、SQL內(nèi)聯(lián)表值函數(shù)、SQL過程、視圖、表。象來替換該基對象。例如，有一個(gè)AdventureWorks中的.Contact表的同義詞MyContacts。如果將Contact表刪除，并用名為.Contact的視圖替換該表，則MyContacts將.Contact視圖。16.12CREATECREATESYNONYM[schema_name_1.]synonym_nameFOR<object<object>::{[server_name.[database_name].[schema_name_2].|database_name.[schema_name_2].|schema_name_2.]object_name}synonym_name：新同義詞的名稱 的如代碼16.13所示16.13CREATESYNONYMAddressType--創(chuàng)建同義詞象創(chuàng)建同義詞。例如在tempdb中為AdventureWorks數(shù)據(jù)庫的.Address表創(chuàng)建同義詞的如代碼16.14所示。16.14CREATESYNONYMFOR 接服務(wù)器中的數(shù)據(jù)，則對應(yīng)的如代碼16.15所示。SELECTFROM代碼16.15SELECTFROM同義詞，并調(diào)用該過程的如代碼16.16所示。代碼代碼16.16通過同義詞調(diào) CREATESYNONYMGetDeparment-- FOR[192.168.100.100].[AdventureWorks].[dbo].[GetDeparment]EXECGetDeparment注意：如果服務(wù)器沒有打 RPC，則無法通過服務(wù)器執(zhí)行過程DROPSYNONYM[schema.] 16.17DROPSYNONYM面介紹的章節(jié)中筆者介紹了OPENROWSET()函數(shù)可以用于將服務(wù)器中來說，查詢并不都運(yùn)行在Oracle服務(wù)器上。例如現(xiàn)有一個(gè)Oracle服務(wù)器ORA，要查本如代碼16.18所示。16.18OracleSELECTSELECTFROMWHERE在SQLServer中運(yùn)行該可能要等上10秒、20秒甚至1分鐘、5分鐘才可能查詢出結(jié)果。但是如果將在Oracle服務(wù)器上直接運(yùn)行，則1秒鐘不到就查詢出結(jié)果了。造成這種情況的是SQLServer查詢服務(wù)器的機(jī)制。不同的數(shù)據(jù)庫對應(yīng)的SQL語言有所不同。而對于Oracle數(shù)據(jù)庫，通過服務(wù)器查T-SQLSQLServerOracleOracle數(shù)據(jù)庫16.18中的查詢，SQLServerOracle數(shù)SQLServerOPENQUERY()函數(shù)用于將查詢語句直接送到取到本地來查詢。OPENQUERY()函數(shù)的語法格式為OPENQUERY(linked_server,'query' 詢字符串，該字符串的最大長度為8KB對于代碼16.18中的查詢OPENQUERY()函數(shù)的如代碼16.19所示。代碼代碼16.19使用OPENQUERY()函數(shù)查 SELECT 使用OPENQUERY()函數(shù)后，Oracle數(shù)據(jù)庫將負(fù)責(zé)查詢，只將查詢的結(jié)果返回給SQL前面已經(jīng)說到可以使用sp_addlinkedsrvlogin添加服務(wù)器的用戶映射，使用立登錄名和。在SQLServer連接到服務(wù)器以執(zhí)行分布式查詢或過程時(shí)，SQLServer將查找正在執(zhí)行查詢或過程的當(dāng)前登錄名的登錄名映射。如果有一個(gè)登錄名映射，則SQLServer在連接到服務(wù)器時(shí)將發(fā)送相應(yīng)的登錄名和。例如，使用p@ssw0rd為服務(wù)器S1建立了一個(gè)從本地登錄名U1到登錄名U2的映射。本地登錄名U1執(zhí)行服務(wù)器S1中的表的分布式查詢過程中，當(dāng)SQLServer連接到服務(wù)器S1時(shí)，將U2和p@ssw0rd作為用戶ID和進(jìn)行傳遞。安全賬戶委托可用，且服務(wù)器支持Windows驗(yàn)證，則支持經(jīng)過Windows驗(yàn)注意：請盡可能使用Windows驗(yàn)證須建立一個(gè)從使用Windows驗(yàn)證的登錄名到服務(wù)器上不使用Windows驗(yàn)證登錄名使用SQLServer驗(yàn)證服務(wù)（WindowsActiveDirectory，活動，簡稱AD）是微軟為企業(yè)環(huán)境設(shè)計(jì)的統(tǒng)一用戶權(quán)限的平臺。微軟的大部分企業(yè)級產(chǎn)品比如Excange、Biztalk、SQLServer、MOSS等都可以集成到AD中。通過SQLServer的服務(wù)器可以實(shí)現(xiàn)對服

服務(wù)的OLEDB接口可 Windows2000錄服務(wù)中的信息。使用此接口的查詢可返回對象的最大數(shù)量為1000。例如創(chuàng)建 務(wù)的服務(wù)器的如代碼16.20所示。EXECEXECsp_addlinkedserver'ADSI','ActiveDirectoryServices'ADSDSOObject''adsdatasource代碼16.20創(chuàng) 對于Windows錄，只需自映射就足以通過使用SQLServer安全委托來目用于服務(wù)的OLEDB接口支持兩種命令方言（LDAP和SQL）查詢服務(wù)。可以使用OPENQUERY()函數(shù)將命令發(fā)送到服務(wù)SELECT語例如創(chuàng)建一個(gè)視圖，它使用OPENQUERY()返回域地址為sales.a 務(wù)器ADSISrv上 該查詢返回對象的Name、SN和ST屬性，這些對象屬于 代碼16.21所示。代碼16.21查 CREATECREATEVIEWviewADContactsSELECT[Name],SN[L