SE の雑記

今月、いろいろとアナウンスが行われている気がしますが、SQL Server 2005 のサポート切れに伴う移行 (アップグレード/マイグレーション) についての参考情報を少しまとめてみたいと思います。

私のブログは相変わらずデータベースエンジン部分ですので、今回もデータベースを対象としています。

■移行の計画

---

移行の計画については、US の SQL Server チームのブログが参考になります。

Planning an efficient upgrade from SQL Server 2005: Step 1 of 3
Planning an efficient upgrade from SQL Server 2005: Step 2 of 3
Planning an efficient upgrade from SQL Server 2005: Step 3 of 3

移行対象の把握や、移行に伴うシステムの考慮事項、移行先の検討、アップグレードの方法などが記載されていますのでざっと読んでおくとよいかと思います。

SQL Server 2005 からアップグレードしますか? も参考になるかと。

 

■データベースの移行方法

---

データベースの移行方法は、

• スクリプトベースの移行 (bacpac 含む)
• データベースのデタッチ / アタッチによる移行
• データベースのバックアップ / リストアによる移行

がよく使用される方法かと思います。

システムデータベースについては、インプレースアップグレードでないと、情報をそのまま使用することができませんので、システムデータベースについては、スクリプトベースで移行するのが一般的な方法となるかと。
# インプレースアップグレードで SQL Server をアップブレードするケースは珍しいと思います。

SQL Server のサービスを停止した状態で、データベースのファイルをコピーするオフラインリストアについては、成功するケースのほうが高いとは思いますが、技術的には正式にサポートされている方法ではなく、ファイルベースでの移行を実施する場合は、データベースを一度デタッチして、そのファイルをコピーするのがサポートされている方法となります。
SQL Server でオフライン バックアップおよびオフライン リストアを行うと問題が出ることがある

デタッチをすることで、サーバーからデータベースの登録が外れますので、「既存のサーバーのデータベースの設定は極力変更したくない」というのが、移行の際に考慮されることになるかと思います。
# 先日アナウンスされた、Double-Take の SQL Server マイグレーションは、Double-Take でデータベースのファイルを 2005 → 2014 の環境に対して複製をし、そのファイルを 2014 上でアタッチする方法をとっているように見受けられました。フィルタードライバー経由で、ディスクのブロックをコピーするようなソフトが使えると、このような手法もとれるかと。

デタッチ/アタッチも使用する機会は少なく、実際には

• システムデータベースで必要となる情報をスクリプト化 (スクリプト化できないものは手動で移行)
• 移行元でユーザーデータベースをバックアップ
• 移行先の SQL Server のサービスがアクセスできる場所にデータベースのバックアップを配置
• 移行先でバックアップをリストア

というような、流れで環境を移行することになるかと思います。

システムデータベースについては、バックアップ / リストアをすることができませんので、必要なオブジェクトについては、スクリプトベースで移行することを検討します。

代表的なものとしては、ログインや SQL Server Agent のジョブでしょうか。

ログインについては、SID を意識して移行する必要があります。
Windows 認証のログインをローカルアカウントで使用している場合は、SID を同一にするのは難しいので、孤立ユーザーのトラブルシューティング (SQL Server) の対応になるかと。
SQL Server 認証のログインは、CREATE LOGIN で SID を指定することができますので、移行元と移行先で SID を揃えてログインを作成しておくと孤立ユーザーの対応が不要となりますので、移行作業のステップを簡単にできるかと。

パスワードも含めて移行したい場合には、How to transfer logins and passwords between instances of SQL Server の方法を使用することができますが、ログインの数が少なく、パスワードが把握できているのであれば、手動でパスワードを設定してしまったほうが楽かもしれませんね。
# SQL Server 2000 版は How to transfer logins and passwords between instances of SQL Server

 

ジョブについては、以下のような手法でスクリプト化することができます。
Transact-SQL を使用してジョブをスクリプト化する方法 (SQL Server Management Studio)

 

SQL Server 2012 以降では、SQL Server 2000 のデータベースのバックアップをリストアすることはできませんが、SQL Server 2005 のバックアップは 2000 の互換性レベルで動作していてもリストアすることができますので、ログ配布のような仕組みを使用して、SQL Server 2005 からの移行を行うというもの検討できるかもしれないですね。
# 上位バージョンのバックアップをリストアすることはできませんが、下位バージョンのバックアップはリストア可能

 

■互換性レベルの変更

---

SQL Server 2005 のバックアップを SQL Server 2012 / 2014 に移行した場合、互換性レベルの変更が行われることがあります。

これについては、以前 SQL Server のバックアップのリストアと互換性レベルの整理 で少しまとめた内容ですね。

• SQL Server 2012 :
  SQL Server 2005 の互換性レベル (90)
• SQL Server 2014 : SQL Server 2008 の互換性レベル (100)

が、各バージョンが対している下位の互換性レベルとなります。

対応していない互換性レベルのデータベースのバックアップをリストアすることは可能なのですが、リストアしたタイミングで、最も下位の互換性レベルに自動的に変更されますので、SQL Server 2005 で使用している互換性レベルに寄っては、互換性レベルの変更による対応が必要となります。

互換性レベルの変更による影響は、以下から確認することができます。

• SQL Server 2008
  • ALTER DATABASE 互換性レベル (Transact-SQL)
    • 90 以下と 100 の相違点
    • 80 と 90 の相違点
• SQL Server 2008 R2
  • ALTER DATABASE 互換性レベル (Transact-SQL)
    • 90 以下と 100 の相違点
    • 80 と 90 の相違点
• SQL Server 2012
  • ALTER DATABASE 互換性レベル (Transact-SQL)
    • 100 以下と 110 の相違点
    • 90 と 100 の相違
• SQL Server 2014
  • ALTER DATABASE 互換性レベル (Transact-SQL)
    • 110 以下と 120 の相違点
    • 90 と 100 の相違点
    • 110 と 120 の相違点

バックアップ / リストアによる移行は、統計情報もそのまま移行が行われます。
上位の互換性レベルに変更しても、統計情報は古いものが使用されているケースがありますので、移行後には、インデックスの再構築や手動での統計情報のメンテナンスを実行して、統計情報を最新の状態にしておくとよいかと。

 

■アップグレードアドバイザーの利用

---

互換性レベル 80 (SQL Server 2005) で使用していたデータベースを SQL Server 2014 で使用する場合、

• 80 と 90 の相違点
• 90 と 100 の相違点
• 100 と 110 の相違点
• 110 と 120 の相違点

を見ていくかと思いますが、これらを机上で確認するのは現実的ではありません。

本番環境だけでなく、開発 / 検証機があるようでしたら、アップグレードアドバイザーを実施するのが良いかと思います。
アップグレード アドバイザーを使用したアップグレードの準備

アップグレードをする際の一般的な考慮事項として以下のようなものがあるかと思います。

• サーバーの設定
• クエリの互換性

 

アップグレードアドバイザーでは、

• インスタンスの設定
• データベース内のオブジェクト
• プロファイラーで取得したテキストを含んでいるクエリ

を元に、アップグレード後の互換性の確認をすることができます。
すべてを網羅するのは難しいと思いますが、アップグレードをした際に、どの程度影響が出そうな箇所があるかの、概要を確認するためには、アップグレードアドバイザーは強力なツールとなります。

• アップグレードアドバイザーによる全体的な影響度のチェック
• アップグレード後の環境を使用した動作確認

をするのが一般的なアプローチとなるかと。

SQL Server 2014 のアップグレードアドバイザーでは、互換性レベル 80 のデータベースをチェックすることができないため、互換性レベル 80 のデータベースをチェックする必要がある場合は、SQL Server 2008 R2 までのアップグレードアドバイザーを使用します。