誰にでも限界はありますが、Access データベースも例外ではありません。 たとえば、Access データベースには 2 GB のサイズ制限があり、255 人を超える同時ユーザーをサポートすることはできません。 そのため、Access データベースを次のレベルに移動する場合は、SQL Server に移行できます。 SQL Server (オンプレミスまたは Azure クラウド内) は、JET/ACE データベース エンジンよりも大量のデータ、より多くの同時使用ユーザーをサポートし、より大きな容量を備えています。 このガイドは、SQL Server の導入をスムーズに開始できるようにし、作成した Access フロントエンド ソリューションを維持するのに役立ち、将来のデータベース ソリューションに Access を使用する動機付けとなることを願っています。 Microsoft SQL Server Migration Assistant (SSMA) を使用して正常に移行するには、次の段階に従います。
始める前に
以降のセクションでは、作業を開始するのに役立つ背景情報とその他の情報を示します。
データベースの分割について
すべての Access データベース オブジェクトは、1 つのデータベース ファイル内に格納することも、フロントエンド データベースとバックエンド データベースの 2 つのデータベース ファイルに格納することもできます。 これは データベースの分割 と呼ばれ、ネットワーク環境での共有を容易にするように設計されています。 バックエンド データベース ファイルに含める必要があるのは、テーブルとリレーションシップのみです。 フロントエンド ファイルには、フォーム、レポート、クエリ、マクロ、VBA モジュール、バックエンド データベースへのリンク テーブルなど、他のすべてのオブジェクトのみを含める必要があります。 Access データベースを移行する場合、SQL Server がサーバー上に現在存在するデータの新しいバックエンドとして機能するという点で、分割データベースに似ています。
結果として、SQL Server テーブルへのリンク テーブルを含むフロントエンド Access データベースを引き続き維持できます。 事実上、Access データベースが提供する迅速なアプリケーション開発の利点と SQL Server の拡張性を引き出すことができます。
SQL Server の利点
SQL Server への移行についてまだ説得力が必要ですか? 考慮すべきその他の利点は次のとおりです。
- より多くの同時ユーザー SQL Server は、Access よりも多くの同時ユーザーを処理でき、より多くのユーザーが追加された場合のメモリ要件を最小限に抑えます。
- 可用性の向上SQL Server を使用すると、データベースの使用中に動的に増分バックアップまたは完全バックアップを行うことができます。 その結果、データをバックアップするために、ユーザーにデータベースへのアクセスを強制的に終了してもらう必要はありません。
- 高いパフォーマンスと拡張性通常、SQL Server データベースは、特に大きなテラバイト サイズのデータベースの場合、Access データベースよりも優れたパフォーマンスを発揮します。 また、SQL Server は、1 つのプロセス内で複数のネイティブ スレッドを使用してユーザー要求を処理することで、クエリを並列処理することで、クエリをはるかに高速かつ効率的に処理します。
- セキュリティの改善信頼できる接続を使用して、SQL Server は Windows システム セキュリティと統合され、ネットワークとデータベースへの単一の統合アクセスを提供し、両方のセキュリティ システムの長所を使用します。 これにより、複雑なセキュリティ スキームの管理がはるかに簡単になります。 SQL Server は、社会保障番号、クレジット カード データ、機密アドレスなどの機密情報を保存する理想的なストレージです。
- 即時回復可能性オペレーティング システムがクラッシュしたり、電源が停止したりした場合、SQL Server はデータベース管理者の介入なしに、データベースをわずか数分で整合性のある状態に自動的に回復できます。
- VPN の使用法 Access と仮想プライベート ネットワーク (VPN) がうまくいかない。 ただし、SQL Server を使用すると、リモート ユーザーはデスクトップ上の Access フロントエンド データベースと、VPN ファイアウォールの背後にある SQL Server バックエンドを引き続き使用できます。
- Azure SQL Server SQL Serverの利点に加えて、ダウンタイムのない動的なスケーラビリティ、インテリジェントな最適化、グローバルなスケーラビリティと可用性、ハードウェア コストの排除、および管理の削減を提供します。
最適な Azure SQL Server オプションを選択する
Azure SQL Server に移行する場合は、次の 3 つのオプションから選択でき、それぞれに異なる利点があります。
- 単一データベース/エラスティック プールこのオプションには、SQL Database サーバーを通じて管理される独自のリソース セットがあります。 1 つのデータベースは、SQL Server の包含データベースのようなものです。 エラスティック プールを追加することもできます。これは、SQL Database サーバーを介して管理される共有リソース セットを持つデータベースのコレクションです。 最も一般的に使用される SQL Server 機能は、組み込みのバックアップ、パッチ適用、回復で使用できます。 ただし、正確なメンテナンス時間は保証されておらず、SQL Server からの移行は難しい場合があります。
- マネージド インスタンスこのオプションは、共有リソース セットを含むシステム データベースとユーザー データベースのコレクションです。 マネージド インスタンスは、オンプレミスの SQL Server との高い互換性を持つ SQL Server データベースのインスタンスのようなものです。 マネージド インスタンスには、バックアップ、パッチ適用、復旧が組み込まれており、SQL Server から簡単に移行できます。 ただし、利用できない SQL Server 機能が少数あり、正確なメンテナンス時間も保証されません。
- Azure 仮想マシン このオプションを使用すると、Azure クラウドの仮想マシン内で SQL Server を実行できます。 SQL Server エンジンを完全に制御でき、容易な移行パスを利用することができます。 ただし、バックアップ、パッチ、リカバリを管理する必要があります。
詳細については、「Azureへのデータベース移行パスの選択」および「Azure SQLとは」を参照してください。
最初の手順
SSMA を実行する前に移行プロセスを合理化するために、前もって対処できる問題がいくつかあります。
- テーブル インデックスと主キーを追加する Access の各テーブルにインデックスと主キーがあることを確認します。 SQL Server では、すべてのテーブルに少なくとも 1 つのインデックスが必要であり、リンク テーブルは主キーである必要があります (テーブルが更新可能な場合)。
- 主キーと外部キーの関係を確認する これらのリレーションシップが、一貫性のあるデータ型とサイズを持つフィールドに基づいていることを確認します。 SQL Server では、外部キー制約で異なるデータ型とサイズを持つ結合列はサポートされていません。
- 添付ファイル列を削除する SSMA は、添付ファイル列を含むテーブルを移行しません。
SSMA を実行する前に、次の最初の手順を実行します。
- Access データベースを閉じます。
- データベースに接続している現在のユーザーもデータベースを閉じていることを確認します。
- データベースが .mdbファイル形式の場合は、 ユーザー レベルのセキュリティを削除します。
- データベースをバックアップします。 詳細については、「 バックアップと復元プロセスによるデータの保護」を参照してください。
ヒントデスクトップに Microsoft SQL Server Express Edition をインストールすることを検討してください。これは最大 10 GB をサポートし、移行を無料で簡単に実行してチェックする方法です。 接続するときは、 データベース インスタンスとして LocalDB を使用します。
ヒント 可能であれば、スタンドアロン バージョンの Access を使用します。
SSMA の実行
Microsoft では、移行を容易にするための Microsoft SQL Server Migration Assistant (SSMA) を提供しています。 SSMA は主にテーブルを移行し、パラメーターなしでクエリを選択します。 フォーム、レポート、マクロ、VBA モジュールは変換されません。 SQL Server メタデータ エクスプローラーには、Access データベース オブジェクトと SQL Server オブジェクトが表示され、両方のデータベースの現在の内容を確認できます。 これらの 2 つの接続は、今後追加のオブジェクトを転送する場合に備えて、移行ファイルに保存されます。
注釈 移行プロセスは、データベース オブジェクトのサイズと転送する必要があるデータの量によっては、時間がかかる場合があります。
- SSMA を使用してデータベースを移行するには、まずダウンロードした MSI ファイルをダブルクリックして、ソフトウェア をダウンロード してインストールします。 お使いのコンピューターに適切な 32 ビット版または 64 ビット版がインストールされていることを確認します。
- SSMA をインストールした後、できれば Access データベース ファイルを含むコンピューターから、デスクトップで開きます。
また、共有フォルダー内のネットワークから Access データベースにアクセスできるコンピューターで開くこともできます。 - SSMA の最初の手順に従って、SQL Server の場所、移行する Access データベースとオブジェクト、接続情報、リンク テーブルを作成するかどうかなどの基本的な情報を入力します。
- SQL Server 2016 以降に移行し、リンク テーブルを更新する場合は、[ツールの確認]>[プロジェクト設定]>[全般] の順に選択して ROWVERSION 列を追加します。
ROWVERSION フィールドは、レコードの競合を回避するのに役立ちます。 Access では、SQL Server リンク テーブルのこの ROWVERSION フィールドを使用して、レコードの最終更新日時を特定します。 また、クエリに ROWVERSION フィールドを追加すると、更新操作の後に行が再度選択されるときにこのフィールドが使用されます。 これにより、元の送信とは異なる結果 (浮動小数点数データ型や列を変更するトリガーで発生する可能性があるなど) が検出されたときに発生する可能性のある書き込み競合エラーやレコード削除シナリオを回避できるため、効率が向上します。 ただし、フォーム、レポート、または VBA コードでは ROWVERSION フィールドを使用しないでください。 詳細については、「ROWVERSION」を参照してください。
注釈 ROWVERSION とタイムスタンプを混同しないようにします。 キーワード (keyword) の timestamp は SQL Server の ROWVERSION のシノニムですが、データ入力にタイムスタンプを付ける方法として ROWVERSION を使用することはできません。 - 正確なデータ型を設定するには、[レビュー ツール]>[プロジェクト設定]>[型マッピング] を選択します。 たとえば、英語のテキストのみを格納する場合は、nvarchar データ型ではなく varchar データ型を使用できます。
オブジェクトの変換
SSMA は Access オブジェクトを SQL Server オブジェクトに変換しますが、オブジェクトをすぐにコピーすることはありません。 SSMA には、移行する次のオブジェクトの一覧が用意されているため、SQL Server データベースに移動するかどうかを決定できます。
- テーブルと列
- [パラメーターなしのクエリ] を選択します。
- 主キーと外部キー
- インデックスと既定値
- 制約の確認 (長さ 0 の列プロパティを許可する、列の入力規則、テーブルの入力規則を許可する)
ベスト プラクティスとしては、SSMA 評価レポートを使用します。このレポートには、エラー、警告、情報メッセージ、移行の実行に必要な時間の見積もり、実際にオブジェクトを移動する前に実行する個々のエラー修正手順など、変換結果が表示されます。
データベース オブジェクトの変換では、Access メタデータからオブジェクト定義が取得され、同等の Transact-SQL (T-SQL) 構文に変換され、この情報がプロジェクトに読み込まれます。 その後、SQL Server または SQL Azure メタデータ エクスプローラーを使用して、SQL ServerまたはSQL Azure オブジェクトとそのプロパティを表示できます。
オブジェクトを SQL Server に変換、読み込み、移行するには、このガイドに従ってください。
ヒント Access データベースを正常に移行したら、後で使用するためにプロジェクト ファイルを保存して、テストまたは最終的な移行のためにデータを再度移行できるようにします。
リンク テーブル
Windows に付属するネイティブ SQL Server ドライバーを使用する代わりに、最新バージョンの SQL Server OLE DB ドライバーと ODBC ドライバーをインストールすることを検討してください。 新しいドライバーは高速であるだけでなく、以前のドライバーではサポートされていない Azure SQL の新機能をサポートしています。 変換したデータベースが使用される各コンピューターにドライバーをインストールできます。 詳細については、「Microsoft OLE DB Driver 18 for SQL Server」および「Microsoft ODBC Driver 17 for SQL Server」を参照してください。
Access テーブルを移行した後、データをホストするようになった SQL Server のテーブルにリンクできます。 Access から直接リンクすると、複雑な SQL Server 管理ツールよりも簡単にデータを表示できます。 リンクされたデータのクエリおよび編集は、SQL Server データベース管理者が設定したアクセス許可に応じて行うことができます。
注釈リンク プロセス中に SQL Server データベースにリンクするときに ODBC DSN を作成する場合は、新しいアプリケーションを使用するすべてのコンピューターで同じ DSN を作成するか、DSN ファイルに格納されている接続文字列をプログラムで使用します。
詳細については、「Azure SQL Server データベースへのリンクまたは Azure SQL Server データベースからのデータのインポートまたはリンク」を参照してください。
ヒント Access のリンク テーブル マネージャーを使って、テーブルを簡単に更新したり再リンクしたりすることを忘れないでください。 詳細については、「 リンク テーブルの管理」を参照してください。
テストと改訂
次のセクションでは、移行中に発生する可能性のある一般的な問題とその対処方法について説明します。
クエリ
Select クエリのみが変換されます。パラメーターを受け取る Select クエリなど、他のクエリは使用されません。 一部のクエリは完全に変換されない場合があり、SSMA は変換プロセス中にクエリ エラーを報告します。 T-SQL 構文を使用して変換しないオブジェクトを手動で編集できます。 構文エラーがある場合は、Access 固有の関数とデータ型を SQL Server に手動で変換することが必要になる場合もあります。 詳細については、「Access SQL と SQL Server TSQL の比較」を参照してください。
データ型
Access と SQL Server のデータ型は似ていますが、次の潜在的な問題に注意してください。
大きい数値 大きい数値データ型は、非通貨の数値を格納し、SQL bigint データ型と互換性があります。 このデータ型を使用すると、効率的に大きな数値を計算できますが、Access 16 (16.0.7812 以降) の .accdb データベース ファイル形式を使用する必要があり、64 ビット版の Access でのパフォーマンスが向上します。 詳細については、「 大きい数値データ型を使用する 」および「 64 ビット版または 32 ビット版の Office を選択する」を参照してください。
はい/いいえ既定では、Access の Yes/No 列は SQL Server のビット フィールドに変換されます。 レコードのロックを回避するには、ビット フィールドが NULL 値を許可しないように設定していることを確認します。 SSMA では、ビット列を選択して、 Allow Nulls プロパティを NO に設定できます。 TSQL では、 CREATE TABLE または ALTER TABLE ステートメントを使用します。
日付と時刻 日付と時刻には、いくつかの考慮事項があります。
データベースの互換性レベルが 130 (SQL Server 2016) 以上で、リンク テーブルに 1 つ以上の datetime 列または datetime2 列が含まれている場合、結果に #deleted メッセージがテーブルから返されることがあります。 詳細については、「 Access リンク テーブルからデータベースへのリンク テーブル SQL-Server #deleted を返す」を参照してください。
Access の日付/時刻データ型を使用して、日時データ型にマップします。 Access の日付/時刻の拡張データ型を使用して、日付と時刻の範囲が大きい datetime2 データ型にマップします。 詳細については、「日付/時刻の拡張データ型の使用」を参照してください。
SQL Server で日付をクエリするときは、日付だけでなく時刻も考慮してください。 次に例を示します。
- DateOrdered 2019/1/1 から 1/31/19 までの間には、すべての注文が含まれない場合があります。
- DateOrdered between 1/1/19 00:00:00 AM And 1/31/19 11:59:59 PM には、すべての注文が含まれます。
添付ファイル 添付ファイル データ型は、Access データベースにファイルを格納します。 SQL Server では、検討すべきオプションがいくつかあります。 Access データベースからファイルを抽出し、それらのファイルへのリンクを SQL Server データベースに格納することを検討してください。 または、FILESTREAM、FileTables、またはリモート BLOB ストア (RBS) を使用して、添付ファイルを SQL Server データベースに保存したままにすることもできます。
ハイパーリンクAccess テーブルには、SQL Server がサポートしていないハイパーリンク列があります。 既定では、これらの列は SQL Server では nvarchar(max) 列に変換されますが、マッピングをカスタマイズして、より小さいデータ型を選択できます。 Access ソリューションでは、コントロールの Hyperlink プロパティを true に設定すれば、フォームやレポートでハイパーリンク動作を使用できます。
複数値を持つフィールドAccess の複数値を持つフィールドは、区切られた値のセットを含む ntext フィールドとして SQL Server に変換されます。 SQL Server では多対多リレーションシップを表す複数値を持つデータ型はサポートされていないため、追加のデザインや変換作業が必要になる可能性があります。
Access データ型と SQL Server データ型のマッピングの詳細については、「データ型の比較」を参照してください。
注釈 複数値を持つフィールドは変換されません。
詳細については、「 日付と時刻の型」、「 文字列型と 2 進数型」、および 「数値型」を参照してください。
Visual Basic
VBA は SQL Server でサポートされていませんが、次の問題に注意してください。
クエリの VBA 関数 Access クエリは、クエリ列内のデータに対する VBA 関数をサポートします。 ただし、VBA 関数を使用する Access クエリは SQL Server では実行できないため、要求されたすべてのデータが Microsoft Access に渡されて処理されます。 ほとんどの場合、これらのクエリは パススルー クエリに変換する必要があります。
クエリのユーザー定義関数 Microsoft Access クエリでは、VBA モジュールで定義された関数を使用して、渡されるデータを処理できます。 クエリには、スタンドアロン クエリ、フォーム/レポート レコード ソース内の SQL ステートメント、フォーム上のコンボ ボックスとリスト ボックスのデータ ソース、レポートとテーブル フィールド、デフォルトまたは入力規則式があります。 SQL Server ではこれらのユーザー定義関数を実行できません。 これらの関数を手動で再設計し、SQL Server 上のストアド プロシージャに変換する必要がある場合があります。
パフォーマンスの最適化
これまでのところ、新しいバックエンド SQL Server のパフォーマンスを最適化する最も重要な方法は、ローカル クエリとリモート クエリのどちらを使用するかを決定することです。 データを SQL Server に移行すると、同時にファイル サーバーからクライアント/サーバー データベース モデルのコンピューティングに移行することになります。 次の一般的なガイドラインに従ってください。
- クライアントで小さな読み取り専用クエリを実行して、アクセスをすばやく行うことができます。
- サーバーで長い読み取り/書き込みクエリを実行して、より優れた処理能力を活用します。
- フィルターと集計を使用してネットワーク トラフィックを最小限に抑え、必要なデータのみを転送します。
詳細については、「 パススルー クエリの作成」を参照してください。
追加の推奨ガイドラインを次に示します。
サーバーにロジックを配置する アプリケーションでは、ビュー、ユーザー定義関数、ストアド プロシージャ、計算フィールド、トリガーを使用して、アプリケーション ロジック、ビジネス ルールとポリシー、複雑なクエリ、データの検証、参照整合性コードを、クライアントではなくサーバーで一元化して共有することもできます。 このクエリまたはタスクをサーバーでより良く、より迅速に実行できるか、自問してください。 最後に、各クエリをテストして最適なパフォーマンスを確保します。
フォームとレポートでビューを使用する Access で、次の操作を行います。
- フォームでは、読み取り専用フォームには SQL ビューを、読み取り/書き込みフォームには SQL インデックス付きビューをレコード ソースとして使用します。
- レポートの場合は、レコード ソースとして SQL ビューを使用します。 ただし、レポートごとに個別のビューを作成すると、他のレポートに影響を与えることなく、特定のレポートをより簡単に更新できます。
フォームやレポートへのデータの読み込みを最小限に抑える ユーザーが要求するまでデータを表示しない。 たとえば、レコードソース プロパティを空白のままにして、ユーザーがフォームでフィルターを選択できるようにしてから、レコードソース プロパティにフィルターを入力します。 または、DoCmd.OpenForm と DoCmd.OpenReport の where 句を使用して、ユーザーが必要とする正確なレコードを表示します。 レコード ナビゲーションをオフにすることを検討してください。
異種クエリに注意するローカルの Access テーブルと SQL Server リンク テーブルを組み合わせたクエリ (ハイブリッド クエリと呼ばれることもあります) は実行しないでください。 この種類のクエリでは、すべての SQL Server データをローカル コンピューターにダウンロードしてからクエリを実行する必要がありますが、SQL Server ではクエリは実行されません。
ローカル テーブルを使用する場合 めったに変更されないデータ (国または地域の都道府県の一覧など) にはローカル テーブルの使用を検討してください。 静的テーブルはフィルター処理によく使用され、Access のフロントエンドでより良いパフォーマンスを発揮できます。
詳細については、「データベース エンジン チューニング アドバイザー」、「パフォーマンス アナライザーを使用して Access データベースを最適化する」、および「SQL Server にリンクされた Microsoft Office Access アプリケーションの最適化」を参照してください。