title: Database Maintenance category: System & Maintenance tags: database, maintenance, optimization, backup, indexes priority: Normal

Database Maintenance

IdentityCenter relies on SQL Server for all identity, compliance, and operational data. Regular database maintenance ensures optimal query performance, reliable operation, and data integrity. This article covers automatic optimization, manual maintenance tasks, the migration system, log cleanup, and backup procedures.

Automatic Index Optimization

The DatabaseOptimizationService handles automatic index maintenance and statistics updates. It runs as part of the scheduled DatabaseIndexMaintenanceJob and DatabaseStatisticsJob.

Index Maintenance Job

The DatabaseIndexMaintenanceJob analyzes index fragmentation and takes appropriate action:

Statistics Update Job

The DatabaseStatisticsJob updates SQL Server statistics to ensure the query optimizer has current data distribution information. Stale statistics lead to poor query plans and slow report execution.

Recommended Schedule

Manual Maintenance Tasks

Administrators can perform the following maintenance tasks manually when needed:

Rebuild Indexes

Force a full rebuild of all database indexes. Use this when:

• Query performance has degraded significantly
• After a large bulk import or sync operation
• Following a major data migration

Update Statistics

Force an update of all table statistics. Use this when:

• Reports are running slower than expected
• After significant data changes
• After adding new indexes

Check Database Integrity

Runs DBCC CHECKDB to verify the physical and logical integrity of all objects in the database. This detects:

• Corrupted pages
• Incorrect page checksums
• Broken allocation chains
• Invalid metadata

Tip: Run integrity checks monthly or after any unexpected SQL Server shutdown. DBCC CHECKDB is a read-only operation but can be resource-intensive on large databases.

Database Migration System

IdentityCenter uses a Dapper-based migration system (no EF Core migrations at runtime). All schema changes are managed by the DatabaseMigrationService using embedded SQL scripts.

Migration Scripts

Migration scripts are located in DataAccessLibrary/Migrations/Scripts/ and are embedded as resources in the assembly:

Migration Behavior

• Migrations run automatically on application startup
• Each migration is idempotent (safe to re-run)
• V002 and V003 include table existence guards, so they skip gracefully on fresh databases
• Migration version is tracked in the database to prevent re-execution
• The SchemaVerificationService validates schema consistency after migrations

Viewing Migration Status

The Updates page (/admin/updates) displays the current database schema version and the history of applied migrations, including version number, script name, and application date.

Log and Session Cleanup

Over time, log tables and session records accumulate and consume disk space. IdentityCenter includes automated cleanup jobs:

LogCleanupJob

• Purges old log entries beyond a configurable retention period
• Default retention: 90 days
• Runs on a scheduled basis (recommended: daily)

SessionCleanupJob

• Removes expired user session records
• Cleans up abandoned sessions that were never properly closed
• Runs on a scheduled basis (recommended: daily)

OrphanedDataCleanupJob

• Identifies and removes orphaned records from deleted parent entities
• Handles group memberships referencing deleted objects
• Runs on a scheduled basis (recommended: weekly)

TempFileCleanupJob

• Removes temporary files created by report exports and bulk operations
• Prevents disk space exhaustion from accumulated temp files
• Runs on a scheduled basis (recommended: daily)

Backup Procedures

SQL Server Native Backup

Use SQL Server Management Studio (SSMS) or T-SQL to create database backups:

BACKUP DATABASE [IdentityCenter]
TO DISK = N'C:\Backups\IdentityCenter_Full.bak'
WITH FORMAT, INIT, COMPRESSION,
     NAME = N'IdentityCenter Full Backup';

Backup Schedule Recommendations

Restore Testing

Regularly verify that backups can be restored:

1. Restore to a test SQL Server instance
2. Verify the database comes online without errors
3. Run DBCC CHECKDB on the restored database
4. Start IdentityCenter against the restored database to verify application compatibility

Tip: Schedule restore tests monthly. A backup that has never been tested is not a reliable backup.

Monitoring Database Size and Growth

Track these metrics to plan capacity:

Best Practices

1. Run index maintenance weekly -- Schedule the DatabaseIndexMaintenanceJob for off-peak hours (Sunday 2 AM recommended)
2. Keep statistics updated daily -- Stale statistics cause poor query plans and slow dashboards
3. Take daily backups -- Full backup daily with differential backups every 4 hours
4. Test restores monthly -- Verify backup integrity by restoring to a test instance
5. Run integrity checks monthly -- DBCC CHECKDB catches corruption early
6. Monitor log file growth -- Large transaction log files indicate missing log backups
7. Enable cleanup jobs -- Log, session, orphaned data, and temp file cleanup prevent unbounded growth
8. Plan for growth -- Each synced object class adds rows; plan storage for 2-3x current data

Next Steps

• System Center -- Overview of system health and diagnostics
• Processing Center -- Monitor maintenance job execution
• Job Monitoring & History -- Track database maintenance job results
• Product Updates -- Updates may include new migration scripts