# artemedi/ms-sql architecture

> Architecture diagram of the GitHub repository artemedi/ms-sql, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-07-31). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/artemedi/ms-sql
- Repository: https://github.com/artemedi/ms-sql

## Overview

#### Purpose and usage model
This is a curated operational runbook of mostly ad hoc Microsoft SQL Server scripts, organized by DBA task rather than a deployable application. There is no declared executable entry point, build system, or README guidance. Operators select and run scripts manually in SQL Server tooling against the appropriate instance/database; a small Windows-tuning set uses PowerShell and Registry artifacts.

#### Operational monitoring and alerts
Alerting scripts query database/server state and can surface conditions through SQL Server Agent-related mechanisms or Slack integrations. Mirrored-database alerts are a distinct scenario.
- `Alerts/Alerts.sql`
- `Alerts/Slack SP.sql`
- `Alerts/Slack Trigger.sql`

#### Server/database audit and inventory
The `Audit` area gathers instance metadata, users/roles/SIDs, permissions, object definitions, table/index sizes, and cross-database discovery results. These scripts form a read-oriented assessment boundary, though some audit utilities may persist or create database objects.
- `Audit/Get SQL and Windows info.sql`
- `Audit/Get users and roles.sql`
- `Audit/Size all DBS.sql`

#### Diagnostics and performance investigation
Troubleshooting scripts provide the largest diagnostic workflow: inspect active sessions, CPU, I/O, memory, cache, locks/latches, waits, TempDB, execution plans, and transaction-log reuse/growth. Inputs are SQL Server DMVs, system views, plans, and logs; outputs are query result sets for operator interpretation.
- `Troubleshooting/Active sessions.sql`
- `Troubleshooting/CPU/High CPU investigation.sql`
- `Troubleshooting/Waits/Server waits.sql`

#### Event capture and historical telemetry
Extended Events scripts define or inspect server/database event sessions for failures, deadlocks, expensive queries, logins, resource consumption, spills, and page splits. This is the principal capture boundary: event sessions collect runtime activity, then companion scripts read settings or captured data.
- `Extendet Events/Deadlocks.sql`
- `Extendet Events/Expensive Queries.sql`
- `Extendet Events/CheckFailedLogins.sql`

#### Maintenance, recovery, and data movement
Scripts support backup verification, restore and point-in-time recovery, index/statistics maintenance, BCP/BULK INSERT transfer, and partition/filegroup analysis. These are mutating or infrastructure-impacting operations and must be run with database-specific paths, credentials, and recovery constraints.
- `Backup&Restore/Backup+verify.sql`
- `Backup&Restore/RestoreDB.sql`
- `Indexes/Index maitenance.sql`
- `BCP/BULK INSERT.sql`

#### Availability, integration, and scheduled operations
Topology-specific scripts cover Always On readable replicas, mirroring, replication, linked servers, SQL Agent jobs, Database Mail, SSIS, and Azure SQL diagnostics. They cross server/database boundaries and depend on configured SQL Server infrastructure and, for Slack, an external notification endpoint.
- `AlwaysOn/Read from ReadOnly Node For AlwaysOn.sql`
- `Replications/Transactional Replication Status Checker.sql`
- `Jobs/CheckJobExecution.sql`

#### Governance and host-level administration
Policy-Based Management scripts define conditions, policies, remediation stored procedures, and retention behavior. Windows scripts separately alter host services, memory compression, registry tuning, and presentation settings; these require Windows administrative execution rather than SQL execution.
- `Policy/Policies/CheckDatabaseSettings.sql`
- `Policy/StoredProcedures/FixDbSettings.sql`
- `WindowsTunning/EnableMemoryCompression.ps1`

Main entry points: [`Audit/Get SQL and Windows info.sql`](https://github.com/artemedi/ms-sql/blob/master/Audit/Get%20SQL%20and%20Windows%20info.sql) (Audit and inventory), [`Troubleshooting/Active sessions.sql`](https://github.com/artemedi/ms-sql/blob/master/Troubleshooting/Active%20sessions.sql) (Performance diagnostics), [`Alerts/Alerts.sql`](https://github.com/artemedi/ms-sql/blob/master/Alerts/Alerts.sql) (Alert scripts), [`Backup&Restore/RestoreDB.sql`](https://github.com/artemedi/ms-sql/blob/master/Backup%26Restore/RestoreDB.sql) (Maintenance and recovery)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_execution["Manual execution"]
  node_operator(("DBA operator<br/>user"))
  node_sql_tooling["SQL Server tooling<br/>execution client"]
  node_windows_shell["Windows admin shell<br/>PowerShell runtime"]
  node_result_sets["Operator result sets<br/>query output"]
end

subgraph group_sql_ops["SQL operational runbooks"]
  node_audit_inventory["Audit and inventory<br/>T-SQL assessment"]
  node_diagnostics["Performance diagnostics<br/>T-SQL investigation"]
  node_maintenance_recovery["Maintenance and recovery<br/>T-SQL operations<br/>[RestoreDB.sql]"]
  node_data_movement["BCP and BULK transfer<br/>T-SQL data movement<br/>[BULK INSERT.sql]"]
  node_availability_operations["Availability and jobs<br/>T-SQL infrastructure operations"]
  node_policy_governance["Policy governance<br/>T-SQL policy automation"]
end

subgraph group_telemetry["Capture and notifications"]
  node_alerts["Alert scripts<br/>T-SQL runbook<br/>[Alerts.sql]"]
  node_slack_integration["Slack integration<br/>T-SQL integration<br/>[Slack SP.sql]"]
  node_extended_events["Extended Events scripts<br/>T-SQL event capture<br/>[Deadlocks.sql]"]
  node_event_sessions[("Event sessions and captures<br/>SQL Server telemetry")]
end

subgraph group_platform["SQL and host infrastructure"]
  node_dmvs_and_logs[("DMVs, plans, and logs<br/>SQL Server telemetry")]
  node_sql_infrastructure[("SQL Server infrastructure<br/>target platform")]
  node_windows_tuning["Windows tuning<br/>PowerShell administration"]
  node_windows_host["Windows host<br/>host platform"]
  node_slack_endpoint{{"Slack endpoint<br/>external notification"}}
end

node_operator -->|"runs manually"| node_sql_tooling
node_operator -->|"runs manually"| node_windows_shell
node_sql_tooling -->|"executes"| node_alerts
node_sql_tooling -->|"executes"| node_audit_inventory
node_sql_tooling -->|"executes"| node_diagnostics
node_sql_tooling -->|"executes"| node_extended_events
node_sql_tooling -->|"executes"| node_maintenance_recovery
node_sql_tooling -->|"executes"| node_data_movement
node_sql_tooling -->|"executes"| node_availability_operations
node_sql_tooling -->|"executes"| node_policy_governance
node_alerts -->|"checks"| node_sql_infrastructure
node_alerts -.->|"uses"| node_slack_integration
node_slack_integration -->|"notifies"| node_slack_endpoint
node_audit_inventory -->|"assesses"| node_sql_infrastructure
node_audit_inventory -->|"returns"| node_result_sets
node_diagnostics -->|"queries"| node_dmvs_and_logs
node_diagnostics -->|"returns"| node_result_sets
node_extended_events -->|"defines and reads"| node_event_sessions
node_event_sessions -.->|"captures activity from"| node_dmvs_and_logs
node_maintenance_recovery -->|"changes or recovers"| node_sql_infrastructure
node_data_movement -->|"imports or exports"| node_sql_infrastructure
node_availability_operations -->|"operates across"| node_sql_infrastructure
node_policy_governance -->|"checks and remediates"| node_sql_infrastructure
node_windows_shell -->|"executes"| node_windows_tuning
node_windows_tuning -->|"modifies"| node_windows_host

click node_alerts "https://github.com/artemedi/ms-sql/blob/master/Alerts/Alerts.sql"
click node_slack_integration "https://github.com/artemedi/ms-sql/blob/master/Alerts/Slack%20SP.sql"
click node_audit_inventory "https://github.com/artemedi/ms-sql/blob/master/Audit/Get%20SQL%20and%20Windows%20info.sql"
click node_diagnostics "https://github.com/artemedi/ms-sql/blob/master/Troubleshooting/Active%20sessions.sql"
click node_extended_events "https://github.com/artemedi/ms-sql/blob/master/Extendet%20Events/Deadlocks.sql"
click node_maintenance_recovery "https://github.com/artemedi/ms-sql/blob/master/Backup%26Restore/RestoreDB.sql"
click node_data_movement "https://github.com/artemedi/ms-sql/blob/master/BCP/BULK%20INSERT.sql"
click node_availability_operations "https://github.com/artemedi/ms-sql/blob/master/AlwaysOn/Read%20from%20ReadOnly%20Node%20For%20AlwaysOn.sql"
click node_policy_governance "https://github.com/artemedi/ms-sql/blob/master/Policy/Policies/CheckDatabaseSettings.sql"
click node_windows_tuning "https://github.com/artemedi/ms-sql/blob/master/WindowsTunning/EnableMemoryCompression.ps1"

classDef toneNeutral fill:#f8fafc,stroke:#334155,stroke-width:1.5px,color:#0f172a
classDef toneBlue fill:#dbeafe,stroke:#2563eb,stroke-width:1.5px,color:#172554
classDef toneAmber fill:#fef3c7,stroke:#d97706,stroke-width:1.5px,color:#78350f
classDef toneMint fill:#dcfce7,stroke:#16a34a,stroke-width:1.5px,color:#14532d
classDef toneRose fill:#ffe4e6,stroke:#e11d48,stroke-width:1.5px,color:#881337
classDef toneIndigo fill:#e0e7ff,stroke:#4f46e5,stroke-width:1.5px,color:#312e81
classDef toneTeal fill:#ccfbf1,stroke:#0f766e,stroke-width:1.5px,color:#134e4a
class node_operator,node_sql_tooling,node_windows_shell,node_result_sets toneBlue
class node_audit_inventory,node_diagnostics,node_maintenance_recovery,node_data_movement,node_availability_operations,node_policy_governance toneAmber
class node_alerts,node_slack_integration,node_extended_events,node_event_sessions toneMint
class node_dmvs_and_logs,node_sql_infrastructure,node_windows_tuning,node_windows_host,node_slack_endpoint toneRose
```

## Components

### Manual execution

Operators choose scripts and run them in SQL Server tooling or Windows shells.

- **DBA operator**: user · Selects the appropriate runbook and interprets results.
- **SQL Server tooling**: execution client · Manual T-SQL execution against a selected instance or database.
- **Windows admin shell**: PowerShell runtime · Runs host-level tuning artifacts with administrative privileges.
- **Operator result sets**: query output · Assessment and diagnostic findings requiring operator interpretation.

### SQL operational runbooks

Task-oriented T-SQL scripts for assessment, investigation, and change.

- [Audit and inventory](https://github.com/artemedi/ms-sql/blob/master/Audit/Get%20SQL%20and%20Windows%20info.sql): T-SQL assessment · Reads instance metadata, identities, permissions, objects, and sizing data. · `Audit/Get SQL and Windows info.sql`
- [Performance diagnostics](https://github.com/artemedi/ms-sql/blob/master/Troubleshooting/Active%20sessions.sql): T-SQL investigation · Investigates sessions, CPU, I/O, memory, locks, waits, TempDB, plans, and logs. · `Troubleshooting/Active sessions.sql`
- [Maintenance and recovery](https://github.com/artemedi/ms-sql/blob/master/Backup%26Restore/RestoreDB.sql): T-SQL operations · Performs backup verification, restore, index/statistics work, and recovery actions. · `Backup&Restore/RestoreDB.sql`
- [BCP and BULK transfer](https://github.com/artemedi/ms-sql/blob/master/BCP/BULK%20INSERT.sql): T-SQL data movement · Moves data through BCP or BULK INSERT using environment-specific inputs. · `BCP/BULK INSERT.sql`
- [Availability and jobs](https://github.com/artemedi/ms-sql/blob/master/AlwaysOn/Read%20from%20ReadOnly%20Node%20For%20AlwaysOn.sql): T-SQL infrastructure operations · Works with replicas, replication, linked servers, Agent jobs, Mail, SSIS, and Azure diagnostics. · `AlwaysOn/Read from ReadOnly Node For AlwaysOn.sql`
- [Policy governance](https://github.com/artemedi/ms-sql/blob/master/Policy/Policies/CheckDatabaseSettings.sql): T-SQL policy automation · Defines policy checks and database-settings remediation procedures. · `Policy/Policies/CheckDatabaseSettings.sql`

### Capture and notifications

Runtime event capture and operational alert delivery.

- [Alert scripts](https://github.com/artemedi/ms-sql/blob/master/Alerts/Alerts.sql): T-SQL runbook · Queries conditions and can invoke Agent or Slack-oriented alert mechanisms. · `Alerts/Alerts.sql`
- [Slack integration](https://github.com/artemedi/ms-sql/blob/master/Alerts/Slack%20SP.sql): T-SQL integration · Stored procedure and trigger artifacts for Slack notifications. · `Alerts/Slack SP.sql`
- [Extended Events scripts](https://github.com/artemedi/ms-sql/blob/master/Extendet%20Events/Deadlocks.sql): T-SQL event capture · Defines or inspects sessions for failures, deadlocks, costly queries, logins, and resource events. · `Extendet Events/Deadlocks.sql`
- **Event sessions and captures**: SQL Server telemetry · Persisted session definitions and captured runtime activity.

### SQL and host infrastructure

Target instances, configured services, and Windows hosts.

- **DMVs, plans, and logs**: SQL Server telemetry · Primary diagnostic inputs exposed by SQL Server.
- **SQL Server infrastructure**: target platform · Instances, databases, Agent, replication, replicas, and related configured services.
- [Windows tuning](https://github.com/artemedi/ms-sql/blob/master/WindowsTunning/EnableMemoryCompression.ps1): PowerShell administration · Changes host services, memory compression, registry, or presentation settings. · `WindowsTunning/EnableMemoryCompression.ps1`
- **Windows host**: host platform · Server operating system requiring administrative access for tuning.
- **Slack endpoint**: external notification · Configured external destination for operational notifications.

## Connections

- DBA operator → SQL Server tooling: runs manually
- DBA operator → Windows admin shell: runs manually
- SQL Server tooling → Alert scripts: executes
- SQL Server tooling → Audit and inventory: executes
- SQL Server tooling → Performance diagnostics: executes
- SQL Server tooling → Extended Events scripts: executes
- SQL Server tooling → Maintenance and recovery: executes
- SQL Server tooling → BCP and BULK transfer: executes
- SQL Server tooling → Availability and jobs: executes
- SQL Server tooling → Policy governance: executes
- Alert scripts → SQL Server infrastructure: checks
- Alert scripts → Slack integration: uses
- Slack integration → Slack endpoint: notifies
- Audit and inventory → SQL Server infrastructure: assesses
- Audit and inventory → Operator result sets: returns
- Performance diagnostics → DMVs, plans, and logs: queries
- Performance diagnostics → Operator result sets: returns
- Extended Events scripts → Event sessions and captures: defines and reads
- Event sessions and captures → DMVs, plans, and logs: captures activity from
- Maintenance and recovery → SQL Server infrastructure: changes or recovers
- BCP and BULK transfer → SQL Server infrastructure: imports or exports
- Availability and jobs → SQL Server infrastructure: operates across
- Policy governance → SQL Server infrastructure: checks and remediates
- Windows admin shell → Windows tuning: executes
- Windows tuning → Windows host: modifies