Zetian.Storage.SqlServer 1.0.3

Zetian.Storage.SqlServer - SQL Server Storage

SQL Server storage provider for Zetian SMTP Server. Provides high-performance message persistence to Microsoft SQL Server databases with features including automatic table creation, message compression, separate attachment storage, retry logic, and full ACID compliance. Perfect for enterprise environments requiring reliable message storage with SQL Server infrastructure.

⚡ Features

• 🔒 ACID Compliance - Full transactional support
• 🔍 Full-Text Search - Indexed fields for fast searching
• 🚀 High Performance - Optimized queries and indexing
• 📏 Size Management - Configurable message size limits
• 🗜️ Compression - GZIP compression for message bodies
• 📦 Batch Operations - Efficient bulk insert/update support
• 🔐 Security - Support for Windows and SQL authentication
• 🔄 Retry Logic - Built-in retry mechanism for transient failures
• 🗺️ Automatic Setup - Tables and indexes created automatically
• 📎 Attachment Handling - Separate table for efficient attachment storage

📦 Installation

# Install SMTP Server and Storage Provider
dotnet add package Zetian
dotnet add package Zetian.Storage.SqlServer

🚀 Quick Start

Basic Configuration

using Zetian.Server;
using Zetian.Storage.SqlServer.Extensions;

// Configure with connection string
var server = new SmtpServerBuilder()
    .Port(25)
    .WithSqlServerStorage("Server=localhost;Database=SmtpDb;Trusted_Connection=true;")
    .Build();

await server.StartAsync();

Advanced Configuration

var server = new SmtpServerBuilder()
    .Port(25)
    .WithSqlServerStorage(
        "Server=localhost;Database=SmtpStorage;Integrated Security=true;",
        config =>
        {
            config.TableName = "SmtpMessages";
            config.AttachmentsTableName = "SmtpAttachments";
            config.SchemaName = "dbo";
            config.AutoCreateTable = true;
            config.StoreAttachmentsSeparately = true;
            config.CompressMessageBody = true;
            config.MaxMessageSizeMB = 50;
            config.EnableRetry = true;
            config.MaxRetryAttempts = 3;
        })
    .Build();

🛠️ Configuration Options

📏 Database Schema

Messages Table

CREATE TABLE [mail].[Messages] (
    Id BIGINT IDENTITY(1,1) PRIMARY KEY,
    MessageId NVARCHAR(255) NOT NULL UNIQUE,
    SessionId NVARCHAR(255) NOT NULL,
    FromAddress NVARCHAR(500) NULL,
    ToAddresses NVARCHAR(MAX) NOT NULL,
    CcAddresses NVARCHAR(MAX) NULL,
    BccAddresses NVARCHAR(MAX) NULL,
    Subject NVARCHAR(1000) NULL,
    ReceivedDate DATETIME2 NOT NULL,
    MessageSize BIGINT NOT NULL,
    MessageBody VARBINARY(MAX) NOT NULL,
    IsCompressed BIT NOT NULL DEFAULT 0,
    Headers NVARCHAR(MAX) NULL,
    HasAttachments BIT NOT NULL DEFAULT 0,
    AttachmentCount INT NOT NULL DEFAULT 0,
    Priority NVARCHAR(50) NULL,
    RemoteIP NVARCHAR(45) NULL,
    CreatedAt DATETIME2 DEFAULT GETUTCDATE(),
    INDEX IX_MessageId NONCLUSTERED (MessageId),
    INDEX IX_ReceivedDate NONCLUSTERED (ReceivedDate DESC),
    INDEX IX_FromAddress NONCLUSTERED (FromAddress),
    INDEX IX_SessionId NONCLUSTERED (SessionId)
)

Attachments Table (Optional)

CREATE TABLE [mail].[Attachments] (
    Id BIGINT IDENTITY(1,1) PRIMARY KEY,
    MessageId NVARCHAR(255) NOT NULL,
    FileName NVARCHAR(500) NOT NULL,
    ContentType NVARCHAR(255) NOT NULL,
    FileSize BIGINT NOT NULL,
    FileContent VARBINARY(MAX) NOT NULL,
    IsCompressed BIT NOT NULL DEFAULT 0,
    CreatedAt DATETIME2 DEFAULT GETUTCDATE(),
    FOREIGN KEY (MessageId) REFERENCES [mail].[Messages](MessageId) ON DELETE CASCADE,
    INDEX IX_MessageId NONCLUSTERED (MessageId)
)

🎯 Usage Examples

Connection String Options

// Windows Authentication
.WithSqlServerStorage(
    "Server=localhost;Database=SmtpDb;Trusted_Connection=true;",
    config => { })

// SQL Server Authentication
.WithSqlServerStorage(
    "Server=localhost;Database=SmtpDb;User Id=sa;Password=YourPassword;",
    config => { })

// Azure SQL Database
.WithSqlServerStorage(
    "Server=tcp:myserver.database.windows.net,1433;Database=SmtpDb;User Id=admin;Password=YourPassword;Encrypt=True;",
    config => { })

// With Connection Pooling
.WithSqlServerStorage(
    "Server=localhost;Database=SmtpDb;Trusted_Connection=true;Min Pool Size=5;Max Pool Size=100;",
    config => { })

Retrieving Messages

// Get message by ID
var message = await messageStore.GetMessageAsync("msg-123");

// List recent messages
var filter = new MessageFilter
{
    StartDate = DateTime.UtcNow.AddDays(-7),
    EndDate = DateTime.UtcNow,
    MaxResults = 100
};
var messages = await messageStore.ListMessagesAsync(filter);

// Count messages
var count = await messageStore.GetMessageCountAsync(filter);

// Check if message exists
var exists = await messageStore.MessageExistsAsync("msg-123");

Handling Large Messages

var server = new SmtpServerBuilder()
    .Port(25)
    .WithSqlServerStorage(
        connectionString,
        config =>
        {
            config.MaxMessageSizeMB = 100; // Allow up to 100MB
            config.CompressMessageBody = true; // Enable compression
            config.StoreAttachmentsSeparately = true; // Store attachments separately
            config.ConnectionTimeoutSeconds = 120; // Increase timeout for large messages
        })
    .Build();

🔧 Performance Optimization

Connection Pooling

// Optimize connection pooling
.WithSqlServerStorage(
    "Server=localhost;Database=SmtpDb;" +
    "Trusted_Connection=true;" +
    "Min Pool Size=10;" +
    "Max Pool Size=100;" +
    "Connect Timeout=30;",
    config => { })

Index Recommendations

-- Additional indexes for better performance
CREATE INDEX IX_Subject ON [mail].[Messages] (Subject);
CREATE INDEX IX_ToAddresses ON [mail].[Messages] (ToAddresses);
CREATE INDEX IX_CreatedAt ON [mail].[Messages] (CreatedAt DESC);

-- Full-text index for searching
CREATE FULLTEXT INDEX ON [mail].[Messages]
    (Subject, MessageBody)
    KEY INDEX PK_Messages;

Maintenance Scripts

-- Clean up old messages
DELETE FROM [mail].[Messages]
WHERE ReceivedDate < DATEADD(MONTH, -6, GETUTCDATE());

-- Rebuild indexes
ALTER INDEX ALL ON [mail].[Messages] REBUILD;

-- Update statistics
UPDATE STATISTICS [mail].[Messages];

☁️ Azure SQL Database

Connection Configuration

.WithSqlServerStorage(
    "Server=tcp:myserver.database.windows.net,1433;" +
    "Initial Catalog=SmtpDb;" +
    "Persist Security Info=False;" +
    "User ID=admin;" +
    "Password={your_password};" +
    "MultipleActiveResultSets=False;" +
    "Encrypt=True;" +
    "TrustServerCertificate=False;" +
    "Connection Timeout=30;",
    config =>
    {
        // Azure-optimized settings
        config.EnableRetry = true;
        config.MaxRetryAttempts = 5; // More retries for cloud
        config.ConnectionTimeoutSeconds = 60; // Longer timeout
    })

Using Azure AD Authentication

// Using Azure AD Managed Identity
.WithSqlServerStorage(
    "Server=tcp:myserver.database.windows.net,1433;" +
    "Authentication=Active Directory Managed Identity;" +
    "Database=SmtpDb;",
    config => { })

🔒 Security Best Practices

Connection String Security

// Store in configuration
var connectionString = Configuration.GetConnectionString("SmtpDatabase");

// Use Azure Key Vault
var connectionString = await keyVaultClient.GetSecretAsync("smtp-db-connection");

// Use environment variables
var connectionString = Environment.GetEnvironmentVariable("SMTP_DB_CONNECTION");

SQL Injection Prevention

The provider uses parameterized queries throughout to prevent SQL injection:

// Safe parameterized query example (internal)
using var command = new SqlCommand(
    "SELECT * FROM Messages WHERE MessageId = @MessageId", 
    connection);
command.Parameters.AddWithValue("@MessageId", messageId);

🔍 Monitoring & Diagnostics

Enable Logging

services.AddLogging(builder =>
{
    builder.AddConsole();
    builder.AddDebug();
});

// Configure storage with logging
.WithSqlServerStorage(
    connectionString,
    config =>
    {
        config.LogErrors = true;
    })

Performance Metrics

-- Monitor table sizes
SELECT 
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.NAME IN ('Messages', 'Attachments')
GROUP BY t.Name, p.Rows;

🎯 Migration from Other Providers

From File System

// Migrate existing file-based messages
var files = Directory.GetFiles("./smtp-messages", "*.eml");
foreach (var file in files)
{
    var message = ParseEmlFile(file);
    await messageStore.SaveMessageAsync(message);
}

From Another Database

// Example migration from PostgreSQL
var pgMessages = await PostgreSqlStore.GetAllMessagesAsync();
foreach (var message in pgMessages)
{
    await sqlServerStore.SaveMessageAsync(message);
}

🔧 Troubleshooting

Common Issues

Connection Timeout

// Increase connection timeout
config.ConnectionTimeoutSeconds = 60;

Table Creation Failed

// Ensure proper permissions
GRANT CREATE TABLE TO [smtp_user];
GRANT ALTER ON SCHEMA::mail TO [smtp_user];

Performance Issues

-- Check for missing indexes
SELECT * FROM sys.dm_db_missing_index_details;

-- Check for blocking
SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id > 0;

📋 Requirements

• .NET 6.0, 7.0, 8.0, 9.0, or 10.0
• Zetian SMTP Server package
• Azure SQL Database (all tiers)
• Microsoft.Data.SqlClient package (included)
• SQL Server 2016 or later (2019+ recommended)

📚 Documentation & Support

• Issues: GitHub Issues
• Examples: GitHub Examples
• Discussions: GitHub Discussions
• Documentation: Zetian Documentation
• SQL Server Docs: Microsoft SQL Documentation

📄 License

MIT License - see LICENSE

Built with ❤️ for the .NET community