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

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

フロントエンド（フォーム、レポート、クエリなど）＋バックエンド（テーブル）

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

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

移行前の修正

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

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

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

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

www.accessdbstudy.net

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

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

まあ、SQL Server ではできないというんだから、文句言っても始まらないので、テーブル構成の見直しなどで対応しておきます。 ただ、一部のテーブルで、２つのテーブル間に同じ主キーに対する外部キーがあり、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 | Microsoft Docs

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

https://www.microsoft.com/en-us/download/details.aspx?id=53121

なお、最初デスクトップに作らせたショートカットから起動すると、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ファイルに含まれる全テーブルを移行します。

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

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

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

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

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

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

stackoverflow.com

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

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

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

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

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

AWS RDS の SQL Server のネイティブバックアップを使ってみました

最近 AWS RDS の SQL Server を使い始めました（もちろんExpressエディションです）。 いろいろ試していると、ネイティブバックアップという機能を使えるということなので、早速試してみました。

SQL Server のネイティブバックアップおよび復元のサポート - Amazon Relational Database Service

オプショングループの作成

ネイティブバックアップを使うためには、オプショングループに該当するオプションを追加する必要があります。 SQL Server 起動時は何も考えてなかったので、デフォルトオプショングループとなっています。このデフォルトオプショングループにオプションを追加することはできないので、新規にオプショングループを作成します。

RDSの管理画面から、『オプショングループ』を選択して、『グループの作成』を選択します。

『名前』と『説明』に適当な値を入れ、『データベースエンジン』は sqlserver-ex 、『メジャーバージョン』として12.00 を選択します（現在のインスタンスに合わせます）。 問題がなければ、オプショングループが作成されます。

オプションの追加

作成したオプショングループに対して、ネイティブバックアップのオプションを追加します。 対象のオプショングループを選択して、『オプションの追加』を選択します。 『オプション』として、SQLSERVER_BACKUP_RESTORE を選択し、IAMロールがない場合、『新規ロールの作成』をクリックします。

上記のような画面が表示されるので、必要に応じて、IAMロール、S3バケットなどを選択し、場合によっては新規作成します。

『すぐに適用』はこのオプショングループがDBインスタンスに割り当てられていないので、どっちを選択していても大丈夫（なはず）です。 『オプションの追加』ボタンを押すと、選択したオプションが追加されます。

オプショングループをインスタンスに割り当て

わかってみれば、大したことないのですが、実はこれが一番わかりませんでした。 要は、オプショングループをDBインスタンスに割り当てるためには、既存のDBインスタンスを選択して、『インスタンスの操作』から『変更』を選択し、インスタンスの内容の変更画面で、オプショングループを選択する、というものでした。

docs.aws.amazon.com

RDS 知ってると当たり前なのかな？もう少しドキュメントの書き方を工夫して、わかりやすくなってほしいと感じた部分です。

なお、オプショングループの変更と一緒に『すぐに適用』にチェックを入れておきます。『次へ』で確認画面が表示されるので、問題がなければ、『DBインスタンスの変更』をクリックします。 これで、問題が無ければすぐに変更作業が始まります。 しばらくすると、変更が完了となるので（インスタンス画面で確認できます）、新しいオプショングループが割り当てられていることが確認できます。

バックアップの取得

ここまでできたら、早速バックアップを取ってみたいと思います。

docs.aws.amazon.com

こちらのページあるように、 SQL Server Management Studio を起動して、SQL Server に接続します。 『新しいクエリ』を選択して、

EXEC msdb.dbo.rds_backup_database
    @source_db_name=N'database name',
    @s3_arn_to_backup_to='arn:aws:s3:::{bucket name}/{filename}',
    @overwrite_s3_backup_file=0;

と入力して、実行します。 問題が無ければ、しばらくするとS3側にバックアップファイルが作成されています。

取得したバックアップファイルは、同じDBインスタンスに復元できないなど制約はあるものの、復元オプションの一つとして使えそうかなと思っています。

QNAP TS-251+ の設定（バックアップ設定など）

さて、下記の記事などで設定して、QNAP TS-251+ を使い始めましたが、やはりバックアップ設定は早めにやっておいたほうがよいと思います。

QNAP TS-251+ の設定（主に公開設定周り） - プログラマーのメモ書き

QNAP TS-251+ の設定（HDD移設など） - プログラマーのメモ書き

ということで、今回はバックアップ設定を行った話をまとめます。

QNAP でのバックアップ設定について

QNAPではバックアップ設定は、『バックアップマネージャ』を通じて行うことができます。 いろいろな設定方法がありますが、主に、

• リモートレプリケーション
• クラウドバックアップ
• 外部バックアップ

に分類できます。また、リモートレプリケーションでは、

• NAS to NAS (QNAP NAS にレプリケーション）
• Rsync (Rsync サーバーにレプリケーション）
• RTRR
• Snapshot レプリカ
• LUN バックアップ

などの選択肢があります。

とりあえず、別のNAS（Netgearの別のNAS, ReadyNas Ultra2）にバックアップを保存する方法を設定したので、それについてまとめておきます。

Rsync によるバックアップの準備

Rsyncによるバックアップを行うために、最初にNetgearのNASのバックアップ用共有を作成し、Rsyncユーザーとパスワードを設定しておきます。

次に、QNAPのバックアップマネージャを開き、『バックアップサーバー』→『Rsyncサーバー』を選択します。特に設定は変更しませんが、『最大ダウンロード速度を有効にする』にチェックを入れておきます。 これで準備完了です。

Rsyncによるバックアップの設定

設定は、『バックアップマネージャ』→『リモートレプリケーション』→『Rsync』を選択します。紛らわしいですが、『NAS to NAS』はQNAPのNAS同士でバックアップを設定する際に使うようです。

（画面は設定後のためジョブが登録済みになっています）

設定画面が表示されたら、『レプリケーションジョブの作成』ボタンをクリックします。

リモートレプリケーション名に、適当な名前を入れて、リモートサイトの『設定』ボタンを押します。

先ほど設定した、バックアップ先のNASのIPアドレス（または名前）、ユーザー名、パスワードを入力します。『テスト』ボタンを押すと、接続テストを行い、平均速度などを計測して表示してくれます。設定に問題なければ『適用』ボタンを押してください。

次に、『ソースフォルダ』（QNAP側のフォルダ）と『宛先フォルダ』（バックアップ先サーバーのフォルダ）を選択します。 なお、ここでの設定は、あくまでのローカルNASのフォルダを、リモートサーバーのフォルダにバックアップするという設定であり、反対向きの設定はできませんので、ご注意ください。

設定に問題がなければ、『追加』ボタンを押してフォルダ情報を追加します。

最後に『バックアップ周期』ボタンを押して、定期的にバックアップする際の周期などを設定します

なお、すぐにバックアップを行わないならば、『バックアップをただちに実行します』のチェックを外しておきます。

最後に適用ボタンを押して登録すれば完了です。

あとは登録したスケジュールでバックアップが実行されていることを確認すれば完了です。

備考

さて、今回 QNAP でバックアップの設定を行いましたが、Netgear と比較すると結構異なっています。気づいた点をちょっと書いておくと

項目	Netgear ReadyNas Ultra2	QNAP TS-251+
共有フォルダへのバックアップ	〇	×
リモートからローカルへのバックアップ設定	〇	×（バックアップ自体は可能だが設定はリモート側で行う必要がある）
外付けディスク等へのバックアップ	〇	〇
Amazon S3 へのバックアップ	×	〇

実際に設定してみると、Readynas の共有フォルダへのバックアップはお手軽にバックアップを設定できて、それはそれで便利だったんだなと実感できました。 まあ、この後、S3へのバックアップも設定する予定なのと、dockerを試したいので、メインのNASとして ReadyNas を使い続けるという選択肢はないのですが。

あと、地味なところとしては、QNAPでは、バックアップが完了した際の完了通知が無いようです。外部デバイスへのバックアップだと設定できるみたいですが、Rsyncの場合は通知が来ません。この辺りは選択できるようになってほしいところです。