這篇文章為大家帶來了關於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本機資料庫可以像存取本機資料庫一樣存取遠端資料庫表中的資料。
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名稱].[對方資料庫名稱]. [對方資料庫下模式名].[對方資料庫表名],表名前面的這些內容一個都不能少。
查詢結果如下圖:
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或更早的版本) 等
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中文網其他相關文章!