SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

RDS の SQL Server にデータベースを移行する際の覚書

leave a comment

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 で紹介されています。
内容としては、

  1. データベースの静止点を作成する
  2. 参照整合性制約 (Foreign Key Constraints) を無効にする
  3. トリガーを無効にする
  4. ログインをエクスポートしたスクリプトを作成する
  5. スクリプトの生成でオブジェクト移行用のスクリプトを生成する
  6. BCP データをエクスポート
  7. スクリプトを実行
  8. BCP データをインポート
  9. 参照整合性制約を有効にする
  10. トリガーを有効にする

という順番で作業をしていく形になります。

データベースの静止点については、AP からの更新がなければスナップショットを取得しないでも作業ができるかと思います。
データインポート時に参照整合性制約が設定されているとインポート時にテーブルの挿入順を考慮する必要が出てきますので、一時的に制約を外しています。
トリガーも一時的に無効にしてデータ挿入時に動作しないようにしています。

これらについては、

  • 移行元では無効にしないでスクリプトを生成
  • 移行先で無効にしてデータをインポート
  • インポート後に有効にする

という順番でも作業が可能かと思いますので、本番に手を入れないで作業をするためには移行先で無効化をすることになるかと思います。

このように移行については現状スクリプトベースで実施する必要があります。

スクリプトベースで移行する際の注意点を以降で少しまとめていきたいと思います。

■照合順序の設定


RDS の照合順序ですが、[SQL_Latin1_General_CP1_CI_AS] が設定されており、変更することができません。
image

そのため、データベースを作成した際に照合順序を指定しなかった場合は、 [SQL_Latin1_Generali_CP1_CI_AS] で作成されます。
Unicode 文字型 (nchar / nvarchar) を使用している場合は日本語の文字を格納できますが、非 Unicode 文字型 (char / nvarchar) の場合は日本語の文字を格納することができません。
# 文字コードの不一致で正常にデータを格納できません。

SSMS から [データベースをスクリプト化] を実行することで CREATE DATABASE のスクリプトを作成することができますが、デフォルトの状態では照合順序はスクリプト化されません。
image

image

SSMS の [ツール] [オプション] から SSMS のスクリプト生成のデフォルトのオプションが変更できます。

image

デフォルトでは [照合順序を含める] が [False] になっているため照合順序のクエリが作成されませんので、これを [True] に変更します。

image

この設定を有効にすると照合順序の設定もクエリに含まれるようになりますので、移行元と同じ照合順序を使用してデータベースを作成することができます。
image

 

■データベース作成のクエリの修正


先ほどの作業で照合順序が出力されるようになりましたが RDS で実行するには何か所が修正する必要があります。

RDS のデータベースは [D:RDSDBDATADATA] に作成する必要があります。
そのため、データベースのファイルの格納場所は上記のディレクトリに変更します。

また、RDS のインスタンスに対してはすべての権限を保持しているわけではないため、いくつかのオプションが設定できません。
具体的には以下のオプションを外す必要があります。

  • TRUSTWORTHY
  • HONOR_BROKER_PRIORITY
  • DB_CHAINING

この変更をすればデータベースの作成はできるようになります。

 

■オブジェクト生成用のスクリプトの作成


移行対象のデータベースを右クリックして [タスク] → [スクリプトの生成] からテーブル等のオブジェクトの移行用スクリプトを作成することができます。

image

CREATE DATABASE を含むデータベース全体とオブジェクトを選択してのスクリプト生成ができますが、私は CREATE DATABASE と他のオブジェクトは分けて生成しています。
image

また、SSMS の初期の状態では以下の設定が False になっています。
image

  • 権限のスクリプトを作成
  • 所有者のスクリプトを作成
  • インデックスのスクリプトを作成
  • トリガーのスクリプトを作成

これらを使用している場合は [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 が出力されますのでこれをコピーして実行します。

image

インポートをする場合は、以下のようなクエリで 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 

image

これでインポートのコマンドが生成できますので、これを実行することでデータの移行が可能となります。

シンプルなデータベースであればこれで移行ができるかと思います。

RDS では時刻が UTC となっていますので、新規に時間のデータを格納する場合には日本時間に変換等のアプリ側の対応も必要となりますが。

# 既存のデータはそのまま移行されますが、新規データは RDS のインスタンスのタイムスタンプとなりますので。

Share

Written by Masayuki.Ozawa

1月 1st, 2014 at 12:07 am

Posted in AWS,SQL Server

Tagged with , ,

Leave a Reply