Access 2002 データベースを SQL Server に移行する

Adam Cogan
Microsoft Regional Director, Australia

December 2004
日本語版最終更新日 2005 年 2 月 24 日

適用対象:
   Microsoft Access 2002
   Microsoft SQL Server 2000 Service Pack 3a (SP3a)

要約: この機能比較は、Access 2002 データベース バック エンドを SQL Server 2000 に移行するための準備で使用します。この記事には英語のページへのリンクも含まれています。

目次

前提条件
はじめに
SQL Server ツール
アーキテクチャ
スケーラビリティとパフォーマンス
データを処理する
まとめ
用語集

前提条件

この記事のすべての比較は、次のソフトウェアが使用されていることを前提としています。

• Microsoft Access 2002 以降
• Microsoft SQL Server 2000 Standard Edition または Enterprise Edition

また、データが現在 SQL Server ではなく Access データベース (.mdb) ファイルに格納されていることと、この記事で説明している SQL Server 機能の多くをサポートする Access Data Project (ADP) を使用していないことも前提としています。

この記事の対象読者

この記事は、Access の機能に精通し、バック エンド インフラストラクチャ (データおよびクエリ) を Microsoft SQL Server に移行することを検討している Access 開発者、Microsoft Visual Basic 開発者、および .NET 開発者を対象としています。

読者は、次の Access 機能に精通している必要があります。

• 基本的な SQL
• さまざまな形式でのデータのインポートおよびエクスポート
• データのバックアップと復元
• セキュリティの実装

この記事では、Access と SQL Server の機能を比較することにより、新規の SQL Server 開発者を支援します。

はじめに

一般に、Microsoft Access 開発者は、パフォーマンス、セキュリティ、および安定性の理由から SQL Server への移行を検討します。このプロセスは "アップサイジング" と呼ばれ、開発者は、Access から SQL Server への移行時に多くの重要な違いがあることに気付きます。これらの違いに注意し、適切な処置を行って、Access から SQL Server へのシームレスで問題のない移行を行うことが重要です。

Microsoft SQL Server は、エンタープライズレベルのデータ管理システムです。業界標準のセキュリティ、スケーラビリティ、および管理性がカプセル化されています。また、Extensible Markup Language (XML) とインターネット クエリもサポートされています。

ヒント: Access から SQL Server への移行のプロセスについては、ここでは扱いません。

移行の詳細については、「Migrating Your Access Database to Microsoft SQL Server 7.0 (英語)」を参照してください (注: この記事は SQL Server 7.0 用に記述され、更新されていません)。

ヒント: データ レプリケーションとデータベース セキュリティの違いについては、ここでは扱いません。
SQL Server でのレプリケーションの実装の詳細については、SQL Server 2000 SDK ドキュメントの「レプリケーションの実装」を参照してください。
SQL Server のセキュリティの詳細については、SQL Server 2000 SDK ドキュメントの「セキュリティ アカウントの管理」を参照してください。

SQL Server ツール

Access データベース ウィンドウのメイン メニューを使用して、クエリの作成、データベースのデザイン、またはデータの参照を行うことができます。データベースからデータをエクスポートするには、[ファイル]、[エクスポート] の順にクリックします。データベースにデータをインポートするには、[ファイル]、[外部データの取り込み]、[インポート] の順にクリックします。

SQL Server には、データの参照、クエリ、インポート、およびエクスポートのプロセスを簡略化する一連の強力なツールが用意されています。これらのツールは次のとおりです。

• SQL Server Enterprise Manager
• SQL Server クエリ アナライザ
• データ変換サービス
• SQL Server プロファイラ

データベースとクエリのデザインおよびデータの参照に使用する SQL Server ツール

SQL Server では、2 つのツールを使用して、データベース保守タスクとデータの参照および編集を実行します。これらのツールは、SQL Server Enterprise Manager と SQL Server クエリ アナライザです。.NET にフォームを移行することを計画している Access フォーム開発者には、Microsoft Visual Studio .NET も役立ちます。このツールを使用すると、同じ開発環境で、SQL Server データベースとデータ アクセス フォームを統合された方法で作成および管理できます。

SQL Server Enterprise Manager

SQL Server Enterprise Manager は、データベースのデザインと管理 (図 1 参照) およびデータの参照 (図 2 参照) を行うために SQL Server にバンドルされているアプリケーションです。また、Enterprise Manager には、次の機能も用意されています。

• テーブル、フィールド、データ、テーブルのリレーションシップ、ストアド プロシージャ、ビュー、トリガ、関数、およびユーザー定義データ型の管理
• データベース ダイアグラムの作成
• データベース バックアップの作成とデータの復元
• データベース ログオンとオブジェクト権限の管理
• データ変換サービス (DTS) を使用するさまざまな形式でのデータのインポートとエクスポート

図 1. SQL Server Enterprise Manager は、メインの Access ダイアログ ボックスの代わりにデータベースのデザインと管理を行う

図 2. Enterprise Manager を使用して、Access とほぼ同じ方法でデータを参照および編集する

SQL Server クエリ アナライザ

SQL Server クエリ アナライザは、メインの Access クエリ デザイナの代わりをする、完全な機能を備えたグラフィカル クエリ ツールです。次のことが可能です。

• クエリの作成とデバッグ
• 複数の同時クエリの実行
• データの表示
• データのエクスポート ([クエリ]、[結果をファイルに出力] の順にクリック)
• クエリの最適化 ([クエリ]、[実行プランの表示] の順にクリック)
• 高度なクエリのデバッグ ([ツール]、[オブジェクト ブラウザ]、[デバッグ] の順にクリック)

  ヒント: クエリ アナライザでは、前述の機能がサポートされ、クエリを簡単に表示およびデバッグするための構文強調表示機能 (図 3 参照) が用意されています。ストアド プロシージャを Enterprise Manager の内部で作成することもできますが (図 4 参照)、Access 開発者にとってはクエリ アナライザの方が機能的に優れています。

  

  図 3. クエリ アナライザは、Access クエリ デザイナの代わりであり、構文強調表示やクエリ デバッグなどの機能を追加する

  

  図 4. 高度なストアド プロシージャを Enterprise Manager の内部で作成することは、クエリ アナライザで作成するほど簡単ではない

Access の [ウィザードを使用してクエリを作成する] 機能に対応する機能は SQL Server にはありません。クエリは、クエリ デザイナまたは SQL Server ステートメントを使用して作成する必要があります。

Visual Studio .NET

Visual Studio .NET では、Enterprise Manager とほぼ同じ方法でデータベースとデータベース オブジェクトを管理できます (図 5 参照)。Visual Studio .NET のバージョンに応じて、次の処理が可能なデータベース プロジェクトを作成できます。

• ストアド プロシージャ、ビュー、トリガ、および関数のデザインと実行
• テーブルの参照
• データの表示

この機能は、統合されたデータベース管理方法を提供するため、.NET 開発者の役に立ちます。開発者は、アプリケーションを開発し、1 つのアプリケーション内でデータベースを管理できます。

図 5. Visual Studio .NET には統合されたデータ管理方法が用意されている

どのバージョンの Visual Studio .NET がどのデータベース管理機能をサポートしているかの詳細については、「Microsoft Visual Database Tools と Visual Studio のエディション」を参照してください。

データのインポートおよびエクスポートに使用する SQL Server ツール

データ変換サービス

データ変換サービス (DTS) では、Microsoft Excel などの OLE DB ベースのアーキテクチャを使用したさまざまなデータ ソース間でのデータのインポートおよびエクスポートが可能になります。DTS は、Access のインポートおよびエクスポート機能の代わりであり (図 7 参照)、次の機能も提供します。

• 別の SQL Server データベースとの間のデータのエクスポートとインポート
• Excel (.xls ファイル)、コンマ区切りファイル (.csv ファイル)、Microsoft Access などのさまざまな形式でのデータのエクスポートとインポート (図 6 参照)
• データの変換の実行

図 6. DTS を使用して、さまざまなデータ形式でインポートおよびエクスポートする

DTS は、Access のインポートとエクスポートのコマンドよりも強力です。Access のインポート プロセスで実行される多くのタスクは複数のステップで行われますが (たとえば、変換を実行するには、一時テーブルにデータを挿入し、複数のクエリを実行します)、DTS ではこれらを 1 ステップで実行できます。データの変換では、SQL クエリを使用してあるテーブルから別のテーブルにデータをコピーできます。または、VBScript コードを実行して、変換先のテーブルに挿入する前にデータの一部を変換できます (図 8 参照)。

図 7. DTS は、Access のインポートおよびエクスポート ウィザードの代わりであり、強力なデータ変換を可能にする

図 8. DTS は強力なデータ変換を実行するが、これを Access で行うと大幅に時間がかかる

SQL Server プロファイラ

SQL Server プロファイラは、データベースのパフォーマンスを最適化するためには不可欠なツールです。Access などのクライアントのみのシステムからの移行後に特に役立ちます。このツールは、開かれた接続と閉じられた接続など、サーバーに対して実行されるすべてのコマンドと、データベース トランザクションを表示します (図 9 参照)。特に時間のかかるトランザクションやリソースを集中的に使用するトランザクションの識別に役立ちます。

図 9. SQL Server プロファイラは、データベース アクティビティを監視してパフォーマンスの最適化を支援する

これらの SQL Server ツールの使用の詳細については、「Migrating Your Access Database to Microsoft SQL Server 7.0 (英語)」を参照してください (注: この記事は SQL Server 7.0 用に記述され、更新されていません)。

アーキテクチャ

Access と SQL Server のアーキテクチャには、いくつかの相違点、類似点、および欠点があります。違いは次のとおりです。

• データ アクセス モデル
• テーブル デザイン
• リレーションシップ
• インデックス作成
• データ クエリの種類
• SQL Server には、データ操作を最適化および簡略化するための次のような強力な機能もあります。
• トリガ
• 一時テーブル
• ユーザー定義関数

システム要件

最小システム要件

SQL Server は、Access よりも多機能で高スケーラビリティであるため、システム要件が若干厳しくなっています。表 1 では、2 つのシステム間の最小システム要件を比較しています。

表 1. SQL Server と Access の最小システム要件

現実的なシステム要件

表 1 に示した最小要件は、通常の動作環境として現実的ではありません。システム要件は、主にデータ量と同時ユーザー数によって異なります。

10 人の同時ユーザーと 1 GB のデータベースのシナリオでは、運用環境で Access または SQL Server を実行するために、表 2 に示すシステムをお勧めします。

表 2. SQL Server と Access の推奨システム要件

SQL Server バージョン

SQL Server 2000 には 6 つのエディションがあります。

• Enterprise
• Standard
• Personal
• Developer
• Desktop Engine (MSDE)
• SQL Server CE (Windows CE との互換バージョン)

表 3 は、各 SQL Server エディションに対応するオペレーティング システム要件を示しています。

表 3. 各 SQL Server エディションに対応するオペレーティング システム要件

エンジンの実装

Access の Jet データベース エンジンは、SQL Server とは異なり、サービスとして永続的には実行されませんが、ユーザーが Access またはその他のデータ アクセス方法を使用して Jet データベース ファイル (.mdb ファイル) を開くたびに起動されます。ユーザーが .mdb ファイルを閉じ、ファイルが使用されていない状態になると、Jet エンジンはメモリからアンロードされます。

重要な違いは、現在 .mdb ファイルにアクセスしているユーザーがいない場合に、Windows を使用してこのファイルを別の場所にコピーまたは移動できることです。SQL Server の場合は、SQL Server サービスが絶えず稼動し、登録済みの SQL Server データベース ファイル (.mdf ファイル) に接続されています。.mdf ファイルをコピーするには、移動の前に SQL Server サービスを停止するか、.mdf ファイルを現在の SQL Server サービスからデタッチする必要があります。

データ アクセス モデル

Access は、クライアントのみのリレーショナル データベース管理システム (RDBMS) です。そのため、並べ替えやフィルタ処理などのすべてのデータ処理は単一のコンピュータで行われます。

一般に、Access 開発者は、データベースを分割することでクライアント/サーバー アプローチのエミュレートを試みます。通常、複数の同時ユーザーが Access を使用する環境では、Access データベースが各クライアント コンピュータに設定されます。このデータベースには、フォーム、レポート、保存されているクエリ、および Microsoft Visual Basic for Applications (VBA) フォーム コードが含まれます。すべてのデータは中央サーバーの Access データベースに保持され、要求されたときにクライアント マシンに返されます。このシナリオでは、ネットワークとクライアントの両方に非常に多くのリソースが必要になります。図 10 は、この構造を示しています。

図 10. Access データベースの分割 (赤はワークロードを示す)

このシナリオでは、データ処理がサーバーで行われません。クライアントがデータを要求すると、データ セット全体がネットワークを通じてクライアントに送信され、すべての処理がクライアント マシンで行われます。

たとえば、ある金融会社のデータベースの Accounts Receivable (売掛管理) テーブル (Access .mdb ファイル) に数百万のレコードがあるとします。この場合、Access アプリケーションで売掛金の合計 (1 つの計算フィールド) を表示するには、Access でネットワーク上に "テーブル全体" を転送し、ワークステーション上で計算を実行する必要があります。

これにより、サーバーとネットワークに関する深刻なパフォーマンスの問題が発生することがあります。大量のデータを複数回要求することによってサーバー リソースが消費され、ネットワーク接続上でのデータ セット全体の受け渡しによってネットワーク速度が大幅に低下します。

一方、SQL Server は純粋なクライアント/サーバー RDBMS です。そのため、クライアントとサーバーの両方で処理の負荷を分担します。クライアント (たとえば、.NET Windows アプリケーション) が任意のパラメータでデータの要求を送信し、サーバーが並べ替えとフィルタ処理を実行して、フィルタ処理したデータ セットのみをクライアントに返します。図 11 は、この構造を示しています。

図 11. SQL Server では、クライアントとサーバー間で処理タスクを分散することにより、ネットワーク トラフィックとサーバー負荷を軽減できる

SQL Server はすべてのフィルタ処理と並べ替えをサーバー上で行うため、指定した結果セットだけが返されます。これにより、クライアントとサーバーとの間で受け渡されるデータが減るため、ネットワーク トラフィックが大幅に削減されます。また、Access の場合ほど多くのレコードを返す必要がないため、サーバー処理の量も少なくなります。

データ型

Access と SQL Server のデータ型にはいくつかの違いがあります。これらのデータ型のほとんどはアップサイジング時に自動的に変換されますが、アップサイジング後にこれを SQL Server データベースで確認することが重要です。表 4 に、Access と SQL Server のデータ型の違いを示します。一部サポートされないデータ型もあります。

表 4. Access と SQL Server のデータ型の比較

ヒント: Access では、ユーザーが新規レコードの編集を開始するとすぐに auto-number 列が自動的に生成されます。SQL Server では、auto-number はレコードの保存時にのみ生成されます。Access で auto-number 値に依存する既存のロジックを再設計する場合は注意が必要です。

ユーザー定義データ型

SQL Server では、"ユーザー定義データ型 (UDDT)" というカスタム データ型を定義できます。UDDT は、既存の SQL Server データ型に基づいています。また、次の目的で制約を型に直接追加できます。

• 既定値を指定する (対象のレコードに対して値が指定されていない場合に、フィールドに自動的に入力される値)。
• 最大フィールド サイズを設定する。
• フィールドを null にできるかどうかを設定する。

UDDT は、プロパティを将来変更する可能性のあるテーブル内のフィールドを指定するときに重要になります。たとえば、基本 SQL Server データ型 varchar(15) (15 文字の文字列) の一意識別子フィールドを定義し、varchar(15) 型のパラメータを受け入れる関連するすべてのストアド プロシージャを定義した場合、フィールドの長さまたはデータ型が変更されたときに、保守に関する重大な問題が発生します。すべてのストアド プロシージャとテーブルを変更して、データ型に対する変更を反映する必要があります。

より適切なソリューションは、たとえば "CodeType" という UDDT を作成し、その UDDT で長さと基本データ型を定義することです。すべてのストアド プロシージャとテーブル定義でこの UDDT を使用するため、フィールド サイズが増えた場合は、UDDT の定義を変更することになります。

UDDT は、Enterprise Manager を使用して定義します (図 12 参照)。

図 12. SQL Server のデータベース オブジェクトで使用する UDDT の指定

テーブル デザイン

テーブルは、Access と SQL Server の両方で同じように表現されます。どちらのデータベース管理システム (DBMS) もリレーショナルであるため、関連データは、一意識別子でリンクされた論理テーブルに格納されます。テーブル デザイン インターフェイスは、Access と SQL Server で同じです (図 13 参照)。

図 13. Access と SQL Server で同様のテーブル デザイン

リレーションシップ

Access では、一方のテーブルの値が変化したときに関連するテーブルの値が自動的に更新 (連鎖更新) されるように、テーブルのフィールドにルールを指定できます。

SQL Server では、Enterprise Manager のダイアグラム デザイナを使用して同じルールを作成できます (図 14 参照)。SQL Server では、次の 5 つの制約クラスがサポートされます。

• NOT NULL - 列に NULL 値を含められないことを指定します。
• CHECK - 列に入力できる値を制限します。次のコードは、値が 10,000 ～ 1,000,000 になるように CHECK 制約を Salary フィールドに追加する Employee テーブルを作成します。
  コピー

  CREATE TABLE Employee
      (
      EmployeeID          int        PRIMARY KEY,
      Name               char(50),
      Address          char(50),
      Salary         money,
      CONSTRAINT chk_Salary CHECK (Salary BETWEEN 10000 and 1000000)
         )
  

• UNIQUE - テーブル内のある列のすべての値が一意になるようにします。この制約は一般に ID 列に使用します。
• PRIMARY KEY - 値がテーブル内の行を一意に識別する列または列セットを識別します。
• FOREIGN KEY - テーブル間のリレーションシップを設定します。次のコードは、前に作成した Employee テーブル内の EmployeeID を参照する EmployeePosition テーブルを作成します。
  コピー

  CREATE TABLE EmployeePosition
      (
      EmployeePositionID    int        PRIMARY KEY,
      EmployeeID           int        FOREIGN KEY
                                       REFERENCES Employee(EmployeeID)
           ON DELETE CASCADE
      Position          char(50)
      )
  

  

  図 14. SQL Server は Access と同様のリレーションシップをサポートする

ON DELETE 句には、次の 2 つのオプションがあります。

• CASCADE - 従業員のレコードが Employee テーブルから削除された場合に、EmployeePosition テーブル内の対応する EmployeeID を持つレコードも削除するように指定します。
• NO ACTION - Employee テーブル内の被参照親レコードが削除された場合に、EmployeePosition のレコードが影響を受けないように指定します。

また、SQL Server では、ON UPDATE 句もサポートされます。この句では、親レコードが更新された場合に実行する操作を指定します。CASCADE オプションと NO ACTION オプションもサポートされます。

SQL Server でのリレーションシップは、Access の場合ほど柔軟ではないことに注意してください。Access では、次の処理を実行できます。

• あるテーブルからそのテーブル自体への連鎖、更新、または削除更新
• Required プロパティが Yes に設定されているテーブル内の外部キーの連鎖、更新、または削除更新

SQL Server ではこれらの 2 つのオプションはサポートされませんが、そのため、データベースがより堅牢になり、リレーションシップとキーの問題が発生しにくくなっています。

循環参照の連鎖更新がサポートされない

Access とは異なり、SQL Server では循環参照整合性は許可されません。たとえば、会社の営業 (sales) 部門に上級 (senior) 従業員がいるとします。データベースでは、従業員の Employee Type は Senior であり、Category は Sales です。しかし、同じデータベースで、Employee Type Senior は Sales Category にあります。図 15 に示すように、これを許可するデータベース構造では "循環参照" が作成されますが、SQL Server では循環参照が許可されません。循環更新制約を作成しようとすると、次のようなエラーが発生します。

リレーションシップ 'FK_EmployeeType_Employee' を作成できません。  
ODBC エラー : [Microsoft][ODBC SQL Server Driver][SQL Server]
テーブル 'EmployeeType' に設定しようとしている FOREIGN KEY 制約 'FK_EmployeeType_Employee' は
パスが循環するか、複数に連鎖する可能性があります。ON DELETE NO ACTION、ON UPDATE NO ACTION、
を指定するか、ほかの FOREIGN KEY 制約を変更してください。
[Microsoft][ODBC SQL Server Driver][SQL Server]制約を作成
できませんでした。以前のエラーを調べてください。

そのため、あるフィールドがいずれか 1 つのテーブルで更新されると、無限ループが発生する可能性があります。この例では、ある CategoryID フィールドを更新すると、(連鎖更新参照整合性に基づいて) 次の CategoryID フィールドが更新され、それによって次の CategoryID フィールドが更新され、終わりなく続きます。

図 15. 循環連鎖更新制約は SQL Server のエラーの原因となる

SQL Server でこの問題を回避するには、テーブルから参照整合性制約を削除し、更新を実行するトリガを各テーブルに作成する必要があります。トリガの使用の詳細については、「トリガを使用したビジネス ルールの実施」を参照してください。

インデックス作成を強化する

Access では、テーブル内の 1 つまたは多数のフィールドに、"複合キー" と呼ばれるインデックスを作成できます。

SQL Server では、ほぼ同じ方法でインデックス作成を処理します。インデックスが作成されたテーブルは、実際にはハード ディスク上で並べ替えられてから格納されます。この処理は、"クラスタリング" と呼ばれます。クラスタリングは、クラスタ化インデックスに基づくハード ディスク上のデータの SQL Server による並べ替えと格納を表します。フィールドのインデックスが作成され、クラスタ化されていない場合、SQL Server は最初にインデックスをクエリしてデータを探す必要があるため、パフォーマンスが低下することがあります。

たとえば、Employees テーブルに、EmployeeID という一意識別子があるとします。ただし、このテーブルは主に FirstName フィールドに基づいて検索されます。EmployeeID フィールドにインデックスを定義し、clustered プロパティを true に設定することで、FirstName 列に対するデータ アクセスが最適化されます (図 16 参照)。クラスタ化されたため、このフィールドのデータは並べ替えられた順序でハード ディスクに物理的に格納され、データ アクセスの効率が上がります。

図 16. パフォーマンスを向上させるために SQL Server でクラスタリングを使用するようにテーブル インデックスを設定する

Access クエリと SQL Server ビュー

SQL Server のビューは、Access のクエリと同様です (図 17 と図 18 参照)。これらを使用すると、複数のテーブルや他のビューから照合される可能性のある、フィルタ処理されたデータ セットを指定できます。

ビューは、セキュリティ関連の問題を処理するのに役立ちます。たとえば、ユーザーのグループが製品の注文に関する情報を表示でき、支払いにリンクされているクレジット カード詳細情報を表示できないようにするには、次の手順に従います。

1. 機密性のないフィールドのみ注文テーブルから取得するビューを作成します。
2. ユーザーのグループによる注文テーブルへのアクセスをすべて拒否します。
3. ユーザーのグループによるビューへのアクセスを許可します。

   

   図 17. Access のクエリ

   

   図 18. SQL Server のビュー

ビューはクエリとは異なり、インデックス作成も利用できるため、アプリケーションのパフォーマンスが大幅に改善される可能性がありますが、クエリは一定の結合や集計を頻繁に実行します。インデックス付きビューによってインデックスをビューに作成でき、ビューの結果セットはデータベース内で並べ替えられ、インデックスが作成されます。

Access クエリと SQL Server ストアド プロシージャ

SQL Server では、ストアド プロシージャを使用してデータのクエリや計算を実行します。ストアド プロシージャの主な利点は、初回実行時にコンパイルされる点です。つまり、SQL Server によって、ストアド プロシージャを実行するための最適な方法が計算され、この "実行プラン" がメモリに格納されます。クエリを実行する最適なパスが既に決定されているため、後続のストアド プロシージャの実行速度は大幅に向上します。

Access クエリが Access で編集されるのと同様に、ストアド プロシージャは SQL Server Enterprise Manager 内で作成および変更されます (図 19 参照)。ストアド プロシージャは、入力パラメータを受け入れるという点で Access クエリに類似しています。

図 19. データのクエリおよび計算を実行するストアド プロシージャ

ストアド プロシージャは T-SQL で記述され、条件ロジックや計算を使用してデータの変更や返却、または別の関数の実行が可能であるため、Access クエリよりも有利です (図 20 参照)。

図 20. T-SQL を使用してクエリ内で条件ロジックや計算を実行する

SQL Server では、ストアド プロシージャのデバッグも可能であり、複雑なビジネス ロジックが含まれたストアド プロシージャを使用する場合に役立ちます。デバッガは、ブレークポイントの設定、ウォッチ式の定義、および 1 ステップずつのプロシージャの作成をサポートします (図 21 参照)。

図 21. SQL Server の高度なクエリのデバッグ

Access クエリと SQL Server ユーザー定義関数

SQL Server の組み込み関数に加えて、T-SQL ステートメントのカスタム ブロックを指定することもできます。これらは、"ユーザー定義関数 (UDF)" と呼ばれます。UDF は、プログラミング言語で関数とほぼ同じ方法で実装され、コードの再利用とビジネス ロジックのカプセル化を可能にする強力な機能です。UDF は、単一 (スカラ) 値またはテーブルを返すことができます。

スカラ UDF

たとえば、金額を受け取って、税の計算を実行し、税込み価格を返す UDF を作成できます。税の計算を必要とする任意のストアド プロシージャで、この関数を呼び出すことができます。

table 型 UDF

SQL Server 2000 では、関数からデータ テーブルを返すことが可能な table データ型が導入されました。UDF での table データ型の使用は、物理テーブルを作成および削除してデータのサブセットにクエリを実行するよりもはるかに効率的です。メモリに格納されたデータを操作するため、ディスクにアクセスする必要がありません。

ユーザー定義関数の詳細については、「ユーザー定義関数」を参照してください。

テーブルおよびビューのトリガ

SQL Server では、"トリガ" のサポートが追加されました。トリガは、テーブルのデータが更新、削除、または挿入されたときに実行されるストアド プロシージャです。特定の行またはフィールドが更新されるとトリガを実行するように設定できます。トリガは、参照整合性の設定のために使用でき、制約に類似しています。ただし、制約はトリガよりも効率的であるため、できる限り制約を使用します。

トリガを使用して、テーブルのデータが変更されたときにカスタム アクションを実行できます。たとえば、挿入または更新されたデータと別のテーブルにある別のフィールド内のデータとを比較し、対応するデータを更新するか、カスタム エラー メッセージを表示するようにトリガを設定できます。トリガを使用したビジネス ルールの適用の詳細については、「トリガを使用したビジネス ルールの実施」を参照してください。

トリガは、SQL Server Enterprise Manager を使用して、または Visual Studio .NET データベース プロジェクト内で作成できます (図 22 参照)。

図 22. Visual Studio .NET データベース プロジェクトで作成されたトリガ

スケーラビリティとパフォーマンス

SQL Server では、増加するビジネス要求に対応するデータベース ソリューションの拡張性が Access よりも大幅に強化されています。また、クライアント/サーバー アーキテクチャの改良により、処理の負荷が分散され、パフォーマンスがさらに向上しました。

多数の同時ユーザーのサポート

Access でサポートされる同時ユーザーの最大数は 255 であるため、エンタープライズレベルのデータ ストレージ ソリューションには適していません。通常、運用環境では、わずか 20 人のユーザーがネットワーク上で Access データベースの同時使用を試みただけで重大なパフォーマンスの問題やデータ破損が発生します。

SQL Server では、使用可能なシステム メモリだけに制限される同時ユーザー ベースをサポートします。最適化されたクエリ処理エンジンを搭載し、複数のコンピュータ、プロセッサ、およびハード ディスクの同時使用が可能であるため、すべてのエンタープライズ要件に対応する拡張性があります。

大規模なデータベースのサポート

Access では、2 GB にリンク テーブルを加えた最大データベース サイズをサポートします。理論上、リンク テーブルの使用によって、より多くのデータの格納が可能になりますが、処理するデータ量が増えるため、パフォーマンスやネットワークの問題が発生することがよくあります。詳細については、この記事の前半の「エンジンの実装」を参照してください。

SQL Server のストレージ機能は大幅に強化され、1,048,516 TB のデータを複数のデバイスに効率的に格納できるようになりました。

すべてのデータベース アクティビティを記録するログ ファイル

SQL Server は、すべてのトランザクション (データベースの更新、挿入、および削除) がログ ファイルに記録されるという点で、Access よりも有利です。このログには、データに対する変更が記録され、各トランザクション中に行われた変更を後から必要に応じて元に戻すための情報が保持されます。

Lumigent Log Explorer などのツールを使用すると、SQL Server トランザクション ログを詳しく調査し、手動でトランザクションを元に戻すことができます (図 23 参照)。詳細については、Lumigent の Web サイトを参照してください。

図 23. Lumigent Log Explorer は、過去のすべてのトランザクションを表示して、SQL Server データベースに対する完全な制御を提供する

複数のデバイスに分割されたデータベースおよびログ ファイル

Access データベースは、単一の .mdb ファイルとして格納されます。そのため、Access データベースの格納と実行が可能なのは 1 台のマシンのみです。この場合、処理能力と記憶領域が 1 台のデータベース サーバー上のハードウェアに制限されるため、データベースおよびユーザー ベースの規模が大きくなると、問題が発生する可能性があります。

SQL Server のデータベースは、SQL Server によって管理される物理ファイルのグループです。これらのファイルは、最小限、"トランザクション ログ ファイル" (拡張子 .ldf) と "プライマリ データ ファイル" (拡張子 .mdf) で構成されます。SQL Server データベースには、1 つ以上の "セカンダリ データ ファイル" (拡張子 .ndf) を含めることもできます。プライマリ データ ファイルは、データベースの出発点として使用され、データやセカンダリ データ ファイルへの参照を含みます。

大規模なデータベースを使用する場合は、トランザクション ログと複数のデータ ファイルを別々のコンピュータ上に格納すると、複数のコンピュータの処理能力を利用できるようになります。また、複数のコンピュータまたはハード ディスクの記憶領域を使用することもできます。

さらに強力なクエリ

• Access 開発者がクエリ、フォーム、またはクエリに基づいたレポートの実行を試みると、"メモリ不足" または "クエリが複雑すぎる" というエラーが発生することがあります。通常、このエラーは、実行しようとしているクエリに含まれるテーブル結合数が、Access で処理可能な数を超えている場合に発生します。この問題を回避するために、Access 開発者は、多くの場合、クエリの再デザインやテーブルの再作成でリソースの浪費を余儀なくされます。

SQL Server は、柔軟性の高いクエリをサポートするように再設計されています。単一のクエリの中では、最大で次の容量を使用できます。

• SELECT ステートメントで 256 個のテーブル
• 約 256 KB のクエリ テキスト
• SELECT ステートメントで 4,096 列

また、Access ではサブクエリのネストが最大 50 個サポートされるのに対して、SQL Server では最大 32 個である点にも注意してください。

データを処理する

Access でのデータ クエリの作成は、SQL Server とは異なります。違いは、クエリ言語とクエリ デザイナです。また、SQL Server では、柔軟かつ効率的な方法でデータ クエリを格納する "ストアド プロシージャ" や "ユーザー定義関数" もサポートされ、ビジネス ロジックの再利用を円滑に行うことができます。さらに、SQL Server には、Access よりも強力な障害復旧モデルが用意されています。

データのクエリを実行する

クエリの最適化

Access でデータのクエリをリモートで実行した場合は、すべてのデータがクライアントに返され、フィルタ処理と並べ替えはすべてクライアント側で行われます。通常は、クライアントからネットワーク経由で SQL Server のデータに対するクエリが実行されるため、ネットワーク帯域幅の重大な問題が発生する可能性があります。そのため、バック エンドを SQL Server に移行する場合は、データ セット全体を返すのではなく、必要なデータ セットだけをクライアントに返すようにクエリを再デザインすることが重要です。たとえば、Access フォームでは、次のようなクエリが実行されます。

SELECT * FROM Customers

上記のクエリは、フォームを開いたときに、Customers テーブル全体を返します。SQL Server では、現在のレコードだけを返すようにこのクエリを最適化する必要があります。最適化後の SQL クエリは、次のようになります。

SELECT * FROM Customers WHERE CustomerID = 'C00010'

このクエリは、1 つの行/レコードだけを返します。ユーザーがフォーム内の前後のレコードに移動するたびに CustomerID が変更されるため、現在のレコードを取得するには、データベースに再度クエリを実行する必要があります。

このようにサーバー側でフィルタ処理する方法では、データベース サーバー上でフィルタ処理と並べ替えが実行されて必要最小限のデータだけが返されるので、ネットワーク トラフィックの削減に役立ちます。

クエリの種類

Access では、データに対してクエリの表示とデザインを行うためのいくつかの方法が用意されています。表 5 は、Access に組み込まれているクエリの種類を SQL Server に移行するときに使用可能なオプションを示しています。

表 5. Access から SQL Server へのクエリの変換オプション

図 24. SELECT クエリのデザインにおける Access と SQL Server の類似性

クエリ言語の機能

表 6 は、Access と SQL Server でサポートされているクエリ言語の機能の主な違いを示しています (『Access 2002 Desktop Developer's Handbook』(Paul Litwin その他著、2001 年、SYBEX Inc.) からの抜粋)。

表 6. Access と SQL Server のデータ クエリの違い

SQL Server での Access クエリのデザインの詳細については、「Migrating Your Access Database to Microsoft SQL Server 7.0 (英語)」を参照してください (注: この記事は SQL Server 7.0 用に記述され、更新されていません)。

オブジェクトのスクリプトを記述する機能

SQL (Structured Query Language) は、データのアクセスと操作のために Access および SQL Server で使用する標準言語です。SQL 言語の最新リビジョンは、完成した年に由来して SQL-92 と呼ばれています。マイクロソフトでは、2 つの DBMS ソリューション間で異なる独自の拡張機能を基本 SQL 言語に対して追加しました。

Access は、SQL-92 に加えて Jet 4 ANSI-92 の拡張機能をサポートし、SQL を使用したトランザクション管理のサポートが追加されています。

Jet 4 ANSI-92 の拡張機能は、簡単なデータベース セキュリティ管理もサポートします。ただし、データベース オブジェクト所有権の設定や変更などの一部の機能は、サポートされません。

SQL Server 2000 では、基本の SQL-92 言語に対してマイクロソフト独自の拡張機能が追加されています。これらの拡張機能は、次のような重要な機能へのスクリプト サポートを追加します。

• ストアド プロシージャ
• 分散トランザクション
• オペレーティング システム機能
• 柔軟性の高いサブクエリ
• クエリの別名
• データのバックアップと復元

T-SQL 言語は、標準の SQL コマンド セットへの強力な拡張機能です。次の各操作に必要なすべての機能を提供します。

• データベース テーブルのデータの取得、変更、削除、および追加
• パラメータの受け取りおよび戻り
• 計算の実行
• 組み込み関数とユーザー定義関数の実行
• サーバー間でのデータのコピー

T-SQL は、データ クエリを条件ロジックや計算と組み合わせることが可能であるという点で、Access クエリと VBA を足したものに類似しています。

SQL Server は SQL-92 標準を完全にサポートするので、拡張機能の使用は必須でない点に注意してください。

テーブル変数: 複雑なクエリに役立つ

Access で、結合テーブル セットに対して計算を実行する場合は、結合を定義するクエリを作成します。そのデータを使用するアプリケーションでは、SQL SELECT ステートメントでクエリを使用するたびに、すべてのテーブルを再結合する必要があり、特に複数のユーザーが使用している環境では、リソースの消費が大きくなる可能性があります。

たとえば、名前が "A" という文字で始まるすべての顧客を削除し、顧客のすべての注文と注文履歴を削除する場合、Access では次の操作を実行します。

1. 必要なすべての顧客 ID を取得する SELECT クエリを作成します。
   コピー

   SELECT Customers.CustomerID
   FROM Customers
   WHERE Customer.FirstName LIKE 'A%'
   

2. 上記の SELECT クエリを 3 つの DELETE クエリの中にラップして、必要な顧客、注文、および注文履歴をすべて削除します。
   コピー

   DELETE FROM Orders
   WHERE Orders.CustomerID IN 
   (
   SELECT Customers.CustomerID
   FROM Customers
   WHERE Customer.FirstName LIKE 'A%'
   )
   And
   DELETE FROM OrderHistory
   WHERE OrderHistory.CustomerID IN 
   (
   SELECT Customers.CustomerID
   FROM Customers
   WHERE Customer.FirstName LIKE 'A%'
   )
   
   And
   DELETE FROM Customers
   WHERE Customer.FirstName LIKE 'A%'
   

この方法は、削除操作のたびに、リソースを大量に消費する LIKE フィルタを顧客テーブルで実行する必要があるため、効率的な処理ではありません。このようなワイルドカード文字の WHERE フィルタを実行すると、顧客テーブルが数百万レコードに増加したとき、重大なパフォーマンス問題が生じます。

この操作を効率的に実行するには、SQL Server に用意されている "テーブル変数" 機能を使用します。テーブル変数は、SQL 構文で通常のテーブルのように使用されます。ただし、ハード ディスクではなく、メモリに一時的に保存されるという点で、通常のテーブルとは異なります。メモリ アクセスは、ハード ディスク アクセスよりもはるかに高速なため、フィルタ処理または結合された同じデータ セットに対して複数の操作を実行する場合は、テーブル変数が役立ちます。

テーブル変数を使用して上記の例を実装するには、次の手順を実行します。

1. テーブルを宣言します。
   コピー

   DECLARE @tmpCustomerIDs TABLE (CustomerID nvarchar(50))
   

2. フィルタ処理されたレコード セットを取得し、テーブル変数に格納します。
   コピー

   INSERT INTO @tmpCustomerIDs (CustomerID)
   (SELECT CustomerID FROM Customers WHERE Customers.ContactName LIKE 'A%')
   

3. テーブル変数の値を使用して、顧客、注文、注文履歴に対するすべての削除操作を実行します。
   コピー

   DELETE FROM Orders
   WHERE Orders.CustomerID IN 
   (
   SELECT CustomerID
   FROM @tmpCustomerIDs
   )
   And
   DELETE FROM OrderHistory
   WHERE OrderHistory.CustomerID IN 
   (
   SELECT CustomerID
   FROM @tmpCustomerIDs
   )
   And
   DELETE FROM Customers
   WHERE Customers.CustomerID IN
   (
   SELECT CustomerID
   FROM @tmpCustomerIDs
   )
   

一時テーブルは、SQL Server で提供されたもう 1 つのメカニズムであり、動的データ セットの操作を効率的に実行します。テーブル変数とは違って、一時テーブルはメモリ内に長く存在するため、データやログ リソースで必要なロックが増加する可能性があります。

システム障害からの復旧

ほとんどの Access 開発者は、壊れたデータベースを開こうとして、"Unrecognized database format" (データベースの形式を認識できません) というエラーを経験したことがあります (図 25 参照)。オペレーティング システム エラーや停電などのシステム障害が発生した場合は、次のオプションがあります。

• Access の最適化/修復ツールを使用して破損した .mdb ファイルのデータ復旧を試み、復旧したデータを空のデータベースにインポートして破損レコード数を最小限にします。これはフェールセーフを実現する手順ではなく、データが失われる可能性があります。
• 最新のバックアップから復元します。ただし、失われたデータの再入力のために使用するリソースの浪費になります。
• Jet 最適化ツール Jetcomp.exe を実行します。多くの場合は、最適化/修復ツールを実行するよりも効果的です。ただし、壊れているデータがないという保証はありません。
• 独自の方法を使用してデータベースのデータを抽出する第三者データベース復旧業者に、破損したデータベースを提出します。この方法は、費用がかかるうえ、外部関係者がデータを処理するため、セキュリティに影響を及ぼす可能性があります。

  

  図 25. Access データベースが壊れて開くことができない場合のエラー

SQL Server では、データの復旧を詳細に制御できます。SQL Server データベースごとに、3 とおりの復旧モデルのいずれかを選択して、データのバックアップ方法およびデータ損失をどの程度まで保護するかを決定できます。次の 3 とおりの復旧モデルがあります。

• 単純復旧 - 最新のバックアップの時点までデータベースを復旧できます。
• フル (完全) 復旧 - 障害が発生した時点までデータベースを復旧できます。このモデルは、最も多くのシステム リソースとディスク容量を必要とします (ログのため)。
• 一括ログ記録復旧 - 最後に行われたログのバックアップの時点までデータベースを復旧できます。フル (完全) 復旧モデルに比べて、必要なシステム リソースとディスク容量は少なくなりますが、データを手動で再入力する可能性が高くなります。
• これらの復旧モデルは、使用可能なシステム リソースと釣り合いを取ってシステム障害から復旧するための最善の方法を選択する柔軟性を提供します。

ヒント: Access バックアップに比べて SQL Server データ バックアップの大きな利点は、データベースの実行中にバックアップを実行でき、ユーザーがログオフする必要がない点です。これによって、データベースの可用性が高まり、アップタイムが飛躍的に向上します。

データベースを比較する

Access で、運用中のデータベースに最新の構造変更を加えてデータベースを最新状態で維持することは、継続的なプロジェクトです。データベースをすばやくオフラインにして構造的な変更を加え、データを変換する必要がありますが、ユーザーがシステムを使用中の場合は、困難になる可能性があります。新しいフィールドやリレーションシップが追加されている可能性があるため、データの変換に時間がかかる場合もあります。

一般に、Access データベースに構造的な変更を加えるには、次の作業を行います。

1. 開発者はアプリケーション データベースを使用して作業し、データが含まれているデータベースに構造的な変更を加えます。
2. データが含まれているデータベースへの変更を追跡し、更新クエリ、DAO、または ADO コードを記述して更新を実行します。
3. 開発が完了したら、データベースをオフラインにして手動で更新を実行します。

SSW Data Renovator などのサードパーティ製アプリケーションを使用すると、システムを使用できないことの不便さを最小限に抑え、処理の一部を自動化して間違いの可能性を減らすのに役立ちます。SSW Data Renovator は、新しいデータベースと運用中のデータベースを比較し、両者のすべての違いについてレポートを生成し、新しい構造にデータを自動的に移動するウィザード形式のインターフェイスを提供します。

SQL Server には、構造の更新のためにデータベースをオフラインにする必要がないという利点がありますが、データベース管理者は次のことを実行する必要があります。

• すべてのデータベース スキーマを分析し、構造の変更のためにログを変更します。
• ターゲット データベースに移行するための移行スクリプトを手動で作成します。

Red-Gate SQL Compare や SSW SQL Deploy などのサードパーティ製ユーティリティは、次の処理を実行して、この作業の自動化に役立ちます。

• 2 つのデータベース内のストアド プロシージャ、リレーションシップ、テーブル、ビュー、およびユーザー定義関数を含むすべてのオブジェクトを比較します。
• すべての相違点をレポートします。
• ターゲット データベース上で直接に実行可能な移行スクリプトを生成します。

まとめ

Microsoft SQL Server 2000 は、Microsoft Access 2002 と比較して、スケーラビリティ、メンテナンス、およびデータベース復旧機能が大幅に改善されたエンタープライズ レベルのデータベース ソリューションです。SQL Server はクライアント/サーバー アーキテクチャに基づいているため、リモート接続でのデータの処理および送信方法が Access とはかなり異なります。また、SQL Server には、データのクエリ、ビジネス ロジックの再利用、およびデータのバックアップをより簡単で柔軟に実行するための多数の機能があります。

用語集

ADO.NET

Microsoft .NET Framework に付属のデータ アクセス モデル。スケーラビリティ、ステートレス、および XML を考慮して、Web のために特別に設計されています。

クライアント/サーバー アーキテクチャ

ソフトウェア アーキテクチャの 1 つで、複数のクライアントが要求を出し、中央のサーバーまたはサーバー グループから結果を受け取ることによって、スケーラビリティを促進します。処理の負荷は、クライアントとサーバー間で分担します。

クラスタリング

ハード ディスク上で直接にデータのインデックス付けと並べ替えを行って、データ クエリの速度を大幅に向上する方法。

データ変換サービス

SQL Server に付属のツール。このツールを使用すると、Microsoft Excel などの OLE DB ベースのアーキテクチャを使用したさまざまなデータ ソース間でのデータのインポートおよびエクスポートが可能になります。

OLAP

オンライン分析処理。さまざまな観点からビジネス データを分析するのに役立つデータ ストレージ モデル。たとえば、OLAP を使用して、特定の地域で特定の期間中に一定の価格以上で販売されたすべての製品を表示できます。

SQL Server Enterprise Manager

SQL Server に付属のツール。データベース オブジェクト、ユーザー、バックアップ、およびデータベースのアクセス許可を簡単に管理できます。

SQL Server プロファイラ

SQL Server に付属のツール。処理に時間のかかるデータベース トランザクションやリソースを集中的に使用するデータベース トランザクションを識別して、クエリの最適化に役立ちます。

SQL Server クエリ アナライザ

SQL Server に付属のツール。データベース クエリの記述およびデバッグが可能になります。

T-SQL

Transact-SQL。SQL-92 クエリ言語標準の拡張機能。ストアド プロシージャ、データのバックアップと復元、分散トランザクションなど、SQL Server の拡張機能を提供します。

UDDT

ユーザー定義データ型。既存の SQL Server 基本データ型に基づいて、独自のデータ型の作成を可能にする SQL Server の機能。UDDT は、より厳しいビジネス ルールのデータへの適用を促進します。

UDF

ユーザー定義関数。データベース アプリケーション全体でのビジネス ロジックの再利用を促進する T-SQL ステートメントのカスタム ブロック。

Visual Studio .NET

さまざまな Microsoft .NET 接続アプリケーションのビジュアルな構築を可能にする統合開発環境 (IDE)。.NET に対応した Web および Windows アプリケーションの設計、構築、テスト、および配置のための強力なツールを提供します。

XML

Extensible Markup Language。広く採用された標準的な方法によって、テキストおよびデータを多くのユーザー操作やマシン操作なしに処理可能な形式で表現します。

詳細について

Microsoft SQL Server 製品ページ

著者紹介

Adam Cogan は、Office および .NET ベースのソリューションを専門とするマイクロソフト認定パートナーである SSW の主任設計者です。SSW で Adam は SQL Server 2000、.NET、Office 2003 などの Microsoft テクノロジを使用して、さまざまな業種にわたってビジネス用のカスタム ソリューションを開発しています。また、シドニーの Microsoft .NET ユーザー グループを運営し、地域の INETA 管理に積極的に関与しています。