遠程連結的伺服器詳細配置

遠程連結的伺服器詳細配置
--建立串連伺服器
EXEC sp_addlinkedserver  '遠程伺服器IP','SQL Server'
--標註儲存
EXEC sp_addlinkedserver
@server = 'server', --連結的伺服器的本地名稱。也允許使用執行個體名稱，例如MYSERVER/SQL1
@srvproduct = 'product_name' --OLE DB資料來源的產品名。對於SQL Server執行個體來說，product_name是'SQL Server'
, @provider = '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
, @datasrc = 'data_source' --這是特定OLE DB提供者解釋的資料來源。對於SQL Server，這是 SQL Server（servername或servername/instancename）的網路名稱。對於Oracle，這是SQL*Net別名。對於 MS Access和MSExcel，這是檔案的完整路徑和名稱。對於ODBC資料來源，這是系統DSN名稱
, @location = 'location' --由特定OLE DB提供者解釋的位置
, @provstr = 'provider_string' --OLE DB 提供者特定的連接字串。對於ODBC串連，這是ODBC連接字串。對於MS Excel，這是Excel 5.0
, @catalog = 'catalog' --catalog的定義變化基於OLE DB提供者的實現。對於SQL Server，這是可選的資料庫名稱，對於DB2，這個目錄是資料庫的名稱

--建立連結的伺服器上遠程登入之間的映射
EXEC sp_addlinkedsrvlogin '遠程伺服器IP','false','sa','架構名','訪問密碼'
--標註儲存
EXEC sp_addlinkedsrvlogin
@rmtsrvname = '遠程伺服器IP', --要添加登入名稱映射的本地連結的伺服器
@useself = false, --當使用true值時，使用本地SQL或Windows登入名稱串連到遠程伺服器名。如果設為false，預存程序 sp_addlinkedsrvlogin的locallogin、rmtuser和rmtpassword參數將應用到新的映射中
@locallogin = NULL, --這是映射到遠程登入名稱的SQL Server登入或Windows使用者的名稱。如果這個參數置為NULL，映射將應用SQL Server執行個體中的所有本地登入名稱
@rmtuser = '架構名', --用來串連到連結的伺服器的使用者／登入名稱的名稱
@rmtpassword = '訪問密碼' --用來串連到連結的伺服器的使用者／登入名稱的密碼

--查詢資料
select top 10 * from [222].pwms.pwms.bas_tb_sku

--查看連結的伺服器資訊
select name,product,provider,data_source,query_timeout,lazy_schema_validation,is_remote_login_enabled,is_rpc_out_enabled
from sys.servers
where is_linked=1

--配置連結的伺服器屬性
exec sp_serveroption '別名','name','遠程伺服器IP'
--標註儲存
EXEC sp_serveroption
@server = 'server' --配置屬性的連結的伺服器的名稱
,@optname = 'option_name' --要配置的選項
,@optvalue = 'option_value' --選項的新值
--附參數
參    數 描    述
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）

--刪除連結的伺服器屬性
exec sp_dropserver '4'
--標註儲存
EXEC sp_dropserver
@server = 'server' --從SQL Server執行個體中刪除的連結的伺服器的名稱
,@droplogins = 'droplogins' --如果指定droplogins，則在刪除連結的伺服器之前要刪除登入名稱映射

--查看連結登入名稱
select s.name linkedServerName,s.data_source linkedserver_source,s.is_linked,s.modify_date,
ll.remote_name,ll.local_principal_id,ll.uses_self_credential,
p.name localLoginName
from sys.Linked_logins ll
inner join sys.servers s on s.server_id=ll.server_id
left join sys.server_principals p on p.principal_id=ll.local_principal_id
where s.is_linked = 1

--刪除連結的伺服器登入名稱映射
exec sp_droplinkedsrvlogin '遠程伺服器IP',NULL
--標註儲存
EXEC sp_droplinkedsrvlogin
@rmtsrvname = '遠程伺服器IP' --登入名稱映射的連結的伺服器名稱
@locallogin = NULL --這是從連結的伺服器刪除的SQL Server登入或Windows使用者映射的名稱