首頁 > 資料庫 > SQL > 主體

SQL Server跨伺服器作業資料庫的圖文方法(LinkedServer)

WBOY
發布: 2022-11-02 16:13:14
轉載
2502 人瀏覽過

這篇文章為大家帶來了關於SQL的相關知識,其中主要介紹了SQL Server跨伺服器操作資料庫的圖文方法,SQL Server Management Studio (SSMS) 是用於管理SQL Server 基礎架構的整合環境,下面一起來看一下,希望對大家有幫助。

SQL Server跨伺服器作業資料庫的圖文方法(LinkedServer)

推薦學習:《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,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教程

以上是SQL Server跨伺服器作業資料庫的圖文方法(LinkedServer)的詳細內容。更多資訊請關注PHP中文網其他相關文章!

相關標籤:
sql
來源:jb51.net
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板