Skip to content

MS_SQLServerLinkedServer

Jump to bottom
nishi_74322014 edited this page Aug 18, 2026 · 1 revision

SQL Server リンクサーバ機能

概要

リンクサーバとは
SQL Server に OLE 接続可能な DBMS を登録し
SQL Server 経由でアクセス可能とする機能である。

SQL Server(本体)
└リンクサーバ
　├OLE接続：SQL Server(本体とは別のインスタンス)
　├OLE接続：Oracle
　├OLE接続：HiRDB
　：

それにより

  • グローバル・トランザクション（2 フェーズ コミット）

  • 他種の DBMS を SQL Server と同じ手段でアクセス
    （例えば、リンクサーバで登録された Oracle を System.Data.SqlClient で操作）

  • SQL Server、リンクサーバ間でテーブルを JOIN して検索（リンクサーバ間でも可能）

などを実現する。

補足（データ仮想化としての位置付け）: リンク サーバは
別の DBMS を自 DB のテーブルのように見せる仕組みであり、
データ連携における「DB 参照（同期）」方式の
SQL Server 版と言える。手軽な反面、

  • 相手側の障害・遅延がそのまま自システムに波及する
  • 実行プランが相手側の統計情報を使えず、非効率になりやすい
  • MS-DTCが絡むと運用が複雑になる

という性質を持つ。定常的な大量連携には向かず、
参照系の補助や移行期の橋渡しとして使うのが妥当である。

補足（最新化：PolyBase という選択肢）: SQL Server 2019 以降は、
PolyBase で外部データ ソース（Oracle / Teradata / MongoDB /
ODBC 汎用 / Azure Blob 等）を外部テーブルとして参照できる。
リンク サーバと似た用途だが、

  • スケールアウト（コンピューティング ノードへの分散）が可能
  • 述語のプッシュダウンが効く場合がある

といった違いがある。
一方で、PolyBase は分散トランザクションに対応しないため、
2 フェーズ コミットが要る場合はリンク サーバのままになる。

手順

動作環境準備

リンクサーバを実行するため以下の設定を行う。

  1. MS DTC サービスの開始
  2. ファイアウォールの設定
    リンクサーバが別のサーバの場合、RPC 通信が行えるように設定を行う。

下記 URL 参照

補足: MS-DTC の構成でつまずく点は
MS-DTCの補足（dcomcnfg のセキュリティ構成、
RPC エンドポイント マッパ TCP 135、名前解決）にまとめてある。
なお、参照のみであれば MS-DTC は不要である（後述）。

リンクサーバを登録する手順

  1. Microsoft SQL Server Management Studio（SSMS）で SQL Server を開く
  2. オブジェクト エクスプローラにて [サーバー オブジェクト]-[リンク サーバー] を
    右クリックし [新しいリンク サーバー] を選択
  3. リンク サーバー名と DBMS への接続文字列を設定し OK ボタンを押下

移行メモ（誤字）: 元ページの「Management Stidio」は
「Management Studio」の誤記。

補足（T-SQL での登録）: GUI ではなくスクリプトで構成する場合は
以下を使う（構成管理の観点ではこちらが望ましい）。

EXEC sp_addlinkedserver
     @server = N'ORCL', @srvproduct = N'Oracle',
     @provider = N'OraOLEDB.Oracle', @datasrc = N'//host:1521/service';

EXEC sp_addlinkedsrvlogin
     @rmtsrvname = N'ORCL', @useself = N'False',
     @rmtuser = N'app', @rmtpassword = N'****';

セキュリティ上の要点は @useself と、
リンク サーバのプロパティにある「ログイン マッピング」である。
「セキュリティ コンテキストなしで作成」や
「現在のセキュリティ コンテキストを使用」の設定次第で、
呼び出し側の権限を超えたアクセスが可能になってしまう
（権限昇格の経路になりうる）ため、
相手側には最小権限のアカウントをマッピングすること。

実装上の考慮点

リンクサーバー上のテーブルの指定

リンクサーバ上のテーブルは下記形式での指定となる。

[リンクサーバー名].[データベース名].[スキーマ名].[テーブル名]

シノニムを利用して別名をつけることも可能である。

Create Synonym [シノニム名] For [リンクサーバー名].[データベース名].[スキーマ名].[テーブル名]

補足（4 部構成名は遅くなりやすい）: 4 部構成名で書くと、
SQL Server が相手側のテーブル全体を取得してからローカルで絞り込む
実行プランを選ぶことがある（述語がプッシュダウンされない）。
大きなテーブルを扱う場合は、
OPENQUERY で相手側に SQL をそのまま投げるほうが確実である。

SELECT * FROM OPENQUERY(ORCL,
    'SELECT id, name FROM emp WHERE dept_id = 10');

ただし OPENQUERY の中身は文字列であり、
パラメータ化できない（動的 SQL の組み立てが必要）ため、
SQL インジェクションに注意が要る。

グローバルトランザクション（2フェーズコミット）の実行

  • グローバル トランザクションを行う場合
SET XACT_ABORT ON

をトランザクション開始直後に発行すると
グローバル トランザクションが実行可能となる。

  • 以下の場合は、上記を発行する必要はない。
    • SQL Server（リンクサーバではない）に対してのみ SQL を実行する場合
    • SELECT 文のみ実行する場合
    • トランザクションを使わない場合（都度コミット）

補足（SET XACT_ABORT ON の意味）: これは
「グローバル トランザクションを有効にするスイッチ」ではなく、
実行時エラーが発生した際にトランザクション全体をアボートする設定である。

リンク サーバ経由の更新（分散トランザクション）では
この設定が必須とされており、指定しないと
「分散トランザクションを開始できません」といったエラーになる。
これは、部分的に失敗した状態で 2 フェーズ コミットに入ると
整合性が壊れるためである。

なお、SET XACT_ABORT ON はリンク サーバに限らず、
トランザクションを扱うストアド プロシージャ全般で推奨される
（ストアド プロシージャ参照）。

サンプルコード

C#

using System.Data;
using System.Data.SqlClient;

SqlConnection con = new SqlConnection("User ID=user1;Password=password1;Initial Catalog=sample;Data Source=127.0.0.1");
SqlCommand com = new SqlCommand("", con);
SqlTransaction tra;

//SQL Serverへ接続
con.Open();

//グローバルトランザクション開始
tra = con.BeginTransaction();
com.Transaction = tra;
com.CommandText = @"SET XACT_ABORT ON";
com.ExecuteNonQuery();

//SQL ServerへSQLを発行
com.CommandText = @"INSERT INTO [Table1] VALUES ('1','2','2')";
com.ExecuteNonQuery();

//リンクサーバへSQLを発行
com.CommandText = @"INSERT INTO [LOCALHOST\MSSQLSERVER].[sample].[dbo].[Table2] VALUES ('1','2','2')";
com.ExecuteNonQuery();

//SQL Server、リンクサーバ間でテーブルをJOINして検索するSQLを発行
DataSet ds = new DataSet();
SqlDataAdapter adp = new SqlDataAdapter(com);
com.CommandText = @"SELECT * FROM [Table1] a,[LOCALHOST\MSSQLSERVER].[sample].[dbo].[Table2] b where a.C1 = b.C1";
adp.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];

//コミット
tra.Commit();

//切断
con.Close();

補足（この方式の利点）: 注目すべきは、
アプリ側は SQL Server に 1 本の接続を張っているだけという点である。
分散トランザクションの調停は SQL Server と MS-DTC が行うため、
アプリ側は TransactionScopeを使う必要がない
（＝ アプリ側での昇格が起きない）。

System.Data.SqlClient は現在メンテナンス モードであり、
新規実装では Microsoft.Data.SqlClient を使う
（ADO.NETデータプロバイダ）。

他のDBMSからSQL Serverにリンク

Oracle や HiRDB などを起点とする場合、
そちらの製品の機能を使用することになる。

調べると、前提条件が、

  • OLE DB ではなくて ODBC 接続。
  • 分散トランザクションも必要。

など、製品によって前提条件が異なるようです。

Oracleからデータベースリンク接続

以下の手順で設定可能（Oracle 10g → SQL Server 2005 の事例）。

前提条件

Generic Connectivity Common Files 10.2.0.1.0 がインストールされていること。

（Generic Connectivity は、

  • 異機種間サービス ODBC エージェントまたは
  • 異機種間サービス OLEDB エージェント

として実装されます。）

補足（最新化）: Oracle 11g 以降、Generic Connectivity（HSODBC）は
**DG4ODBC（Database Gateway for ODBC）**に置き換えられている。
後述の inithsodbc.ora / HSODBC は、現在は
initdg4odbc.ora / DG4ODBC に読み替える。

ODBCの設定

  • ODBC のシステム DSN からドライバとして SQL Server を選択する。

  • データソース名を付与し、サーバの IP アドレス（＋インスタンス名）を指定

    • [xxx.xxx.xxx.xxx\SQL2005] 等と指定する。

  • 認証方式を選択（Windows 認証か、SQL Server 認証）。

  • 必要に応じて、既定のデータベースを選択。

  • その他、必要なオプションを設定。

移行メモ（誤字）: 元ページの「システム DNS」は
「システム DSN（Data Source Name）」の誤記。
「Windwos 認証」も「Windows 認証」の誤記。

inithsodbc.oraの編集

%ORACLE_HOME%\HS\ADMIN\inithsodbc.ora を編集する。

  • HS_FDS_CONNECT_INFO = [ODBCのデータソース名]
  • HS_FDS_TRACE_LEVEL = OFF

listener.oraの編集

%ORACLE_HOME%\network\admin\listener.ora を編集する。

（SID_DESC の追加）

(SID_LIST =
  (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
    (PROGRAM = extproc)
  )
  (SID_DESC =
    (SID_NAME = HSODBC)
    (ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
    (PROGRAM = HSODBC)
  )
)

移行メモ（誤字）: 元ページの %ORCALE_HOME% は
%ORACLE_HOME% の誤記（本ページでは修正して掲載）。

リスナー起動

Windows 認証の場合は、以下の設定が必要になる。
＃Oracle のサービスがケルベライズされていないためベース クライアント認証は不可能。

  • Windows サービスから Oracle のリスナーを停止し、
    ログイン・ユーザを SQL Server に登録したユーザに変更する。

補足: 「ベース クライアント認証が不可能」というのは、
呼び出し元ユーザの資格情報を Oracle → SQL Server へ委任できないため、
リスナーのサービス アカウントの権限で接続することになるという意味である。
リソース アクセスの方式については
リソース アクセス ストラテジを参照。

tnsnames.oraの編集

%ORACLE_HOME%\network\admin\tnsnames.ora を編集する。

（hsodbc の追加）

hsodbc =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA = (SID=hsodbc))
    (HS=OK)
  )

データベースリンクの作成

下記 SQL で「データベース リンク」を作成する。

CREATE DATABASE LINK DBLINK_SQLSRV
USING 'HSODBC'
/

データベースリンクのテスト

SQL Server のテーブルにアクセスするためには、テーブル名の後ろに、
アット マーク(@) に続けてデータベース リンク名を指定する。

SELECT * FROM TBL_NAME@DBLINK_SQLSRV;

Oracle からの分散トランザクションの動作は以下を参照。

参考

Oracle

HiRDB

Tags: 移行, データアクセス, SQL Server

このWikiは「Open棟梁Project」,「OSSコンソーシアム 開発基盤部会」によって運営されています。

NetDevInfraWiki

マイクロソフト系技術情報 Wiki
Open 棟梁 Wiki

（未着手）

開発基盤部会 Wiki

移行管理: DONE ／ TODO

Clone this wiki locally