SE の雑記

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

Resource データベースについて

leave a comment

先日、
@IT などを運営されている
アイティメディア株式会社様

の会議スペースをお借りして開催された、SQL Server の勉強会に参加をしてきました。

その勉強会の中で SQL Server の [Resource] データベースについて質問を受けたので、このデータベースについて少し書いてみたいと思います。

Resource データベースに関しては、Books Online で以下のように記載されています。

Resource データベースは、読み取り専用のデータベースで、SQL Server に含まれるすべてのシステム オブジェクトがこれに格納されます。
SQL Server システム オブジェクト (sys.objects など) は、物理的には Resource データベースに保存されていますが、論理的にはすべてのデータベースの sys スキーマ内にあるように扱われます。
Resource データベースには、ユーザーのデータやユーザーのメタデータは保持されません。

各データベースのシステムビューやシステムストアドプロシージャーなどで使用されている、[sys.xxxx] 関連の情報は [Resource] データベースの内容を透過的に参照している形になります。
# Resource データベースは SSMS (SQL Server Management Studio) には表示されません。
imageimage

普段何気なく使用している、[sys.master_files] のようなシステムビューですが、これは [ビュー] と名前がついている用に実体ではなく、実表を参照しているものになります。

ではこの実体はどこに格納されているかというと実体は [master] データベースに保存されています。
試しに [sys.master_files] のビュー定義を確認してみたいと思います。
確認するためには、[SELECT OBJECT_DEFINITION(OBJECT_ID(‘sys.master_files’))] というように OBJECT_DEFINITION を使います。

CREATE VIEW sys.master_files AS  
SELECT
    database_id  = f.dbid,
    file_id   = f.fileid,
    file_guid   = f.fileguid,
    type   = f.filetype,
    type_desc  = ft.name,
    data_space_id  = f.grpid,
    name    = f.lname,
    physical_name  = f.pname,
    state   =
        convert(tinyint,
            case f.filestate  — Map enum EMDFileState to AvailablityStates         
                when 0 then 0 when 10 then 0 — ONLINE       
                when 4 then 7 — DEFUNCT       
                when 5 then 3
                when 9 then 3 — RECOVERY_PENDING       
                when 7 then 1
                when 8 then 1
                when 11 then 1 — RESTORING       
                when 12 then 4 — SUSPECT       
            else 6 end), — OFFLINE   
    state_desc  = st.name,
    f.size,
    max_size    = f.maxsize,
    f.growth,
    is_media_read_only  = sysconv(bit, f.status & 8),  — FIL_READONLY_MEDIA
    is_read_only    = sysconv(bit, f.status & 16), — FIL_READONLY
    is_sparse    = sysconv(bit, f.status & 256), — FIL_SPARSE_FILE
    is_percent_growth  = sysconv(bit, f.status & 32), — FIL_PERCENT_GROWTH
    is_name_reserved  = sysconv(bit, case f.filestate when 3 then 1 else 0 end), — x_efs_DroppedReusePending
    create_lsn   = GetNumericLsn(f.createlsn),
    drop_lsn    = GetNumericLsn(f.droplsn),
    read_only_lsn   = GetNumericLsn(f.readonlylsn),
    read_write_lsn  = GetNumericLsn(f.readwritelsn),
    differential_base_lsn = GetNumericLsn(f.diffbaselsn),
    differential_base_guid = f.diffbaseguid,
    differential_base_time = nullif(f.diffbasetime, 0),
    redo_start_lsn   = GetNumericLsn(f.redostartlsn),
    redo_start_fork_guid = f.redostartforkguid,
    redo_target_lsn  = GetNumericLsn(f.redotargetlsn),
    redo_target_fork_guid = f.forkguid,
    backup_lsn   = GetNumericLsn(f.backuplsn)
FROM
    master.sys.sysbrickfiles f
    LEFT JOIN
        sys.syspalvalues st
        ON
        st.class = ‘DBFS’
        AND
        st.value = f.filestate
    LEFT JOIN
        sys.syspalvalues ft
        ON
        ft.class = ‘DBFT’
        AND ft.value = f.filetype
WHERE
    f.dbid < 0x7fff — consistent with sys.databases
    AND
    f.pruid = 0
    AND
    f.filestate NOT IN (1, 2) — x_efs_Dummy, x_efs_Dropped
    AND
    has_access(‘MF’, 1) = 1 

ビューの元になるデータは [master.sys.sysbrickfiles] というテーブルになっています。
Resource データベースはこのようなシステムビューやシステムストアドプロシージャを格納し、master 上に格納されている情報を透過的に見せるために使用されています。
# 今回のベース表になっている、[master.sys.sysbrickfiles] は直接参照できないので。

この Resource データベースですが、以下の 2 つのファイルから構成されています。

  • mssqlsystemresource.mdf
  • mssqlsystemresource.ldf

通常、システムデータベースは
[C:Program FilesMicrosoft SQL ServerMSSQL10_50.<インスタンス ID>MSSQLDATA]
というディレクトリに格納されるのですが、リソースデータベースのファイルは、
[C:Program FilesMicrosoft SQL ServerMSSQL10_50.<インスタンス ID>MSSQLBinn]
に格納がされます。
インスタンスのプログラム扱いでファイルが格納されていますね。

この Resource データベースですが、バージョンがあり [SELECT SERVERPROPERTY(‘ResourceVersion’)] というクエリを実行することでバージョンを確認することができます。

このバージョンですが、SQL Server のバージョンと一致するようで、

  • SQL Server 2008 R2 RTM : [10.50.1600]
  • SQL Server 2008 R2 CU4 : [10.50.1746]

となります。

修正プログラムをインストールすると、Resource データベースのバージョンも更新されます。

Resouce データベースには実データではなく実データを見せるための定義が格納されているので、Books Online で

SQL Server では、Resource データベースをバックアップできません。
ファイル ベースまたはディスク ベースのバックアップは、mssqlsystemresource.mdf ファイルをデータベース ファイルではなくバイナリ (.EXE) ファイルのように実行できますが、SQL Server を使用してバックアップを復元することはできません。
mssqlsystemresource.mdf のバックアップ コピーの復元は手動でのみ実行できます。
また、現在の Resource データベースを古いバージョンや安全でない可能性のあるバージョンで上書きしないように注意する必要があります。

と書かれているのは、実データは含まれず定義のみが格納されており適切なバージョンの SQL Server をインストールすれば最適な状態になるからだと思います。

 

Resource データベースは SSMS に表示がされないので通常はどのような情報が格納されているのか見ることはできないのですが、実はデータベースのファイルをコピーしてアタッチすることで内容を確認することができます。
# mssqlsystemresource.mdf / ldf は読み取り専用で SQL Server 上で認識されているのでオンラインでもコピー可能です。

通常のアタッチでは、以下のようなエラーになります。
image
image

このエラーですが、[mssqlsystemresource] というデータベース名でアタッチをしようとしたために発生しています。
# Resource データベースですが内部的には、[mssqlsystemresource] で認識されているためだと思います。

[次の名前でアタッチ] のデータベースの名前を変更することで Resource データベースをアタッチすることが可能です。
今回は、Resource というデータベース名でアタッチしています。
image

アタッチをすることで Resource データベースの内容を直接確認することが可能になります。
image

通常、[sys.xxxxx] はシステム ビューやシステム ストアド プロシージャーとして認識されているのですが、アタッチした Resource データベースではユーザービュー / ユーザー ストアド プロシージャーとして認識がされます。
# システムビューやシステム ストアド プロシージャーとしても認識はされるのですが。

imageimage

システム ビュー / システム ストアド プロシージャーの状態では、スクリプト化ができないため [OBJECT_DEFINITION] を介して定義を確認していました。
image

ユーザーオブジェクトになっていればスクリプト化が可能なため、[sys.xxxx] といったものも簡単に内容を確認できます。
imageimage

 

Resource データベースは SQL Server 2005 になってから搭載されたものなのですが、通常使用している中ではあまり意識はしないので、どんなデータが入っているのかイメージがしにくいのですよね…。
# 私は、sys. 系の定義が入っているだけだと考えているのですが。

Written by masayuki.ozawa

11月 7th, 2010 at 10:54 pm

Posted in SQL Server

Leave a Reply

*