Skip to content

MS_SQLServerSettingsRetrieval

Jump to bottom
nishi_74322014 edited this page Aug 13, 2026 · 1 revision

SQL Server での設定取得方法

概要

基本的に Microsoft Learn で確認できそう。

補足（3つの取得口）: SQL Server の設定は、
どのスコープの設定かで参照先が変わる。ここを取り違えると
「設定したはずなのに反映されていない」という混乱になる。

スコープ 参照 設定
インスタンス（サーバ構成オプション） sys.configurations / sp_configure sp_configure + RECONFIGURE
インスタンス（読み取り専用の属性） SERVERPROPERTY() 不可（インストール時に決定）
データベース sys.databases / DATABASEPROPERTYEX() ALTER DATABASE ... SET
データベース スコープの構成 sys.database_scoped_configurations ALTER DATABASE SCOPED CONFIGURATION（2016 以降）

詳細

データベース

データディレクトリ

各データベースファイルの状態定義

各データベースの設定（論理名、パス、初期サイズ）

各データベースファイルに対する自動管理操作（自動拡張）

SELECT
  name as '名前',

  CASE
    WHEN is_percent_growth = 0 THEN
      LTRIM(STR(growth * 8.0 / 1024,10,1)) + ' MB単位で拡張、'
    ELSE
      CAST(growth AS VARCHAR) + ' %単位で拡張、'
    END +
  CASE
    WHEN max_size = -1 THEN
      '拡張制限無し。'
    ELSE
      LTRIM(STR(max_size * 8.0 / 1024,10,1)) + ' MBまでに拡張を制限。'
    END
  AS '自動拡張'

FROM sys.database_files

  • 自動管理操作は

    • DATABASEPROPERTYEX（IsAutoShrink、IsAutoCreateStatistics）
    • sys.database_files（growth）

    だけでなく、SQL Server Agent の
    設定（保守計画）などの確認が必要かもしれない。

補足（is_percent_growth を見る理由）: 自動拡張が
パーセント指定になっていると、DB が大きくなるほど 1 回の拡張量が増え、
拡張中の待ち時間が伸びていく（10% 拡張は 100GB の DB では 10GB になる）。
MB 単位の固定値にしておくのが定石。
併せて AUTO_SHRINK は OFF（既定）であることを確認する。

照合順序

接続認証方式

ログイン監査

同時接続の最大数

user connections サーバー構成オプションの構成 >
Transact-SQL の使用 > user connections オプションを構成するには
https://learn.microsoft.com/ja-jp/sql/database-engine/configure-windows/configure-the-user-connections-server-configuration-option

sp_configure 'show advanced options', 1;
に指定し
RECONFIGURE;
した後の
sp_configure;
でサーバー構成オプションの user connections を確認する。

補足: user connections の既定値は **0（無制限）**であり、
明示的に上限を設ける必要はほとんどない。
実際の同時接続数は sys.dm_exec_connections を数えて確認する
（SQL Server のコネクションとセッション）。

リモートサーバ接続設定

既定のインデックス FILL FACTOR

sp_configure 'show advanced options', 1;
に指定し
RECONFIGURE;
した後の
sp_configure;
でサーバー構成オプションの FILL FACTOR を確認する。

インデックス単位では、

SELECT * FROM sys.indexes

で確認できる。

補足（サーバ既定の FILL FACTOR は変えない）: サーバ構成の
fill factor を 0（＝100%）以外にすると、全インデックスに一律で効いてしまう。
読み取り中心のインデックスまで隙間だらけになり、
ページ数が増えて I/O が悪化する。
調整するならインデックス単位で
CREATE / ALTER INDEX ... WITH (FILLFACTOR = n) を指定する
（SQL Server のインデックス参照）。

設定ユーザと各ユーザのロール

データベース プリンシパルのすべての権限を一覧表示する。

SELECT pr.principal_id, pr.name, pr.type_desc,
  pr.authentication_type_desc, pe.state_desc, pe.permission_name
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
  ON pe.grantee_principal_id = pr.principal_id;

データベース内のスキーマ オブジェクト上の権限を一覧表示する

SELECT pr.principal_id, pr.name, pr.type_desc,
  pr.authentication_type_desc, pe.state_desc,
  pe.permission_name, s.name + '.' + o.name AS ObjectName
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
  ON pe.grantee_principal_id = pr.principal_id
JOIN sys.objects AS o
  ON pe.major_id = o.object_id
JOIN sys.schemas AS s
  ON o.schema_id = s.schema_id;

SQLServerの設定情報で管理すべき情報

管理の目的による。

  • 例えば、

    • 再インストール復旧を目標とするなら、

      • 「設定した全てが必要なもの」と考えた方が良い（再設定できるように）。
      • 設定していない値は、既定値で OK（自動パラメタのチューニングの考え方に従う）

    • 全てのパラメタ情報というなら、システム DB を含めてバックアップする。

補足（既定値との差分だけを管理する）: 実務では、
「既定値から変更した項目」だけを構成管理の対象にするのが扱いやすい。
sys.configurations は value_in_use と
既定値を比較できる情報を持つため、差分の抽出が容易である。

SELECT name, value_in_use, is_dynamic, is_advanced, description
FROM sys.configurations
WHERE value_in_use <> value          -- 未反映（RECONFIGURE 待ち）
   OR name IN (N'max server memory (MB)', N'min server memory (MB)',
               N'max degree of parallelism', N'cost threshold for parallelism',
               N'optimize for ad hoc workloads', N'backup compression default')
ORDER BY name;

なお、sp_configure で値を変えても
RECONFIGURE を実行するまで value_in_use は変わらない。
上のクエリの 1 行目は、その「未反映」を検出するためのもの。
システム DB（特に master と msdb）のバックアップは、
ログイン・ジョブ・メンテナンス プランの復旧に必須である。

参考

Tags: 移行, データアクセス, SQL Server

このWikiは「Open棟梁Project」,「OSSコンソーシアム 開発基盤部会」によって運営されています。

NetDevInfraWiki

マイクロソフト系技術情報 Wiki
Open 棟梁 Wiki

（未着手）

開発基盤部会 Wiki

移行管理: DONE ／ TODO

Clone this wiki locally