組織は多くの場合、異なる機能を担う、または異なる地理的位置にある複数のサーバーやデータベースにデータを分散して管理しています。これらの分散データベースに格納されたデータへのアクセスと分析は、意思決定、データ分析、レポート作成に不可欠です。本記事では、複数サーバーにまたがるSQLクエリを実行するためのさまざまな方法を、それぞれのステップバイステップの手順とともに解説します。サーバー間でのSQLクエリ実行をより深く理解することで、データ管理の実践を最適化し、分散データベース環境から貴重なインサイトを引き出すことができます。

複数サーバーにまたがるSQLクエリを実行する5つの方法

異なるサーバー間でSQLクエリを実行するために利用できるツールや方法はさまざまで、それぞれに独自の利点とユースケースがあります。このセクションでは、マルチサーバークエリ実行の最も代表的なアプローチを紹介します。

1. リンクサーバーの使用

リンクサーバーは、データベース管理システム（DBMS）の機能で、異なるデータベースサーバー間の接続を可能にします。これらのサーバーは、同じタイプ（例：2つのSQL Serverインスタンス）でも、異なるタイプ（例：SQL ServerとOracle）でも構いません。

リンクサーバーを設定することで、リモートサーバー上のテーブルやデータを、ローカルオブジェクトであるかのように参照するクエリを実行できます。この機能により分散クエリが可能になり、さまざまなソースからのデータを単一のクエリ内で結合・分析でき、データインテグレーションとレポート作成プロセスが簡素化されます。リンクサーバーは、ネットワーク全体に分散したデータにアクセスし操作するための便利な方法を提供し、サーバー間で手動でデータを転送する必要がなくなります。

リンクサーバーの設定

SQL Serverでリンクサーバーを作成・管理するには、いくつかの手順が必要です。以下にステップバイステップのガイドを示します：

1. SQL Server Management Studio (SSMS) を開く：

   • SSMSを起動し、リンクサーバーを作成するSQL Serverインスタンスに接続します。

2. 「Server Objects」に移動：

   • Object Explorerでサーバーを展開し、「Server Objects」を展開して「Linked Servers」ノードを表示します。

3. 「Linked Servers」を右クリックして「New Linked Server...」を選択：

   • これにより「New Linked Server」ダイアログボックスが開きます。

4. リンクサーバーの詳細を入力：

   • 「New Linked Server」ダイアログボックスの「General」タブで、以下の詳細を入力します：

     • Linked server：リンクサーバーの名前

     • Server type：リンクサーバーのタイプを選択（SQL Server、Oracleなど）

     • Provider：リンクサーバータイプに適したOLE DBプロバイダーを選択

     • Data source：リンクサーバーのネットワーク名またはIPアドレス

     • Catalog：（オプション）リンクサーバーに接続するデフォルトデータベース

5. セキュリティオプションの設定：

   • 「Security」タブで、SQL Serverがリンクサーバーに接続する方法を指定します。

   • 適切なセキュリティコンテキストを選択します：

     • Be made using the login's current security context：現在ログインしているユーザーの資格情報を使用して接続します。

     • Be made using this security context：リンクサーバーへの接続に使用するリモートログインとパスワードを指定します。

6. サーバーオプションの設定：

   • 「Server Options」タブで、RPC（Remote Procedure Call）やRPC Out設定などの追加オプションを構成します。

7. 接続のテスト：

   • 「Test Connection」ボタンをクリックして、SQL Serverがリンクサーバーへの接続を正常に確立できることを確認します。

8. 「OK」をクリックしてリンクサーバーを作成：

   • すべての設定が完了したら「OK」をクリックします。Object Explorerの「Linked Servers」の下に表示されます。

9. リンクサーバーのプロパティを管理：

   • 新しく作成したリンクサーバーを右クリックし、「Properties」を選択してセキュリティ、サーバーオプション、プロバイダー固有のプロパティなどの設定を変更できます。

10. リンクサーバー上のオブジェクトにアクセス：

    • リンクサーバーが作成されると、4部構成の命名規則を使用してSQLクエリでオブジェクト（テーブル、ビュー、ストアドプロシージャなど）を参照できます：
      [linked_server_name].[database_name].[schema_name].[object_name].

リンクサーバーを構成すると、以下のようなJOINを実行できます：

SELECT * FROM [LinkedServerName].[RemoteDatabaseName].[dbo].[RemoteTable] AS R JOIN [LocalTableName] AS L ON R.CommonColumn = L.CommonColumn;

SQL Serverがネイティブにサポートしていない数百のデータソースへのリンクサーバーを設定したい場合は、Connect AIを使用した外部データソースへのリンクサーバーの作成に関する記事をご覧ください。

2. PolyBaseの使用

PolyBaseは、SQL Serverに統合された強力な技術で、外部ソースに格納されたデータのクエリと分析をシームレスに行うことができます。PolyBaseは、外部のSQL Server、Teradata、Oracle、HadoopインスタンスなどをローカルのSQLデータベースであるかのようにクエリすることをサポートしており、ローカルデータベースと外部データベース間でJOINを実行できます。PolyBaseは外部データベースと直接通信できるため、外部データベースのデータは元の形式と場所に留まります。

外部データソースを含むクエリがSQL Serverに送信されると、PolyBaseのクエリオプティマイザーがクエリを分析し、最適化された実行プランを生成します。その後、外部ソースとSQL Server間で必要なデータのみを移動し、ネットワークオーバーヘッドを最小限に抑え、データ転送を最適化します。

PolyBaseの多用途性とスケーラビリティにより、慣れ親しんだSQL Server環境を活用しながら多様なデータソースからインサイトを引き出したい組織にとって、価値あるツールとなっています。

PolyBaseの設定とクエリの実行

1. PolyBaseのインストールを確認： SQL ServerインスタンスにPolyBase機能がインストールされ構成されていることを確認します。SQL Server のインストール時、または SQL Server 構成マネージャーで機能セットを変更することで確認できます。

2. 外部データソースの構成： SQL Server Management Studio（SSMS）または Transact-SQL（T-SQL）を使用して、Hadoop や Azure Blob Storage など、クエリ対象の外部システムを指す外部データソースを作成します。例：

   CREATE EXTERNAL DATA SOURCE MyHadoopCluster
   WITH (LOCATION = 'hdfs://', CREDENTIAL = HadoopCredentials);

3. 外部テーブルの定義： 外部ソースのデータにマッピングする外部テーブルをSQL Serverに作成します。このステップでは、外部データのスキーマとメタデータを定義します。例：
   
   CREATE EXTERNAL TABLE dbo.MyExternalTable
(
  Column1 INT,
  Column2 VARCHAR(50)
)
WITH
(
  LOCATION = '/path/to/external/data',
  DATA_SOURCE = MyHadoopCluster
);
   
   

4. 資格情報の設定： セキュリティで保護された外部データソースにアクセスする場合、認証用の資格情報を作成・設定します。例：
   
   CREATE DATABASE SCOPED CREDENTIAL HadoopCredentials
WITH IDENTITY = 'username',
SECRET = 'password';
   
   

5. クエリの記述： SQL Server 上で定義した外部テーブルと、クエリ対象のネイティブ SQL Server テーブルを組み合わせた T-SQL クエリを作成します。例：
   
   SELECT * FROM dbo.NativeTable nt
INNER JOIN dbo.MyExternalTable et ON nt.CommonColumn = et.CommonColumn;
   
   

6. クエリの実行： SSMS またはその他の SQL クライアントを使用して、SQL Server に対して PolyBase クエリを実行します。SQL Server インスタンスに外部データソースへのアクセスとクエリ実行に必要な権限があることを確認してください。

3. OPENQUERYの使用

OPENQUERYは、リンクサーバー上でパススルークエリを実行するために使用されるSQL Serverのクエリ関数です。リモートデータベースサーバーにクエリを送信し、結果をローカルのSQL Serverインスタンスに直接取得できます。OPENQUERYは、ローカルサーバーではサポートされていないが、リンクサーバーでは利用可能な関数や機能を含むクエリを実行する必要がある場合に特に有用です。

まず、リモートSQL Serverインスタンスへのリンクサーバーを定義します：

EXEC sp_addlinkedserver 'RemoteServer', 'SQL Server';

ここで、"RemoteServer" は作成するリンクサーバーの名前、"SQL Server" はリンク先のサーバータイプを指定しています。

次に、OPENQUERY を使用してリンクサーバーに対して SELECT クエリを実行します。

SELECT * FROM OPENQUERY(RemoteServer, 'SELECT * FROM RemoteDatabase.dbo.RemoteTable');

4. OPENROWSETの使用

OPENROWSETは、SQL Serverの関数で、OLE DBデータソースに対してアドホッククエリを実行できます。リンクサーバーを作成する必要なく、外部ソースからデータにアクセスしてクエリを実行できます。OPENQUERYとOPENROWSETの主な違いは、OPENROWSETがリモートサーバーからデータを取得してからローカルでクエリを実行するのに対し、OPENQUERYはリモートサーバーにクエリを実行させる点です。

OPENROWSET の構文は次のとおりです。

SELECT * FROM OPENROWSET('SQLNCLI', 'Server=RemoteServer;Database=RemoteDatabase;Trusted_Connection=yes;', 'SELECT * FROM dbo.RemoteTable')

この例では、"SQLNCLI" は使用する OLE DB プロバイダー、"RemoteServer" は接続先のリモート SQL Server インスタンス、"RemoteDatabase" は接続先のリモートデータベースを表しています。

5. SQL Serverレプリケーションの使用

SQL Serverレプリケーションは、複数のSQL Serverインスタンス間でデータをレプリケートできるデータ分配・同期技術です。一つのサーバー（パブリッシャー）から一つ以上の宛先サーバー（サブスクライバー）にデータをレプリケートすることで、複数サーバーにわたるデータのクエリに使用できます。データがレプリケートされると、各サブスクライバーサーバーでローカルにクエリでき、分散クエリが可能になります。

自動レプリケーションを活用して、パブリッシャー上のデータの変更をニアリアルタイムでサブスクライバーサーバーに伝播できます。

SQL Serverレプリケーションのセットアップ

SQL Server レプリケーションを使用してサーバー間のデータをクエリするには、以下の手順を実行します（具体的な手順は使用するレプリケーションツールによって異なります）。

1. レプリケーションのセットアップ：パブリッシャーサーバーでパブリケーションを定義し、サブスクライバーサーバーでサブスクリプションを定義して、SQL Server レプリケーションを設定します。

2. データのレプリケート： パブリッシャーからサブスクライバーサーバーに目的のテーブルやデータベースをレプリケートします。

3. レプリケートされたデータのクエリ： レプリケーションのセットアップと同期が完了すれば、各サブスクライバーサーバー上で標準的な SQL クエリを使用してレプリケートされたデータをローカルにクエリできます。これにより、リンクサーバーなどの複雑な構成を必要とせずに、複数のサーバーにまたがる分散クエリを実行できます。

CData Connect AI：あらゆるクラウドツールのためのSQL Server仮想化

データ仮想化は論理的なデータレイヤーを作成し、複数のデータソースからのデータに一度にアクセスできる単一の統合ハブを提供します。これにより、複数のデータソースからのデータを組み合わせるクエリを含め、単一のインターフェースから統合データにクエリを実行できます。

CData Connect AIは、データ仮想化を活用して数百のデータソースへの接続を確立し、アナリティクス、ビジネスインテリジェンス、データパイプラインなどの幅広いデータアプリケーションとインテグレーションします。

※本記事はCData US ブログ How to Run SQL Queries Across Servers and Access Multiple Databasesの翻訳です。