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

本篇文章給大家帶來了關于SQL的相關知識，其中主要介紹了SQL Server跨服務器操作數據庫的圖文方法，SQL Server Management Studio (SSMS) 是用于管理SQL Server 基礎結構的集成環境，下面一起來看一下，希望對大家有幫助。

Linux系統運維及項目正式上線：進入學習

推薦學習：《SQL教程》

基礎知識介紹

以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,1433MSSQLSERVER

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

Provider=sqloledb;Data Source=10.10.0.73,1433MSSQLSERVER;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,1433MSSQLSERVER', @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教程》