Zetian.Storage.SqlServer

./nugetz

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

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();

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();

Option	Type	Default	Description
`ConnectionString`	string	required	SQL Server connection string
`TableName`	string	"SmtpMessages"	Name of the messages table
`SchemaName`	string	"dbo"	Database schema name
`AutoCreateTable`	bool	true	Auto-create table if it doesn't exist
`StoreAttachmentsSeparately`	bool	false	Store attachments in separate table
`AttachmentsTableName`	string	"SmtpAttachments"	Name of the attachments table
`MaxMessageSizeMB`	double	100	Maximum message size in MB
`CompressMessageBody`	bool	false	Compress message bodies
`EnableRetry`	bool	true	Enable retry logic
`MaxRetryAttempts`	int	3	Maximum retry attempts
`RetryDelayMs`	int	1000	Delay between retries
`ConnectionTimeoutSeconds`	int	30	Connection timeout
`LogErrors`	bool	true	Whether to log errors

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)
)

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)
)

// 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 => { })

// 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");

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();

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

-- 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;

-- 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];

.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 Managed Identity
.WithSqlServerStorage(
    "Server=tcp:myserver.database.windows.net,1433;" +
    "Authentication=Active Directory Managed Identity;" +
    "Database=SmtpDb;",
    config => { })

// 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");

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

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

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

-- 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;

// 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);
}

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

// Increase connection timeout
config.ConnectionTimeoutSeconds = 60;

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

-- 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;

Soferity/Zetian.Storage.SqlServerv1.0.4

Get Started

Readme

Zetian.Storage.SqlServer - SQL Server Storage

⚡ Features

📦 Installation

🚀 Quick Start

Basic Configuration

Advanced Configuration

🛠️ Configuration Options

📏 Database Schema

Messages Table

Attachments Table (Optional)

🎯 Usage Examples

Connection String Options

Retrieving Messages

Handling Large Messages

🔧 Performance Optimization

Connection Pooling

Index Recommendations

Maintenance Scripts

☁️ Azure SQL Database

Connection Configuration

Using Azure AD Authentication

🔒 Security Best Practices

Connection String Security

SQL Injection Prevention

🔍 Monitoring & Diagnostics

Enable Logging

Performance Metrics

🎯 Migration from Other Providers

From File System

From Another Database

🔧 Troubleshooting

Common Issues

📋 Requirements

📚 Documentation & Support

📄 License