サーバー間でデータベースを操作するための SQL Server グラフィック方式 (LinkedServer)

この記事では、SQL に関する関連知識を提供します。主に SQL Server のクロスサーバー データベース操作のグラフィック手法を紹介します。SQL の管理には SQL Server Management Studio (SSMS) が使用されます。サーバーインフラの統合環境について、皆様のお役に立てれば幸いです。

推奨学習: 「SQL チュートリアル 」

基礎知識の紹介

SQL Server データベース管理ツール SSMS (SQL Server Management Studio) プラットフォームで動作します。

SQL Server Management Studio (SSMS) は、SQL Server インフラストラクチャを管理するための統合環境です。 SSMS を使用すると、SQL Server、Azure SQL Database、SQL Data Warehouse のすべてのコンポーネントにアクセス、構成、管理、開発できます。 SSMS は、多数のグラフィカル ツールとリッチ スクリプト エディターを 1 つの包括的なユーティリティに統合し、あらゆるスキル レベルの開発者とデータベース管理者に SQL Server へのアクセスを提供します。

クロスサーバー操作とは何ですか?

クロスサーバー操作とは、リモート サーバー上のデータベースにローカルに接続し、追加、削除、変更、検索など、相手のデータベースに対して関連するデータベース操作を実行できることを意味します。

なぜクロスサーバー運用が必要なのか

データ量の増加や業務量の拡大に伴い、異なるデータベースを異なるサーバーにインストールする必要があります。サーバー内のデータを統合するには、サーバー間での操作が必要です。

クロスサーバー操作用のツールとは何ですか?

DBLINK (データベース リンク) は、名前が示すとおり、データベースへのリンクです。電話回線と同じように、チャネルです。ローカル データベースを介して別のデータベース テーブルのデータにアクセスしたい場合は、ローカル データベースは、リモート データベースの dblink を作成する必要があります。ローカル データベースは、ローカル データベースと同様に、dblink を介してリモート データベース テーブル内のデータにアクセスできます。

方法 1: SSMS を使用して SQL Server リモート リンク サーバー (LinkedServer) を作成します -- リモート SqlServer への単純なリンク

1. SSMS を開く --> ローカル データベースにログインします--> サーバー オブジェクト --> リンク サーバー (右クリック) --> 以下に示すように、新しいリンク サーバーを作成します:

2. 関連する情報を入力します。ポップアップ ダイアログ ボックスの情報

# [リンク サーバー] に他のサーバーの IP アドレスを入力します;

#● [サーバーの種類] で [SQL Server] を選択します;

3. 左側の[セキュリティ]をクリックすると以下のページが表示されるので、手順3で相手のデータベースのアカウントとパスワードを入力します。

[OK] ボタンをクリックすると、リンク サーバー (LinkedServer) が正常に作成されます。この時点で、作成されたリンク サーバーが表示されます:

リンク サーバーのコードを表示します: 作成されたリンク サーバーを右クリックし、リンク サーバー スクリプトを次のように記述します。 --> ; [作成先] --> [新しいクエリ エディター] ウィンドウで、作成したばかりのリンク サーバーのスクリプトを開きます。

--リンク サーバー (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='########';

Note : 欠点は、リンクがリモート SqlServer 全体のすべてのデータベースにあり (通常、特定のデータベースは 1 つだけ必要です)、リンクされたサーバーの名前は IP であり、カスタマイズできないことです。したがって、リンク データベースをコードによって直接作成するのが最善の方法です (「3. コードの詳細な説明」を参照)。

リンク サーバーが正常に作成されたら、作成した DBLINK を使用してリモート リンク サーバーに接続できます。作成したテーブルを使って相手のサーバー上のテーブルにクエリを実行して検証してみましょう。

--LinkedServer のデータをクエリするメソッド: [DBLINK 名].[他のデータベース名].[他のデータベースのモード名].[他のデータベース テーブル名]

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

上記の FROM フィールドの後には、[DBLINK 名].[パートナー データベース名].[スキーマ名]が続きます。 [相手のデータベースのテーブル名].[相手のデータベースのテーブル名]. テーブル名の前の内容が欠けてはいけません。

クエリ結果は次のとおりです。

方法 2: 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 中国語 Web サイトの他の関連記事を参照してください。