plugin sdk database

About plugin database integration

Plugins can integrate with SQL Server for persistent storage. The database infrastructure is managed by Security Center through a state machine.

Database Architecture

flowchart TB
    A[Plugin Host Database Layer] --> B[Creates/Upgrades/Connects to SQL Server]
    B --> C[Calls plugin's DatabaseManager methods]
    C --> D[Plugin uses connection string for data access]

Loading

Database state machine:

stateDiagram-v2
    [*] --> Startup
    Startup --> Connected: Version valid
    Startup --> WaitingForUpgrade: Version too old
    Startup --> Disconnected: Connection failed or no DB
    Disconnected --> Startup: Retry or recovered
    Disconnected --> Creating: CreateDatabase or auto-create
    Creating --> Startup: Create complete
    Creating --> Error: Create failed
    WaitingForUpgrade --> Upgrading: UpgradeDatabase
    WaitingForUpgrade --> Disconnected: Connection lost
    Upgrading --> Startup: Upgrade finished
    Connected --> Disconnected: Connection lost
    Connected --> BackingUp: BackupDatabase
    Connected --> Restoring: RestoreDatabase
    Connected --> Dropping: DropDatabase
    Connected --> Cleanup: CleanupDatabase
    Connected --> ResolvingConflicts: ResolveConflicts
    BackingUp --> Connected: Backup complete (prev Connected)
    BackingUp --> Startup: Backup complete (prev not Connected)
    BackingUp --> Error: Backup failed
    Restoring --> Startup: Restore complete
    Restoring --> Error: Restore failed
    Dropping --> Disconnected: Drop complete
    Cleanup --> Startup: Cleanup complete
    ResolvingConflicts --> Startup
    Error --> Startup: Retry

Loading

Key concepts:

• Security Center manages database lifecycle
• Plugin provides schema and logic via DatabaseManager
• Each plugin role instance gets its own database
• Security Center provides database configuration for each role
• Supports SQL Server only (no other databases)

Implementing Database Support

Implement IPluginDatabaseSupport interface:

public class MyPlugin : Plugin, IPluginDatabaseSupport
{
    public DatabaseManager DatabaseManager => new MyDatabaseManager();
}

Interface requirements:

• Must provide a DatabaseManager instance
• DatabaseManager defines schema, upgrades, and cleanup
• Security Center calls DatabaseManager methods at appropriate times

DatabaseManager Class

Create a class inheriting from DatabaseManager:

public class MyDatabaseManager : DatabaseManager
{
    private DatabaseConfiguration m_config;

    public override string GetSpecificCreationScript(string databaseName) { }
    public override void SetDatabaseInformation(DatabaseConfiguration config) { }
    public override void OnDatabaseStateChanged(DatabaseNotification notification) { }
    public override IEnumerable<DatabaseCleanupThreshold> GetDatabaseCleanupThresholds() { }
    public override void DatabaseCleanup(string name, int retentionPeriod) { }
    public override IEnumerable<DatabaseUpgradeItem> GetDatabaseUpgradeItems() { }
}

UseSingleUserDatabaseCreation property

Controls whether the database is created in single-user mode:

public class MyDatabaseManager : DatabaseManager
{
    // Override to disable single-user mode during database creation
    public override bool UseSingleUserDatabaseCreation => false;
}

Default value: true

When true (default):

• Database is created in single-user mode
• Prevents other connections during creation
• Provides exclusive access for schema setup
• Recommended for most scenarios

When false:

• Database is created in multi-user mode
• Other connections may access the database during creation
• Use when single-user mode causes issues with your database setup

Database Lifecycle

Initialization Sequence

The database layer starts after OnPluginLoaded() and before OnPluginStart():

flowchart TB
    A[1. Plugin constructed] --> B["2. Plugin.Initialize(engine, roleGuid, culture)<br/>OnPluginLoaded() runs"]
    B --> C{Implements IPluginDatabaseSupport?}
    C -->|No| H1[3. OnPluginStart called]
    C -->|Yes| D[3. DatabaseManager.SetDatabaseInformation]
    D --> E["4. Database layer created<br/>reads GetDatabaseUpgradeItems()"]
    E --> F[5. Database state machine starts]
    F --> G["6. Creating state calls GetSpecificCreationScript()<br/>when database is missing"]
    F --> I["7. Upgrading state runs upgrade items<br/>when version is behind"]
    F --> J["8. OnDatabaseStateChanged notifications<br/>Startup, Disconnected, Creating, Upgrading, Connected, Error"]
    F --> H2[9. OnPluginStart called]

Loading

Critical timing:

• In OnPluginLoaded(), database may not be ready
• OnPluginStart() does not guarantee DatabaseState.Connected
• Use OnDatabaseStateChanged() to wait for DatabaseState.Connected

See Plugin SDK Lifecycle for lifecycle details.

DatabaseManager Methods

GetSpecificCreationScript()

Called if database doesn't exist. Returns SQL to create tables and schema.

public override string GetSpecificCreationScript(string databaseName)
{
    return $@"
        CREATE TABLE [{databaseName}].[dbo].[EventLog] (
            [Id] INT IDENTITY(1,1) PRIMARY KEY,
            [Timestamp] DATETIME2 NOT NULL,
            [EventType] NVARCHAR(50) NOT NULL,
            [Description] NVARCHAR(MAX),
            [EntityGuid] UNIQUEIDENTIFIER
        );
        
        CREATE INDEX IX_EventLog_Timestamp 
        ON [{databaseName}].[dbo].[EventLog]([Timestamp]);
        
        CREATE TABLE [{databaseName}].[dbo].[Configuration] (
            [Key] NVARCHAR(100) PRIMARY KEY,
            [Value] NVARCHAR(MAX)
        );
    ";
}

Important

• Security Center supplies the database name
• Always use the databaseName parameter in your SQL
• If the script fails, database creation fails

SetDatabaseInformation()

Called before the database layer starts and provides connection info.

public override void SetDatabaseInformation(DatabaseConfiguration config)
{
    m_config = config;

    // Use CreateSqlDatabaseConnection() for a ready-to-use connection
    // or use ConnectionString + DatabaseCredential for manual connection creation

    Logger.TraceInformation("Database connection configured");
}

DatabaseConfiguration properties:

• ConnectionString - SQL Server connection string (does not include credentials if using SqlCredential)
• DatabaseCredential - SqlCredential for authentication (null if using Windows integrated security)
• CreateSqlDatabaseConnection() - Creates a configured SqlConnection ready for use

When called:

• Before the database layer starts
• Before OnPluginStart()

OnDatabaseStateChanged()

Called on database state transitions.

public override void OnDatabaseStateChanged(DatabaseNotification notification)
{
    switch (notification.State)
    {
        case DatabaseState.Disconnected:
            Logger.TraceWarning("Database disconnected");
            ModifyPluginState(new PluginStateEntry("Database", "Disconnected") { IsWarning = true });
            break;
            
        case DatabaseState.Creating:
            Logger.TraceInformation("Creating database...");
            ModifyPluginState(new PluginStateEntry("Database", "Creating database"));
            break;
            
        case DatabaseState.Upgrading:
            Logger.TraceInformation("Upgrading database...");
            ModifyPluginState(new PluginStateEntry("Database", "Upgrading schema"));
            break;
            
        case DatabaseState.Connected:
            Logger.TraceInformation("Database connected");
            ModifyPluginState(new PluginStateEntry("Database", "Connected"));
            break;
            
        case DatabaseState.Error:
            Logger.TraceError("Database error state");
            ModifyPluginState(new PluginStateEntry("Database",
                "Database error") { IsError = true });
            break;
    }
}

DatabaseNotification properties:

• State - Current database state

Database Cleanup

Config Tool allows administrators to configure data retention policies for plugins.

GetDatabaseCleanupThresholds()

Define data retention thresholds that appear in Config Tool:

public override IEnumerable<DatabaseCleanupThreshold> GetDatabaseCleanupThresholds()
{
    yield return new DatabaseCleanupThreshold(
        name: "EventLog",
        title: "Event Log",
        defaultIsEnabled: true,
        defaultRetentionPeriod: 90);

    yield return new DatabaseCleanupThreshold(
        name: "AuditTrail",
        title: "Audit Trail",
        defaultIsEnabled: true,
        defaultRetentionPeriod: 365);
}

DatabaseCleanupThreshold constructor:

DatabaseCleanupThreshold(string name, string title, bool defaultIsEnabled = true, int defaultRetentionPeriod = 90)

• name - Unique identifier for this threshold (used in DatabaseCleanup callback)
• title - User-friendly title displayed in Config Tool
• defaultIsEnabled - Whether cleanup is enabled by default
• defaultRetentionPeriod - Default days to retain data (must be > 0 if enabled, must be 0 if disabled)

Config Tool displays these thresholds and allows administrators to:

• Adjust retention periods
• Schedule automatic cleanup
• Manually trigger cleanup

DatabaseCleanup()

Called when cleanup is triggered (scheduled or manual):

public override void DatabaseCleanup(string name, int retentionPeriod)
{
    switch (name)
    {
        case "EventLog":
            CleanupEventLog(retentionPeriod);
            break;

        case "AuditTrail":
            CleanupAuditTrail(retentionPeriod);
            break;
    }
}

private void CleanupEventLog(int retentionDays)
{
    var cutoffDate = DateTime.UtcNow.AddDays(-retentionDays);

    using (var connection = m_config.CreateSqlDatabaseConnection())
    {
        connection.Open();

        using (var command = connection.CreateCommand())
        {
            command.CommandText = @"
                DELETE FROM EventLog
                WHERE Timestamp < @CutoffDate";
            command.Parameters.AddWithValue("@CutoffDate", cutoffDate);

            int deleted = command.ExecuteNonQuery();
            Logger.TraceInformation($"Deleted {deleted} old event log entries");
        }
    }
}

Important

• Cleanup runs on a background thread

• Can be long-running without blocking plugin
• Should handle errors gracefully
• Log progress and results

Database Upgrades

Support database schema versioning with upgrade scripts.

GetDatabaseUpgradeItems()

Define incremental schema upgrades:

public override IEnumerable<DatabaseUpgradeItem> GetDatabaseUpgradeItems()
{
    // Upgrade from version 1 to version 2
    yield return new DatabaseUpgradeItem(
        sourceVersion: 1,
        targerVersion: 2,
        upgradeScript: @"
            ALTER TABLE EventLog
            ADD [Severity] INT NOT NULL DEFAULT 0;
        ");

    // Upgrade from version 2 to version 3
    yield return new DatabaseUpgradeItem(
        sourceVersion: 2,
        targerVersion: 3,
        upgradeScript: @"
            CREATE TABLE AuditLog (
                Id INT IDENTITY(1,1) PRIMARY KEY,
                Timestamp DATETIME2 NOT NULL,
                Action NVARCHAR(100),
                UserGuid UNIQUEIDENTIFIER
            );
        ");

    // Upgrade from version 3 to version 4
    yield return new DatabaseUpgradeItem(
        sourceVersion: 3,
        targerVersion: 4,
        upgradeScript: @"
            CREATE INDEX IX_AuditLog_Timestamp
            ON AuditLog(Timestamp);
        ");
}

DatabaseUpgradeItem constructor:

DatabaseUpgradeItem(int sourceVersion, int targerVersion, string upgradeScript)

• sourceVersion - The version to upgrade from
• targerVersion - The version to upgrade to (note: parameter name contains a typo in the API)
• upgradeScript - SQL script to run for this upgrade

Note

The parameter name targerVersion contains a typo in the SDK API. Use this exact spelling when using named parameters.

How upgrades work:

• Security Center tracks current database version
• Applies upgrades sequentially (1 -> 2 -> 3 -> 4)
• Each upgrade runs in a transaction
• If upgrade fails, database stays at previous version
• Upgrades run during database state transitions and can still be in progress when OnPluginStart() runs

Version numbering rules:

• Each upgrade specifies source and target versions
• Upgrades must be sequential (no gaps)
• Never change existing upgrade scripts after deployment

Using the Database

Database Access

Use the DatabaseConfiguration from SetDatabaseInformation(). Wait for DatabaseState.Connected in OnDatabaseStateChanged() before using the database.

private DatabaseConfiguration m_dbConfig;

public override void SetDatabaseInformation(DatabaseConfiguration config)
{
    m_dbConfig = config;
}

public override void OnDatabaseStateChanged(DatabaseNotification notification)
{
    if (notification.State == DatabaseState.Connected)
    {
        InsertEvent("Plugin started", EventType.Information);
    }
}

private void InsertEvent(string description, EventType type)
{
    using (var connection = m_dbConfig.CreateSqlDatabaseConnection())
    {
        connection.Open();

        using (var command = connection.CreateCommand())
        {
            command.CommandText = @"
                INSERT INTO EventLog (Timestamp, EventType, Description)
                VALUES (@Timestamp, @EventType, @Description)";
            command.Parameters.AddWithValue("@Timestamp", DateTime.UtcNow);
            command.Parameters.AddWithValue("@EventType", type.ToString());
            command.Parameters.AddWithValue("@Description", description);

            command.ExecuteNonQuery();
        }
    }
}

Async Database Operations

Use async methods for database access:

private async Task<List<EventRecord>> GetRecentEventsAsync(int count)
{
    var events = new List<EventRecord>();

    using (var connection = m_dbConfig.CreateSqlDatabaseConnection())
    {
        await connection.OpenAsync();

        using (var command = connection.CreateCommand())
        {
            command.CommandText = @"
                SELECT TOP (@Count) Timestamp, EventType, Description
                FROM EventLog
                ORDER BY Timestamp DESC";
            command.Parameters.AddWithValue("@Count", count);

            using (var reader = await command.ExecuteReaderAsync())
            {
                while (await reader.ReadAsync())
                {
                    events.Add(new EventRecord
                    {
                        Timestamp = reader.GetDateTime(0),
                        EventType = reader.GetString(1),
                        Description = reader.GetString(2)
                    });
                }
            }
        }
    }

    return events;
}

Database Configuration in Config Tool

Administrators configure database settings in Config Tool:

Path: System > Roles > PLUGIN-ROLE > Resources > Database

Replace PLUGIN-ROLE with your plugin role name.

Configurable settings:

• SQL Server instance
• Database name (auto-generated, can be changed)
• Connection encryption
• Cleanup thresholds and schedules

Database capabilities:

• Database resources are added to the role when IPluginDatabaseSupport is implemented
• Database encryption support is available with IPluginDatabaseSupport

Related Guides

• Plugin SDK Overview - Plugin architecture
• Plugin SDK Threading - Threading for database operations
• Plugin SDK Lifecycle - Database initialization timing
• Plugin SDK State Management - Reporting database state
• Logging - Database operation logging