プログラマーのメモ書き

伊勢在住のプログラマーが気になることを気ままにメモったブログです

Access -> SQL Server への移行 (1/2)

とあるDBを Accessで運用しています。Access での構成は、

フロントエンド(フォーム、レポート、クエリなど)+バックエンド(テーブル)

となっています。バックエンドのaccessファイルに対して、フロントエンドからリンクテーブルを貼る形式です。基本は一人だけが使う構成ですね。

今回、このDBを多人数で使いたい、かつ、外出時も使いたいとの要望があり、 SQL Server に移行することになりました。どうするのが一番簡単に移行できるかと考えてみて、テーブルの部分を SQL Server に移行して、ODBCで接続して、リンクテーブルを貼れば、割と簡単に移行できるのではないかと考えて、試してみました。めったにやらない作業だったので、以下にその際にやったことをメモ書きで残しておきます。

移行前の修正

access を移行する前に、テーブル間の外部キー制約を見直しています。 主に見直した点は、

  • 2つのテーブル間に同じ主キーに対する外部キー(参照整合性制約あり)が設定されている
  • 複数の外部キーを設定してあるテーブルがあって、連鎖削除がある場合に、複数の経路で削除される場合がある

を解消するというものでした。どちらも SQL Server では実現できないというものです。

一つ目については、下記サイトとかがわかりやすいと思います。 www.accessdbstudy.net

二つ目については、マイクロソフトの下記の記事が(比較的)わかりやすいかな。

1 回の DELETE または UPDATE 操作によって起動される連鎖参照動作は、循環参照を含まないツリー形式になっている必要があります。DELETE または UPDATE 操作によって生じる一連の連鎖参照動作の影響を複数回受けるテーブルがあってはいけません。また、連鎖参照動作のツリーはすべての特定のテーブルへのパスが一意である必要があります。ツリーの分岐は、NO ACTION が指定されているテーブルか既定値であるテーブルに到達したところで終了します。 連鎖参照整合性制約

まあ、SQL Server ではできないというんだから、文句言っても始まらないので、テーブル構成の見直しなどで対応しておきます。 ただ、一部のテーブルで、2つのテーブル間に同じ主キーに対する外部キーがあり、ON DELETE CASCADE (と同等の動作)をどうしても実装したかったので、最終的には、SQL Server のafterトリガーを使って実現しました。

若干補足

ちなみに、こういう場合は、下記サイトのように instead of delete トリガーで代替する方法がよく見つかります。今回は、主キー側のテーブルに外部キーが設定されており、これが使えないので、afterトリガーにて実現しました。 www.mssqltips.com

なかなか一筋縄ではいかないですね。

バックエンドaccess ファイルを SQL Server へ移行

Access から SQL Server への移行には SQL Server Migration Assistant を使います。

SQL Server Migration Assistant

下記ページからダウンロードして、インストールします。作業時点のバージョンは 7.0 でした。

Download Microsoft SQL Server Migration Assistant 7.0 for Access from Official Microsoft Download Center

なお、最初デスクトップに作らせたショートカットから起動すると、64bit版が起動して、インストール済みのOfficeが32bit版だったので、起動に失敗してました。

最初は原因が不明で焦ったのですが、ネットを漁るとちらほら似たような情報があり、bit数が違うことが原因だと分かりました。で、スタートメニューから32bit版を起動すると、問題なく起動できました。 Officeは32bit版を使うことを推奨しているんだから、この辺りも気を使ってもらえると嬉しんですけどね>マイクロソフトさん

と、この記事書きながら気づいたんですが、SSMAインストール後に見れるreadmeを読むとインストール要件として、DAOプロバイダーが挙げられています。で、これというのはOficeをインストールすると一緒にインストールされるともあります。SSMAをインストールした環境のOfficeは32bit版だったので、DAOプロバイダーのbit数もおそらく32bitだと思われます。なので、Officeのbit数というよりは、Officeと一緒にインストールされているDAOプロバイダーのbit数といったほうが正確かもしれませんね。 このreadmeのインストール要件にも、Access/Officeが必要、とは書かれてないですからね(移行時にAccessがないと移行できません、なんて制約、うれしくないですしね)。

さて、問題なく起動出来たら、あとは画面のウィザードに従って、SQL Server に移行したいものを選択していくだけです。今回はaccdbファイルに含まれる全テーブルを移行します。

f:id:junichim:20161111151858p:plain

f:id:junichim:20161111151910p:plain

下記の画面で、移行したいAccessファイルを指定します。今回の場合、バックエンドのファイルになります。

f:id:junichim:20161111151919p:plain

テーブル一覧が表示されるので、移行したいテーブルを選択します。画面では全テーブルを指定しています。

f:id:junichim:20161111151929p:plain

SQL Server への接続情報を入力します。今回は、 AWS RDS 上に SQL Server Express Edition (12.0) を立ち上げてそれを使いました。

f:id:junichim:20161111151943p:plain

リンクテーブルを貼るかどうか聞かれるので、状況に応じて設定します。今回はバックエンド側のファイルを移行させるので、作成しませんでした。

f:id:junichim:20161111151952p:plain

上記のようにウィザードに従って作業すると、特に問題もなく作業が完了しました。

一応、移行レポートが表示されたので、あれこれ見ていたら、SQL Server に作成されたテーブルに、SSMA timestamp という謎のカラムが追加されています。 これはなんだ?と思って調べてみると、特に問題はないようです。このままにしとけ、というのが大勢のようなので、特に不都合を感じるまではこのままにしておきます。

stackoverflow.com

再度補足:移行方法について

このDBの開発・運用環境として Access 2013 を使っています。一つ前の Access 2010 まではアップサイジングウィザードという機能があり、これを利用してSQL Server に移行することができたようです。 最初は、Access2010を引っ張り出してきて、この機能を使っての移行も試してみました。一応、こちらのツールでも問題なく移行はできたのですが、nullableのプロパティが全カラムについてnullを許容する、などとなっていました。

これだと、都合が悪いので、SSMAで移行した次第です。

また、他にも SQL Server の管理ツールである SQL Server Management Studio のインポートを使うという手もあるようですが、これだと主キーの設定から再度やらないといけないので、やはり採用しませんでした。

ここまでで、バックエンド側の移行が無事に終わった(ような)ので、次はフロントエンド側Accessの改修作業に移ります。