のえら

技術備忘とかメモとか.間違いはつっこんでいただきたい所存.アフィリエイトはやっていません.

データベースコピーウィザードを使用してSQL Server 2005 から SQL Server 2008 R2にデータを移行する

SQL Server windows

データベースコピーウィザードを使用するにあたって、色々問題が出てしまったのでググったり設定変更して実行してみたり、試した結果うまくいったのでメモしておく。
ググったとき、「これをこうする」「これならできた」、と情報が色々あり、以下の記述と異なる場合があるけど、あくまで例、ということで。

概要

データベースコピーウィザードは、SQL Server Management Studio(SSMS)で使用できるタスクのひとつ。

https://technet.microsoft.com/ja-jp/library/ms188664(v=sql.105).aspx
引用：

データベース コピー ウィザードを使用すると、サーバーを停止することなく、データベースとそのオブジェクトをサーバー間で簡単に移動またはコピーできます。

「簡単」かどうかはさておいて、移行作業がひとつの画面でできるのは便利。

使用方法は上記公式ページや以下の参考ページにあるので省略。
http://madogiwatosan.blogspot.jp/2013/02/sqlserver.html
http://www.sunvisor.net/db/copy_wizard

予備知識
  • データベースコピーウィザードは、データの転送を行うSSIS(SQL Server Integration Services)パッケージ作成・実行するタスクなので、呼び出しにはSQL Server Agentが使用される
  • 実行時にエラーが発生した場合、SSISの基礎知識があると原因を調査しやすい(と、思う)

前提

  • 移行元はSQL Server 2005、移行先はSQL Server 2008 R2で、それぞれのサーバーOSに対して同名同パスワードの管理者権限アカウントが存在している
  • SSMSの起動は移行先のサーバーにログインして行う
  • 移行先のSQL Serverはインストール＋SP3更新済み、それ以外は未設定
  • SQL Serverの認証は「混合(Windows認証＋SQL Server認証）」で、SSMSの操作は各インスタンスいずれもsaアカウントで行う
  • Active Directoryは未使用

事前準備

【移行元サーバー】
・移行対象のDBファイル(mdf, ndf, ldf)があるフォルダを共有にする
　対象のフォルダ右クリック＞プロパティから、共有タブを開いて各種設定
　※共有名は任意/Everyoneフルコントロール
　　⇒ウィザード内で共有のパスを指定するので短くてわかりやすいのがよいやもしれん
・移行対象のDBファイル(mdf, ndf, ldf)のアクセス権をEveryoneフルコントロールにする

＃実はこのアクセス権の設定いらないんじゃないだろうか・・・？

【データベースコピーウィザード実行PC】
・SQL Server Agentのサービス実行アカウントをOSに作成した管理者ユーザーアカウントにして、サービスを起動しておく
　⇒ビルトインアカウントではファイルアクセスの段階で失敗する（アクセス拒否というエラーになる）
　※管理者ユーザーアカウントのユーザー名とパスワードは移行先のサーバに登録されているものと同一である必要がある
　　存在しないユーザーを指定していた場合、「ログオン失敗: ユーザー名を認識できないか、またはパスワードが間違っています。」とエラーが表示される
　　＃これはSSISのジョブの仕組み・・・？
　　　http://stackoverflow.com/a/2595916
　　　上の回答で引用している元のページはこれかな
　　　https://msdn.microsoft.com/ja-jp/library/aa337083(v=sql.105).aspx
引用：

ローカル アカウントを使用した接続
クライアント コンピューターのローカル Windows アカウントで作業している場合、リモート コンピューターの Integration Services サービスに接続できるのは、同じ名前、同じパスワード、および十分な権限が設定されたローカル アカウントがリモート コンピューター上に存在する場合だけです。

でもリモートのSSISに接続してるわけじゃないんだよな・・・？

で、この準備をしておかないと、実行時に以下のようなエラーが出力される
「パス '\\xxx\～\HOGE.mdf' へのアクセスが拒否されました。」

データベースコピーウィザードの実行にあたって

実行前に、移行元移行先ともにSQL Serverのサービスが起動していることを確認する。また、移行作業(SSMSの起動)を行うPCの、SQL Server Agentのサービスも特定のアカウント(前述の管理者アカウント)で開始していることを確認する。
移行対象のユーザーDBがアタッチされていない場合、SSMSとウィザードで検出することができないので注意する。

コピー方法の選択

移行中のユーザーDBの扱いについて、コピー方法を「SQL管理オブジェクトの方法を使用する」にした場合、デタッチせずにコピーすることもできるが、容量やアクセスによっては結構遅くなる、らしい。
＃プロセスを生かしたままコピーするから当たり前か
ちなみに、この方法でコピーする場合は共有設定などの事前準備は不要(おそらく、SQL Server経由でデータを移している/そもそもデタッチしないと通常のファイルコピーは不可能なので)

「デタッチ後にアタッチする方法を使用する」の場合、ウィザードの中でDBファイルが存在する共有パスを指定するが、パスを参照できるであろう[...]ボタンが機能してくれないようなので注意。
実際にアクセスできるパスを手入力しても「ネットワークパスが見つかりません」と怒られる。謎。
で、この方法はDBファイルにアクセスするので、ここで前述のアクセス権の設定が絡んでくる。

コピーオプション

このウィザードではオプションで、ユーザーDBに紐付くいくつかのオブジェクトも一緒にコピーができる
使用可能な関連オブジェクト：

  • masterデータベースからのストアドプロシージャ
  • SQL Servereエージェントジョブ
  • ユーザー定義エラーメッセージ
  • エンドポイント
  • SSISパッケージ
  • ログイン

ただし、masterなどのシステムデータベースは移行不可。
https://technet.microsoft.com/ja-jp/library/ms188664(v=sql.105).aspx
引用：

model、msdb、および master の各データベースをデータベース コピー ウィザードでコピーまたは移動することはできません。

紐付くオブジェクトの中に「ログイン」があるが、これはユーザーDBに登録されているログインの情報を元に再作成してくれる、という機能。便利といえば便利だけど、以下の理由によりオススメしない。
・SID、パスワードが引き継がれない(新規作成となるため)
・アカウントが初期状態では無効になっている
・ユーザーDBに関連しないログインは作成されない
⇒後述する「ログインの移行」を参照に、移行元のサーバから生成するのがよさげ

その他データのコピー

ユーザーDBの外部に格納される情報は別途必要に応じて移行する。
「外部の項目」はここ参照。
https://msdn.microsoft.com/ja-jp/library/ms187580(v=sql.105).aspx
⇒見落とし注意なのが、「リンクサーバー」と「ロール」、「データベースの所有権」あたり？

ログインの移行

移行元環境からSIDとパスワードを引き継いだままログインを作成するには、Microsoftが提供しているスクリプトを実行してSPを作成し、それを実行して移行用のクエリを出力させるのが一番手っ取り早い、というか確実。
https://support.microsoft.com/us-en/kb/918992
１）移行元のサーバーに対して上記ページ内のクエリを実行する
２）生成された以下のSPを実行

EXEC sp_help_revlogin

３）出力されたクエリを移行先のサーバーで実行する
※クエリ内のビルトインアカウントやsaユーザーなど不要なログインはコメントアウトするか削除しておく

この作業を行ったタイミングで、コピー済みのユーザーDBのログインとサーバーインスタンスのログインがマッピングされる。（いわゆる「不明なログイン」が解消される）

ロールの付与

インスタンスに付随するログイン(ユーザー)のロールを移行するようには出力されないので、以下のページを参考に移行元のサーバーのログインに付与されているロールを、各ログインに付与するクエリを生成する。
生成したクエリを移行先のサーバーで実行すると、各ログインに対してロールが再設定される。
http://www.sqlservercentral.com/Forums/Topic647463-146-1.aspx

引用：

Declare @Prefix varchar(255)
Declare @tmpstr varchar(MAX)

Set @Prefix = '
exec master.dbo.sp_addsrvrolemember @loginame='''

Set @tmpstr=''

Select @tmpstr = @tmpstr
    + Case When sysadmin = 1 Then @Prefix + [LoginName] + ''', @rolename=''sysadmin''' Else '' End
    + Case When securityadmin = 1 Then @Prefix + [LoginName] + ''', @rolename=''securityadmin''' Else '' End
    + Case When serveradmin = 1 Then @Prefix + [LoginName] + ''', @rolename=''serveradmin''' Else '' End
    + Case When setupadmin = 1 Then @Prefix + [LoginName] + ''', @rolename=''setupadmin''' Else '' End
    + Case When processadmin = 1 Then @Prefix + [LoginName] + ''', @rolename=''processadmin''' Else '' End
    + Case When diskadmin = 1 Then @Prefix + [LoginName] + ''', @rolename=''diskadmin''' Else '' End
    + Case When dbcreator = 1 Then @Prefix + [LoginName] + ''', @rolename=''dbcreator''' Else '' End
    + Case When bulkadmin = 1 Then @Prefix + [LoginName] + ''', @rolename=''bulkadmin''' Else '' End
 From (select convert(varchar(100),suser_sname(sid)) as [LoginName],
		sysadmin,
		securityadmin,
		serveradmin,
		setupadmin,
		processadmin,
		diskadmin,
		dbcreator,
		bulkadmin
	from sys.syslogins
	where sysadmin<>0
	or    securityadmin<>0
	or    serveradmin<>0
	or    setupadmin<>0
	or    processadmin<>0
	or    diskadmin<>0
	or    dbcreator<>0
	or    bulkadmin<>0) L

Print @tmpstr

※地道に調べて手作業で設定、でもいいし、ログインを右クリック＞ログインをスクリプト化＞CREATE、からロール部分だけ抜粋してもいい。が、上記スクリプトのほうが早いと思われる。

リンクサーバーの生成

リンクサーバーを右クリック→CREATE文の生成でクエリを作成し、移行先のサーバーにて実行。
※hostsファイルを編集する必要があれば、別途対応する

余談

当初、自身の作業PCでSSMSを起動してデータコピーを行おうとしたが、コピー前にエラーが発生していた。SSISの実行アカウントについて試したのは以下のパターン。ログ内容抜粋。

  • ビルトインアカウント(ローカルシステムアカウント)

⇒「"xxx.mdf"へのアクセスが拒否されました」のエラー

  • 移行先に登録がない名称のローカルユーザーアカウント

⇒「ログオン失敗: ユーザー名を認識できないか、またはパスワードが間違っています。」のエラー

  • 移行先に登録がある名称のローカルユーザーアカウント

⇒コピー実行まで進む