多くのシステムがオンプレミスからクラウドへの移行を検討していると思いますが、システムの特性上クラウドへの移行が困難なため、クラウドへ移行可能なシステムのみを移行し、オンプレミス上のシステムと連携するハイブリット構成を採用したり、Azure SQL Database、Azure SQL Database Managed Instance などの PaaS ではなく、Azure 仮想マシン上に SQL Server をインストールするなど、SQL Server を管理していく人もまだまだ多いかと思います。
※ 海外では、クラウドからオンプレミス環境へ戻す企業も増えているという情報もあります。
引用 :
上記のような背景を踏まえて、SQL Server の基本についてまとめてみようと思います。なお、今回は「システム データベース」について、 自分の整理も兼ねて、まとめてみようと思います。
システム データベースとは
「システム データベース」は、名前の通り、SQL Server 内部で使用されるデータベースであり、SQL Server をインストールしたタイミングで作成されます。
※「ユーザー データベース」は、名前の通り、ユーザーで作成したデータベースとなります。
SQL Server Management Studio (以下 SSMS) で SQL Server インスタンスに接続すると、システム データベースを確認することができます。
なお、SSMSで表示されている「master」、「model」、「msdb」、「tempdb」と、SSMSでは表示されませんが、SQL Serverの実行モジュールが配置されているパス (既定: C:\Program Files\Microsoft SQL Server\MSSQL**.MSSQLSERVER\MSSQL\Binn) に配置されている「resource」がシステム データベースとなります。
システム データベースの用途について
各システム データベースの用途についてまとめてみます。
master データベース
ログイン ユーザー、データベースの配置パス、 システム構成情報(最大メモリ設定などのサーバー構成情報。システム カタログ ビュー「sys.configurations」 から確認可能)、エンドポイント(SQL Server AlwaysOn 可用性グループ、ミラーリングなどに使用)、サービス マスターキーなどのキー情報などを保持しているデータベース。
model データベース
新規データベースを作成する際のテンプレートとなるデータベース。
システム データベース「tempdb」は、 SQL Server 起動の毎に「model」データベースを使用して作成されることなります。また、新規でユーザーデータベースを作成する場合も「model」データベースを使用して作成されることになる。
例：「model」データベースにテーブル「tab1」を作成した状態で、新規ユーザーデータベース「UDB1」を作成すると、「UDB1」は「model」データベースをベースに作成されるため、既にテーブル「tab1」が存在した状態で作成されます。
msdb データベース
主に SQL Server Agent によって使用され、ジョブの設定、ジョブの実行履歴、メンテナンスプラン情報、 SQL Server Integration Services (以下 SSIS) のパッケージ、警告の設定などが保持されているデータベース。
tempdb データベース
一時テーブル、テーブル変数、カーソル、並び替え、結合操作(ハッシュ結合など)、インデックスの再構築、インデックスの作成、バージョンストア (行のバージョン情報を保持。本機能により、Oracleでいう読み取り一貫性と類似の機能を実現) などのオブジェクトが作成されるデータベース。
「tempdb」は、SQL Server インスタンスが起動する毎に再作成されるデータベースであり、「tempdb」に保持されている情報は、SQL Server インスタンスを再起動するとすべて消えてしまう。
[補足]
「tempdb」上に作成するオブジェクトへのアクセスが競合により遅延(ラッチ競合など) するという現象の発生を未然に防ぐため、「tempdb」のデータベース物理ファイルの数は、論理プロセッサが８個以下の場合は、論理プロセッサと同じ数、論理プロセッサが8個以上の場合、8個 作成し、競合の発生状況をみて、4の倍数ずつ増やしていくことが推奨されています。
SQL Server で「tempdb」のデータベース物理ファイル数を増やしていない場合は、増やすことを検討されると良いかと思います。
[補足]
SQL Server 2019 では、「メモリ最適化 tempdb メタデータ」機能が実装され、以前の SQL Server で発生していたメタデータの競合を解消することができるようになりましたが、一時テーブルに列ストア インデックスが作成することが出来ないなどの制約が発生するため、「tempdb」のデータベース物理ファイル数を増やしてもメタデータの競合が改善しない場合以外では、現時点では使用しなくても良いかと思います。
resource データベース
システム オブジェクト (sys.databases などのシステム カタログ ビューなど) を格納する読み取り専用のデータベース。SQL Server の以前のバージョン (SQL Server 2000 より以前) では、「master」データベースにシステム オブジェクトが含まれていたようですが、「resource」データベースにすべてのシステム オブジェクトを格納することで、「resource」データベースファイルをローカルにコピーするだけで、SQL Server を新しいバージョンへアップグレードできるように改良されています。
システム データベースのバックアップについて
master データベース
基本的に、ログインを追加するなど「master」データベースに対して更新が発生したタイミング、SQL Server サービスパック、累積的な更新プログラムパッケージを適用前、適用後に完全バックアップを採取すると良いかと思います。
「master」データベースが破損した場合、SQL Server インスタンスを起動することができなくなるなります。その場合、バックアップが存在すれば、バックアップからのリカバリが可能となり、バックアップが存在しない場合、 システム データベースの再構築が必要なります。
model データベース
基本的に、「model」データベースに対して更新が発生したタイミング、SQL Server サービスパック、累積的な更新プログラムパッケージを適用前、適用後に完全バックアップを採取すると良いかと思います。
msdb データベース
基本的に、SQL Server サービスパック、累積的な更新プログラムパッケージを適用前、適用後に完全バックアップを採取すると良いかと思います。
なお、仮に「msdb」が破損し、「msdb」のバックアップが存在しない場合においても、データは失われますが「msdb」を再作成することでリカバリすることができます。
tempdb データベース
SQL Server インスタンスが起動する毎に再作成されるデータベースであるため、バックアップを実施する必要がありません。
resource データベース
本データベースをバックアップすることはできません。 SQL Server サービスを停止したうえで、「resource」データベースが配置されたパス (既定: C:\Program Files\Microsoft SQL Server\MSSQL**.MSSQLSERVER\MSSQL\Binn) にあるデータベース物理ファイル(.mdf)、トランザクションログファイル(.ldf) をコピーして退避することはできます。
本データベースが破損した場合、SQL Server インスタンスが起動できなくなりますが、破損前と同じバージョンの「resource」データベースのデータベース物理ファイル(.mdf)、トランザクションログファイル(.ldf) が存在する場合は、該当ファイルを「resouce」データベースが配置されているパスにコピーすることでリカバリが可能です。
なお、破損前と同じバージョンの「resource」データベースのデータベース物理ファイル(.mdf)、トランザクションログファイル(.ldf) が存在しない場合、SQL Server インストール メディアが必要になりますが、修復インストールによりリカバリすることが可能です。
まとめ
今回は、SQL Server の「システム データベース」に関する内容をまとめてみました。
Azure SQL Database、Azure SQL Database Managed Instance などの PaaS サービスの場合、「システム データベース」で問題が発生した場合の対処をユーザー側で直接実施する必要はないかと思いますが、オンプレミス環境上の SQL Server、Azure 仮想マシン上にインストールされた SQL Server を使用する場合、「システム データベース」が破損するなどの現象が発生した場合、ユーザー側で復旧しなければならないため、「システム データベース」を復旧させる手順についても、 しっかりと理解して、対応できるようにしておくと良いかと思います。
【第2回】基本から始める SQL Server【データベース ファイル】へ
