本篇文章給大家?guī)砹岁P(guān)于SQL的相關(guān)知識，其中主要介紹了SQL Server跨服務(wù)器操作數(shù)據(jù)庫的圖文方法，SQL Server Management Studio (SSMS) 是用于管理SQL Server 基礎(chǔ)結(jié)構(gòu)的集成環(huán)境，下面一起來看一下，希望對大家有幫助。

Linux系統(tǒng)運維及項目正式上線：進入學(xué)習(xí)

推薦學(xué)習(xí)：《SQL教程》

基礎(chǔ)知識介紹

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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中的所有數(shù)據(jù)庫(一般只需要一個特定的數(shù)據(jù)庫)，而且鏈接服務(wù)器的名稱是個IP且無法自定義！ 所以，最好的方式還是通過代碼直接創(chuàng)建鏈接數(shù)據(jù)庫(見“三、代碼詳解”)。

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

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

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

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

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

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

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

2.【安全性】選擇頁：

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

1.【常規(guī)】頁

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

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

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

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

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

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

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

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

2.【安全性】頁

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

▶[遠程登錄]： 遠程數(shù)據(jù)庫的連接賬號

▶[使用密碼]： 遠程數(shù)據(jù)庫連接賬號的密碼

--鏈接服務(wù)器(LinkedServer)創(chuàng)建完成后會自動生成相關(guān)代碼 —— 鏈接到遠程的SQLServer數(shù)據(jù)庫(自定義)：  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='########';  /****** 實際例子 系統(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,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創(chuàng)建SQLServer鏈接服務(wù)器(LinkedServer)–鏈接到非SqlServer的其它數(shù)據(jù)庫

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

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

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

–LinkedServer鏈接到遠程SQLServer數(shù)據(jù)庫：

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

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

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

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

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

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

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

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

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,遠程數(shù)據(jù)庫的賬號和密碼

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數(shù)據(jù)庫(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數(shù)據(jù)庫(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');

推薦學(xué)習(xí)：《SQL教程》