# バッチ設計書 12-sp_AllNightLog_Setup

## 概要

本ドキュメントは、sp_AllNightLog_Setupストアドプロシージャのバッチ設計書です。本プロシージャは、sp_AllNightLogの実行環境を構築・設定するための非推奨（Deprecated）ストアドプロシージャです。

### 本バッチの処理概要

sp_AllNightLog_Setupは、sp_AllNightLogを実行するために必要なデータベース、テーブル、SQL Server Agentジョブを自動的に作成・設定するセットアッププロシージャです。初期構築と設定更新の両方の機能を提供します。

**業務上の目的・背景**：本バッチは、sp_AllNightLogによるログバックアップ・リストア自動化環境を構築するために必要です。手動でのデータベース作成、テーブル作成、ジョブ作成の手間を省き、一貫した構成を確保します。ただし、現在は非推奨となっており、代替手段としてAlways On可用性グループやネイティブのログシッピングソリューションの使用が推奨されます。

**バッチの実行タイミング**：初期セットアップ時に1回実行、または設定変更時に随時実行します。

**主要な処理内容**：
1. 前提条件の検証（SQL Server Agent稼働状態、依存プロシージャの存在確認）
2. msdbCentralデータベースの作成（存在しない場合）
3. backup_configuration、backup_workerテーブルの作成と初期データ投入
4. restore_configuration、restore_workerテーブルの作成（msdbデータベース内）
5. ten_secondsスケジュールの作成
6. ポーリングジョブ（PollForNewDatabases、PollDiskForNewDatabases）の作成
7. バックアップワーカージョブ（最大20個）の作成
8. リストアワーカージョブ（最大20個）の作成
9. 設定更新時のRPO/RTO/パス設定の更新

**前後の処理との関連**：本プロシージャを実行後、sp_AllNightLogのワーカージョブが有効化されます。Ola HallengrenのCommandExecute、DatabaseBackup、およびsp_DatabaseRestoreが事前にインストールされている必要があります。

**影響範囲**：msdbCentralデータベースの作成、msdbデータベースへのテーブル追加、SQL Server Agentジョブの作成

## バッチ種別

環境構築 / 設定管理

## 実行スケジュール

| 項目 | 内容 |
|-----|------|
| 実行頻度 | 随時（初期セットアップ時、設定変更時） |
| 実行時刻 | 任意 |
| 実行曜日 | 該当なし |
| 実行日 | 該当なし |
| トリガー | 手動実行 |

## 実行条件

### 前提条件

| 条件 | 説明 |
|-----|------|
| xp_cmdshell有効化 | ディスクディレクトリの読み取りに必要 |
| SQL Server Agent稼働中 | ジョブ作成・実行に必要 |
| Ola Hallengren CommandExecute | 同一データベースにインストール必須 |
| Ola Hallengren DatabaseBackup | 同一データベースにインストール必須 |
| sp_DatabaseRestore | 同一データベースにインストール必須 |
| @BackupPath | @RunSetup=1時は必須パラメータ |

### 実行可否判定

- @RunSetup=1または@UpdateSetup=1のいずれかが設定されていること
- @RPOSecondsが正の値（14400秒未満推奨）
- @Jobsが1〜20の範囲内
- @EnableBackupJobsと@EnableRestoreJobsの両方が1にならないこと
- SQL Server Agentが稼働中であること

## 入力仕様

### 入力パラメータ

| パラメータ名 | 型 | 必須 | デフォルト値 | 説明 |
|-------------|-----|-----|-------------|------|
| @RPOSeconds | BIGINT | No | 30 | ログバックアップの間隔（秒） |
| @RTOSeconds | BIGINT | No | 30 | ログリストアの間隔（秒） |
| @BackupPath | NVARCHAR(MAX) | Yes* | NULL | バックアップファイルの出力先パス（*@RunSetup=1時必須） |
| @RestorePath | NVARCHAR(MAX) | No | NULL | リストア元のバックアップファイルパス |
| @Jobs | TINYINT | No | 10 | 作成するワーカージョブ数（1-20） |
| @RunSetup | BIT | No | 0 | 1の場合、初期セットアップを実行 |
| @UpdateSetup | BIT | No | 0 | 1の場合、設定更新を実行 |
| @EnableBackupJobs | INT | No | NULL | バックアップジョブの有効/無効設定 |
| @EnableRestoreJobs | INT | No | NULL | リストアジョブの有効/無効設定 |
| @Debug | BIT | No | 0 | 1の場合、デバッグ情報を出力 |
| @FirstFullBackup | BIT | No | 0 | 1の場合、初回フルバックアップ必要フラグを設定 |
| @FirstDiffBackup | BIT | No | 0 | 1の場合、初回差分バックアップ必要フラグを設定 |
| @MoveFiles | BIT | No | 1 | 1の場合、リストア時にファイルをデフォルトパスに移動 |
| @Help | BIT | No | 0 | 1の場合、ヘルプ情報を表示 |
| @Version | VARCHAR(30) | No | NULL | OUTPUT: バージョン番号を返却 |
| @VersionDate | DATETIME | No | NULL | OUTPUT: バージョン日付を返却 |
| @VersionCheckMode | BIT | No | 0 | 1の場合、バージョン確認のみで終了 |

### 入力データソース

| データソース | 形式 | 説明 |
|-------------|------|------|
| sys.databases | DB | 既存データベースの確認 |
| sys.dm_server_services | DB | SQL Server Agent稼働状態の確認 |
| sys.procedures | DB | 依存プロシージャの存在確認 |
| sys.configurations | DB | xp_cmdshell設定の確認 |
| msdb.dbo.sysschedules | DB | スケジュールの存在確認 |
| msdb.dbo.sysjobs | DB | ジョブの存在確認 |

## 出力仕様

### 出力データ

| 出力先 | 形式 | 説明 |
|-------|------|------|
| msdbCentralデータベース | DB | 新規作成（存在しない場合） |
| msdbCentral.dbo.backup_configuration | DB | バックアップ設定テーブル |
| msdbCentral.dbo.backup_worker | DB | バックアップワーカーキューテーブル |
| msdb.dbo.restore_configuration | DB | リストア設定テーブル |
| msdb.dbo.restore_worker | DB | リストアワーカーキューテーブル |
| SQL Server Agentジョブ | DB | ポーリングジョブ、ワーカージョブ |
| SQL Server Agentスケジュール | DB | ten_secondsスケジュール |

### 出力ファイル仕様

| 項目 | 内容 |
|-----|------|
| ファイル名 | 該当なし（DBのみ） |
| 出力先 | 該当なし |
| 文字コード | 該当なし |
| 区切り文字 | 該当なし |

## 処理フロー

### 処理シーケンス

```
1. パラメータ検証
   └─ @RunSetup/@UpdateSetup、@RPOSeconds、@Jobs、@BackupPathの妥当性確認
2. 前提条件確認
   └─ xp_cmdshell、SQL Server Agent、依存プロシージャの確認
3. @UpdateSetup=1の場合
   └─ UpdateConfigsへジャンプ
4. @RunSetup=1の場合
   4.1 msdbCentralデータベース作成
   4.2 backup_configurationテーブル作成・初期データ投入
   4.3 backup_workerテーブル作成・初期データ投入
   4.4 restore_configurationテーブル作成・初期データ投入
   4.5 restore_workerテーブル作成・初期データ投入
   4.6 ten_secondsスケジュール作成
   4.7 PollForNewDatabasesジョブ作成
   4.8 PollDiskForNewDatabasesジョブ作成
   4.9 バックアップワーカージョブ作成（ループ）
   4.10 リストアワーカージョブ作成（ループ）
5. UpdateConfigs処理
   └─ RPO/RTO/パス設定の更新、ジョブの有効/無効切り替え
```

### フローチャート

```mermaid
flowchart TD
    A[バッチ開始] --> B{パラメータ検証}
    B -->|無効| Z[エラー終了]
    B -->|有効| C{前提条件チェック}
    C -->|失敗| Z
    C -->|成功| D{@UpdateSetup?}
    D -->|1| E[UpdateConfigs]
    D -->|0| F{@RunSetup?}
    F -->|0| Z
    F -->|1| G[msdbCentral作成]
    G --> H[テーブル作成]
    H --> I[スケジュール作成]
    I --> J[ポーリングジョブ作成]
    J --> K[バックアップジョブ作成ループ]
    K --> L[リストアジョブ作成ループ]
    L --> M[セットアップ完了]
    E --> N{設定更新処理}
    N --> O[RPO/RTO更新]
    O --> P[パス設定更新]
    P --> Q[ジョブ有効/無効切替]
    Q --> R[更新完了]
    M --> S[バッチ終了]
    R --> S
```

## データベース操作仕様

### 操作別データベース影響一覧

| 処理 | 対象テーブル | 操作種別 | 概要 |
|-----|-------------|---------|------|
| DB作成 | msdbCentral | CREATE DATABASE | リポジトリデータベース作成 |
| テーブル作成 | msdbCentral.dbo.backup_configuration | CREATE TABLE | バックアップ設定テーブル |
| テーブル作成 | msdbCentral.dbo.backup_worker | CREATE TABLE | バックアップワーカーテーブル |
| テーブル作成 | msdb.dbo.restore_configuration | CREATE TABLE | リストア設定テーブル |
| テーブル作成 | msdb.dbo.restore_worker | CREATE TABLE | リストアワーカーテーブル |
| 初期データ投入 | msdbCentral.dbo.backup_configuration | INSERT | RPO、パス等の設定値 |
| 初期データ投入 | msdbCentral.dbo.backup_worker | INSERT | 既存データベース一覧 |
| 初期データ投入 | msdb.dbo.restore_configuration | INSERT | RTO、パス等の設定値 |
| 設定更新 | msdbCentral.dbo.backup_configuration | UPDATE | RPO/パス変更 |
| 設定更新 | msdb.dbo.restore_configuration | UPDATE | RTO/パス変更 |

### テーブル別操作詳細

#### msdbCentral.dbo.backup_configuration

| 操作 | 項目（カラム名） | 更新値・取得条件 | 備考 |
|-----|-----------------|-----------------|------|
| INSERT | database_name | 'all' | 全データベース共通設定 |
| INSERT | configuration_name | 'log backup frequency' | RPO設定 |
| INSERT | configuration_setting | @RPOSeconds | 秒単位 |
| INSERT | configuration_name | 'log backup path' | バックアップパス設定 |
| INSERT | configuration_setting | @BackupPath | パス文字列 |
| INSERT | configuration_name | 'change backup type' | デフォルト'MSDB' |
| INSERT | configuration_name | 'encrypt' | 暗号化設定（NULL） |
| INSERT | configuration_name | 'encryptionalgorithm' | 暗号化アルゴリズム（NULL） |
| INSERT | configuration_name | 'servercertificate' | サーバー証明書（NULL） |

#### msdbCentral.dbo.backup_worker

| 操作 | 項目（カラム名） | 更新値・取得条件 | 備考 |
|-----|-----------------|-----------------|------|
| INSERT | database_name | sys.databasesから | database_id > 4のみ |
| デフォルト | last_log_backup_start_time | '19000101' | 初期値 |
| デフォルト | last_log_backup_finish_time | '99991231' | 初期値 |
| デフォルト | is_started | 0 | 初期値 |
| デフォルト | is_completed | 0 | 初期値 |
| デフォルト | ignore_database | 0 | 初期値 |
| デフォルト | full_backup_required | @FirstFullBackup値 | パラメータ依存 |
| デフォルト | diff_backup_required | @FirstDiffBackup値 | パラメータ依存 |

## エラー処理

### エラーケース一覧

| エラーコード | エラー種別 | 発生条件 | 対処方法 |
|------------|----------|---------|---------|
| N/A | パラメータエラー | @RunSetupと@UpdateSetupの両方が0 | いずれかを1に設定 |
| N/A | パラメータエラー | @RPOSecondsが負の値 | 正の値を指定 |
| N/A | パラメータエラー | @Jobsが1-20の範囲外 | 1-20の範囲で指定 |
| N/A | パラメータエラー | @RPOSecondsが14400以上 | より小さい値を指定（またはSIMPLEモード推奨） |
| N/A | パラメータエラー | @EnableBackupJobsと@EnableRestoreJobsの両方が1 | どちらか一方のみ1に |
| N/A | 構成エラー | xp_cmdshell無効 | xp_cmdshellを有効化 |
| N/A | 構成エラー | SQL Server Agent停止中 | SQL Server Agentを起動 |
| N/A | 依存エラー | CommandExecute未インストール | 依存プロシージャをインストール |
| N/A | 依存エラー | DatabaseBackup未インストール | 依存プロシージャをインストール |
| N/A | 依存エラー | sp_DatabaseRestore未インストール | 依存プロシージャをインストール |
| N/A | パラメータエラー | @BackupPathがNULL（@RunSetup=1時） | 有効なパスを指定 |
| N/A | パラメータエラー | @BackupPathが無効なパス形式 | 正しいパス形式で指定 |

### リトライ仕様

| 項目 | 内容 |
|-----|------|
| リトライ回数 | なし（セットアップは1回実行） |
| リトライ間隔 | 該当なし |
| リトライ対象エラー | 該当なし |

### 障害時対応

セットアップ中にエラーが発生した場合、TRY/CATCHブロックでエラーが捕捉され、エラー情報がRAISERRORで出力されます。トランザクションが開いている場合はROLLBACKが実行されます。エラー発生後は手動で環境を確認し、必要に応じて部分的に作成されたオブジェクトを削除してから再実行してください。

## トランザクション仕様

| 項目 | 内容 |
|-----|------|
| トランザクション範囲 | エラー発生時のロールバック用 |
| コミットタイミング | 各操作は暗黙的にコミット |
| ロールバック条件 | TRY/CATCHでエラー捕捉時 |

## パフォーマンス要件

| 項目 | 内容 |
|-----|------|
| 想定処理件数 | 1回（セットアップ） |
| 目標処理時間 | 数秒〜1分程度 |
| メモリ使用量上限 | SQL Server設定に依存 |

## 排他制御

- セットアップ処理は同時実行を想定していない
- ジョブ有効/無効切り替え時、カーソルを使用してジョブを順次更新
- リストアジョブ無効化時、実行中ジョブの完了を最大2分間待機

## ログ出力

| ログ種別 | 出力タイミング | 出力内容 |
|---------|--------------|---------|
| 進捗ログ | 各処理ステップ | Checking for msdbCentral, Creating table等 |
| 完了ログ | セットアップ完了時 | Setup complete! |
| 更新ログ | 設定更新時 | Attempting to update RPO/RTO/Path setting |
| 完了ログ | 更新完了時 | Update complete! |
| エラーログ | エラー発生時 | エラー番号、エラーメッセージ |
| デバッグログ | @Debug=1時 | 動的SQL文 |

## 監視・アラート

| 監視項目 | 閾値 | アラート先 |
|---------|-----|----------|
| セットアップエラー | エラー発生 | 手動確認（RAISERRORメッセージ） |
| ジョブ切り替えタイムアウト | 2分超過 | 処理中断、手動対応必要 |

## 備考

- バージョン：8.19（2024-02-22）
- 本プロシージャは非推奨（Deprecated）であり、Deprecatedフォルダに移動済み
- msdbCentralデータベース名はハードコードされており変更不可
- 作成されるジョブは「Database Maintenance」カテゴリに分類される
- ジョブオーナーはsaユーザー（0x01 SID）に設定される
- @EnableBackupJobs=1と@EnableRestoreJobs=1の同時指定は禁止（相互排他）
- リストアジョブを無効化してからバックアップジョブを有効化する際、実行中のリストアジョブの完了を最大2分間待機
- msdbCentralが復元中（restoring）状態の場合、バックアップジョブ有効化時に自動的にRESTORE WITH RECOVERYを実行
