SQLServer之建立連結伺服器

小子pk了君發表於2019-03-28
SQLServer伺服器

建立連結伺服器注意事項

當我們要跨本地資料庫，訪問另外一個資料庫表中的資料時，本地資料庫中就必須要建立遠端資料庫的DBLINK，通過DBLINNK資料庫可以像訪問本地資料庫一樣訪問遠端資料庫表中的資料。

連結伺服器允許訪問針對OLE DB資料來源的分散式異構查詢。建立連結伺服器後，可以針對此伺服器執行分散式查詢，並且查詢可以連線來自多個資料來源的表。如果連結伺服器被定義為SQL Server的例項，則可以執行遠端儲存過程。

連結伺服器的功能和必需引數可能會有很大差異。

使用SSMS資料庫管理工具建立DBLINK

1、連線伺服器-》展開伺服器-》展開伺服器物件-》展開連結伺服器-》右鍵點選連結伺服器-》點選新建連結伺服器。

SQLServer之建立連結伺服器

2、在新建連結伺服器彈出框-》點選常規-》輸入連結伺服器名稱-》選擇伺服器型別。

SQLServer之建立連結伺服器

3、在新建連結伺服器視窗-》點選安全性-》選擇連結伺服器的登陸型別-》新增或者刪除登陸遠端伺服器的對映。

SQLServer之建立連結伺服器

4、在新建連線伺服器彈出框-》點選伺服器選項-》選擇伺服器選項的屬性。

SQLServer之建立連結伺服器

5、在新建連結伺服器彈窗框-》點選確定-》在物件資源管理器檢視結果。

SQLServer之建立連結伺服器

使用SSMS資料庫管理工具建立DBLINK

語法

--宣告資料庫引用
use master;
go
 
--建立DbLink語法
--第一步：定義DBLINK型別
exec master.dbo.sp_addlinkedserver @server='連結伺服器名稱',@srvproduct='SQL Server';
go

--第二步：定義DBLINK連線屬性
--第一種安全性：不建立連線（刪除下邊的登陸）
--第二種安全性：不使用安全上下文建立連線
--exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'連結伺服器名稱', @locallogin = NULL , @useself = N'False'
--go
--第三種安全性：使用登入名的當前安全上下文建立連線
--exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'連結伺服器名稱', @locallogin = NULL , @useself = N'True'
--go
--第四種安全性：使用此安全上下文建立連線
--exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname='連結伺服器名稱',@locallogin=NULL,@useself='False',@rmtuser='登入名',@rmtpassword='密碼';
--go

--排序規則相容
exec master.dbo.sp_serveroption @server=N'連結伺服器名稱', @optname=N'collation compatible', @optvalue=N'true' | N'false'
go
--資料訪問
exec master.dbo.sp_serveroption @server=N'連結伺服器名稱', @optname=N'data access', @optvalue=N'true' | N'false'
go
--訂閱伺服器
exec master.dbo.sp_serveroption @server=N'連結伺服器名稱', @optname=N'dist', @optvalue=N'true' | N'false'
go
--釋出伺服器
exec master.dbo.sp_serveroption @server=N'連結伺服器名稱', @optname=N'pub', @optvalue=N'true' | N'false'
go
--RPC
exec master.dbo.sp_serveroption @server=N'連結伺服器名稱', @optname=N'rpc', @optvalue=N'true' | N'false'
go
--RPC 超時
exec master.dbo.sp_serveroption @server=N'連結伺服器名稱', @optname=N'rpc out', @optvalue=N'true' | N'false'
go
--分發伺服器
exec master.dbo.sp_serveroption @server=N'連結伺服器名稱', @optname=N'sub', @optvalue=N'true' | N'false'
go
--連線超時值
exec master.dbo.sp_serveroption @server=N'連結伺服器名稱', @optname=N'connect timeout', @optvalue=N'0'
go
--排序規則名稱
exec master.dbo.sp_serveroption @server=N'連結伺服器名稱', @optname=N'collation name', @optvalue=null
go
--惰性架構驗證
exec master.dbo.sp_serveroption @server=N'連結伺服器名稱', @optname=N'lazy schema validation', @optvalue=N'true' | N'false'
go
--查詢超時值
exec master.dbo.sp_serveroption @server=N'連結伺服器名稱', @optname=N'query timeout', @optvalue=N'0'
go
--使用遠端排序規則
exec master.dbo.sp_serveroption @server=N'連結伺服器名稱', @optname=N'use remote collation', @optvalue=N'true' | N'false'
go
--為RPC啟用針對分散式事務的升級
exec master.dbo.sp_serveroption @server=N'連結伺服器名稱', @optname=N'remote proc transaction promotion', @optvalue=N'true' | N'false'
go
複製程式碼

語法解析

第一步和第二步必須同時執行，後面DBLINK屬性可以不寫使用系統預設。

示例：以我自己本機為例

--宣告資料庫引用
use master;
go
 
--建立DbLink語法
--第一步：定義DBLINK型別
exec master.dbo.sp_addlinkedserver @server='TANG\SQLEXPRESS',@srvproduct='SQL Server';
go
 
----排序規則相容
--exec master.dbo.sp_serveroption @server=N'TEST', @optname=N'collation compatible', @optvalue=N'false'
--go
----資料訪問
--exec master.dbo.sp_serveroption @server=N'TEST', @optname=N'data access', @optvalue=N'true'
--go
----訂閱伺服器
--exec master.dbo.sp_serveroption @server=N'TEST', @optname=N'dist', @optvalue=N'false'
--go
----釋出伺服器
--exec master.dbo.sp_serveroption @server=N'TEST', @optname=N'pub', @optvalue=N'false'
--go
----RPC
--exec master.dbo.sp_serveroption @server=N'TEST', @optname=N'rpc', @optvalue=N'false'
--go
----RPC 超時
--exec master.dbo.sp_serveroption @server=N'TEST', @optname=N'rpc out', @optvalue=N'false'
--go
----分發伺服器
--exec master.dbo.sp_serveroption @server=N'TEST', @optname=N'sub', @optvalue=N'false'
--go
----連線超時值
--exec master.dbo.sp_serveroption @server=N'TEST', @optname=N'connect timeout', @optvalue=N'0'
--go
----排序規則名稱
--exec master.dbo.sp_serveroption @server=N'TEST', @optname=N'collation name', @optvalue=null
--go
----惰性架構驗證
--exec master.dbo.sp_serveroption @server=N'TEST', @optname=N'lazy schema validation', @optvalue=N'false'
--go
----查詢超時值
--exec master.dbo.sp_serveroption @server=N'TEST', @optname=N'query timeout', @optvalue=N'0'
--go
----使用遠端排序規則
--exec master.dbo.sp_serveroption @server=N'TEST', @optname=N'use remote collation', @optvalue=N'true'
--go
----為RPC啟用針對分散式事務的升級
--exec master.dbo.sp_serveroption @server=N'TEST', @optname=N'remote proc transaction promotion', @optvalue=N'true'
--go
 
--第二步：定義DBLINK連線屬性
--第一種安全性：不建立連線（刪除下邊的登陸）
--第二種安全性：不使用安全上下文建立連線
--exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'TANG\SQLEXPRESS', @locallogin = NULL , @useself = N'False'
--go
--第三種安全性：使用登入名的當前安全上下文建立連線
exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'TANG\SQLEXPRESS', @locallogin = NULL , @useself = N'True'
go
--第四種安全性：使用此安全上下文建立連線
--exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname='TANG\SQLEXPRESS',@locallogin=NULL,@useself='False',@rmtuser='tests',@rmtpassword='1234';
--go
複製程式碼

示例結果：顯示建立結果

SQLServer之建立連結伺服器

DBLINK使用

示例

SELECT * FROM   [testss].[dbo].[test1]     AS A
INNER JOIN  [TANG\SQLEXPRESS].[testss].[dbo].[test3] AS B ON A.classid=B.id
複製程式碼

結果

SQLServer之建立連結伺服器

DBLINK連結優缺點

優點

1、允許跨伺服器訪問。

2、資料量少的情況下用dblink比較簡單，迅速。

3、可以執行遠端儲存過程等。

缺點

1、遠端查詢時易受網路等影響。

2、連結穩定性較差。

3、大量消耗資料庫資源。

4、可擴充套件性較差。

5、維護性差、安全性較低。

相關文章