# JBRhoylle DBA Suite v2.4.0
# Complete User Guide & Operations Manual

---

## Document Information

| Field | Value |
|-------|-------|
| **Product** | JBRhoylle DBA Suite |
| **Version** | 2.4.0 |
| **Author** | JB Gakumba, Sr SQL DBA / DBAOps Architect |
| **Organization** | JBRhoylle DBA Suite |
| **Last Updated** | January 29, 2026 |
| **Classification** | Internal Use |

---

# Table of Contents

1. [Introduction](#1-introduction)
2. [System Architecture](#2-system-architecture)
3. [Getting Started](#3-getting-started)
4. [Web Dashboard Guide](#4-web-dashboard-guide)
5. [PowerShell Modules Reference](#5-powershell-modules-reference)
6. [Daily Operations](#6-daily-operations)
7. [On-Call Management](#7-on-call-management)
8. [Monitoring & Alerting](#8-monitoring--alerting)
9. [Compliance Management](#9-compliance-management)
10. [Predictive Analytics](#10-predictive-analytics)
11. [Backup & Patch Management](#11-backup--patch-management)
12. [Troubleshooting](#12-troubleshooting)
13. [Administration](#13-administration)
14. [Appendix](#14-appendix)

---

# 1. Introduction

## 1.1 What is JBRhoylle DBA Suite?

JBRhoylle DBA Suite is an enterprise-grade SQL Server infrastructure management platform designed specifically for government and large-scale environments. It provides centralized monitoring, compliance automation, predictive analytics, and operational management for SQL Server estates.

## 1.2 Key Features

- **Centralized Inventory Management** - Single view of all SQL Servers via CMS
- **Real-Time Monitoring** - Server health, disk space, reachability status
- **Predictive Analytics** - Capacity forecasting and anomaly detection
- **Compliance Automation** - CriminalJustice, NIST, STIG, DISA framework tracking
- **On-Call Management** - DBA rotation scheduling and contact info
- **Web Dashboard** - Browser-based monitoring interface
- **Alerting System** - Multi-channel notifications (email, SMS, Teams)
- **Drift Detection** - Schema change monitoring and alerts
- **Patch Management** - SQL Server update tracking and deployment
- **Help Desk Intelligence** - Ticket pattern analysis

## 1.3 System Requirements

| Component | Requirement |
|-----------|-------------|
| Central Server OS | Windows Server 2016+ |
| SQL Server | 2016+ (Standard or Enterprise) |
| PowerShell | 5.1+ |
| Web Browser | Chrome, Edge, Firefox (modern versions) |
| Network | Access to all managed SQL Servers |
| Memory | 8GB+ recommended for central server |
| Disk Space | 50GB+ for repository database |

## 1.4 JBRhoylle Environment Overview

| Item | Value |
|------|-------|
| Central Server | YOURSERVER |
| Repository Database | JBRhoylleRepository |
| Web Dashboard URL | https://dbaopsdash/ |
| Managed Servers | 119 SQL Server instances |
| License | PERPETUAL (Unlimited servers) |

---

# 2. System Architecture

## 2.1 Architecture Diagram

```
                    ┌─────────────────────────────────────┐
                    │         DBAOps Web Dashboard        │
                    │       https://dbaopsdash/           │
                    └─────────────────┬───────────────────┘
                                      │
                    ┌─────────────────▼───────────────────┐
                    │       YOURSERVER (Central)       │
                    │  ┌─────────────────────────────┐    │
                    │  │    JBRhoylleRepository DB      │    │
                    │  │  - config schema            │    │
                    │  │  - monitoring schema        │    │
                    │  │  - compliance schema        │    │
                    │  │  - fact schema              │    │
                    │  └─────────────────────────────┘    │
                    │  ┌─────────────────────────────┐    │
                    │  │    PowerShell Modules       │    │
                    │  │  - JBRhoylle.Alerting          │    │
                    │  │  - JBRhoylle.PredictiveAnalytics│   │
                    │  │  - JBRhoylle.DriftDetection    │    │
                    │  │  - JBRhoylle.Pipeline          │    │
                    │  │  - JBRhoylle.SchemaControl     │    │
                    │  └─────────────────────────────┘    │
                    │  ┌─────────────────────────────┐    │
                    │  │    SQL Agent Jobs           │    │
                    │  │  - Dashboard Refresh (5min) │    │
                    │  │  - Compliance Checks        │    │
                    │  │  - Disk Space Collection    │    │
                    │  └─────────────────────────────┘    │
                    └─────────────────┬───────────────────┘
                                      │
          ┌───────────────────────────┼───────────────────────────┐
          │                           │                           │
          ▼                           ▼                           ▼
    ┌───────────┐             ┌───────────┐             ┌───────────┐
    │ SQL Server│             │ SQL Server│             │ SQL Server│
    │  (PROD)   │             │   (DEV)   │             │   (QA)    │
    │  Cluster  │             │  Servers  │             │  Servers  │
    └───────────┘             └───────────┘             └───────────┘
```

## 2.2 Component Overview

### Central Server (YOURSERVER)
The hub of all DBAOps operations. Hosts the repository database, runs data collection jobs, and serves the web dashboard.

### Repository Database (JBRhoylleRepository)
Stores all configuration, monitoring data, compliance results, and historical metrics.

### PowerShell Modules
Custom modules providing DBAOps functionality:
- Located at: `E:\DBAOps\PowerShell\`
- Loaded via: `Import-JBRhoylleModules.ps1`

### Web Dashboard
Browser-based interface for real-time monitoring:
- Main URL: https://dbaopsdash/
- Analytics: https://dbaopsdash/analytics.html
- Files: `E:\DBAOps\WebDashboard\`

### SQL Agent Jobs
Automated tasks running on schedule:
- Data collection every 5 minutes
- Compliance checks daily
- Capacity forecasts weekly

## 2.3 File System Structure

```
E:\DBAOps\
├── PowerShell\              # PowerShell modules and scripts
│   ├── Import-JBRhoylleModules.ps1
│   ├── JBRhoylle.Alerting.psm1
│   ├── JBRhoylle.PredictiveAnalytics.psm1
│   ├── JBRhoylle.DriftDetection.psm1
│   ├── JBRhoylle.Pipeline.psm1
│   ├── JBRhoylle.SchemaControl.psm1
│   ├── Get-JBRhoylleDashboardData.ps1
│   └── Update-JBRhoylleDashboard.ps1
│
├── WebDashboard\            # Web interface files
│   ├── index.html           # Main dashboard
│   ├── analytics.html       # Predictive analytics page
│   ├── data.json            # Dashboard data (auto-updated)
│   └── assets/              # CSS, JS, images
│
├── Scripts\                 # Utility scripts
├── Logs\                    # Log files
├── Config\                  # Configuration files
│   ├── license.json         # DBAOps license
│   └── DBAOps.config.json   # Main config
│
├── Backups\                 # Backup files
└── Documentation\           # Documentation
```

## 2.4 Database Schema Overview

### config Schema
Configuration and inventory tables:
- `config.ServerInventory` - All managed servers
- `config.OnCallRotation` - On-call DBA schedule
- `config.ReachabilityStatus` - Server online/offline status
- `config.AlertChannels` - Notification channels
- `config.LicenseInfo` - License details

### fact Schema
Metric and measurement data:
- `fact.DiskSpaceMetrics` - Disk space history
- `fact.BackupHistory` - Backup records
- `fact.PerformanceMetrics` - Performance data

### compliance Schema
Compliance tracking:
- `compliance.Controls` - Control definitions
- `compliance.Results` - Scan results
- `compliance.Frameworks` - Framework definitions

### monitoring Schema
Real-time monitoring data:
- `monitoring.Alerts` - Active alerts
- `monitoring.Events` - Event log

---

# 3. Getting Started

## 3.1 First-Time Setup (New DBA)

### Step 1: Verify Network Access
Ensure you can connect to the central server:
```powershell
Test-Connection YOURSERVER
```

### Step 2: Access Web Dashboard
Open browser and navigate to:
```
https://dbaopsdash/
```

### Step 3: Load PowerShell Modules
Open PowerShell on your workstation:
```powershell
# Connect to central server and load modules
Enter-PSSession -ComputerName YOURSERVER

# Load DBAOps modules
. E:\DBAOps\PowerShell\Import-JBRhoylleModules.ps1
```

You should see:
```
Loading DBAOps v2.0...
  OK JBRhoylle.Pipeline.psm1
  OK JBRhoylle.SchemaControl.psm1
  OK JBRhoylle.DriftDetection.psm1
  OK JBRhoylle.Alerting.psm1
  OK JBRhoylle.PredictiveAnalytics.psm1
DBAOps v2.0 Ready!
```

### Step 4: Verify Your Access
```powershell
# Check who's on call
Get-OnCallDBA

# View server inventory
Invoke-DbaQuery -SqlInstance YOURSERVER -Database JBRhoylleRepository -Query "SELECT TOP 10 * FROM config.ServerInventory"
```

## 3.2 Daily Checklist

Every day as a DBA, you should:

| Task | How | Frequency |
|------|-----|-----------|
| Check dashboard | https://dbaopsdash/ | Morning |
| Review alerts | Dashboard → Alerts section | Morning |
| Check disk space | Dashboard → Capacity section | Morning |
| Verify backups | Dashboard → Backup Status | Morning |
| Review compliance | Dashboard → Compliance section | Weekly |
| Check analytics | https://dbaopsdash/analytics.html | Weekly |

## 3.3 Essential Commands Quick Reference

```powershell
# Load modules (always do this first)
. E:\DBAOps\PowerShell\Import-JBRhoylleModules.ps1

# Who's on call?
Get-OnCallDBA

# Get predictive analytics summary
Get-PredictiveAnalyticsSummary

# Check server reachability
Invoke-DbaQuery -SqlInstance YOURSERVER -Database JBRhoylleRepository -Query "SELECT * FROM config.ReachabilityStatus WHERE IsOnline = 0"

# View recent disk alerts
Invoke-DbaQuery -SqlInstance YOURSERVER -Database JBRhoylleRepository -Query "SELECT TOP 20 * FROM fact.DiskSpaceMetrics WHERE FreePercent < 20 ORDER BY CaptureDate DESC"

# Refresh dashboard data manually
& E:\DBAOps\PowerShell\Update-JBRhoylleDashboard.ps1
```

---

# 4. Web Dashboard Guide

## 4.1 Accessing the Dashboard

**Main Dashboard:** https://dbaopsdash/
**Predictive Analytics:** https://dbaopsdash/analytics.html

## 4.2 Main Dashboard Sections

### Overview Section
The top of the dashboard shows key metrics at a glance:
- **Total Servers** - Number of managed SQL Servers
- **Online/Offline** - Current reachability status
- **Compliance Score** - Overall compliance percentage
- **Active Alerts** - Number of unresolved alerts

### Server Health Section
Color-coded status of all servers:
- 🟢 Green = Healthy
- 🟡 Yellow = Warning (needs attention)
- 🔴 Red = Critical (immediate action required)

### Compliance Section
Shows compliance status by framework:
- CriminalJustice (Business Application)
- NIST (National Institute of Standards)
- STIG (Security Technical Implementation Guide)
- DISA (Defense Information Systems Agency)

### Backup Status Section
Displays backup health:
- Last backup time for each server
- Backup success/failure status
- Overdue backups highlighted in red

### Disk Capacity Section
Shows disk space across all servers:
- Servers with less than 20% free space highlighted
- Trend indicators (growing/shrinking)

### SQL Intelligence Section
Query and performance insights:
- Long-running queries
- Blocking sessions
- High CPU queries

## 4.3 Predictive Analytics Page

Access: https://dbaopsdash/analytics.html

### Environment Summary
- Total uptime percentage
- Online server count
- Environment health score

### On-Call Information
- Primary DBA name and contact
- Backup DBA name and contact
- Rotation end date

### Capacity Alerts
- Servers predicted to run out of space
- Days until capacity issues
- Recommended actions

### Auto-Refresh
The analytics page refreshes automatically every 5 minutes. You can also manually refresh with Ctrl+Shift+R.

## 4.4 Dashboard Data Flow

```
SQL Agent Job (Every 5 min)
        │
        ▼
Update-JBRhoylleDashboard.ps1
        │
        ▼
Queries all servers via CMS
        │
        ▼
Writes to data.json
        │
        ▼
Dashboard displays data.json
```

---

# 5. PowerShell Modules Reference

## 5.1 Loading Modules

Always load modules before using DBAOps commands:

```powershell
# On central server
. E:\DBAOps\PowerShell\Import-JBRhoylleModules.ps1

# Or remotely
Invoke-Command -ComputerName YOURSERVER -ScriptBlock {
    . E:\DBAOps\PowerShell\Import-JBRhoylleModules.ps1
}
```

## 5.2 JBRhoylle.Alerting Module

### Get-OnCallDBA
Returns current on-call DBA information.

```powershell
Get-OnCallDBA
```

**Output:**
```
PrimaryDBA        : JB Gakumba
PrimaryPhone      : +18323143066
PrimaryEmail      : admin@dbaopsframework.com
BackupDBA         : Your Tech
BackupPhone       : 
BackupEmail       : 
RotationStartDate : 1/29/2026 11:01:24 AM
RotationEndDate   : 2/5/2026 11:01:24 AM
DaysRemaining     : 7
```

### Set-OnCallRotation
Updates the on-call rotation schedule.

```powershell
Set-OnCallRotation -PrimaryDBA "New DBA Name" `
                   -PrimaryPhone "+1234567890" `
                   -PrimaryEmail "newdba@dbaopsframework.com" `
                   -BackupDBA "Backup DBA Name"
```

### Send-DBAOpsAlert
Sends an alert through configured channels.

```powershell
Send-DBAOpsAlert -Title "Disk Space Warning" `
                 -Message "Server XYZ is at 95% disk capacity" `
                 -Severity "Warning"
```

**Severity Levels:** Info, Warning, Critical

## 5.3 JBRhoylle.PredictiveAnalytics Module

### Get-PredictiveAnalyticsSummary
Returns comprehensive analytics overview.

```powershell
Get-PredictiveAnalyticsSummary
```

**Output:**
```
============================================
  DBAOps PREDICTIVE ANALYTICS SUMMARY
  Source: CMS (Central Management Server)
============================================

1. Capacity Forecasting
Forecast complete: 422 drives analyzed
  Critical: 0 | Warning: 0

2. Anomaly Detection
No anomalies detected

3. Failure Prediction
Predictions complete: 0 items (0 high risk)

============================================
  SUMMARY
============================================
  Health Score: 100/100
============================================
```

### Get-CapacityForecast
Predicts when disks will run out of space.

```powershell
Get-CapacityForecast -DaysAhead 90
```

### Find-PerformanceAnomaly
Detects unusual performance patterns.

```powershell
Find-PerformanceAnomaly
```

### Get-FailurePrediction
Predicts potential hardware/software failures.

```powershell
Get-FailurePrediction
```

## 5.4 JBRhoylle.DriftDetection Module

### New-SchemaBaseline
Creates a baseline of current schema state.

```powershell
New-SchemaBaseline -ServerName "ServerName" -DatabaseName "DatabaseName"
```

### Test-SchemaDrift
Compares current schema against baseline.

```powershell
Test-SchemaDrift -ServerName "ServerName" -DatabaseName "DatabaseName"
```

### Start-DriftMonitor
Starts continuous drift monitoring.

```powershell
Start-DriftMonitor -Interval 60  # Check every 60 minutes
```

## 5.5 JBRhoylle.Pipeline Module

### Get-DeploymentHistory
Shows recent database deployments.

```powershell
Get-DeploymentHistory -Last 10
```

### New-Deployment
Records a new deployment.

```powershell
New-Deployment -ServerName "Server" -DatabaseName "DB" -Version "1.2.3" -DeployedBy "DBA Name"
```

## 5.6 JBRhoylle.SchemaControl Module

### Get-MigrationHistory
Shows schema migration history.

```powershell
Get-MigrationHistory -DatabaseName "MyDatabase"
```

### New-SchemaMigration
Records a schema migration.

```powershell
New-SchemaMigration -DatabaseName "MyDatabase" -Version "2.0" -Description "Added new tables"
```

---

# 6. Daily Operations

## 6.1 Morning Health Check

### Step 1: Check Dashboard
1. Open https://dbaopsdash/
2. Look for any red indicators
3. Note any servers showing offline
4. Check disk space warnings

### Step 2: Review Alerts (PowerShell)
```powershell
. E:\DBAOps\PowerShell\Import-JBRhoylleModules.ps1

# Check for offline servers
Invoke-DbaQuery -SqlInstance YOURSERVER -Database JBRhoylleRepository -Query @"
SELECT ServerName, LastChecked, ErrorMessage 
FROM config.ReachabilityStatus 
WHERE IsOnline = 0
"@

# Check disk space issues
Invoke-DbaQuery -SqlInstance YOURSERVER -Database JBRhoylleRepository -Query @"
SELECT TOP 20 ServerName, DriveLetter, FreePercent, FreeSpaceGB, CaptureDate
FROM fact.DiskSpaceMetrics
WHERE FreePercent < 20
ORDER BY CaptureDate DESC
"@
```

### Step 3: Verify Backups
```powershell
# Check for servers with no recent backup
Get-DbaLastBackup -SqlInstance (Get-DbaRegisteredServer -SqlInstance YOURSERVER) | 
    Where-Object { $_.LastFullBackup -lt (Get-Date).AddDays(-1) } |
    Select-Object SqlInstance, Database, LastFullBackup
```

## 6.2 Adding a New Server to DBAOps

### Step 1: Add to CMS
```powershell
# Add server to Central Management Server
Add-DbaRegServer -SqlInstance YOURSERVER -ServerName "NewServer\Instance" -Group "Production"
```

### Step 2: Add to DBAOps Inventory
```powershell
Invoke-DbaQuery -SqlInstance YOURSERVER -Database JBRhoylleRepository -Query @"
INSERT INTO config.ServerInventory (ServerName, Environment, IsActive, IsSQLServer)
VALUES ('NewServer\Instance', 'PROD', 1, 1)
"@
```

### Step 3: Verify
```powershell
# Check server appears in inventory
Invoke-DbaQuery -SqlInstance YOURSERVER -Database JBRhoylleRepository -Query @"
SELECT * FROM config.ServerInventory WHERE ServerName = 'NewServer\Instance'
"@
```

### Step 4: Wait for Data Collection
The SQL Agent job runs every 5 minutes. After the next run, the new server will appear on the dashboard.

## 6.3 Removing a Server from DBAOps

```powershell
# Mark as inactive (soft delete - preserves history)
Invoke-DbaQuery -SqlInstance YOURSERVER -Database JBRhoylleRepository -Query @"
UPDATE config.ServerInventory 
SET IsActive = 0, ModifiedDate = GETDATE()
WHERE ServerName = 'OldServer\Instance'
"@

# Remove from CMS
Remove-DbaRegServer -SqlInstance YOURSERVER -ServerName "OldServer\Instance" -Confirm:$false
```

## 6.4 Manually Refreshing Dashboard Data

If you need to refresh immediately without waiting for the scheduled job:

```powershell
# Run on central server
Invoke-Command -ComputerName YOURSERVER -ScriptBlock {
    & E:\DBAOps\PowerShell\Update-JBRhoylleDashboard.ps1
}
```

Or directly on the server:
```powershell
& E:\DBAOps\PowerShell\Update-JBRhoylleDashboard.ps1
```

---

# 7. On-Call Management

## 7.1 Viewing Current On-Call

```powershell
Get-OnCallDBA
```

Or via SQL:
```powershell
Invoke-DbaQuery -SqlInstance YOURSERVER -Database JBRhoylleRepository -Query @"
SELECT * FROM config.OnCallRotation WHERE IsActive = 1
"@
```

## 7.2 Updating On-Call Rotation

### Using PowerShell:
```powershell
# First, deactivate current rotation
Invoke-DbaQuery -SqlInstance YOURSERVER -Database JBRhoylleRepository -Query @"
UPDATE config.OnCallRotation SET IsActive = 0 WHERE IsActive = 1
"@

# Add new rotation
Invoke-DbaQuery -SqlInstance YOURSERVER -Database JBRhoylleRepository -Query @"
INSERT INTO config.OnCallRotation 
(PrimaryDBA, PrimaryPhone, PrimaryEmail, BackupDBA, BackupPhone, BackupEmail, RotationStartDate, RotationEndDate, IsActive)
VALUES 
('New Primary DBA', '+1234567890', 'primary@dbaopsframework.com', 'Backup DBA', '+0987654321', 'backup@dbaopsframework.com', GETDATE(), DATEADD(DAY, 7, GETDATE()), 1)
"@
```

### Via Direct SQL:
```sql
-- Run in SSMS connected to YOURSERVER
USE JBRhoylleRepository;

-- Deactivate current
UPDATE config.OnCallRotation SET IsActive = 0 WHERE IsActive = 1;

-- Add new rotation
INSERT INTO config.OnCallRotation 
(PrimaryDBA, PrimaryPhone, PrimaryEmail, BackupDBA, RotationStartDate, RotationEndDate, IsActive)
VALUES 
('DBA Name', '+1234567890', 'dba@dbaopsframework.com', 'Backup Name', GETDATE(), DATEADD(DAY, 7, GETDATE()), 1);
```

## 7.3 On-Call Best Practices

1. **Rotate weekly** - Standard rotation is 7 days
2. **Update before leaving** - Always update rotation before your shift ends
3. **Verify contact info** - Ensure phone/email are correct
4. **Handoff notes** - Brief the next DBA on any ongoing issues
5. **Check dashboard** - Review system state at start of rotation

---

# 8. Monitoring & Alerting

## 8.1 Alert Severity Levels

| Level | Color | Response Time | Examples |
|-------|-------|---------------|----------|
| **Critical** | 🔴 Red | Immediate | Server down, disk full, backup failure |
| **Warning** | 🟡 Yellow | Same day | Disk < 20%, long-running query |
| **Info** | 🔵 Blue | Review weekly | Maintenance complete, new server added |

## 8.2 Viewing Active Alerts

### Dashboard
Check the Alerts section on https://dbaopsdash/

### PowerShell
```powershell
Invoke-DbaQuery -SqlInstance YOURSERVER -Database JBRhoylleRepository -Query @"
SELECT * FROM monitoring.Alerts WHERE ResolvedDate IS NULL ORDER BY CreatedDate DESC
"@
```

## 8.3 Resolving Alerts

```powershell
# Resolve a specific alert
Invoke-DbaQuery -SqlInstance YOURSERVER -Database JBRhoylleRepository -Query @"
UPDATE monitoring.Alerts 
SET ResolvedDate = GETDATE(), ResolvedBy = 'Your Name', Resolution = 'Description of fix'
WHERE AlertID = 123
"@
```

## 8.4 Alert Channels Configuration

DBAOps supports multiple alert channels:

| Channel | Use Case |
|---------|----------|
| Email | Standard notifications |
| SMS | Critical after-hours alerts |
| Microsoft Teams | Team notifications |
| Slack | Team notifications |

### View Configured Channels:
```powershell
Invoke-DbaQuery -SqlInstance YOURSERVER -Database JBRhoylleRepository -Query @"
SELECT * FROM config.AlertChannels
"@
```

## 8.5 Server Reachability Monitoring

DBAOps continuously monitors if servers are reachable:

```powershell
# View all server statuses
Invoke-DbaQuery -SqlInstance YOURSERVER -Database JBRhoylleRepository -Query @"
SELECT 
    si.ServerName,
    si.Environment,
    rs.IsOnline,
    rs.LastChecked,
    rs.ResponseTimeMs,
    rs.ErrorMessage
FROM config.ServerInventory si
LEFT JOIN config.ReachabilityStatus rs ON si.ServerName = rs.ServerName
WHERE si.IsActive = 1
ORDER BY rs.IsOnline ASC, si.Environment
"@
```

---

# 9. Compliance Management

## 9.1 Supported Frameworks

| Framework | Description | Applicability |
|-----------|-------------|---------------|
| **CriminalJustice** | Business Application | Law enforcement data |
| **NIST** | National Institute of Standards | Federal systems |
| **STIG** | Security Technical Implementation Guide | DoD systems |
| **DISA** | Defense Information Systems Agency | Military systems |

## 9.2 Viewing Compliance Status

### Dashboard
The Compliance section shows overall percentage and breakdown by framework.

### PowerShell
```powershell
# View compliance summary
Invoke-DbaQuery -SqlInstance YOURSERVER -Database JBRhoylleRepository -Query @"
SELECT 
    FrameworkName,
    COUNT(*) as TotalControls,
    SUM(CASE WHEN Status = 'Compliant' THEN 1 ELSE 0 END) as Compliant,
    SUM(CASE WHEN Status = 'Non-Compliant' THEN 1 ELSE 0 END) as NonCompliant,
    CAST(SUM(CASE WHEN Status = 'Compliant' THEN 1.0 ELSE 0 END) / COUNT(*) * 100 AS DECIMAL(5,2)) as CompliancePercent
FROM compliance.Results
GROUP BY FrameworkName
"@
```

## 9.3 Compliance Controls

### View All Controls:
```powershell
Invoke-DbaQuery -SqlInstance YOURSERVER -Database JBRhoylleRepository -Query @"
SELECT ControlCode, ControlName, FrameworkName, Severity, Category
FROM compliance.Controls
ORDER BY FrameworkName, ControlCode
"@
```

### Common CriminalJustice Controls:
| Control | Description |
|---------|-------------|
| CriminalJustice-5.4.1 | Account Management |
| CriminalJustice-5.5.2 | Access Enforcement |
| CriminalJustice-5.6.1 | Auditable Events |
| CriminalJustice-5.10.1 | Encryption at Rest |

## 9.4 Running Compliance Checks

Compliance checks run automatically via SQL Agent job. To run manually:

```powershell
# Trigger compliance check job
Invoke-DbaQuery -SqlInstance YOURSERVER -Query "EXEC msdb.dbo.sp_start_job @job_name = 'DBAOps - Compliance Check'"
```

---

# 10. Predictive Analytics

## 10.1 Overview

DBAOps Predictive Analytics uses historical data to forecast future issues:
- **Capacity Forecasting** - Predicts when disks will fill up
- **Anomaly Detection** - Identifies unusual patterns
- **Failure Prediction** - Estimates hardware/software failure risk

## 10.2 Accessing Analytics

### Web Interface
https://dbaopsdash/analytics.html

### PowerShell
```powershell
. E:\DBAOps\PowerShell\Import-JBRhoylleModules.ps1
Get-PredictiveAnalyticsSummary
```

## 10.3 Capacity Forecasting

### How It Works
1. Collects disk space metrics daily
2. Analyzes growth trends over 30/60/90 days
3. Projects when each drive will reach capacity
4. Alerts if critical threshold predicted within 30 days

### View Capacity Forecasts:
```powershell
Get-CapacityForecast -DaysAhead 90 | Where-Object { $_.DaysUntilFull -lt 60 }
```

### Understanding Results:
| DaysUntilFull | Action Required |
|---------------|-----------------|
| < 7 days | Critical - Immediate action |
| 7-30 days | Warning - Plan expansion |
| 30-90 days | Info - Monitor trend |
| > 90 days | Healthy |

## 10.4 Anomaly Detection

Anomalies are detected based on:
- CPU usage spikes
- Memory pressure
- Disk I/O patterns
- Query duration changes
- Connection count changes

```powershell
Find-PerformanceAnomaly | Format-Table -AutoSize
```

## 10.5 Health Score

The Health Score (0-100) is calculated from:
- Capacity alerts (40%)
- Anomaly count (30%)
- Failure predictions (30%)

| Score | Status | Action |
|-------|--------|--------|
| 90-100 | Excellent | Continue monitoring |
| 70-89 | Good | Address warnings |
| 50-69 | Fair | Investigation needed |
| < 50 | Poor | Immediate attention |

---

# 11. Backup & Patch Management

## 11.1 Backup Monitoring

### View Recent Backups (Dashboard)
The Backup Status section shows last backup time for each server.

### PowerShell Commands:
```powershell
# Check all servers' last backup
Get-DbaLastBackup -SqlInstance (Get-DbaRegisteredServer -SqlInstance YOURSERVER) |
    Select-Object SqlInstance, Database, LastFullBackup, LastDiffBackup, LastLogBackup |
    Sort-Object LastFullBackup

# Find databases without recent backup
Get-DbaLastBackup -SqlInstance (Get-DbaRegisteredServer -SqlInstance YOURSERVER) |
    Where-Object { $_.LastFullBackup -lt (Get-Date).AddDays(-1) -and $_.Database -ne 'tempdb' } |
    Select-Object SqlInstance, Database, LastFullBackup
```

## 11.2 Backup Alerts

DBAOps generates alerts for:
- Full backup > 24 hours old
- Diff backup > 12 hours old (production)
- Log backup > 1 hour old (production, full recovery)

## 11.3 SQL Server Patch Status

### View Patch Levels:
```powershell
Get-DbaRegisteredServer -SqlInstance YOURSERVER | 
    Get-DbaBuild | 
    Select-Object SqlInstance, Build, NameLevel, SPLevel, CULevel, KBLevel |
    Sort-Object NameLevel
```

### Check for Available Updates:
```powershell
Get-DbaRegisteredServer -SqlInstance YOURSERVER | 
    Get-DbaBuild | 
    Test-DbaBuild -MaxBehind "1CU" |
    Where-Object { $_.Compliant -eq $false }
```

## 11.4 Patch Management Workflow

1. **Identify** - Use DBAOps to find servers needing patches
2. **Plan** - Schedule maintenance window
3. **Test** - Apply to non-prod first
4. **Deploy** - Apply to production
5. **Verify** - Confirm successful update
6. **Document** - Record in DBAOps

---

# 12. Troubleshooting

## 12.1 Common Issues

### Dashboard Not Loading

**Symptoms:** Dashboard shows blank or error

**Solutions:**
1. Check IIS is running:
   ```powershell
   Invoke-Command -ComputerName YOURSERVER -ScriptBlock { Get-Service W3SVC }
   ```

2. Verify data.json exists and is valid:
   ```powershell
   Test-Path "\\YOURSERVER\E$\DBAOps\WebDashboard\data.json"
   Get-Content "\\YOURSERVER\E$\DBAOps\WebDashboard\data.json" | ConvertFrom-Json
   ```

3. Clear browser cache: Ctrl+Shift+R

### Dashboard Shows Old Data

**Symptoms:** Data hasn't updated for > 5 minutes

**Solutions:**
1. Check SQL Agent job status:
   ```powershell
   Get-DbaAgentJob -SqlInstance YOURSERVER -Job "DBAOps - Dashboard Data Refresh"
   ```

2. Check job history for errors:
   ```powershell
   Get-DbaAgentJobHistory -SqlInstance YOURSERVER -Job "DBAOps - Dashboard Data Refresh" -StartDate (Get-Date).AddHours(-1)
   ```

3. Run manually:
   ```powershell
   Invoke-Command -ComputerName YOURSERVER -ScriptBlock { & E:\DBAOps\PowerShell\Update-JBRhoylleDashboard.ps1 }
   ```

### PowerShell Module Not Loading

**Symptoms:** Commands not recognized

**Solutions:**
1. Verify file exists:
   ```powershell
   Test-Path "E:\DBAOps\PowerShell\Import-JBRhoylleModules.ps1"
   ```

2. Check for syntax errors:
   ```powershell
   $null = [System.Management.Automation.Language.Parser]::ParseFile("E:\DBAOps\PowerShell\JBRhoylle.Alerting.psm1", [ref]$null, [ref]$errors)
   $errors
   ```

3. Import individually to find issue:
   ```powershell
   Import-Module "E:\DBAOps\PowerShell\JBRhoylle.Alerting.psm1" -Verbose
   ```

### Server Shows Offline But Is Running

**Symptoms:** Dashboard shows server offline, but you can connect

**Solutions:**
1. Check reachability table:
   ```powershell
   Invoke-DbaQuery -SqlInstance YOURSERVER -Database JBRhoylleRepository -Query "SELECT * FROM config.ReachabilityStatus WHERE ServerName = 'ServerName'"
   ```

2. Update manually:
   ```powershell
   Invoke-DbaQuery -SqlInstance YOURSERVER -Database JBRhoylleRepository -Query "UPDATE config.ReachabilityStatus SET IsOnline = 1, LastChecked = GETDATE() WHERE ServerName = 'ServerName'"
   ```

3. Force refresh:
   ```powershell
   & E:\DBAOps\PowerShell\Update-JBRhoylleDashboard.ps1
   ```

### Certificate Trust Error

**Symptoms:** "The certificate chain was issued by an authority that is not trusted"

**Solution:**
```powershell
Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true -Register
```

## 12.2 Log Files

DBAOps logs are located at: `E:\DBAOps\Logs\`

```powershell
# View recent logs
Get-ChildItem "\\YOURSERVER\E$\DBAOps\Logs" | Sort-Object LastWriteTime -Descending | Select-Object -First 10

# Read a log file
Get-Content "\\YOURSERVER\E$\DBAOps\Logs\DBAOps_20260129.log" -Tail 50
```

## 12.3 SQL Agent Job Troubleshooting

```powershell
# List all DBAOps jobs
Get-DbaAgentJob -SqlInstance YOURSERVER | Where-Object { $_.Name -like "DBAOps*" }

# Check job history
Get-DbaAgentJobHistory -SqlInstance YOURSERVER -Job "DBAOps - Dashboard Data Refresh" -StartDate (Get-Date).AddDays(-1) | 
    Select-Object JobName, StepName, RunStatus, RunDate, RunDuration, Message

# Restart a failed job
Start-DbaAgentJob -SqlInstance YOURSERVER -Job "DBAOps - Dashboard Data Refresh"
```

## 12.4 Getting Help

1. **Check this documentation** first
2. **Search logs** for error messages
3. **Contact on-call DBA:** `Get-OnCallDBA`
4. **Escalate** to DBAOps administrator if needed

---

# 13. Administration

## 13.1 SQL Agent Jobs

### DBAOps Jobs on YOURSERVER:

| Job Name | Schedule | Purpose |
|----------|----------|---------|
| DBAOps - Dashboard Data Refresh | Every 5 min | Updates web dashboard |
| DBAOps - Compliance Check | Daily 2 AM | Runs compliance scans |
| DBAOps - Disk Space Collection | Every 15 min | Collects disk metrics |
| DBAOps - Backup Monitor | Every hour | Checks backup status |
| DBAOps - Reachability Check | Every 5 min | Pings all servers |

### Managing Jobs:
```powershell
# View all DBAOps jobs
Get-DbaAgentJob -SqlInstance YOURSERVER | Where-Object { $_.Name -like "DBAOps*" } | Select-Object Name, IsEnabled, LastRunDate, NextRunDate

# Disable a job
Set-DbaAgentJob -SqlInstance YOURSERVER -Job "JobName" -Enabled $false

# Enable a job
Set-DbaAgentJob -SqlInstance YOURSERVER -Job "JobName" -Enabled $true
```

## 13.2 Database Maintenance

### Repository Database Size:
```powershell
Get-DbaDatabase -SqlInstance YOURSERVER -Database JBRhoylleRepository | Select-Object Name, SizeMB, DataSpaceUsage, IndexSpaceUsage
```

### Purge Old Data (> 90 days):
```sql
-- Run carefully in SSMS
USE JBRhoylleRepository;

DELETE FROM fact.DiskSpaceMetrics WHERE CaptureDate < DATEADD(DAY, -90, GETDATE());
DELETE FROM monitoring.Events WHERE CreatedDate < DATEADD(DAY, -90, GETDATE());
DELETE FROM compliance.Results WHERE ScanDate < DATEADD(DAY, -90, GETDATE());
```

## 13.3 License Information

### View Current License:
```powershell
Get-Content "\\YOURSERVER\E$\DBAOps\Config\license.json" | ConvertFrom-Json | Select-Object -ExpandProperty License
```

### JBRhoylle License Details:
- **Key:** DBAOPS-55BBB-BEE02-F40DB-53592
- **Organization:** JBRhoylle DBA Suite
- **Tier:** PERPETUAL
- **Max Servers:** Unlimited
- **Expires:** Never

## 13.4 Backup & Recovery

### Backup DBAOps Configuration:
```powershell
# Backup config files
$backupPath = "E:\DBAOps\Backups\Config_$(Get-Date -Format 'yyyyMMdd')"
New-Item -ItemType Directory -Path $backupPath -Force
Copy-Item "E:\DBAOps\Config\*" $backupPath -Recurse
Copy-Item "E:\DBAOps\PowerShell\*.psm1" $backupPath

# Backup repository database
Backup-DbaDatabase -SqlInstance YOURSERVER -Database JBRhoylleRepository -Path "E:\DBAOps\Backups" -CompressBackup
```

### Restore (if needed):
```powershell
# Restore database
Restore-DbaDatabase -SqlInstance YOURSERVER -Path "E:\DBAOps\Backups\JBRhoylleRepository_backup.bak" -DatabaseName JBRhoylleRepository -WithReplace

# Restore config
Copy-Item "E:\DBAOps\Backups\Config_20260129\*" "E:\DBAOps\Config\" -Force
```

---

# 14. Appendix

## 14.1 Quick Reference Card

### URLs
- **Dashboard:** https://dbaopsdash/
- **Analytics:** https://dbaopsdash/analytics.html

### Key Paths (on YOURSERVER)
- **Install:** E:\DBAOps\
- **Modules:** E:\DBAOps\PowerShell\
- **Dashboard:** E:\DBAOps\WebDashboard\
- **Config:** E:\DBAOps\Config\
- **Logs:** E:\DBAOps\Logs\

### Essential Commands
```powershell
# Load modules
. E:\DBAOps\PowerShell\Import-JBRhoylleModules.ps1

# Who's on call
Get-OnCallDBA

# Health check
Get-PredictiveAnalyticsSummary

# Refresh dashboard
& E:\DBAOps\PowerShell\Update-JBRhoylleDashboard.ps1
```

## 14.2 Database Schema Reference

### config.ServerInventory
| Column | Type | Description |
|--------|------|-------------|
| ServerID | INT | Primary key |
| ServerName | NVARCHAR(255) | Server\Instance name |
| Environment | NVARCHAR(50) | PROD, DEV, QA, etc. |
| SQLVersion | NVARCHAR(100) | SQL Server version |
| IsActive | BIT | Active in monitoring |
| IsSQLServer | BIT | Is SQL Server (vs Windows) |
| CreatedDate | DATETIME | When added |

### config.OnCallRotation
| Column | Type | Description |
|--------|------|-------------|
| RotationID | INT | Primary key |
| PrimaryDBA | NVARCHAR(255) | Primary on-call name |
| PrimaryPhone | NVARCHAR(50) | Primary phone number |
| PrimaryEmail | NVARCHAR(255) | Primary email |
| BackupDBA | NVARCHAR(255) | Backup on-call name |
| RotationStartDate | DATETIME | Start of rotation |
| RotationEndDate | DATETIME | End of rotation |
| IsActive | BIT | Current rotation |

### fact.DiskSpaceMetrics
| Column | Type | Description |
|--------|------|-------------|
| MetricID | INT | Primary key |
| ServerName | NVARCHAR(255) | Server name |
| DriveLetter | NVARCHAR(10) | Drive (C:, D:, etc.) |
| TotalSpaceGB | DECIMAL(18,2) | Total capacity |
| FreeSpaceGB | DECIMAL(18,2) | Free space |
| FreePercent | DECIMAL(5,2) | Percentage free |
| CaptureDate | DATETIME | When collected |

## 14.3 Contact Information

### DBAOps Support
- **On-Call DBA:** Run `Get-OnCallDBA`
- **DBAOps Administrator:** JB Gakumba
- **Email:** admin@dbaopsframework.com

### Emergency Contacts
For critical database emergencies, contact the on-call DBA immediately via phone.

## 14.4 Version History

| Version | Date | Changes |
|---------|------|---------|
| 2.4.0 | 2026-01-29 | Added Predictive Analytics, licensing system |
| 2.3.0 | 2026-01-16 | Added drift detection, compliance automation |
| 2.2.0 | 2026-01-10 | Web dashboard enhancements |
| 2.1.0 | 2025-12-15 | Added on-call management |
| 2.0.0 | 2025-11-01 | Major rewrite, modular architecture |
| 1.0.0 | 2025-06-01 | Initial release |

---

**End of Document**

*JBRhoylle DBA Suite v2.4.0 - Enterprise SQL Server Infrastructure Management*
*© 2026 JBRhoylle DBA Suite*
