2014 年最初の投稿は RDS の SQL Server にオンプレミスの SQL Server のデータベースを移行する際の覚書などを。
基本的な作業は Importing Data into SQL Server on Amazon RDS になります。
■移行の方法
SQL Server をインスタンス間で移行するためには以下の方法があるかと思います。
- バックアップ / リストア
- デタッチ / アタッチ
- BACPAC
- スクリプト + BCP (または SSIS)
RDS で使えるのは [スクリプト + BCP (または SSIS)] になるかと思います。
バックアップ / リストアとデタッチ / アタッチをするためには移行先で使用するためのファイルを RDS のインスタンスから操作できる場所に配置する必要があるのですが、これらの操作は許可されていないため移行元で使用しているデータベースをそのまま使用することができません。
# BACKUP / RESTORE は権限自体が許可されていなかったはずですが。
BACPAC についてはインポートしようとするとエラーになってしまい使うことができませんでした。
そのため、RDS に移行するためにはスクリプトの生成で作成したオブジェクトの定義と BCP 等でエクスポートしたデータを使用する必要があります。
基本的な流れについては、冒頭で紹介した Importing Data into SQL Server on Amazon RDS で紹介されています。
内容としては、
- データベースの静止点を作成する
- 参照整合性制約 (Foreign Key Constraints) を無効にする
- トリガーを無効にする
- ログインをエクスポートしたスクリプトを作成する
- スクリプトの生成でオブジェクト移行用のスクリプトを生成する
- BCP データをエクスポート
- スクリプトを実行
- BCP データをインポート
- 参照整合性制約を有効にする
- トリガーを有効にする
という順番で作業をしていく形になります。
データベースの静止点については、AP からの更新がなければスナップショットを取得しないでも作業ができるかと思います。
データインポート時に参照整合性制約が設定されているとインポート時にテーブルの挿入順を考慮する必要が出てきますので、一時的に制約を外しています。
トリガーも一時的に無効にしてデータ挿入時に動作しないようにしています。
これらについては、
- 移行元では無効にしないでスクリプトを生成
- 移行先で無効にしてデータをインポート
- インポート後に有効にする
という順番でも作業が可能かと思いますので、本番に手を入れないで作業をするためには移行先で無効化をすることになるかと思います。
このように移行については現状スクリプトベースで実施する必要があります。
スクリプトベースで移行する際の注意点を以降で少しまとめていきたいと思います。
■照合順序の設定
RDS の照合順序ですが、[SQL_Latin1_General_CP1_CI_AS] が設定されており、変更することができません。
そのため、データベースを作成した際に照合順序を指定しなかった場合は、 [SQL_Latin1_Generali_CP1_CI_AS] で作成されます。
Unicode 文字型 (nchar / nvarchar) を使用している場合は日本語の文字を格納できますが、非 Unicode 文字型 (char / nvarchar) の場合は日本語の文字を格納することができません。
# 文字コードの不一致で正常にデータを格納できません。
SSMS から [データベースをスクリプト化] を実行することで CREATE DATABASE のスクリプトを作成することができますが、デフォルトの状態では照合順序はスクリプト化されません。
SSMS の [ツール] [オプション] から SSMS のスクリプト生成のデフォルトのオプションが変更できます。
デフォルトでは [照合順序を含める] が [False] になっているため照合順序のクエリが作成されませんので、これを [True] に変更します。
この設定を有効にすると照合順序の設定もクエリに含まれるようになりますので、移行元と同じ照合順序を使用してデータベースを作成することができます。
■データベース作成のクエリの修正
先ほどの作業で照合順序が出力されるようになりましたが RDS で実行するには何か所が修正する必要があります。
RDS のデータベースは [D:RDSDBDATADATA] に作成する必要があります。
そのため、データベースのファイルの格納場所は上記のディレクトリに変更します。
また、RDS のインスタンスに対してはすべての権限を保持しているわけではないため、いくつかのオプションが設定できません。
具体的には以下のオプションを外す必要があります。
- TRUSTWORTHY
- HONOR_BROKER_PRIORITY
- DB_CHAINING
この変更をすればデータベースの作成はできるようになります。
■オブジェクト生成用のスクリプトの作成
移行対象のデータベースを右クリックして [タスク] → [スクリプトの生成] からテーブル等のオブジェクトの移行用スクリプトを作成することができます。
CREATE DATABASE を含むデータベース全体とオブジェクトを選択してのスクリプト生成ができますが、私は CREATE DATABASE と他のオブジェクトは分けて生成しています。
また、SSMS の初期の状態では以下の設定が False になっています。
- 権限のスクリプトを作成
- 所有者のスクリプトを作成
- インデックスのスクリプトを作成
- トリガーのスクリプトを作成
これらを使用している場合は [True] に設定してスクリプトに含めたほうがよいかと思います。
■BCP のコマンド生成
以下のようなクエリを実行すると BCP のコマンドを生成することができます。
DECLARE @table_name SYSNAME DECLARE @schema_name SYSNAME DECLARE @dirname SYSNAME = N'C:BcpData' + DB_NAME() + '' DECLARE table_cursor CURSOR FOR SELECT name, SCHEMA_NAME(schema_id) FROM sys.tables OPEN table_cursor FETCH NEXT FROM table_cursor INTO @table_name, @schema_name WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'bcp ' + DB_NAME() + '.' + @schema_name + '.' + QUOTENAME(@table_name) +' out ' + @dirname + @table_name + '.dat -n -k -T -b 10000' FETCH NEXT FROM table_cursor INTO @table_name, @schema_name END CLOSE table_cursor DEALLOCATE table_cursor GO
実行すると以下のように SSMS のメッセージエクスポートの BCP が出力されますのでこれをコピーして実行します。
インポートをする場合は、以下のようなクエリで BCP のインポートコマンドを生成します。
DECLARE @server SYSNAME = N'' DECLARE @user SYSNAME = N'' DECLARE @password SYSNAME = N'' DECLARE @table_name SYSNAME DECLARE @schema_name SYSNAME DECLARE @dirname SYSNAME = N'C:BcpData' + DB_NAME() + '' DECLARE table_cursor CURSOR FOR SELECT name, SCHEMA_NAME(schema_id) FROM sys.tables OPEN table_cursor FETCH NEXT FROM table_cursor INTO @table_name, @schema_name WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'bcp ' + DB_NAME() + '.' + @schema_name + '.' + QUOTENAME(@table_name) +' in ' + @dirname + @table_name + '.dat -E -k -n -b 10000 -S '+ @server + ' -U ' + @user + ' -P ' + @password FETCH NEXT FROM table_cursor INTO @table_name, @schema_name END CLOSE table_cursor DEALLOCATE table_cursor GO
これでインポートのコマンドが生成できますので、これを実行することでデータの移行が可能となります。
シンプルなデータベースであればこれで移行ができるかと思います。
RDS では時刻が UTC となっていますので、新規に時間のデータを格納する場合には日本時間に変換等のアプリ側の対応も必要となりますが。
# 既存のデータはそのまま移行されますが、新規データは RDS のインスタンスのタイムスタンプとなりますので。