SQL Server メンテナンス プランを作成する
定期的な SQL Server メンテナンスのスケジュールを設定できる一般的なアクティビティは次のとおりです。
- データベースとトランザクション ログのバックアップ
- データベースの整合性チェック
- インデックスのメンテナンス
- 統計の更新
すべてのデータベースのバックアップの重要性と、インデックスと統計のメンテナンスを理解することが重要です。 データベース整合性チェック ( CHECKDB とも呼ばれます) (コマンド DBCC CHECKDBを使用) は、データベース全体の破損をチェックする唯一の方法であるため、同様に重要です。 データベースのサイズとアップタイムの要件によっては、これらすべてのアクティビティを夜間に実行できます。 ただし、運用システムでは、インデックスのメンテナンスと整合性チェックの両方が非常に I/O を集中的に使用し、通常は週末の時間帯に行われるので、メンテナンス操作は 1 週間にわたって分散されることがよくあります。
多くの DBA は大規模なデータベースのバックアップをずらし、週に 1 回の完全バックアップを実行し、差分およびトランザクション ログ バックアップを使用して特定の時点への復旧を管理します。 SQL Server には、メンテナンス プランを使用してこれらすべてのタスクを管理するための組み込みの方法が用意されています。 メンテナンス プランでは、データベースをサポートするタスクのワークフローが作成され、Integration Services パッケージとして作成されるため、メンテナンス アクティビティをスケジュールできます。 さらに、多くの DBA では、データベースのメンテナンスにオープンソース スクリプトを使用して、メンテナンス アクティビティの柔軟性と制御を強化します。
メンテナンス プランのベスト プラクティス
メンテナンス プランは、データベースのメンテナンスを実行するのに役立つだけでなく、SQL Server エージェントのデータ ストアとして機能する msdb データベースからデータを排除するオプションも提供します。 また、メンテナンス プランでは、ディスクからの古いデータベース バックアップの削除を指定できます。 古いバックアップ ファイルを削除すると、バックアップ ボリュームのサイズが小さくなります。また、msdb データベースのサイズを管理するのに役立ちます。
バックアップ保有期間が整合性チェック時間より長くなるようにしてください。 たとえば、整合性チェックを毎週実行する場合は、整合性チェック中に検出された潜在的な破損から回復するために十分なバックアップ履歴を保持する必要があります。 バックアップ操作ではデータベースの破損が検出されないため、バックアップ ファイル内で破損する可能性があることに注意してください。 メンテナンス プランの操作は、SQL Server エージェント ジョブとして実行がスケジュールされます。
メンテナンス プランを作成する
次に示すように、SQL Server Management Studio を使用してメンテナンス プランを作成できます。 この例では、複数のメンテナンス タスクが 1 つのメンテナンス プランに結合されています。 ただし、ベスト プラクティスは、タスクの種類ごとに個別のメンテナンス プランを作成し、場合によってはサーバー上の特定のデータベースに対しても作成することです。 たとえば、1 つのメンテナンス プランを作成してシステム データベースをバックアップし、もう 1 つを作成してユーザー データベースをバックアップできます。 さらに、特に大規模なユーザー データベースのバックアップを処理するための個別のメンテナンス プランを用意することもできます。 次の図と次の例は、メンテナンス プラン ウィザードを使用してメンテナンス プランを作成する方法を示しています。
この図は、SQL Server Management Studio (SSMS) のメンテナンス プラン ウィザードの最初の画面を示しています。 メンテナンス プランの名前と実行アカウントを指定する必要があります。 ほとんどのメンテナンス タスクは SQL Server エージェント サービス アカウントとして実行されますが、セキュリティ上の理由から、一部のタスクは別のアカウントとして実行する必要があります。 たとえば、特定のアカウントのみがアクセスできるファイル共有にバックアップする必要がある場合は、SQL Server エージェントのコンポーネントであるプロキシ ユーザーを使用します。
プロキシ アカウントとは
プロキシ アカウントは、SQL Server エージェントが指定されたユーザーとして特定のジョブ ステップを実行するために使用できる、保存された資格情報を持つアカウントです。 このユーザーのログイン情報は、資格情報として SQL Server インスタンスに格納されます。 プロキシ アカウントは、通常、特定のジョブ ステップで非常に細かいセキュリティ権限が必要な場合に使用されます。
データベースをネットワーク ファイル共有にバックアップする必要がある SQL Server エージェント ジョブがあるとします。 SQL Server エージェント サービス アカウントにファイル共有へのアクセス権がない場合は、必要なアクセス許可を持つプロキシ アカウントを作成できます。 その後、このプロキシ アカウントを使用してバックアップ 手順を実行し、必要なアクセス権があることを確認できます。
ジョブスケジュール
ジョブ スケジュールは、msdb システム データベースのジョブ システムの一部です。 SQL Server エージェントのジョブとスケジュールには多対多のリレーションシップがあります。つまり、各ジョブには複数のスケジュールを設定でき、各スケジュールを複数のジョブに割り当てることができます。 ただし、メンテナンス プラン ウィザードでは、独立したスケジュールを作成できません。 代わりに、メンテナンス プランごとに特定のスケジュールが作成されます。
次の例は、毎週の実行のスケジュールを示していますが、時間単位または日単位の繰り返しを使用してスケジュールを作成することもできます。
次の手順では、プランに追加するメンテナンス タスクを選択します。 次の例は、メンテナンス プランで実行できる操作を示しています。
データベースの整合性を確認 する - このタスクでは、 DBCC CHECKDB コマンドを実行して、各データベース ページの論理的および物理的な整合性を検証します。 このタスクは定期的に実行し、バックアップリテンション期間に合わせる必要があります。 破損の引き継ぎを防ぐために、以前のバックアップを破棄する前に整合性チェックを完了してください。
データベースの圧縮 - このタスクは、ページ上の空き領域にデータを移動することで、データベースまたはトランザクション ログ ファイルのサイズを小さくします。 十分な領域が解放されると、ファイル システムに返すことができます。 インデックスの断片化が深刻になり、データベースのパフォーマンスが低下するため、このアクションを定期的なメンテナンスに含めないことをお勧めします。 また、この操作は I/O と CPU の負荷が非常に高く、システムのパフォーマンスに大きな影響を与える可能性があります。
インデックスの再構成/再構築 - このタスクは、データベースのインデックス内の断片化のレベルをチェックし、ユーザー定義の断片化レベルに基づいてインデックスを再構築または再構成します。 インデックスを再構築すると、その統計も更新されます。
統計の更新 - このタスクは、SQL Server がクエリ実行プランを作成するために使用する列とインデックスの統計を更新します。 クエリ オプティマイザーが最適な意思決定を行うには、正確な統計が不可欠です。 スキャンするテーブルとインデックス、およびスキャンする行の割合または数を選択できます。 通常、既定のサンプリング レートで十分ですが、特定のテーブルに対してより詳細な統計が必要になる場合があります。
クリーンアップ履歴 - このタスクは、 msdb データベースからのバックアップ操作と復元操作の履歴と、SQL Server エージェント ジョブの履歴を削除します。
msdb データベースのサイズを管理するのに役立ちます。
SQL Server エージェント ジョブの実行 - このタスクは、ユーザー定義の SQL Server エージェント ジョブを実行します。
データベースのバックアップ (完全/差分/ログ) - このタスクは、SQL Server インスタンス上のデータベースをバックアップします。 完全バックアップはデータベース全体をキャプチャし、復元の開始点として機能します。 差分バックアップでは、前回の完全バックアップ以降に変更されたページがキャプチャされ、増分復元ポイントが提供されます。 トランザクション ログ バックアップでは、トランザクション ログ内のアクティブなページがキャプチャされるため、復旧ポイントの目標を定義できます。 SIMPLE 復旧モードでは、データベースに対してトランザクション ログ バックアップを実行できないことに注意してください。
たとえば、毎週平日の完全バックアップと差分バックアップを実行して、木曜日の正午にデータベースを復元する場合は、日曜日の完全バックアップ、水曜日の差分バックアップ、およびトランザクション ログ バックアップを水曜日の差分から木曜日の正午に復元します。
メンテナンス クリーンアップ タスク - このタスクは、テキスト レポートやバックアップ ファイルなど、メンテナンス プランに関連する古いファイルを削除します。 指定したフォルダー内のバックアップのみが削除されるため、サブフォルダーを明示的に一覧表示する必要があります。または、サブフォルダーはスキップされます。
各タスクは、ユーザー データベース、システム データベース、またはデータベースのカスタム選択にスコープを設定でき、それぞれに特定の構成オプションがあります。
作成すると、プランは SQL Server エージェントにジョブとして表示されます。 作成プロセス中またはプロセス後にスケジュールを追加した場合、そのジョブが実行され、メンテナンス タスクが実行されます。
マルチサーバー環境
マルチサーバー環境では、SQL Server エージェントでは、1 つのサーバーを、ターゲット サーバー (TSX) と呼ばれる他のサーバーでジョブを実行できる中央サーバー (MSX) として指定できます。 中央サーバーは、ジョブのメイン ソースを格納し、ターゲット サーバーに配布します。 ターゲット サーバーは、定期的に中央サーバーに接続してジョブ スケジュールを更新します。 このセットアップにより、ジョブを 1 回定義し、企業全体に展開できます。 たとえば、プライマリ サーバーでデータベース メンテナンス タスクを構成し、それらをターゲット サーバーのグループにプッシュして、一貫性のあるデプロイを確保できます。