SQL Server跨伺服器作業資料庫的圖文方法(LinkedServer)
- WBOY轉載
- 2022-11-02 16:13:142425瀏覽
這篇文章為大家帶來了關於SQL的相關知識，其中主要介紹了SQL Server跨伺服器操作資料庫的圖文方法，SQL Server Management Studio (SSMS) 是用於管理SQL Server 基礎架構的整合環境，下面一起來看一下，希望對大家有幫助。
推薦學習：《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中文網其他相關文章！