SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

Archive for 12月 19th, 2010

SQL Server のインスタンス ルート ディレクトリを変更した場合の注意点

one comment

SQL Server のインストールですが、インストール時に [インスタンス ルート ディレクトリ] を設定します。
image

インスタンス ルート ディレクトリは任意のディレクトリを指定できますので、デフォルトの [C:Program FilesMicrosoft SQL Server] から変更することが可能です。

このインスタンス ルート ディレクトリですが、変更した際に一点注意点があります。
今回は、C ドライブではなく E ドライブに設定をしてみました。
image

インスタンス ルート ディレクトリ以外は C ドライブに設定しています。
image

SQL Server をインストールすると以下のサービスがインストールされます。

  • SQL Active Directory Helper Service
  • SQL Server
  • SQL Server Agent
  • SQL Server Browser
  • SQL Server VSS Writer

インスタンス ルート ディレクトリに選択したディレクトリには、[SQL Server] [SQL Server Agent] のサービスのファイルが格納されます。
image
image

サービス用のプログラムが C ドライブ以外にあることは問題ないのですが、この状態では Windows Server バックアップで [システム状態] [ベアメタル 回復] のバックアップを取得する際に、SQL Server 用のサービスに必要なプログラムが格納されているドライブも取得対象となります。
# このあたりは、System Writer の VSS Writer が制御しているみたいですね。レジストリ (ImagePath の値) を直接修正して、サービスで使用するプログラムの場所を変更すればインスタンス ルート ディレクトリを取らなくすることもできますが。危険すぎてこの方法は使わないですよね…。

最初は [ベア メタル回復] のバックアップ取得を試してみます。
image

ベア メタル回復を選択すると、自動的にベアメタル回復に必要なドライブが選択されるのですが、C ドライブだけでなく E ドライブも自動的に選択がされます。
E ドライブを外そうとしても以下のメッセージが表示され、ドライブ / ドライブ以下のフォルダを外すことができません。
image

[システム状態] を選択すると、システム状態の回復に必要なものが取得されます。
# ドライブの選択状況は変わらず、内部でシステム状態の回復に必要なものが自動で取得されます。
image

システム状態を選択した際に取得されるバックアップを確認してみます。
3 つの VHD ファイルが作成されています。
image

これですが、[システムで予約済み] (通常にインストールをした際の先頭 100MB) [C ドライブ] [E ドライブ] のバックアップとなっています。

Windows Server 2008 R2 の場合はファイル単位でバックアップが取得できるようになているので、各ドライブのバックアップはシステム状態として必要最低限のファイルがバックアップされています。
# C ドライブであればユーザープロファイルのディレクトリは除外されています。

E ドライブのバックアップを確認してみるとサービスで使用している 2 つのファイルのみが取得されています。
image

Windows Server 2008 R2 であれば、ファイル単位で取れるのでシステム状態を取得した時にインスタンス ルート ディレクトリを C ドライブ以外にしても最小限のファイルがバックアップされることになります。

 

Windows Server 2008 ではどうなるでしょう。

Win
dows Server 2008 では[システム回復を有効にする] がベアメタル回復相当のバックアップとなります。
このオプションを有効にすると、インスタンス ルート ディレクトリに指定したドライブも自動的に選択されます。
image

この動きは、Windows Server 2008 R2 と同じですね。

それでは、システム状態のバックアップを取得するとどうなるでしょう。
Windows Server 2008 の場合は、[wbadmin start systemstate

backup] というコマンドラインを使用して、システム状態だけを取得することが可能です。
# [Start Backup] で取得する場合は、[-allCritical] というオプションを指定します。

システム状態を取得する場合に必要となるファイルが含まれているドライブを自動で判断する動きは Windows Server 2008 R2 と変わりません。
image

Windows Server 2008 の場合は、デフォルトでは先頭の 100MB の領域は作成されないので、2 つの VHD ファイルが取得されます。
image

E ドライブ用に取得された VHD の中身を見てみます。
image

Windows Server 2008 R2 と異なり、サービス以外のファイルも取得されています。

Windows Server 2008 の場合は、ボリューム単位でのバックアップしか取得ができないため、SQL Server のサービスで使用しているファイル以外のものも取得がされます。

そのため、インスタンス ルート ディレクトリのドライブに SQL Server のデータベースを格納していると、Windows Server 2008 ではシステム状態のバックアップでデータベースのファイルも取得がされてしまい、バックアップのファイルサイズが肥大化する可能性があります。

インスタンス ルート ディレクトリを変更することはあまりないかもしれませんが、変更する場合はバックアップの取得で影響がないようにデータベースのファイルを配置するような考慮が必要そうですね。

Written by Masayuki.Ozawa

12月 19th, 2010 at 10:11 pm

LocalSystem 以外で WMI イベント警告を受信

leave a comment

先日、SQL Server の WMI イベント警告について投稿をしました。

WMI イベント警告は SQL Server Agent 経由で実行されるため、SQL Server の権限も SQL Server Agent のサービスアカウントのものになります。

BOL に記載されているのですが、WMI イベント警告で警告を受信するためには、以下の権限が必要となります。

WMI 警告の通知を受信するには、SQL Server エージェントのサービス アカウントに、WMI イベントを含む名前空間、および ALTER ANY EVENT NOTIFICATION に対する権限が許可されている必要があります。

 

SQL Server Agent をローカルシステムアカウント (LocalSystem : NT AUTHORITYSYSTEM) で実行した場合は、特に設定を変更しなくても WMI イベント警告を受信できると思います。

SQL Server Agent を一般ユーザー (Users グループのユーザー) で起動した場合はどうなるでしょう。

イベントの条件に達してもイベントビューアーのアプリケーションに [124] のエラーが出力されて WMI イベント警告が実行されません。
image
image

この挙動の原因ですが、ローカルシステムアカウントの場合は、[ALTER ANY EVENT NOTIFICATION] のサーバーレベルの権限が付与されています。
サーバーレベルの権限は以下のクエリで確認ができます。

EXECUTE? AS LOGIN = ‘NT AUTHORITYSYSTEM’
SELECT SUSER_NAME(), USER_NAME()
SELECT * from fn_my_permissions(NULL, ‘SERVER’) ORDER BY permission_name
REVERT

SQL Server Agent が SQL Server に接続する場合は、[NT SERVICESQLAgent$<インスタンス名>] のログインで実行がされます。
# OS によってはサービスアカウントがそのまま使用されていることもあります。
image

このログインは、Windows 認証になっているので、Windows アカウントを使用したログインとなっています。
Windwos? Server 2008 R2 の場合はこのアカウントはサービス SID になっていて、Windows のログインやグループではなく、サービスに直接関連づいたアカウントになっています。

サービス SID の場合は、上記のクエリだと権限が見えないのですよね…。
サービス SID を使用していない OS (XP / 2003)? の場合は、SQL Server Agent の起動アカウントに、[ALTER ANY EVENT NOTIFICATION] の権限は付与されていませんでした。
ローカルシステム以外で SQL Server Agent を起動しているときは、以下のクエリを実行して、権限を付与する必要があります。

USE [master]
GO
GRANT ALTER ANY EVENT NOTIFICATION TO [NT SERVICESQLAgent$SQL2008R2]

権限を外す場合はこちらになります。
# REVOKE で権限を外します。

USE [master]
GO
REVOKE ALTER ANY EVENT NOTIFICATION TO [NT SERVICESQLAgent$SQL2008R2]

ALTER ANY EVENT NOTIFICATION の権限を付与することで、WMI イベントを SQL Server Agent のサービス起動ユーザーで受信できるようになり、WMI イベント警告を実行できるようになります。

WMI イベントを含む名前空間に関しては、インストール直後の状態の権限で問題は無いようです。

WMI の権限の確認についてですが、ファイル名を指定して実行から MMC を起動して、[WMI コントロール] を追加します。image

MMC に WMI のスナップインを追加した後に、WMI コントロールのプロパティを開きます。
image

WMI コントロールのプロパティが開いたら、[セキュリティ] タブの [RootMicrosoftSqlServerServerEvents<インスタンス名>] を選択して、[セキュリティ] をクリックします。
image

そうすると、WMI 名前空間に対してのセキュリティを確認することができます。
image

SQL Server Agent のサービス SID には、[メソッドの実行] [アカウントの有効化] [セキュリティの読み取り] の権限が付与されています。

WMI イベント警告を実行するために必要な権限はこの権限でよいようで、デフォルトから設定は変更しなくても実行することができました。

サーバーレベルで権限を付与する必要があり、この操作は普段あまりやらない作業だと思いますので、ちょっと戸惑うかもしれないですね。

Written by Masayuki.Ozawa

12月 19th, 2010 at 3:10 pm