SQL Server跨服務器操作數據庫的圖文方法(LinkedServer)

Posted on by

基礎知識介紹

以SQL Server的數據庫管理工具SSMS(SQL Server Management Studio)為平臺進行操作。

SQL Server Management Studio (SSMS) 是用於管理SQL Server 基礎結構的集成環境。 使用 SSMS，可以訪問、配置、管理和開發 SQL Server、Azure SQL 數據庫和 SQL 數據倉庫的所有組件。 SSMS 在一個綜合實用工具中匯集瞭大量圖形工具和豐富的腳本編輯器，為各種技能水平的開發者和數據庫管理員提供對 SQL Server 的訪問權限。

 

什麼是跨服務器操作？

跨服務器操作就是可以在本地連接到遠程服務器上的數據庫，可以在對方的數據庫上進行相關的數據庫操作，比如增刪改查。

為什麼要進行跨服務器操作

隨著數據量的增多，業務量的擴張，需要在不同的服務器安裝不同的數據庫，有時候因為業務需要，將不同的服務器中的數據進行整合，這時候就需要進行跨服務器操作瞭。

跨服務器操作的工具是什麼？

DBLINK(數據庫鏈接)，顧名思義就是數據庫的鏈接，就像電話線一樣，是一個通道，當我們要跨本地數據庫，訪問另外一個數據庫表中的數據時，本地數據庫中就必須要創建遠程數據庫的dblink,通過dblink本地數據庫可以像訪問本地數據庫一樣訪問遠程數據庫表中的數據。

方法一：用SSMS創建SQL Server遠程鏈接服務器(LinkedServer)–簡單鏈接到遠程SqlServer

1. 打開SSMS –>登錄到本地數據庫 –> 服務器對象 –> 鏈接服務器(右鍵) –> 新建鏈接服務器，如下圖：

 

2. 在彈出的對話框中輸入相關信息

● 在【鏈接服務器】輸入對方服務器的IP地址；

● 在【服務器類型】中選擇【SQL Server】；

 

3. 點擊左側的【安全性】，出現如下頁面，在第3步中輸入對方數據庫的賬號密碼即可。

 

點擊確定按鈕後，鏈接服務器(LinkedServer)就創建成功瞭。這時可以看到創建好的鏈接服務器：

 

查看鏈接服務器的代碼： 在創建好的鏈接服務器上點右鍵，編寫鏈接服務器腳本為 –> Create到 –>新查詢編輯器窗口，即可打開剛剛創建的鏈接服務器的腳本。

 

–鏈接服務器(LinkedServer)創建完成後會自動生成相關代碼 —— 鏈接到遠程SQLServer數據庫：

EXEC master.dbo.sp_addlinkedserver @server = N'192.168.110.189,1433',@srvproduct=N'SQL Server';
-- @rmtsrvname
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'192.168.110.189',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='########';

註意： 這裡有一個弊端，那就是鏈接的是整個遠程SqlServer中的所有數據庫(一般隻需要一個特定的數據庫)，而且鏈接服務器的名稱是個IP且無法自定義！ 所以，最好的方式還是通過代碼直接創建鏈接數據庫(見“三、代碼詳解”)。

鏈接服務器(LinkedServer)就創建成功後，我們就可以用創建好的DBLINK鏈接到遠程的Linked服務器瞭。下面我們用創建好的試著查詢對方服務器上的表來驗證一下。

–查詢鏈接服務器(LinkedServer)中數據的方法： [DBLINK名].[對方數據庫名].[對方數據庫下模式名].[對方數據庫表名]

SELECT * FROM [192.168.110.189].[erp25new].[dbo].[fee_data]

上面FROM字段後面依此是[DBLINK名].[對方數據庫名].[對方數據庫下模式名].[對方數據庫表名]，表名前面的這些內容一個都不能少。

查詢結果如下圖：

 

方法二：SSMS創建SQLServer鏈接服務器(LinkedServer)–自定義鏈接到SqlServer的其它數據庫

1. 【常規】選擇頁：

 

2.【安全性】選擇頁：

 

自定義鏈接數據庫到SQLServer【新建鏈接服務器】對話框中需輸入的相關信息說明：

1.【常規】頁

● 在【鏈接服務器】中，輸入 自定義的鏈接服務器別名，如：DBLINK_TO_TESTDB

● 在【服務器類型】中選擇【其他數據源】；

▶[提供程序]中選擇 第一個Microsoft OLE DB Provider for SQL Server

▶[產品名稱]中，可以空白不填，也可以填寫SQL Server { 註意提供程序是OLE DB Provider for SQL Server時產品名稱這裡必須為空白！}

▶[數據源]中 遠程數據庫的地址,端口\實例名 ，如 10.10.0.73,1433\MSSQLSERVER

▶[訪問接口字符串]中，可以空著不填； 也可以填下方的：(註意######是密碼,請換成自己的密碼)

Provider=sqloledb;Data Source=10.10.0.73,1433\MSSQLSERVER;Initial Catalog=TESTDB;User Id=apps;Password=#####;

▶[目錄]就是數據庫名稱，這裡填上我們需要遠程連上的數據庫 TESTDB (可以換成自己實際的)。

2.【安全性】頁

● 選擇【使用此安全上下文建立連接(M)】

▶[遠程登錄]： 遠程數據庫的連接賬號

▶[使用密碼]： 遠程數據庫連接賬號的密碼

--鏈接服務器(LinkedServer)創建完成後會自動生成相關代碼 —— 鏈接到遠程的SQLServer數據庫(自定義)：

EXEC master.dbo.sp_addlinkedserver @server = N'DBLINK_TO_TESTDB',@srvproduct=N'',@provider=N'SQLNCLI', @datasrc=N'10.10.0.73';EXEC master.dbo.sp_addlinkedsrvlogin@rmtsrvname=N'DBLINK_TO_TESTDB',@useself=N'False',@locallogin=NULL,@rmtuser=N'apps',@rmtpassword='########';

/****** 實際例子 系統生成的Object: LinkedServer [DBLINK_TO_TESTDB] ******/

USE [master]

GO

EXEC master.dbo.sp_addlinkedserver @server = N'DBLINK_TO_TESTDB', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'10.10.0.73,1433\MSSQLSERVER', @catalog=N'TESTDB'

/*For security reasons the linked server remote logins password is changed with ########*/

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TEST',@useself=N'False',@locallogin=NULL,@rmtuser=N'apps',@rmtpassword='########'

其他方式： 提供程序換成其它的， 如本機SQL Server Native Client 11.0 (SQL Server Native Client 11.0 不支持連接到SQL Server 2000或更早的版本) 等

 

方法三：用SSMS創建SQLServer鏈接服務器(LinkedServer)–鏈接到非SqlServer的其它數據庫

 

四、代碼詳解：方法一和方法二是通過SSMS直接操作的,下方直接使用sql腳本來創建鏈接服務器(LinkedServer)

A. SSMS鏈接到遠程SQLServer數據庫

(本地SQLServer數據庫鏈接服務器(LinkedServer)到遠程SQLServer數據庫。)

–LinkedServer鏈接到遠程SQLServer數據庫：

–1. 聲明將要鏈接的‘鏈接名稱(自定義)’,遠程數據庫產品名(或別名),(提供商,數據庫服務器地址及實例名)

EXEC master.dbo.sp_addlinkedserver @server = N'DBLINK_TO_TESTDB',@srvproduct=N'SQL Server';

–2. 聲明‘鏈接名稱(自定義)’,@useself=N'False',@locallogin=NULL,將要鏈接的數據庫服務器的賬號和密碼

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'192.168.110.189',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='########';

B. SSMS鏈接到遠程非SQLServer數據庫

(本地SQLServer數據庫鏈接服務器(LinkedServer)到遠程非SQLServer的數據庫。如遠程的MySQL、Oracle等數據庫。)

–鏈接到遠程的非SQLServerd數據庫(如鏈接到遠程MySQL、Oracle等數據庫)：

–1. 聲明‘自定義的鏈接名稱’,遠程數據庫產品名(或別名),提供商,數據庫服務器地址及實例名

EXEC master.dbo.sp_addlinkedserver @server = N'TEST_SQL_SERVER',@srvproduct=N'TEST',@provider=N'SQLNCLI11', @datasrc=N'192.168.110.189';-

-2. 聲明登錄信息 ‘自定義的鏈接名稱’,@useself=N'False',@locallogin=NULL,遠程數據庫的賬號和密碼

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TEST_SQL_SERVER',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='########';

實際例子-SQL Server通過Linkserver連接MySql

--通過SSMS鏈接到遠程MySql數據庫(SQL Server連接MySql)--使用的訪問接口為：MySql Provider for OLE DB--

EXEC master.dbo.sp_addlinkedserver @server = N'DBLINK_TO_MysqlTESTDB', @srvproduct = N'MySql', @provider = N'MSDASQL', @provstr = N'Driver={MySQL ODBC 5.1 Driver};Server=10.167.69.6,3306/sytv;Database=TESTDB;User=root;Password=root;Option=3';--

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'DBLINK_TO_MysqlTESTDB', @useself = N'False', @locallogin = N'10.167.69.6,3306/sytv', @rmtuser = N'root', @rmtpassword = N'root';

實際例子-SQL Server通過Linkserver連接Oracle

--通過SSMS鏈接到遠程Oracle數據庫(SQL Server連接Oracle)

--使用的訪問接口為：Oracle Provider for OLE DB

USE [master]

GO

--Declare Oracle OLEDB 'OraOLEDB.Oracle':

EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1;--Create the Linked Server to the ECT database in Oracle:

EXEC sp_addlinkedserver 'DBLINK_TO_OraTESTDB', 'Oracle', 'OraOLEDB.Oracle', '10.167.69.6/prt';--EXEC master.dbo.sp_addlinkedserver @server = N'DBLINK_TO_OraTESTDB', @srvproduct=N'oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'10.167.69.6/orcl'

--Create the Remote Login for the Oracle Linked Server:

EXEC sp_addlinkedsrvlogin @rmtsrvname=N'DBLINK_TO_OraTESTDB',@useself=N'False',@locallogin=N'apps',@rmtuser=N'SYSTEM',@rmtpassword='######'; 

--最後可以測試一下是否連接成功 --select * from openquery(DBLINK_TO_OraTESTDB,'select * from SYSTEM.HELP');

到此這篇關於SQL Server跨服務器操作數據庫的圖文方法(LinkedServer)的文章就介紹到這瞭,更多相關SQL Server跨服務器操作數據庫內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：