caddis-plugin: Instructions file for GitHub Copilot

SQL Server DBA Guidelines

DBA-focused operations: server configuration, maintenance, monitoring, security, HA, and troubleshooting. For SQL coding conventions and query style, see sql.instructions.md.

---

Server Configuration and Tuning

Memory

-- Set max server memory (leave 4GB+ for OS on dedicated server)
EXEC sp_configure 'max server memory (MB)', 28672;  -- 28GB on a 32GB server
RECONFIGURE;

-- Check current memory usage
SELECT
    physical_memory_in_use_kb / 1024 AS memory_used_mb,
    locked_page_allocations_kb / 1024 AS locked_pages_mb,
    total_virtual_address_space_kb / 1024 AS virtual_space_mb
FROM sys.dm_os_process_memory;

TempDB

-- TempDB best practice: one data file per logical CPU core (up to 8),
-- all equal size, on fast storage separate from user databases
ALTER DATABASE tempdb ADD FILE (
    NAME = tempdev2, FILENAME = 'T:\TempDB\tempdev2.ndf', SIZE = 8192MB, FILEGROWTH = 1024MB
);
-- Repeat for each additional file. Keep all files the same size.

Max Degree of Parallelism (MAXDOP)

-- General guidance: set to number of cores per NUMA node (max 8)
EXEC sp_configure 'max degree of parallelism', 4;
EXEC sp_configure 'cost threshold for parallelism', 50;  -- raise from default 5
RECONFIGURE;

---

Backup and Restore Strategies

Full + Differential + Log Backup Pattern

-- Full backup (weekly or nightly depending on size)
BACKUP DATABASE [MyDatabase] TO DISK = 'D:\Backups\MyDatabase_FULL.bak'
    WITH COMPRESSION, CHECKSUM, INIT, FORMAT;

-- Differential backup (every 4-6 hours between fulls)
BACKUP DATABASE [MyDatabase] TO DISK = 'D:\Backups\MyDatabase_DIFF.bak'
    WITH DIFFERENTIAL, COMPRESSION, CHECKSUM, INIT;

-- Transaction log backup (every 15-30 min for point-in-time recovery)
BACKUP LOG [MyDatabase] TO DISK = 'D:\Backups\MyDatabase_LOG.trn'
    WITH COMPRESSION, CHECKSUM;

Restore Sequence

-- 1. Full → 2. Latest Diff → 3. All Logs after Diff
RESTORE DATABASE [MyDatabase] FROM DISK = 'D:\Backups\MyDatabase_FULL.bak'
    WITH NORECOVERY, REPLACE;
RESTORE DATABASE [MyDatabase] FROM DISK = 'D:\Backups\MyDatabase_DIFF.bak'
    WITH NORECOVERY;
RESTORE LOG [MyDatabase] FROM DISK = 'D:\Backups\MyDatabase_LOG.trn'
    WITH RECOVERY;  -- RECOVERY on last step only