DEV Community: Ev Vasilev

How We Build Reliable .NET Applications: Clean Architecture & Development Culture

Ev Vasilev — Thu, 13 Feb 2025 14:54:28 +0000

Introduction
- 1.1. What Is Clean Architecture & Why It Matters
- 1.2. Our Commitment to Quality & Simplicity
Architecture Overview
- 2.1. Project Structure Overview
  - Dependency Diagram
- 2.2. Principles: Separation of Concerns, Clear Boundaries, Scalability
Achieving Reliability
- 3.1. Flexible Testing Strategy
- 3.2. Automated Migrations & Monitoring
- 3.3. Health Checks & Observability
Team Collaboration & Growth
- 4.1. ADR as a Knowledge Sharing Tool
- 4.2. Code Reviews & Mentorship
- 4.3. Reducing Onboarding Time
Strengthening Teams Through Simplicity
- 5.1. Controllers & Use Cases Free of Excess Logic
- 5.2. Transparent Folder Structure & Code Style
- 5.3. Minimal Boilerplate via Shared Configs
Adapting to Change
- 6.1. Service Scaling
- 6.2. Long-Term Maintenance
Looking Ahead
- 7.1. Continuous Improvement Plan
- 7.2. Invitation to Collaborate
Conclusion
- 8.1. Key Benefits for Clients, Developers, & Business
- 8.2. Final Thoughts on Innovation & Culture

1. Introduction

1.1. What Is Clean Architecture & Why It Matters

Clean Architecture splits application code into distinct layers. Its main goal is separation of concerns, simplicity, and adaptability. This lets us change or add components without breaking existing logic.

Key Points:

Easier Maintenance: Clearly separated code is simpler to test, debug, and update.
Flexibility: Not tied to a specific framework or database.
Long-Term Viability: Scalable and easier to maintain as the project grows.

In .NET, it means isolating business logic (Core) from implementations (Infrastructure, API, etc.), boosting reliability and ease of change.

1.2. Our Commitment to Quality & Simplicity

Clean Architecture isn’t just a buzzword. We focus on:

Quality: Transparent code structure minimizes errors and duplication.
Simplicity: Less noise means faster feature delivery and maintenance.

We invest in processes like ADRs to document decisions and help everyone on the team understand the architecture and its evolution.

2. Architecture Overview

2.1. Project Structure Overview

Solution/
├── src/
│   ├── API                      // ASP.NET Core Web API: Controllers, middleware, routing, configuration.
│   ├── Core                     // Business logic, use cases, interfaces, dependency injection registration.
│   ├── Infrastructure           // EF Core DbContext, repository implementations, external integrations, caching, dependency injection registration.
│   ├── Consumers                // Message consumers (e.g., using Kafka/RabbitMQ clients).
│   ├── Jobs                     // Background job processing (using Hangfire or similar).
│   ├── Contracts                // Domain models, Shared DTOs and API contracts.
│   ├── Data.Migration           // Console app for running database migrations.
│   └── Tests                    // Tests
│       ├── Tests.Unit           // Unit tests for Core and isolated components.
│       └── Tests.Integration    // Integration tests using Docker/Testcontainers.

Our .NET ecosystem is split into several projects with defined roles:

API
- Handles HTTP requests/responses.
- Focuses on controllers and middleware for security, routing, and input validation.
- Uses Core for business logic and Infrastructure for data access.
Core
- Contains business logic (Services, UseCases), rules (Validators), and interfaces.
- Framework-agnostic to avoid binding logic to specific implementations.
Infrastructure
- Implements data access, external service integration, etc.
- Provides concrete implementations of Core interfaces.
Consumers
- Processes messages from external queues (e.g., Kafka, RabbitMQ).
- Leverages Core for business logic and Infrastructure for data operations.
Jobs
- Manages background tasks (using tools like Hangfire).
- Uses Core for rules and Infrastructure for data access.
Tests
- Unit Tests: Isolated testing of Core components with mocks.
- Integration Tests: Verify interactions between API, Core, and Infrastructure, often using Docker Compose.
Data Migrations
- Contains scripts and migrations to update the database schema.
Contracts
- Defines shared DTOs, models, and controller interfaces.
- Provides universal descriptions for communication across services.

Dependency Diagram

PlantUml

@startuml CleanArchitectureDependencies
allowmixing

!define ProjectColor LightSteelBlue
!define FolderColor LightGray
!define InterfaceColor LightGoldenRodYellow
!define ClassColor PaleGreen

skinparam package {
  BackgroundColor ProjectColor
  BorderColor Black
}

skinparam class {
  BackgroundColor FolderColor
  BorderColor Black
  ArrowColor Black
}

package "API Project" as Api {
  folder "Controllers" as Api.Controllers {
    class Controller {
    }
  }
}

package "Contracts Project" as Contracts {
  folder "DTOs" as Contracts.DTOs {
    class DTOs
  }
  folder "Models" as Contracts.Models {
    class Models
  }
  folder "Controller Interfaces" as Contracts.ControllerInterfaces {
    interface IController
  }
  folder "Clients" as Contracts.Clients {
    interface IApiClient
  }  
  folder "Events" as Contracts.Events {
    class Event
  }
}

package "Core Project" as Core {
  folder "UseCases" as Core.UseCases {
    class UseCase
  }
  folder "Services" as Core.Services {
    class Service
  }
  folder "Interfaces" as Core.Interfaces {
    interface IRepository
    interface IExternalServiceClient
  }
  folder "Validators" as Core.Validators {
    class Validator
  }
}

package "Infrastructure Project" as Infrastructure {
  folder "Repositories" as Infrastructure.Repositories {
    class Repository {
       +DbContext
    }
  }
  folder "Entities" as Infrastructure.Entities {
    class Entity
  }
  folder "External Service Clients" as Infrastructure.ExternalServiceClients {
    class ExternalServiceClient {
        +HttpClient
    }
  }
  folder "DI" as Infrastructure.DI {
    class InfrastructureDI
  }
}

package "Consumers Project" as Consumers {
  folder "Event Processing" as Consumers.EventProcessing {
    class EventProcessor
  }
}

package "Jobs Project" as Jobs {
  folder "Jobs" as Jobs.Jobs {
    class Job
  }
}

package "Data Migrations Project" as DataMigrations {
  folder "Migrations" as DataMigrations.Migrations {
    class Migration
  }
}

package "Tests" as Tests {
    package "Unit Tests Project" as Tests.Unit {
      folder "Unit Tests" as Tests.Unit.UnitTests {
        class UnitTest
      }
    }

    package "Integration Tests Project" as Tests.Integration {
      folder "Integration Tests" as Tests.Integration.IntegrationTests {
        class IntegrationTest
      }
    }
}

database "Database" as DB

' Dependencies arrows
Api.Controllers.Controller -u-> Contracts.ControllerInterfaces.IController : Implements
Api.Controllers.Controller --> Core.UseCases.UseCase : Uses
Api.Controllers.Controller -d-> Contracts.Events.Event : Uses
Api --> Infrastructure.DI.InfrastructureDI : Uses
Api -r-> Contracts : Uses

Contracts.ControllerInterfaces.IController -d-> Contracts.DTOs.DTOs : Uses
Contracts.Clients.IApiClient -u-> Contracts.ControllerInterfaces.IController : Implements

Core.UseCases.UseCase --> Core.Services.Service : Uses
Core.UseCases.UseCase --> Core.Interfaces.IRepository : Uses
Core.UseCases.UseCase --> Core.Interfaces.IExternalServiceClient : Uses
Core.UseCases.UseCase -r-> Contracts.DTOs.DTOs : Uses
Core.Services.Service --> Core.Interfaces.IRepository : Uses
Core.Services.Service -u-> Core.Validators.Validator : Uses
Core.Services.Service -r-> Contracts.Models.Models : Uses

Infrastructure.Repositories.Repository -u-> Core.Interfaces.IRepository : Implements
Infrastructure.Repositories.Repository --> Infrastructure.Entities.Entity : Uses
Infrastructure.Repositories.Repository -d-> DB : Uses
Infrastructure.ExternalServiceClients.ExternalServiceClient -u-> Core.Interfaces.IExternalServiceClient : Implements
Infrastructure.ExternalServiceClients.ExternalServiceClient -r-> Contracts.Clients.IApiClient : Uses
Infrastructure.DI.InfrastructureDI --> Core.Interfaces.IRepository : Registers
Infrastructure.DI.InfrastructureDI --> Core.Interfaces.IExternalServiceClient : Registers

Consumers.EventProcessing.EventProcessor -u-> Contracts.Events.Event : Uses
Consumers.EventProcessing.EventProcessor -d-> Contracts.Clients.IApiClient : Uses

Jobs.Jobs.Job --> Core.UseCases.UseCase : Uses

DataMigrations.Migrations.Migration --> Infrastructure.Repositories.Repository : Uses

Tests.Unit.UnitTests.UnitTest --> Core : Tests
Tests.Integration.IntegrationTests.IntegrationTest -r-> Api : Tests
Tests.Integration.IntegrationTests.IntegrationTest --> Core : Tests
Tests.Integration.IntegrationTests.IntegrationTest -l-> Infrastructure : Tests

' Styling
hide empty members
@enduml

2.2. Principles: Separation of Concerns, Clear Boundaries, Scalability

Separation of Concerns (SoC)
- Each class or module has a single responsibility.
- Controllers don’t contain business logic; they simply forward data to Use Cases.
Clear Boundaries
- Layers interact only through well-defined interfaces.
- Core relies on abstractions, not on specific implementations.
- Infrastructure handles external communication without business logic.
Scalability
- Modular design lets us scale API, Consumers, or Jobs independently.
- Facilitates refactoring and task distribution across teams.

3. Achieving Reliability

3.1. Flexible Testing Strategy

We cover all application layers:

Unit Tests
- Test individual Core classes and methods with mocks.
- Fast feedback and isolated error detection.
Integration Tests
- Verify interactions among API, Core, and Infrastructure.
- Use Testcontainers (PostgreSQL) with Docker Compose for real-environment emulation.
End-to-End Tests (E2E)
- Simulate the full system flow from HTTP request to DB write.
- Run tests in Docker Compose, including external services, for full-cycle validation.

Benefits:

Unit tests catch logic errors quickly.
Integration/E2E tests prevent surprises with real services.
All tests run in CI/CD to catch regressions early.
Use retry policies (Polly) to handle unstable environments.
Scripts like dotnet test and ReportGenerator simplify test execution and analysis.

3.2. Automated Migrations & Monitoring

Data Migrations
- A dedicated project holds SQL scripts and FluentMigrator migrations.
- Schema updates are automatically checked during startup or CI/CD.
- Ensures consistent DB evolution without hidden changes.
Monitoring
- Tools like Elastic APM track performance, errors, and metrics in real time.
- Helps spot and fix issues before they impact users.
Alerting
- Alerts (e.g., via Slack) trigger on abnormal response times or error rates.
- Enables quick incident response.

3.3. Health Checks & Observability

Health Checks
- Each service exposes a /health endpoint checking DB, cache, external APIs, etc.
Logging
- Use Serilog for structured logging to the console.
- Logs integrate with tools like Filebeat; sensitive data is carefully handled.
Tracing & Metrics
- OpenTelemetry or Elastic APM trace requests across all layers.
- Metrics (operation durations, message counts) help analyze load and scale services.

4. Team Collaboration & Growth

4.1. ADR as a Knowledge Sharing Tool

Architecture Decision Records (ADRs) document key decisions:

Record decisions on project structure, tools, data schemas, etc.
Explain the rationale and alternatives considered.
Provide transparent context for the whole team, aiding new members.

4.2. Code Reviews & Mentorship

Code Reviews: Every pull request is checked for both correctness and adherence to Clean Architecture.
Pair/Mob Programming: Accelerate complex tasks and share expertise.
Mentorship: Senior developers guide newcomers, reducing the onboarding curve.

4.3. Reducing Onboarding Time

Clear Separation: Distinct roles for API, Core, Infrastructure, Consumers, etc.
ADRs: Offer historical context for decisions.
Standardized Practices: Unified DI, logging, and testing setups help new developers get up to speed fast.

5. Strengthening Teams Through Simplicity

5.1. Controllers & Use Cases Free of Excess Logic

Controllers: Handle request validation and forward data to Use Cases.
Use Cases: Encapsulate business logic without peripheral concerns.
Reduces coupling and makes functionality easy to locate.

5.2. Transparent Folder Structure & Code Style

Core: Contains models, services, use cases, and interfaces.
Infrastructure: Houses entities, repositories, external clients, and DI.
API, Jobs, Consumers: Each has its own folders and middleware.
Contracts: Defines shared DTOs and interfaces.
EditorConfig and Directory.Build.props standardize formatting and linting.

5.3. Minimal Boilerplate via Shared Configs

Shared Configs:
- Directory.Packages.props manages NuGet package versions.
- DI extensions centralize service configuration (logging, Swagger, Health Checks).
- Docker Compose provides a one-command local setup.
This minimizes boilerplate and lets developers focus on business logic.

6. Adapting to Change

6.1. Service Scaling

As the project grows, new subdomains emerge:

Subdomain Segregation
- In Core, create namespaces like Core.Services.[Subdomain] and Core.UseCases.[Subdomain].
- In Infrastructure, segregate repositories similarly.
- Example: For Customer Management:
  - Core.Services.CustomerManagement
  - Core.UseCases.CustomerManagement
  - Infrastructure.Repositories.CustomerManagement
Gradual Splitting
- When a module becomes too complex, extract it into its own microservice.
- Core logic remains reusable, thanks to clear separations.
- Example: For Order Management:
  - Separate libraries for Core and Infrastructure.
  - Create an OrderService with its own repository.
  - Communicate via REST or message brokers.

Scaling Examples:

API: Horizontal scaling via load balancers; caching (Redis) to reduce DB load.
Consumers: Use competing consumers or partition topics for parallel processing.
Jobs: Run tasks in parallel (Hangfire) or distribute by task type.

6.2. Long-Term Maintenance

Regular Refactoring
- Scheduled sessions to address technical debt and optimize code.
- ADRs track changes and reasons.
- Example: Splitting complex modules or optimizing DB queries.
Smooth Tech Transitions
- Switching technologies (e.g., PostgreSQL to MS SQL) only affects Infrastructure.
- Example: Implement a new IRepository for MS SQL, update DI, test, and adjust migrations.

7. Looking Ahead

7.1. Continuous Improvement Plan

Regularly review and update ADRs.
Internal meetups and workshops on Clean Architecture, DevOps, etc.
Maximize automation—from testing to environment setup—to focus on business logic.

7.2. Invitation to Collaborate

We’re growing and always seek talented developers, DevOps engineers, and analysts who value:

Clean, Simple Code
Openness to New Ideas & Continuous Learning
Teamwork & Knowledge Sharing

8. Conclusion

8.1. Key Benefits for Clients, Developers, & Business

Clients: Stable, reliable services that evolve quickly.
Developers: Clean, modular code reduces risk and boosts professional growth.
Business: Faster time-to-market with high quality and predictable support.

8.2. Final Thoughts on Innovation & Culture

Our architecture is a living system reflecting our values: transparency, collaboration, and sustainable growth. Regular ADRs, comprehensive testing, flexible infrastructure, and a supportive team environment keep us competitive and adaptive in a changing market.

Quick Start: Elasticsearch + OpenTelemetry Collector

Ev Vasilev — Thu, 16 Jan 2025 12:07:34 +0000

I recently began overhauling our logging architecture to address several challenges, including centralized log management, debugging difficulties, performance overhead, rate limiting, authentication concerns, data enrichment needs, and robust error handling. To validate the new approach, I developed a Proof of Concept (PoC) and an Architectural Decision Record (ADR), which I have detailed in this blog post to share our findings and the benefits of the updated architecture.

Why This Architecture?

In a distributed microservices world, observability becomes a must-have rather than a nice-to-have. Traditional “log to disk” methods don’t cut it when you need to troubleshoot cross-service issues or correlate logs with traces. By combining Elasticsearch with an OpenTelemetry Collector, you get:

Centralized Logs for simplified searching and analytics.

Structured, ECS-compliant log data, making it easy to parse and visualize.

Scalability and resilience via the Collector’s buffering, batching, and resource limits.

Future-proofing: OpenTelemetry is quickly becoming the standard for logs, metrics, and traces.

Problems Solved:

Flexible Indexing: This architecture allows you to control which index the logs are sent to.

Difficult debugging: With distributed tracing and ECS fields, you can quickly pinpoint errors.

Performance overhead: The Collector offloads log processing tasks from your app, so your services can focus on business logic.

Rate Limiting: Manage the flow of log data to prevent system overload and ensure consistent performance.

Authentication: Secure the log pipeline by ensuring only authorized sources can send logs.

Data Enrichment: Enhance logs with additional context for more insightful analysis.

Error Handling: Implement robust error handling to gracefully manage failures in log processing.

Monitoring and Alerts: Set up monitoring and alerting to proactively address issues in the logging pipeline.

Additional Reading:

OpenTelemetry Official Docs

Elastic Common Schema (ECS)

Elasticsearch Observability Overview

Why Set Up Elasticsearch + OpenTelemetry Collector?

Centralize Logs: Capture logs from various sources in a single store (Elasticsearch).
Structured Logging: By sending logs via OTLP (OpenTelemetry) in JSON format, your data is easier to query and analyze.
Scalability: The Collector buffers and processes logs, reducing overhead on your apps.
Observability: Combine logs with distributed tracing or metrics to gain deeper insights.

Step 1: Generate `docker-compose.yml`


$composeContent = @"
services:
  elasticsearch:
    image: docker.elastic.co/elasticsearch/elasticsearch:7.10.1
    environment:
      - discovery.type=single-node
    ports:
      - "39200:9200"
    healthcheck:
      test: ["CMD", "curl", "-f", "http://localhost:9200"]
      interval: 3s
      timeout: 3s
      retries: 15

  opentelemetry-collector:
    image: otel/opentelemetry-collector-contrib:latest
    environment:
      - ELASTIC_API_KEY=elasticsearch-apikey
    command: --config /etc/otel-collector-config.yml
    volumes:
      - ./otel-collector-config.yml:/etc/otel-collector-config.yml
    ports:
      - "4317:4317"
      - "4318:4318"
    depends_on:
      elasticsearch:
        condition: service_healthy
"@

$composeFile = "docker-compose.yml"
$composeContent | Out-File -FilePath $composeFile -Encoding UTF8

Explanation

Elasticsearch runs in single-node mode, exposed at port 39200.
OpenTelemetry Collector listens on ports 4317 (gRPC) and 4318 (HTTP).
We write the Compose file to docker-compose.yml on the fly, ensuring everyone gets the same environment.

Step 2: Create `otel-collector-config.yml`


$otelConfig = @"
receivers:
  otlp:
    protocols:
      grpc:
        endpoint: 0.0.0.0:4317
      http:
        endpoint: 0.0.0.0:4318

processors:
  memory_limiter:
    check_interval: 1s
    limit_mib: 4000
    spike_limit_mib: 800

# natively that setup do not support substitute values for creating indexes like that '[service.name]-logs-yyyy.MM'
# So we need to fill in elasticsearch.index.prefix and elasticsearch.index.suffix or use logstash
  resource:
    attributes:
      - action: upsert
        key: "elasticsearch.index.prefix"
        from_attribute: "service.name"

  batch:
    timeout: 10s
    send_batch_size: 10000
    send_batch_max_size: 11000

exporters:
  elasticsearch:
    endpoint: "http://elasticsearch:9200"
    headers:
      Authorization: "ApiKey ${ELASTIC_API_KEY}"
    logs_index: "logs"
    logs_dynamic_index:
      enabled: true
    mapping:
      mode: "ecs"
    timeout: 5s

service:
  pipelines:
    logs:
      receivers: [otlp]
      processors: [memory_limiter, resource, batch]
      exporters: [elasticsearch]
"@

$otelConfigFile = "otel-collector-config.yml"
$otelConfig | Out-File -FilePath $otelConfigFile -Encoding UTF8

Explanation

Receivers: Accept OTLP logs over HTTP/gRPC.
Processors: Add a memory limiter, resource enrichment (e.g., elasticsearch.index.prefix), and batching.
Exporters: Send logs to Elasticsearch in ECS-compliant format.

Step 3: Spin Up Docker Services


Write-Host "`n--- Bringing up Docker Compose services ---"

# Down any existing services
docker compose -p otel-poc -f $composeFile down

docker compose -p otel-poc -f $composeFile up -d

# Optional: Wait for Elasticsearch / Collector to be healthy
Write-Host "`nWaiting up to 60 seconds for Elasticsearch to become healthy..."
$healthCheckPassed = $false
for ($i = 1; $i -le 20; $i++) {
    # Check if ES responds on port 39200
    try {
        $result = Invoke-RestMethod -Uri "http://localhost:39200" -UseBasicParsing -ErrorAction Stop
        if ($result.name) {
            Write-Host "Elasticsearch is healthy."
            $healthCheckPassed = $true
            break
        }
    }
    catch {
        Write-Host "Still waiting..."
    }
    Start-Sleep -Seconds 3
}

if (-not $healthCheckPassed) {
    Write-Host "`nElasticsearch did not become healthy in time. Exiting..."
    exit 1
}

Explanation

docker compose down: Stops any existing containers with the same project name (otel-poc).
docker compose up -d: Spins up fresh containers in the background.
We then poll http://localhost:39200 to confirm Elasticsearch is ready before proceeding.

Step 4: Send a Test Log to the Collector


Write-Host "`n--- Sending test log to the Collector ---"

# Adjust $otlpEndpoint or add Authorization headers as needed
$otlpEndpoint = "http://localhost:4318/v1/logs"
$headers = @{
    "Content-Type" = "application/json"
    # "Authorization" = "Bearer otlp-collector-token"  # If needed
}

$logPayload = @"
{
  "resourceLogs": [
    {
      "resource": {
        "attributes": [
          {
            "key": "service.name",
            "value": { "stringValue": "myservice-" }
          },
          {
            "key": "elasticsearch.index.prefix",
            "value": { "stringValue": "myservice-" }
          },
          {
            "key": "elasticsearch.index.suffix",
            "value": { "stringValue": "-data" }
          }
        ]
      },
      "scopeLogs": [
        {
          "scope": {
            "name": "test-logger",
            "version": "1.0"
          },
          "logRecords": [
            {
              "timeUnixNano": "1673452395000000000",
              "severityNumber": 9,
              "severityText": "ERROR",
              "body": { "stringValue": "This is a test log from PowerShell" },
              "attributes": [
                {
                  "key": "attribute_key",
                  "value": { "stringValue": "attribute_value" }
                }
              ]
            }
          ]
        }
      ]
    }
  ]
}
"@

try {
    $response = Invoke-RestMethod -Uri $otlpEndpoint -Method Post -Headers $headers -Body $logPayload
    Write-Host "Collector Response: $response"
} catch {
    Write-Host "Error sending log: $($_.Exception.Message)"
    exit 1
}

Explanation

We build a JSON payload with a single log record:
- service.name is set to “myservice-”.
- severityText is “ERROR” (just an example).
- The body is a short string indicating a test log.
We then POST it to http://localhost:4318/v1/logs, the OTLP (HTTP) endpoint for the Collector.

Step 5: Query Elasticsearch

# --- STEP 5: Query Elasticsearch to verify log ingestion ---
# <# We need to wait a bit for the log to be ingested #>
# Start-Sleep -Seconds 60

Write-Host "`n--- Querying Elasticsearch to see if our log is present ---"

$esQueryBody = @{
  query = @{
    match_all = @{}
  }
}

try {
    $searchResponse = Invoke-RestMethod `
        -Method Post `
        -Uri "http://localhost:39200/myservice-logs-data/_search?pretty" `
        -ContentType "application/json" `
        -Body ($esQueryBody | ConvertTo-Json)

    $hits = $searchResponse.hits.hits

    if ($hits.Count -gt 0) {
        Write-Host "`nFound $($hits.Count) log(s) in Elasticsearch!"
        Write-Host "Here are the hits:"
        $hits | ForEach-Object { $_._source | ConvertTo-Json -Depth 10 }
    }
    else {
        Write-Host "`nNo logs found in Elasticsearch index 'test-logs'."
    }
}
catch {
    Write-Host "Error querying Elasticsearch: $($_.Exception.Message)"
}

Write-Host "`nDone!"

Explanation

We construct a match_all query to the myservice-logs-data index.
If logs exist, it prints the result to the console in JSON format.
This confirms the log made the full journey: Collector → Elasticsearch → Queried.

Summary

This step-by-step script helps you quickly validate:

Elasticsearch is up and responding.
OpenTelemetry Collector is receiving logs and exporting them in ECS-compatible format.
Your custom log payload arrives in Elasticsearch as expected.

By breaking the script into separate blocks, you can understand each phase and modify it as needed—maybe you want to add a Kibana container, use TLS, or fine-tune the Collector’s memory settings. This quick start approach should get you up and running fast, so you can focus on observability rather than wrestling with config files.

Enjoy building out your structured logging pipeline! Drop a comment below if you have any questions or improvements. Happy logging!

Building a Solid SQL Backup & Recovery Strategy (With T-SQL PoC)

Ev Vasilev — Wed, 15 Jan 2025 12:45:07 +0000

While Documenting Backup Strategies, I Turned My Notes into This Quick Post

I’ve been working on thorough documentation for backup strategies across different databases. While putting together the artifacts, I figured I'd share a short post on SQL Server backup and restore techniques. I hope it's useful for anyone looking to ramp up their SQL backups. I'll cover strategies for other databases in upcoming posts—stay tuned!

Overview

A reliable backup and recovery strategy is critical for any production SQL Server environment. Here’s a quick guide on how to set up full, differential, and transaction log backups (T-Log) for point-in-time restoration. We’ll also walk through a proof-of-concept script showing how to back up data, restore it, and skip unwanted transactions.

Why Care?

Protect against data loss and corruption
Enable faster restores when things go wrong
Meet compliance/audit requirements

1. Backup Types & Scheduling

Full Backup

Captures everything: data, schema, indexes.
Frequency: Often once a week or after major DB changes.

BACKUP DATABASE YourDatabase
TO DISK = 'C:\Backups\YourDB_Full.bak'
WITH INIT, NAME = 'Full Backup', STATS = 10;

Differential Backup

Only changes since the last full backup.
Frequency: Daily or multiple times a day.

BACKUP DATABASE YourDatabase
TO DISK = 'C:\Backups\YourDB_Diff.bak'
WITH DIFFERENTIAL, INIT, NAME = 'Differential Backup', STATS = 10;

Transaction Log Backup (T-Log)

Captures transaction log records since the last T-Log backup.
Frequency: Every 15–30 minutes (or more often if you can’t afford to lose data).

BACKUP LOG YourDatabase
TO DISK = 'C:\Backups\YourDB_Log.trn'
WITH INIT, NAME = 'T-Log Backup', STATS = 10;

Key: Make sure your database is in FULL (or BULK_LOGGED) recovery mode to allow T-Log backups.

2. Restoring Databases

Basic Restore Flow

Full Backup → WITH NORECOVERY
Differential Backup → WITH NORECOVERY
T-Log Backup → WITH NORECOVERY
Final Step → WITH RECOVERY

RESTORE DATABASE YourDatabase
FROM DISK = 'C:\Backups\YourDB_Full.bak'
WITH NORECOVERY, REPLACE;

RESTORE DATABASE YourDatabase
FROM DISK = 'C:\Backups\YourDB_Diff.bak'
WITH NORECOVERY;

RESTORE LOG YourDatabase
FROM DISK = 'C:\Backups\YourDB_Log.trn'
WITH NORECOVERY;

/* Once done with all backups/logs, finalize: */
RESTORE DATABASE YourDatabase WITH RECOVERY;

Point-in-Time Recovery

If you need to exclude bad transactions, you can stop at a specific time:

RESTORE LOG YourDatabase
FROM DISK = 'C:\Backups\YourDB_Log.trn'
WITH STOPAT = 'YYYY-MM-DD HH:MM:SS',
     RECOVERY;

3. Common Pitfalls

Transaction Log Is Full (Error 9002)

Backup the Log to free space in the log file.
Check for open transactions using DBCC OPENTRAN(YourDatabase).
Avoid switching to SIMPLE unless in an absolute emergency (it breaks point-in-time recovery).

Corrupted or Missing Backups

Always verify backups with RESTORE VERIFYONLY.
Include timestamps in filenames (e.g., YourDB_Full_20250114.bak) to avoid overwrites.

4. The Proof-of-Concept (PoC) T-SQL Script

Below is a single script you can run in a test environment. It creates a sample database, does full, differential, and transaction log backups, then simulates a disaster and recovers up to a chosen point in time (excluding unwanted rows).

Note: Adjust file paths to match your environment (e.g., C:\Backups\..., /var/opt/mssql/backups/, etc.).

/****************************************************************************
 SINGLE SCRIPT: FULL, DIFF, TLOG BACKUPS + POINT-IN-TIME RESTORE (STOPAT)
****************************************************************************/

/**************************************************
 STEP 0: Clean Up from Previous POC Runs (If Any)
***************************************************/
USE master;
GO

IF DB_ID('PoCDatabase') IS NOT NULL
BEGIN
    PRINT '--- Dropping existing PoCDatabase for a clean start ---';
    ALTER DATABASE PoCDatabase 
        SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE PoCDatabase;
END
GO

/**************************************************
 STEP 1: Create PoCDatabase in FULL Recovery
***************************************************/
PRINT '--- Creating PoCDatabase in FULL recovery mode ---';
CREATE DATABASE PoCDatabase;
GO

ALTER DATABASE PoCDatabase 
    SET RECOVERY FULL;
GO

/**************************************************
 STEP 2: Create Table & Insert Initial Data
***************************************************/
USE PoCDatabase;
GO

IF OBJECT_ID('dbo.SampleData') IS NOT NULL
    DROP TABLE dbo.SampleData;

CREATE TABLE dbo.SampleData (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Description VARCHAR(100),
    CreatedDate DATETIME DEFAULT GETDATE()
);

INSERT INTO dbo.SampleData (Description)
VALUES ('Initial row 1'),
       ('Initial row 2'),
       ('Initial row 3');

SELECT 'Initial Data in PoCDatabase:' AS [Info], *
FROM dbo.SampleData;
GO

/**************************************************
 STEP 3: Perform a FULL Backup
***************************************************/
USE master;
GO

PRINT '--- Performing FULL Backup ---';
BACKUP DATABASE PoCDatabase
    TO DISK = N'/var/opt/mssql/backups/PoCDatabase_Full.bak'
    WITH INIT,
         NAME = 'PoCDatabase Full Backup',
         STATS = 10;

PRINT '--- Verifying FULL Backup ---';
RESTORE VERIFYONLY
    FROM DISK = N'/var/opt/mssql/backups/PoCDatabase_Full.bak';
GO

/**************************************************
 STEP 4: Insert Data & Perform DIFFERENTIAL Backup
***************************************************/
USE PoCDatabase;
GO

PRINT '--- Inserting data for differential backup ---';
INSERT INTO dbo.SampleData (Description)
VALUES ('Diff insert row A'),
       ('Diff insert row B');

SELECT 'After Diff Inserts:' AS [Info], *
FROM dbo.SampleData;
GO

USE master;
GO

PRINT '--- Performing DIFFERENTIAL Backup ---';
BACKUP DATABASE PoCDatabase
    TO DISK = N'/var/opt/mssql/backups/PoCDatabase_Diff.bak'
    WITH DIFFERENTIAL,
         INIT,
         NAME = 'PoCDatabase Differential Backup',
         STATS = 10;

PRINT '--- Verifying DIFFERENTIAL Backup ---';
RESTORE VERIFYONLY
    FROM DISK = N'/var/opt/mssql/backups/PoCDatabase_Diff.bak';
GO

/**************************************************
 STEP 5: Additional Data & TLog #1
***************************************************/
USE PoCDatabase;
GO

PRINT '--- Inserting TLog #1 data ---';
INSERT INTO dbo.SampleData (Description)
VALUES ('TLog #1 row - Keep me');

SELECT 'After TLog #1 Insert:' AS [Info], *
FROM dbo.SampleData;
GO

USE master;
GO

PRINT '--- Performing TRANSACTION LOG Backup #1 ---';
BACKUP LOG PoCDatabase
    TO DISK = N'/var/opt/mssql/backups/PoCDatabase_Log1.trn'
    WITH INIT,
         NAME = 'PoCDatabase Transaction Log Backup #1',
         STATS = 10;

PRINT '--- Verifying TLog #1 ---';
RESTORE VERIFYONLY
    FROM DISK = N'/var/opt/mssql/backups/PoCDatabase_Log1.trn';
GO

/**************************************************
 STEP 6: Insert Two Rows & TLog #2 
   - Keep the 1st
   - Exclude the 2nd with STOPAT
***************************************************/

/*
   We'll store the "stop time" in a global #temp table so we can 
   retrieve it even after new GO statements or dropping the DB.
   This avoids variable-scope issues.
*/
IF OBJECT_ID('tempdb..##StopTimeStore') IS NOT NULL
    DROP TABLE ##StopTimeStore;

CREATE TABLE ##StopTimeStore (
    StopTime DATETIME NOT NULL
);

USE PoCDatabase;
GO

PRINT '--- Inserting two new records for point-in-time demo ---';

/* 1) Insert the first record (we DO want to keep). */
INSERT INTO dbo.SampleData (Description)
VALUES ('Point-in-time row 1 - Good');
WAITFOR DELAY '00:00:01'; 
/* 2) Capture the exact timestamp AFTER the first insert. */
INSERT INTO ##StopTimeStore(StopTime)
VALUES (GETDATE());

/* 
   3) Wait 5 seconds so the second row definitely 
      has a later timestamp than the first.
*/
WAITFOR DELAY '00:00:05';  -- 5 seconds

/* 4) Insert second row (we want to exclude it). */
INSERT INTO dbo.SampleData (Description)
VALUES ('Point-in-time row 2 - EXCLUDE');

SELECT 'Two new rows inserted (pre TLog #2):' AS [Info], *
FROM dbo.SampleData
ORDER BY ID;
GO

USE master;
GO

PRINT '--- Performing TRANSACTION LOG Backup #2 (contains both new rows) ---';
BACKUP LOG PoCDatabase
    TO DISK = N'/var/opt/mssql/backups/PoCDatabase_Log2.trn'
    WITH INIT,
         NAME = 'PoCDatabase Transaction Log Backup #2',
         STATS = 10;

PRINT '--- Verifying TLog #2 ---';
RESTORE VERIFYONLY
    FROM DISK = N'/var/opt/mssql/backups/PoCDatabase_Log2.trn';
GO

/**************************************************
 STEP 7: Simulate Disaster & Restore to STOPAT
***************************************************/
USE master;
GO

PRINT '--- Dropping PoCDatabase to simulate disaster ---';
IF DB_ID('PoCDatabase') IS NOT NULL
BEGIN
    ALTER DATABASE PoCDatabase 
        SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE PoCDatabase;
END
GO

PRINT '--- Restoring FULL Backup (NORECOVERY) ---';
RESTORE DATABASE PoCDatabase
    FROM DISK = N'/var/opt/mssql/backups/PoCDatabase_Full.bak'
    WITH NORECOVERY,
         REPLACE,
         STATS = 10;
GO

PRINT '--- Restoring DIFFERENTIAL Backup (NORECOVERY) ---';
RESTORE DATABASE PoCDatabase
    FROM DISK = N'/var/opt/mssql/backups/PoCDatabase_Diff.bak'
    WITH NORECOVERY,
         STATS = 10;
GO

PRINT '--- Restoring TLog #1 (NORECOVERY) ---';
RESTORE LOG PoCDatabase
    FROM DISK = N'/var/opt/mssql/backups/PoCDatabase_Log1.trn'
    WITH NORECOVERY,
         STATS = 10;
GO

/*
   Retrieve the saved stop time (captured after first row insertion).
   We'll restore TLog #2 up to (but NOT including) the second row.
*/
DECLARE @StopTime DATETIME;
SELECT TOP 1 @StopTime = StopTime 
FROM ##StopTimeStore;  -- read from global temp table

PRINT '--- Restoring TLog #2 with STOPAT (RECOVERY) ---';
RESTORE LOG PoCDatabase
    FROM DISK = N'/var/opt/mssql/backups/PoCDatabase_Log2.trn'
    WITH 
        STOPAT = @StopTime,   -- exclude row(s) inserted after this time
        RECOVERY,
        STATS = 10;
GO

/**************************************************
 STEP 8: Validate Data
***************************************************/
USE PoCDatabase;
GO

PRINT '--- Final State: only the first new row is present ---';
SELECT 'FINAL DATABASE STATE:' AS [Info], *
FROM dbo.SampleData
ORDER BY ID;

PRINT '--- Point-in-time restore POC COMPLETE ---';
GO

When you run this script, you’ll see that the second row (the one we wanted to exclude) never makes it back into the restored database.

5. Final Tips

Automation: Use SQL Server Agent or your preferred CI/CD system (Jenkins, Azure DevOps, etc.) to schedule and monitor backups.
Test Restores: Periodically restore your backups in a dev environment to confirm they’re valid.
Keep Enough Retention: Balance retention needs (e.g., 2 weeks to months) with storage costs.
Document Everything: Store your scripts in version control (Git) or a wiki/Confluence page for easy updates and collaboration.

That’s It!

A robust backup and restore strategy can save your bacon when the unexpected happens. Try out the PoC script in a sandbox, tweak the scheduling to match your RPO/RTO needs, and enjoy a bit more peace of mind knowing your SQL databases are protected.

Questions? Feel free to drop a comment below or share your own backup/restore tips!

DEV Community: Ev Vasilev

How We Build Reliable .NET Applications: Clean Architecture & Development Culture

Table of Contents

1. Introduction

1.1. What Is Clean Architecture & Why It Matters

1.2. Our Commitment to Quality & Simplicity

2. Architecture Overview

2.1. Project Structure Overview

Dependency Diagram

PlantUml

2.2. Principles: Separation of Concerns, Clear Boundaries, Scalability

3. Achieving Reliability

3.1. Flexible Testing Strategy

3.2. Automated Migrations & Monitoring

3.3. Health Checks & Observability

4. Team Collaboration & Growth

4.1. ADR as a Knowledge Sharing Tool

4.2. Code Reviews & Mentorship

4.3. Reducing Onboarding Time

5. Strengthening Teams Through Simplicity

5.1. Controllers & Use Cases Free of Excess Logic

5.2. Transparent Folder Structure & Code Style

5.3. Minimal Boilerplate via Shared Configs

6. Adapting to Change

6.1. Service Scaling

6.2. Long-Term Maintenance

7. Looking Ahead

7.1. Continuous Improvement Plan

7.2. Invitation to Collaborate

8. Conclusion

8.1. Key Benefits for Clients, Developers, & Business

8.2. Final Thoughts on Innovation & Culture

Quick Start: Elasticsearch + OpenTelemetry Collector

Why Set Up Elasticsearch + OpenTelemetry Collector?

Step 1: Generate docker-compose.yml

Explanation

Step 2: Create otel-collector-config.yml

Explanation

Step 3: Spin Up Docker Services

Explanation

Step 4: Send a Test Log to the Collector

Step 5: Query Elasticsearch

Summary

Building a Solid SQL Backup & Recovery Strategy (With T-SQL PoC)

While Documenting Backup Strategies, I Turned My Notes into This Quick Post

Overview

Why Care?

1. Backup Types & Scheduling

Full Backup

Differential Backup

Transaction Log Backup (T-Log)

2. Restoring Databases

Basic Restore Flow

Point-in-Time Recovery

3. Common Pitfalls

Transaction Log Is Full (Error 9002)

Corrupted or Missing Backups

4. The Proof-of-Concept (PoC) T-SQL Script

5. Final Tips

That’s It!

Step 1: Generate `docker-compose.yml`

Step 2: Create `otel-collector-config.yml`