SQL-Server-Operations-Guide

Operational reference documentation for SQL Server — covering performance tuning, disaster recovery, failover clustering, security hardening, monitoring, and development standards.

These runbooks were built from hands-on experience managing SQL Server environments at enterprise scale (1,000+ instances) and as a sole DBA responsible for 24/7 availability. They're opinionated, practical, and focused on what actually matters in production.

Every document includes both T-SQL and dbatools PowerShell approaches. Where applicable, guidance is aligned to the CIS Microsoft SQL Server Benchmark.

Documents

Performance

Document	Description
Performance Practices	Disk configuration, Instant File Initialization, Max Server Memory, MAXDOP, Cost Threshold for Parallelism, TempDB configuration, index analysis, and fragmentation management.

Disaster Recovery

Document	Description
Log Shipping Setup	Log shipping architecture, prerequisites, SSMS and dbatools setup, server object synchronization, and monitoring.
Log Shipping Failover	Step-by-step failover and failback procedures with T-SQL and dbatools paths, plus traffic redirection strategies (DNS, config, SQL aliases).

Clustering

Document	Description
Windows Cluster Setup	WSFC preparation — disk layout, BIOS tuning, Active Directory prep, cluster creation, quorum configuration, and resource naming.
SQL Cluster Installation	SQL Server FCI installation on WSFC — key configuration decisions, post-install hardening, and utility stored procedure deployment.

Security

Document	Description
Security Practices	Authentication strategy, custom database roles, environment permission matrix, SA hardening, surface area reduction, backup encryption, and compliance auditing.

Operations

Document	Description
Backup and Restore	Full, differential, and log backups with T-SQL, Ola Hallengren, and dbatools. Restore procedures including point-in-time recovery and backup verification.
Monitoring	Real-time activity monitoring, blocking detection, wait statistics, Agent job monitoring, disk space, health checks, log shipping status, and proactive alerting.

Standards

Document	Description
Development and Configuration Standards	Instance configuration baseline, database design standards, T-SQL development practices, naming conventions, indexing guidelines, and reporting database design.

Tools Referenced

These community and Microsoft tools are referenced throughout the runbooks:

Tool	Purpose
dbatools	PowerShell module with 500+ commands for SQL Server administration
Ola Hallengren Maintenance Solution	Backup, index, and statistics maintenance stored procedures
Brent Ozar First Responder Kit	sp_Blitz, sp_BlitzIndex, sp_BlitzCache, sp_BlitzFirst, sp_BlitzWho
sp_WhoIsActive	Real-time activity monitoring stored procedure

Related Repositories

Repository	Description
SQL-Server-Security-Audit	Scripted security audit checks aligned to CIS Microsoft SQL Server Benchmark 2022 v1.2.1
DBAOps	SQL Server utility database and CI/CD deployment toolkit
SQL-Patching-Jupyter-Notebook	Interactive SQL Server patching runbook using .NET Interactive notebooks

License

This project is licensed under the MIT License.

Disclaimer

These runbooks reflect practices from specific SQL Server environments and may need adaptation for yours. Configuration values, schedules, and architecture decisions were tuned for the workloads and SLAs in those environments — treat them as a starting point, not a prescription. Always test in a non-production environment before applying changes.

Contributions, corrections, and suggestions are welcome via issues or pull requests.