基礎(chǔ)知識(shí)介紹

以SQL Server的數(shù)據(jù)庫(kù)管理工具SSMS(SQL Server Management Studio)為平臺(tái)進(jìn)行操作。

SQL Server Management Studio (SSMS) 是用于管理SQL Server 基礎(chǔ)結(jié)構(gòu)的集成環(huán)境。 使用 SSMS，可以訪問(wèn)、配置、管理和開發(fā) SQL Server、Azure SQL 數(shù)據(jù)庫(kù)和 SQL 數(shù)據(jù)倉(cāng)庫(kù)的所有組件。 SSMS 在一個(gè)綜合實(shí)用工具中匯集了大量圖形工具和豐富的腳本編輯器，為各種技能水平的開發(fā)者和數(shù)據(jù)庫(kù)管理員提供對(duì) SQL Server 的訪問(wèn)權(quán)限。

什么是跨服務(wù)器操作？

跨服務(wù)器操作就是可以在本地連接到遠(yuǎn)程服務(wù)器上的數(shù)據(jù)庫(kù)，可以在對(duì)方的數(shù)據(jù)庫(kù)上進(jìn)行相關(guān)的數(shù)據(jù)庫(kù)操作，比如增刪改查。

為什么要進(jìn)行跨服務(wù)器操作

隨著數(shù)據(jù)量的增多，業(yè)務(wù)量的擴(kuò)張，需要在不同的服務(wù)器安裝不同的數(shù)據(jù)庫(kù)，有時(shí)候因?yàn)闃I(yè)務(wù)需要，將不同的服務(wù)器中的數(shù)據(jù)進(jìn)行整合，這時(shí)候就需要進(jìn)行跨服務(wù)器操作了。

跨服務(wù)器操作的工具是什么？

DBLINK(數(shù)據(jù)庫(kù)鏈接)，顧名思義就是數(shù)據(jù)庫(kù)的鏈接，就像電話線一樣，是一個(gè)通道，當(dāng)我們要跨本地?cái)?shù)據(jù)庫(kù)，訪問(wèn)另外一個(gè)數(shù)據(jù)庫(kù)表中的數(shù)據(jù)時(shí)，本地?cái)?shù)據(jù)庫(kù)中就必須要?jiǎng)?chuàng)建遠(yuǎn)程數(shù)據(jù)庫(kù)的dblink,通過(guò)dblink本地?cái)?shù)據(jù)庫(kù)可以像訪問(wèn)本地?cái)?shù)據(jù)庫(kù)一樣訪問(wèn)遠(yuǎn)程數(shù)據(jù)庫(kù)表中的數(shù)據(jù)。

方法一：用SSMS創(chuàng)建SQL Server遠(yuǎn)程鏈接服務(wù)器(LinkedServer)--簡(jiǎn)單鏈接到遠(yuǎn)程SqlServer

1. 打開SSMS -->登錄到本地?cái)?shù)據(jù)庫(kù) --> 服務(wù)器對(duì)象 --> 鏈接服務(wù)器(右鍵) --> 新建鏈接服務(wù)器，如下圖：

2. 在彈出的對(duì)話框中輸入相關(guān)信息

● 在【鏈接服務(wù)器】輸入對(duì)方服務(wù)器的IP地址；

● 在【服務(wù)器類型】中選擇【SQL Server】；

3. 點(diǎn)擊左側(cè)的【安全性】，出現(xiàn)如下頁(yè)面，在第3步中輸入對(duì)方數(shù)據(jù)庫(kù)的賬號(hào)密碼即可。

點(diǎn)擊確定按鈕后，鏈接服務(wù)器(LinkedServer)就創(chuàng)建成功了。這時(shí)可以看到創(chuàng)建好的鏈接服務(wù)器：

查看鏈接服務(wù)器的代碼： 在創(chuàng)建好的鏈接服務(wù)器上點(diǎn)右鍵，編寫鏈接服務(wù)器腳本為 --> Create到 -->新查詢編輯器窗口，即可打開剛剛創(chuàng)建的鏈接服務(wù)器的腳本。

--鏈接服務(wù)器(LinkedServer)創(chuàng)建完成后會(huì)自動(dòng)生成相關(guān)代碼 —— 鏈接到遠(yuǎn)程SQLServer數(shù)據(jù)庫(kù)：

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='########';

注意： 這里有一個(gè)弊端，那就是鏈接的是整個(gè)遠(yuǎn)程SqlServer中的所有數(shù)據(jù)庫(kù)(一般只需要一個(gè)特定的數(shù)據(jù)庫(kù))，而且鏈接服務(wù)器的名稱是個(gè)IP且無(wú)法自定義！ 所以，最好的方式還是通過(guò)代碼直接創(chuàng)建鏈接數(shù)據(jù)庫(kù)(見(jiàn)“三、代碼詳解”)。

鏈接服務(wù)器(LinkedServer)就創(chuàng)建成功后，我們就可以用創(chuàng)建好的DBLINK鏈接到遠(yuǎn)程的Linked服務(wù)器了。下面我們用創(chuàng)建好的試著查詢對(duì)方服務(wù)器上的表來(lái)驗(yàn)證一下。

--查詢鏈接服務(wù)器(LinkedServer)中數(shù)據(jù)的方法： [DBLINK名].[對(duì)方數(shù)據(jù)庫(kù)名].[對(duì)方數(shù)據(jù)庫(kù)下模式名].[對(duì)方數(shù)據(jù)庫(kù)表名]

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

上面FROM字段后面依此是[DBLINK名].[對(duì)方數(shù)據(jù)庫(kù)名].[對(duì)方數(shù)據(jù)庫(kù)下模式名].[對(duì)方數(shù)據(jù)庫(kù)表名]，表名前面的這些內(nèi)容一個(gè)都不能少。

查詢結(jié)果如下圖：

方法二：SSMS創(chuàng)建SQLServer鏈接服務(wù)器(LinkedServer)--自定義鏈接到SqlServer的其它數(shù)據(jù)庫(kù)

1. 【常規(guī)】選擇頁(yè)：

2.【安全性】選擇頁(yè)：

自定義鏈接數(shù)據(jù)庫(kù)到SQLServer【新建鏈接服務(wù)器】對(duì)話框中需輸入的相關(guān)信息說(shuō)明：

1.【常規(guī)】頁(yè)

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

● 在【服務(wù)器類型】中選擇【其他數(shù)據(jù)源】；

?[提供程序]中選擇 第一個(gè)Microsoft OLE DB Provider for SQL Server

?[產(chǎn)品名稱]中，可以空白不填，也可以填寫SQL Server { 注意提供程序是OLE DB Provider for SQL Server時(shí)產(chǎn)品名稱這里必須為空白！}

?[數(shù)據(jù)源]中 遠(yuǎn)程數(shù)據(jù)庫(kù)的地址,端口\實(shí)例名 ，如 10.10.0.73,1433\MSSQLSERVER

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

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

?[目錄](méi)就是數(shù)據(jù)庫(kù)名稱，這里填上我們需要遠(yuǎn)程連上的數(shù)據(jù)庫(kù) TESTDB (可以換成自己實(shí)際的)。

2.【安全性】頁(yè)

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

?[遠(yuǎn)程登錄](méi)： 遠(yuǎn)程數(shù)據(jù)庫(kù)的連接賬號(hào)

?[使用密碼]： 遠(yuǎn)程數(shù)據(jù)庫(kù)連接賬號(hào)的密碼

--鏈接服務(wù)器(LinkedServer)創(chuàng)建完成后會(huì)自動(dòng)生成相關(guān)代碼 —— 鏈接到遠(yuǎn)程的SQLServer數(shù)據(jù)庫(kù)(自定義)：

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='########';

/****** 實(shí)際例子 系統(tǒng)生成的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='########'

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

方法三：用SSMS創(chuàng)建SQLServer鏈接服務(wù)器(LinkedServer)--鏈接到非SqlServer的其它數(shù)據(jù)庫(kù)

四、代碼詳解：方法一和方法二是通過(guò)SSMS直接操作的,下方直接使用sql腳本來(lái)創(chuàng)建鏈接服務(wù)器(LinkedServer)

A. SSMS鏈接到遠(yuǎn)程SQLServer數(shù)據(jù)庫(kù)

(本地SQLServer數(shù)據(jù)庫(kù)鏈接服務(wù)器(LinkedServer)到遠(yuǎn)程SQLServer數(shù)據(jù)庫(kù)。)

--LinkedServer鏈接到遠(yuǎn)程SQLServer數(shù)據(jù)庫(kù)：

--1. 聲明將要鏈接的‘鏈接名稱(自定義)’,遠(yuǎn)程數(shù)據(jù)庫(kù)產(chǎn)品名(或別名),(提供商,數(shù)據(jù)庫(kù)服務(wù)器地址及實(shí)例名)

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

--2. 聲明‘鏈接名稱(自定義)’,@useself=N'False',@locallogin=NULL,將要鏈接的數(shù)據(jù)庫(kù)服務(wù)器的賬號(hào)和密碼

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

B. SSMS鏈接到遠(yuǎn)程非SQLServer數(shù)據(jù)庫(kù)

(本地SQLServer數(shù)據(jù)庫(kù)鏈接服務(wù)器(LinkedServer)到遠(yuǎn)程非SQLServer的數(shù)據(jù)庫(kù)。如遠(yuǎn)程的MySQL、Oracle等數(shù)據(jù)庫(kù)。)

--鏈接到遠(yuǎn)程的非SQLServerd數(shù)據(jù)庫(kù)(如鏈接到遠(yuǎn)程MySQL、Oracle等數(shù)據(jù)庫(kù))：

--1. 聲明‘自定義的鏈接名稱’,遠(yuǎn)程數(shù)據(jù)庫(kù)產(chǎn)品名(或別名),提供商,數(shù)據(jù)庫(kù)服務(wù)器地址及實(shí)例名

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,遠(yuǎn)程數(shù)據(jù)庫(kù)的賬號(hào)和密碼

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

實(shí)際例子-SQL Server通過(guò)Linkserver連接MySql

--通過(guò)SSMS鏈接到遠(yuǎn)程MySql數(shù)據(jù)庫(kù)(SQL Server連接MySql)--使用的訪問(wèn)接口為：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';

實(shí)際例子-SQL Server通過(guò)Linkserver連接Oracle

--通過(guò)SSMS鏈接到遠(yuǎn)程Oracle數(shù)據(jù)庫(kù)(SQL Server連接Oracle)

--使用的訪問(wèn)接口為：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='######'; 

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

到此這篇關(guān)于SQL Server跨服務(wù)器操作數(shù)據(jù)庫(kù)的圖文方法(LinkedServer)的文章就介紹到這了,更多相關(guān)SQL Server跨服務(wù)器操作數(shù)據(jù)庫(kù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論