# バッチ設計書 11-sp_AllNightLog

## 概要

本ドキュメントは、sp_AllNightLogストアドプロシージャのバッチ設計書です。本プロシージャは、SQL Serverにおけるトランザクションログのバックアップとリストア処理を自動化するための非推奨（Deprecated）ストアドプロシージャです。

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

sp_AllNightLogは、SQL Serverのデータベースに対して継続的なログバックアップとログリストアを自動化するワーカープロシージャです。複数のモード（ポーリング、バックアップ、リストア）で動作し、設定された間隔で新しいデータベースの検出、ログバックアップの実行、ログリストアの実行を行います。

**業務上の目的・背景**：本バッチは、ディザスタリカバリ環境において、プライマリサーバーからセカンダリサーバーへのログシッピングを自動化するために設計されました。RPO（Recovery Point Objective）とRTO（Recovery Time Objective）を最小化し、データ損失リスクを低減することが主な目的です。しかし、現在は非推奨となっており、代替手段としてAlways On可用性グループやネイティブのログシッピングソリューションの使用が推奨されます。

**バッチの実行タイミング**：SQL Server Agentジョブにより10秒間隔でスケジュール実行されます。ジョブは常時稼働し、継続的にポーリングまたは処理を行います。

**主要な処理内容**：
1. PollForNewDatabases：sys.databasesを定期的にポーリングし、新規データベースをバックアップワーカーキューに追加
2. PollDiskForNewDatabases：指定されたリストアパスを監視し、新規データベースフォルダをリストアワーカーキューに追加
3. Backup：msdbCentral.dbo.backup_workerテーブルからバックアップ対象を取得し、Ola HallengrenのDatabaseBackupを使用してログバックアップを実行
4. Restore：msdb.dbo.restore_workerテーブルからリストア対象を取得し、sp_DatabaseRestoreを使用してログリストアを実行

**前後の処理との関連**：sp_AllNightLog_Setupで環境構築後に実行されます。Ola HallengrenのCommandExecute、DatabaseBackup、およびsp_DatabaseRestoreとの連携が必須です。

**影響範囲**：msdbCentralデータベース、msdbデータベースのワーカーテーブル、対象データベースのトランザクションログ、バックアップ・リストア先のファイルシステム

## バッチ種別

データ連携 / バックアップ・リストア自動化

## 実行スケジュール

| 項目 | 内容 |
|-----|------|
| 実行頻度 | 継続実行（10秒間隔でリスタート） |
| 実行時刻 | 常時（24時間365日） |
| 実行曜日 | 毎日 |
| 実行日 | 該当なし |
| トリガー | SQL Server Agentジョブ（ten_secondsスケジュール） |

## 実行条件

### 前提条件

| 条件 | 説明 |
|-----|------|
| xp_cmdshell有効化 | ディスクディレクトリの読み取りに必要 |
| Ola Hallengren CommandExecute | 同一データベースにインストール必須 |
| Ola Hallengren DatabaseBackup | 同一データベースにインストール必須 |
| sp_DatabaseRestore | 同一データベースにインストール必須 |
| msdbCentral.dbo.backup_worker | バックアップモード時に必要 |
| msdbCentral.dbo.backup_configuration | バックアップ設定テーブル |
| msdb.dbo.restore_worker | リストアモード時に必要 |
| msdb.dbo.restore_configuration | リストア設定テーブル |

### 実行可否判定

- 少なくとも1つの動作モードパラメータ（@PollForNewDatabases、@Backup、@PollDiskForNewDatabases、@Restore）が1に設定されていること
- xp_cmdshellが有効化されていること
- 必要な依存プロシージャがインストールされていること
- 設定テーブルに有効な構成値が存在すること

## 入力仕様

### 入力パラメータ

| パラメータ名 | 型 | 必須 | デフォルト値 | 説明 |
|-------------|-----|-----|-------------|------|
| @PollForNewDatabases | BIT | No | 0 | 1の場合、sys.databasesを継続的にポーリングして新規データベースを検出 |
| @Backup | BIT | No | 0 | 1の場合、バックアップワーカーモードで動作しログバックアップを実行 |
| @PollDiskForNewDatabases | BIT | No | 0 | 1の場合、リストアパスを継続的にポーリングして新規データベースフォルダを検出 |
| @Restore | BIT | No | 0 | 1の場合、リストアワーカーモードで動作しログリストアを実行 |
| @Debug | BIT | No | 0 | 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 | 新規データベースの検出に使用 |
| msdbCentral.dbo.backup_worker | DB | バックアップ対象データベースのキュー管理 |
| msdbCentral.dbo.backup_configuration | DB | バックアップ設定（RPO、パス等） |
| msdb.dbo.restore_worker | DB | リストア対象データベースのキュー管理 |
| msdb.dbo.restore_configuration | DB | リストア設定（RTO、パス等） |
| ファイルシステム（バックアップパス） | ファイル | リストア対象のバックアップファイル |

## 出力仕様

### 出力データ

| 出力先 | 形式 | 説明 |
|-------|------|------|
| バックアップファイル | DB/ファイル | ログバックアップファイル（.trn） |
| msdbCentral.dbo.backup_worker | DB | バックアップ状態の更新 |
| msdb.dbo.restore_worker | DB | リストア状態の更新 |
| RAISERROR出力 | メッセージ | 処理状況やエラー情報 |

### 出力ファイル仕様

| 項目 | 内容 |
|-----|------|
| ファイル名 | Ola Hallengrenの命名規則に従う |
| 出力先 | backup_configurationで指定されたパス |
| 文字コード | N/A（バイナリファイル） |
| 区切り文字 | N/A |

## 処理フロー

### 処理シーケンス

```
1. パラメータ検証
   └─ 動作モードの確認、前提条件チェック
2. 前提条件確認
   └─ xp_cmdshell、依存プロシージャの存在確認
3. モード別分岐（GOTO使用）
   └─ Pollster / DiskPollster / LogShamer / Restoregasm_Addict
4. ワーカーループ開始
   └─ WHILEループで継続処理
5. トランザクション開始
   └─ 排他ロック付きで対象データベースを取得
6. 処理実行
   └─ バックアップまたはリストア処理の呼び出し
7. 状態更新
   └─ ワーカーテーブルの更新
8. ループ継続判定
   └─ ジョブの有効/無効状態確認
```

### フローチャート

```mermaid
flowchart TD
    A[バッチ開始] --> B{パラメータ検証}
    B -->|無効| Z[エラー終了]
    B -->|有効| C{前提条件チェック}
    C -->|失敗| Z
    C -->|成功| D{動作モード判定}
    D -->|PollForNewDatabases| E[Pollsterモード]
    D -->|Backup| F[LogShamerモード]
    D -->|PollDiskForNewDatabases| G[DiskPollsterモード]
    D -->|Restore| H[Restoregasmモード]
    E --> I[WHILEループ]
    F --> I
    G --> I
    H --> I
    I --> J{対象DB取得}
    J -->|なし| K[3秒待機]
    J -->|あり| L[処理実行]
    L --> M{処理成功?}
    M -->|成功| N[状態更新:完了]
    M -->|失敗| O[状態更新:エラー]
    N --> P{ジョブ有効?}
    O --> P
    K --> P
    P -->|有効| I
    P -->|無効| Q[バッチ終了]
```

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

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

| 処理 | 対象テーブル | 操作種別 | 概要 |
|-----|-------------|---------|------|
| 新規DB検出 | msdbCentral.dbo.backup_worker | INSERT | 新規データベースをキューに追加 |
| 状態リセット | msdbCentral.dbo.backup_worker | UPDATE | 長時間処理中のDBをリセット |
| バックアップ開始 | msdbCentral.dbo.backup_worker | UPDATE | is_started, last_log_backup_start_time更新 |
| バックアップ完了 | msdbCentral.dbo.backup_worker | UPDATE | is_completed, last_log_backup_finish_time更新 |
| エラー記録 | msdbCentral.dbo.backup_worker | UPDATE | error_number, last_error_date更新 |
| 新規DB検出 | msdb.dbo.restore_worker | INSERT | 新規データベースをキューに追加 |
| リストア開始 | msdb.dbo.restore_worker | UPDATE | is_started, last_log_restore_start_time更新 |
| リストア完了 | msdb.dbo.restore_worker | UPDATE | is_completed, last_log_restore_finish_time更新 |

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

#### msdbCentral.dbo.backup_worker

| 操作 | 項目（カラム名） | 更新値・取得条件 | 備考 |
|-----|-----------------|-----------------|------|
| INSERT | database_name | sys.databasesから取得 | database_id > 4のみ |
| UPDATE | is_started | 1 | バックアップ開始時 |
| UPDATE | is_completed | 0/1 | 処理状態に応じて |
| UPDATE | last_log_backup_start_time | GETDATE() | バックアップ開始時 |
| UPDATE | last_log_backup_finish_time | GETDATE() | バックアップ完了時 |
| UPDATE | error_number | ERROR_NUMBER() | エラー発生時 |
| UPDATE | last_error_date | GETDATE() | エラー発生時 |

## エラー処理

### エラーケース一覧

| エラーコード | エラー種別 | 発生条件 | 対処方法 |
|------------|----------|---------|---------|
| N/A | 設定エラー | 動作モードパラメータ未指定 | パラメータを1つ以上指定 |
| N/A | 構成エラー | xp_cmdshell無効 | xp_cmdshellを有効化 |
| N/A | 依存エラー | 必要プロシージャ未インストール | 依存プロシージャをインストール |
| N/A | 設定エラー | 設定テーブル存在しない | sp_AllNightLog_Setup実行 |
| N/A | 設定エラー | 設定値NULL | 設定テーブルを確認・更新 |
| -1 | 処理エラー | リストア先DBが正常状態でない | 手動確認が必要 |
| 正の整数 | バックアップ/リストアエラー | 各種処理エラー | 自動リトライ対象 |

### リトライ仕様

| 項目 | 内容 |
|-----|------|
| リトライ回数 | 無制限（WHILEループ継続） |
| リトライ間隔 | 3秒（対象なし時）/ 1秒（エラー時） |
| リトライ対象エラー | error_number > 0 のエラー |
| リトライ除外 | error_number < 0（手動対応必要） |

### 障害時対応

エラー発生時は、error_numberとlast_error_dateがワーカーテーブルに記録されます。負の値のerror_numberは手動対応が必要なことを示し、自動リトライの対象外となります。ジョブが無効化された場合、プロシージャは正常終了します。

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

| 項目 | 内容 |
|-----|------|
| トランザクション範囲 | 対象データベース取得時のみ |
| コミットタイミング | 対象DB取得・状態更新後即座にコミット |
| ロールバック条件 | 対象DB取得時のエラー |

## パフォーマンス要件

| 項目 | 内容 |
|-----|------|
| 想定処理件数 | 設定RPO/RTO間隔に依存 |
| 目標処理時間 | RPO秒以内にログバックアップ完了 |
| メモリ使用量上限 | SQL Server設定に依存 |

## 排他制御

- UPDLOCK, HOLDLOCK, ROWLOCKヒントを使用してワーカーテーブルから対象DBを取得
- 複数ワーカージョブ（最大10+）の同時実行を想定
- READPASTヒントを使用してロック待ちを回避

## ログ出力

| ログ種別 | 出力タイミング | 出力内容 |
|---------|--------------|---------|
| 開始ログ | 各モード開始時 | Beginning Pollster/DiskPollster/Backups/Restores |
| 進捗ログ | 処理中 | 対象DB名、処理状態 |
| 終了ログ | ジョブ無効化検知時 | gracefully exiting |
| エラーログ | エラー発生時 | エラー番号、エラーメッセージ |
| デバッグログ | @Debug=1時 | 動的SQL、詳細処理情報 |

## 監視・アラート

| 監視項目 | 閾値 | アラート先 |
|---------|-----|----------|
| バックアップ遅延 | RPO秒超過 | ワーカーテーブルのlast_log_backup_start_time確認 |
| リストア遅延 | RTO秒超過 | ワーカーテーブルのlast_log_restore_start_time確認 |
| エラー発生 | error_number < 0 | 手動確認必要 |

## 備考

- バージョン：8.19（2024-02-22）
- 本プロシージャは非推奨（Deprecated）であり、Deprecatedフォルダに移動済み
- SQL Server 2016以降を推奨（それ以前のバージョンはサポート外の可能性）
- 暗号化バックアップをリストアする場合、暗号化証明書が事前にインストールされている必要がある
- バックアップジョブとリストアジョブは同時に有効化できない（相互排他）
