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

ホーム＞技術情報＞ SQL Server 実践「SQL Server 2016 への移行とアップグレードの実践」

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

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

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

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

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

00455

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

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

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

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

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

構成オプションは、既定値（インストール直後の値）から変更のあったもののみを簡単に確認することができます。これは、次のように Management Studio でサーバー名を右クリックして、［レポート］の［標準レポート］から「サーバーダッシュボード」をクリックします。

00457

このように、サーバーダッシュボードを利用すれば、どのオプションが変更されたのかが一目瞭然になるので、大変便利です。

なお、このレポートは、SQL Server 2005 の SP2 以降で利用できる機能なので、SQL Server 2005 の SP1 以前を利用している場合は、SQL Server 2016 の Management Studio を利用して、SQL Server 2005 にリモート接続してレポートを開いてみてください。

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

構成オプションに関しては、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 2016 上）で実行すれば、構成オプションを移行することができますが、前掲のダッシュボードレポートを利用して、変更があったオプションのみを、移行先で実行するようにします。

移行先で実行後は、次のように RECONFIGURE WITH OVERRIDE を実行して、構成を反映させるようにします。

-- 移行先で実行
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

構成オプションによっては、SQL Server サービスの再起動が必要なものがありますが、再起動が必要かどうかは、オンラインブックの以下のトピックに記載されています。

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

00459

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 2016 でサポートされなくなった構成オプション

以前の SQL Server と比較して、SQL Server 2016 でサポートされなくなった構成オプションには、次のものがあります。

AWE Enabled オプション（SQL Server 2012 以降で未サポート）
SQL Mail XPs（SQL Server 2012 以降で未サポート）
Web Assistant Procedures（SQL Server 2008 以降で未サポート）

AWE Enabled（AWE の有効化）と SQL Mail XPs（SQL Mail 機能）は、SQL Server 2012 以降で未サポートになり、Web Assistant 機能は SQL Server 2008 以降で未サポートになっています。

Note： SQL Server 2005 の構成オプションとの比較
SQL Server 2005 の構成オプションの既定値は、次のようになっています。
00460

多くのオプションが「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 2016 との比較
SQL Server 2005 と SQL Server 2016 では、ほとんどの構成オプションの既定値が同じです。違いは、次の 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

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