SQL Server跨服務(wù)器操作數(shù)據(jù)庫的圖文方法(LinkedServer)
基礎(chǔ)知識介紹
以SQL Server的數(shù)據(jù)庫管理工具SSMS(SQL Server Management Studio)為平臺進(jìn)行操作。
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 在一個(gè)綜合實(shí)用工具中匯集了大量圖形工具和豐富的腳本編輯器,為各種技能水平的開發(fā)者和數(shù)據(jù)庫管理員提供對 SQL Server 的訪問權(quán)限。
什么是跨服務(wù)器操作?
跨服務(wù)器操作就是可以在本地連接到遠(yuǎn)程服務(wù)器上的數(shù)據(jù)庫,可以在對方的數(shù)據(jù)庫上進(jìn)行相關(guān)的數(shù)據(jù)庫操作,比如增刪改查。
為什么要進(jìn)行跨服務(wù)器操作
隨著數(shù)據(jù)量的增多,業(yè)務(wù)量的擴(kuò)張,需要在不同的服務(wù)器安裝不同的數(shù)據(jù)庫,有時(shí)候因?yàn)闃I(yè)務(wù)需要,將不同的服務(wù)器中的數(shù)據(jù)進(jìn)行整合,這時(shí)候就需要進(jìn)行跨服務(wù)器操作了。
跨服務(wù)器操作的工具是什么?
DBLINK(數(shù)據(jù)庫鏈接),顧名思義就是數(shù)據(jù)庫的鏈接,就像電話線一樣,是一個(gè)通道,當(dāng)我們要跨本地?cái)?shù)據(jù)庫,訪問另外一個(gè)數(shù)據(jù)庫表中的數(shù)據(jù)時(shí),本地?cái)?shù)據(jù)庫中就必須要?jiǎng)?chuàng)建遠(yuǎn)程數(shù)據(jù)庫的dblink,通過dblink本地?cái)?shù)據(jù)庫可以像訪問本地?cái)?shù)據(jù)庫一樣訪問遠(yuǎn)程數(shù)據(jù)庫表中的數(shù)據(jù)。
方法一:用SSMS創(chuàng)建SQL Server遠(yuǎn)程鏈接服務(wù)器(LinkedServer)--簡單鏈接到遠(yuǎn)程SqlServer
1. 打開SSMS -->登錄到本地?cái)?shù)據(jù)庫 --> 服務(wù)器對象 --> 鏈接服務(wù)器(右鍵) --> 新建鏈接服務(wù)器,如下圖:
2. 在彈出的對話框中輸入相關(guān)信息
● 在【鏈接服務(wù)器】輸入對方服務(wù)器的IP地址;
● 在【服務(wù)器類型】中選擇【SQL Server】;
3. 點(diǎn)擊左側(cè)的【安全性】,出現(xiàn)如下頁面,在第3步中輸入對方數(shù)據(jù)庫的賬號密碼即可。
點(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)建完成后會自動生成相關(guān)代碼 —— 鏈接到遠(yuǎ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='########';
注意: 這里有一個(gè)弊端,那就是鏈接的是整個(gè)遠(yuǎn)程SqlServer中的所有數(shù)據(jù)庫(一般只需要一個(gè)特定的數(shù)據(jù)庫),而且鏈接服務(wù)器的名稱是個(gè)IP且無法自定義! 所以,最好的方式還是通過代碼直接創(chuàng)建鏈接數(shù)據(jù)庫(見“三、代碼詳解”)。
鏈接服務(wù)器(LinkedServer)就創(chuàng)建成功后,我們就可以用創(chuàng)建好的DBLINK鏈接到遠(yuǎn)程的Linked服務(wù)器了。下面我們用創(chuàng)建好的試著查詢對方服務(wù)器上的表來驗(yàn)證一下。
--查詢鏈接服務(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)容一個(gè)都不能少。
查詢結(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ù)源】;
?[提供程序]中選擇 第一個(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ù)庫的地址,端口\實(shí)例名 ,如 10.10.0.73,1433\MSSQLSERVER
?[訪問接口字符串]中,可以空著不填; 也可以填下方的:(注意######是密碼,請換成自己的密碼)
Provider=sqloledb;Data Source=10.10.0.73,1433\MSSQLSERVER;Initial Catalog=TESTDB;User Id=apps;Password=#####;
?[目錄]就是數(shù)據(jù)庫名稱,這里填上我們需要遠(yuǎn)程連上的數(shù)據(jù)庫 TESTDB (可以換成自己實(shí)際的)。
2.【安全性】頁
● 選擇【使用此安全上下文建立連接(M)】
?[遠(yuǎn)程登錄]: 遠(yuǎn)程數(shù)據(jù)庫的連接賬號
?[使用密碼]: 遠(yuǎn)程數(shù)據(jù)庫連接賬號的密碼
--鏈接服務(wù)器(LinkedServer)創(chuàng)建完成后會自動生成相關(guān)代碼 —— 鏈接到遠(yuǎ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='########'; /****** 實(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ù)庫
四、代碼詳解:方法一和方法二是通過SSMS直接操作的,下方直接使用sql腳本來創(chuàng)建鏈接服務(wù)器(LinkedServer)
A. SSMS鏈接到遠(yuǎn)程SQLServer數(shù)據(jù)庫
(本地SQLServer數(shù)據(jù)庫鏈接服務(wù)器(LinkedServer)到遠(yuǎn)程SQLServer數(shù)據(jù)庫。)
--LinkedServer鏈接到遠(yuǎn)程SQLServer數(shù)據(jù)庫:
--1. 聲明將要鏈接的‘鏈接名稱(自定義)’,遠(yuǎn)程數(shù)據(jù)庫產(chǎn)品名(或別名),(提供商,數(shù)據(jù)庫服務(wù)器地址及實(shí)例名)
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鏈接到遠(yuǎn)程非SQLServer數(shù)據(jù)庫
(本地SQLServer數(shù)據(jù)庫鏈接服務(wù)器(LinkedServer)到遠(yuǎn)程非SQLServer的數(shù)據(jù)庫。如遠(yuǎn)程的MySQL、Oracle等數(shù)據(jù)庫。)
--鏈接到遠(yuǎn)程的非SQLServerd數(shù)據(jù)庫(如鏈接到遠(yuǎn)程MySQL、Oracle等數(shù)據(jù)庫):
--1. 聲明‘自定義的鏈接名稱’,遠(yuǎn)程數(shù)據(jù)庫產(chǎn)品名(或別名),提供商,數(shù)據(jù)庫服務(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ù)庫的賬號和密碼
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TEST_SQL_SERVER',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='########';
實(shí)際例子-SQL Server通過Linkserver連接MySql
--通過SSMS鏈接到遠(yuǎn)程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';
實(shí)際例子-SQL Server通過Linkserver連接Oracle
--通過SSMS鏈接到遠(yuǎn)程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');
到此這篇關(guān)于SQL Server跨服務(wù)器操作數(shù)據(jù)庫的圖文方法(LinkedServer)的文章就介紹到這了,更多相關(guān)SQL Server跨服務(wù)器操作數(shù)據(jù)庫內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
- SQL Server中通用數(shù)據(jù)庫角色權(quán)限的處理詳解
- SQL SERVER 利用存儲過程查看角色和用戶信息的寫法
- sqlserver 2000中每個(gè)服務(wù)器角色的解釋
- SQL?Server跨庫/服務(wù)器查詢及拓展知識點(diǎn)
- SQL?Server無法連接服務(wù)器的幾種情況分析解決
- 如何在WindowsServer服務(wù)器上配置SQL?Server數(shù)據(jù)庫
- SQL Server如何通過SQL語句直接操作另一臺服務(wù)器上的SQL SERVER的數(shù)據(jù)
- SQL?Server查看服務(wù)器角色的實(shí)現(xiàn)方法詳解
相關(guān)文章
清空數(shù)據(jù)庫中所有表記錄 記錄ID恢復(fù)從0開始
近來發(fā)現(xiàn)數(shù)據(jù)庫過大,空間不足,因此打算將數(shù)據(jù)庫的數(shù)據(jù)進(jìn)行全面的清理,但表非常多,一張一張的清空,實(shí)在麻煩,因此就想利用SQL語句一次清空所有數(shù)據(jù).找到了三種方法進(jìn)行清空.使用的數(shù)據(jù)庫為MS SQL SERVER.2010-08-08SqlServer數(shù)據(jù)庫提示 “tempdb” 的日志已滿 問題解決方案
本文主要講述了筆者在執(zhí)行sql語句的過程中,遇到提示“數(shù)據(jù)庫 'tempdb' 的日志已滿。請備份該數(shù)據(jù)庫的事務(wù)日志以釋放一些日志空間?!钡慕鉀Q過程,希望對大家有所幫助2014-08-08sql server中隨機(jī)函數(shù)NewID()和Rand()
在SQL語言中,隨機(jī)函數(shù)NEWID和rand()用法并不相同,下面就將為您示例分析這兩個(gè)隨機(jī)函數(shù)的區(qū)別,供您參考,希望對您深入理解SQL函數(shù)能起到作用2015-10-10SQL SERVER 開啟CDC 實(shí)操詳細(xì)
這篇文章主要個(gè)代價(jià)介紹SQL SERVER CDC開啟實(shí)操詳細(xì)內(nèi)容,文明芝麻官內(nèi)容有詳細(xì)代碼及解說,需要的小伙伴可以參考一下2021-10-10SQLServer存儲過程創(chuàng)建和修改的實(shí)現(xiàn)代碼
這篇文章主要介紹了SQLServer存儲過程創(chuàng)建和修改的實(shí)現(xiàn)代碼,需要的朋友可以參考下2017-05-05SQL?Server數(shù)據(jù)庫入門教程之多表查詢
這篇文章主要給大家介紹了關(guān)于SQL?Server數(shù)據(jù)庫入門教程之多表查詢的相關(guān)資料,多表查詢是SQL數(shù)據(jù)庫里的高級查詢語句,文中通過圖文以及實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-06-06MSSQL 2005/2008 日志壓縮清理方法小結(jié)
本教程會詳細(xì)介紹下MSSQL 2005和MSSQL 2008刪除或壓縮數(shù)據(jù)庫日志的方法,感興趣的朋友可以參考下哈,希望可以幫助到你2013-03-03