SQL Server パフォーマンスチューニング、コンサルティング、アドバイス、相談、定期診断、トレーニング

ホーム＞技術情報＞ SQL Server 2014 実践 No.2 SQL Server 2014 への移行とアップグレードの実践

SQL Server 2014 実践シリーズ（HTML 版）
「No.2 SQL Server 2014 への移行とアップグレードの実践」

松本美穂と松本崇博が執筆した SQL Server 2014 実践シリーズの「No.2 SQL Server 2014 への移行とアップグレードの実践」の HTML 版です。日本マイクロソフトさんの Web サイトで Word または PDF 形式でダウンロードできますが、今回、HTML 版として公開する許可をいただきましたので、ここに掲載いたします。[2015年12月29日]

目次へ | 前のページへ | 次のページへ

5.18　構成オプション（sp_configure）の移行

構成オプションは、次のようにサーバーのプロパティや sp_configure ストアドプロシージャで設定したサーバーのオプション（最大メモリや接続オプションの設定など）です。

00486

現在の構成オプションは、次のように sp_configure システムストアドプロシージャでまとめて確認することができます。

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure

show advanced options を 1 へ設定してすべてのオプションを表示するようにして、その後「sp_configure」を実行することで、現在の値（run_value）を確認することができます。

これらの構成オプションも master データベース（システムデータベース）内に格納されているので、設定を変更している場合には別途移行を行う必要があります。

◆ 構成オプションのスクリプト化

構成オプションに関しても、Management Studio からは（GUI 操作では）スクリプトを生成することができないので、次のように「sp_configure」の結果を一時テーブルに保存して、これをカーソルでループ処理することでスクリプトを生成することができます。

-- show advanced options を1 に設定
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
-- sp_configure の結果を一時テーブルへ保存
CREATE TABLE #temp1
( name sysname
,minimum int
,maximum int
,config_value int
,run_value int )
INSERT INTO #temp1 EXEC sp_configure
-- 一時テーブル内の run_value をカーソルでループ
DECLARE @name sysname, @run_value int
DECLARE cur1 CURSOR FOR
                SELECT name, run_value FROM #temp1
OPEN cur1
FETCH NEXT FROM cur1 INTO @name, @run_value
WHILE (@@FETCH_STATUS <> -1)
BEGIN
  -- 現在の構成値（run_value）をもとにスクリプト化
  PRINT 'EXEC sp_configure ''' + @name + ''', ' + CONVERT(varchar, @run_value) + ''
  FETCH NEXT FROM cur1 INTO @name, @run_value
END
CLOSE cur1
DEALLOCATE cur1
DROP TABLE #temp1

このように、ストアドプロシージャの結果と同じ列数のテーブルを作成して、INSERT .. EXEC を実行することで、EXEC の結果をテーブルに保存することができます。このテーブルを利用して、カーソルループを行えば、現在の設定値（run_value）をもとに sp_configure をスクリプト化することができます。

あとは、生成されたスクリプトを移行先（SQL Server 2014 上）で実行すれば、構成オプションを移行することができますが、次のようにスクリプトの実行前に、移行先でも show advanced options を 1 へ設定してすべてのオプションを表示するようにしておきます。

-- 移行先で実行
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
-- スクリプトの実行（スクリプト生成したものを貼り付け）
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
EXEC sp_configure 'affinity I/O mask', 0
：（中略）
EXEC sp_configure 'xp_cmdshell', 1
-- 最後に RECONFIGURE を実行する
RECONFIGURE WITH OVERRIDE

また、スクリプトの最後には、上のように RECONFIGURE WITH OVERRIDE を追加して、構成を反映させるようにします。構成オプションによっては、SQL Server サービスの再起動が必要なものがありますが、再起動が必要かどうかは、オンラインブックの以下のトピックに記載されています。

サーバー構成オプション
http://msdn.microsoft.com/ja-jp/library/ms189631.aspx

00489

RR と表記されるものが再起動が必要なオプションで、これらをまとめると、次のようになります。

affinity64 mask、c2 audit mode、common criteria compliance enabled、fill factor、lightweight pooling、locks、media retention、open objects、priority boost、remote access、set working set size、user connections

これらの構成オプションを変更している場合には、スクリプト実行後に、SQL Server サービスを再起動する必要があります。

◆ SQL Server 2014 でサポートされなくなった構成オプション

移行先でスクリプトを実行すると、SQL Server 2014 ではサポートされなくなった構成オプションがいくつかあり、そのオプションに関しては、次のようにエラーが発生します。

00490

画面は、AWE Enabled オプション（AWE の有効化オプション）でエラーになっていますが、このオプションは SQL Server 2012 以降でサポートされなくなっています。そのほかに、以下のオプションでエラーになります。

SQL Mail XPs（SQL Server 2012 以降で未サポート）
Web Assistant Procedures（SQL Server 2008 以降で未サポート）

SQL Mail XPs（SQL Mail 機能）は、SQL Server 2012 以降で未サポートになり（代わりにデータベースメール機能を利用）、Web Assistant 機能は SQL Server 2008 以降で未サポートになっています。

◆ SQL Server 2005 の構成オプションの既定値

SQL Server 2005 をインストールした直後の構成オプションの既定値は、次のようになっています（現在のサーバーで、どのオプションを変更したかの参考になると思います）。

00491

多くのオプションが「0」に設定されていて、0 は、動作に関するオプション（Affinity Mask や Max Degree of Parallelism、Locks、Max Worker Threads など）であれば "自動調整"、セキュリティ関連のオプション（Ad Hoc Distributed Queries や clr enabled、Database Mail XPs、xp_cmdshell など）であれば "無効" という意味になります。なお、Agent XPs は、SQL Server Agent サービスを自動起動に設定している場合は 1 に設定されます。

◆ SQL Server 2005 と SQL Server 2014 で既定値が変わったもの

SQL Server 2005 と SQL Server 2014 では、ほとんどの構成オプションの既定値が同じです。違いは、次の 2つのみです。

min server memory (MB) が 16 に変更されている（SQL Server 2005 では 0）
remote login timeout (s) が 10 に変更されている（SQL Server 2005 では 20）

構成オプションの詳細については、オンラインブックの以下のトピックが参考になると思います。

サーバー構成オプション
http://msdn.microsoft.com/ja-jp/library/ms189631.aspx

◆ SQL Server 2014 で既定値から変更のあった構成オプションのみを表示

SQL Server 2014 では、既定値（インストール直後の値）から変更のあった構成オプションのみを表示する機能が提供されています（この機能は SQL Server 2008 から利用することができます）。これを利用するには、次のようにサーバーを右クリックして、［レポート］の［標準レポート］から「サーバーダッシュボード」をクリックします。

00492