Archiving Framework - Best Practices

This guide provides best practices and recommendations for implementing, configuring, and maintaining the ExMeX Archiving Framework to ensure optimal performance, data integrity, and compliance.

Planning and Design

Assess Your Data Lifecycle Requirements

Before implementing archiving, clearly define your data lifecycle requirements:

Determine Retention Periods

• Identify legal and regulatory requirements for data retention
• Consider business requirements for historical data access
• Document retention periods for different data object categories
• Plan for both active retention and archive retention periods

Example Retention Strategy:

Data Category	Retention Period	Archive Retention	Total	Rationale
Process Logs	30 days	365 days	395 days	Compliance requires 1 year
Audit Trails	90 days	2555 days	2645 days	Legal requirement: 7 years
Performance Metrics	180 days	545 days	725 days	Historical analysis: 2 years
Debug Logs	7 days	23 days	30 days	Only recent data relevant

Analyze Data Growth Patterns

Monitor Table Growth

-- Query to analyze table growth and archiving needs
SELECT
    OBJECT_SCHEMA_NAME(ps.object_id) AS SchemaName,
    OBJECT_NAME(ps.object_id) AS TableName,
    SUM(ps.reserved_page_count) * 8.0 / 1024 / 1024 AS SizeGB,
    SUM(ps.row_count) AS [RowCount],
    MAX(t.create_date) AS TableCreatedDate
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.tables t ON ps.object_id = t.object_id
WHERE ps.index_id IN (0,1)
GROUP BY ps.object_id
HAVING SUM(ps.row_count) > 1000000  -- Tables with > 1M rows
ORDER BY SizeGB DESC

Use this information to:

• Identify tables that need archiving
• Estimate storage savings from archiving
• Plan archiving schedules based on data accumulation rates

Design Archive Schema Structure

Follow Naming Conventions

The ExMeX Framework uses consistent naming patterns:

• Archive Schema: [SourceSchema]Archive
• Archive Table: [SourceTable]Archive

Maintain consistency across your data solution:

-- Good: Follows convention
Source: MetadataZone.ProcessStepVapourTrail
Archive: MetadataZoneArchive.ProcessStepVapourTrailArchive

-- Alternative: Custom schema for centralized archiving
Source: MetadataZone.ProcessStepVapourTrail
Archive: CentralArchive.ProcessStepVapourTrailArchive

Create Archive Schemas in Advance

Before registering data objects, ensure archive schemas exist:

-- Check if archive schema exists
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = N'MetadataZoneArchive')
BEGIN
    EXEC('CREATE SCHEMA MetadataZoneArchive AUTHORIZATION dbo')
    PRINT 'Archive schema MetadataZoneArchive created successfully.'
END

Data Object Registration

Index Strategy

Always Create Indexes Before Registration

The archiving column must have an index for optimal performance. Create the index before calling uspSetArchivingDataObject:

-- Create index on archiving column BEFORE registration
CREATE NONCLUSTERED INDEX [IX_MetadataZone_ProcessStepVapourTrail_ProcessEndDateTime]
    ON [MetadataZone].[ProcessStepVapourTrail] ([ProcessEndDateTime] ASC)
    WITH (DATA_COMPRESSION = PAGE, ONLINE = ON)
GO

-- Then register the data object
DECLARE @ReturnProcessStatusNumber INT
EXEC @ReturnProcessStatusNumber = MetadataZoneCore.uspSetArchivingDataObject
     @SetArchivingExec     = N'A',
     @SourceSchemaName     = N'MetadataZone',
     @SourceDataObject     = N'ProcessStepVapourTrail',
     @ArchivingColumnCode  = N'ProcessEndDateTime',
     @IsActive             = 1,
     @Comment              = N'Process logging with 30+365 day retention'
SELECT @ReturnProcessStatusNumber AS [Status]
GO

Index Design Recommendations:

• Use NONCLUSTERED indexes on archiving columns
• Enable DATA_COMPRESSION = PAGE to reduce storage
• Consider ONLINE = ON for production environments (Enterprise Edition)
• Include frequently queried columns with INCLUDE clause if needed

Archiving Column Selection

Choose Appropriate Date Columns

Select columns that accurately represent when data becomes eligible for archiving:

Good Column Choices:

• ProcessEndDateTime - Completion timestamp
• CreatedDate - Record creation date
• ModifiedAt - Last modification timestamp
• AssertionTimestamp - Data assertion time
• YearMonth - Period identifier (e.g., 202501)

Avoid:

• Columns that get updated frequently (e.g., LastAccessedDate)
• Nullable columns without proper handling
• Columns without indexes

Validate Column Format Before Registration

Always test if your column contains valid date data:

-- Test archiving column validity
DECLARE @SourceDatabaseName NVARCHAR(128) = N'<your database name>'
DECLARE @SourceSchemaName NVARCHAR(128) = N'<your schema name>'
DECLARE @SourceDataObject NVARCHAR(128) = N'<your data object name>'
DECLARE @ArchivingColumnCode NVARCHAR(128) = N'<your archiving column name>'
DECLARE @Sql NVARCHAR(MAX) = N''

-- Test if archiving column code contains valid format
SET @Sql = N'
IF (SELECT TOP 1 ISDATE(CAST([' + @ArchivingColumnCode + '] AS VARCHAR(24)))
    FROM [' + @SourceDatabaseName + '].[' + @SourceSchemaName + '].[' + @SourceDataObject + ']) <> 0
    SELECT ''VALID format''
ELSE
BEGIN
    IF (SELECT ISDATE(CONCAT(CAST(MAX([' + @ArchivingColumnCode + ']) AS VARCHAR(24)),''01''))
        FROM [' + @SourceDatabaseName + '].[' + @SourceSchemaName + '].[' + @SourceDataObject + ']) <> 0
        SELECT ''VALID format''
    ELSE
    BEGIN
        IF EXISTS (
            SELECT ''VALID format''
            FROM [' + @SourceDatabaseName + '].sys.objects so
            INNER JOIN [' + @SourceDatabaseName + '].sys.schemas ss
                ON so.schema_id = ss.schema_id
            INNER JOIN [' + @SourceDatabaseName + '].sys.syscolumns sc
                ON so.object_id = sc.id
            INNER JOIN [' + @SourceDatabaseName + '].sys.types t
                ON sc.xtype = t.user_type_id
            WHERE so.type = N''U''
            AND so.name = N''' + @SourceDataObject + '''
            AND ss.name = N''' + @SourceSchemaName + '''
            AND sc.name = N''' + @ArchivingColumnCode + '''
            AND t.name = ''datetime2''
        )
            SELECT ''VALID format''
        ELSE
            SELECT ''INVALID format''
    END
END';

EXEC (@Sql)

Set Appropriate Retention Periods

Start Conservative, Then Optimize

Begin with longer retention periods and gradually reduce based on actual usage patterns:

-- Initial conservative setup
EXEC MetadataZoneCore.uspSetArchivingDataObject
     @SetArchivingExec          = N'A',
     @SourceSchemaName          = N'MetadataZone',
     @SourceDataObject          = N'NewLoggingTable',
     @ArchivingColumnCode       = N'CreatedDate',
     @IsActive                  = 0,  -- Start inactive
     @RetentionPeriod           = 90,  -- Conservative: 3 months
     @ArchivingRetentionPeriod  = 730, -- Conservative: 2 years
     @Comment                   = N'Initial setup - monitor before activation'
GO

-- After monitoring, optimize
EXEC MetadataZoneCore.uspSetArchivingDataObject
     @SetArchivingExec          = N'U',
     @SourceSchemaName          = N'MetadataZone',
     @SourceDataObject          = N'NewLoggingTable',
     @IsActive                  = 1,   -- Activate
     @RetentionPeriod           = 30,  -- Optimized: 1 month
     @ArchivingRetentionPeriod  = 365, -- Optimized: 1 year
     @Comment                   = N'Optimized after usage analysis'
GO

Configure Execution Order

Order Dependent Data Objects Correctly

When tables have foreign key relationships or logical dependencies, use @ArchivingExecutionOrder:

-- Archive parent/header tables first (lower order number)
EXEC MetadataZoneCore.uspSetArchivingDataObject
     @SetArchivingExec          = N'U',
     @SourceSchemaName          = N'MetadataZone',
     @SourceDataObject          = N'BatchRunVapourTrail',
     @ArchivingExecutionOrder   = 1,  -- Archive first
     @Comment                   = N'Parent table - archive before details'
GO

-- Archive child/detail tables later (higher order number)
EXEC MetadataZoneCore.uspSetArchivingDataObject
     @SetArchivingExec          = N'U',
     @SourceSchemaName          = N'MetadataZone',
     @SourceDataObject          = N'ProcessStepVapourTrail',
     @ArchivingExecutionOrder   = 2,  -- Archive after parent
     @Comment                   = N'Detail table - archive after parent'
GO

Execution Order Guidelines:

• Lower numbers execute first (1, 2, 3, ...)
• NULL values execute last (no specific order)
• Use increments of 10 (10, 20, 30) to allow for future insertions
• Document dependencies in @Comment field

Scheduling and Execution

Schedule Archiving Jobs Appropriately

Off-Peak Hours Execution

Schedule archiving during maintenance windows or off-peak hours:

SQL Server Agent Job Example:

-- Create SQL Agent Job for nightly archiving
USE msdb
GO

EXEC dbo.sp_add_job
    @job_name = N'ExMeX Archiving - Nightly',
    @enabled = 1,
    @description = N'Executes ExMeX archiving framework to archive and delete data based on retention policies'
GO

EXEC dbo.sp_add_jobstep
    @job_name = N'ExMeX Archiving - Nightly',
    @step_name = N'Execute Archiving',
    @subsystem = N'TSQL',
    @database_name = N'YourDatabase',
    @command = N'EXEC MetadataZoneCore.uspArchivingExecute;',
    @retry_attempts = 3,
    @retry_interval = 15
GO

EXEC dbo.sp_add_schedule
    @schedule_name = N'Nightly at 2 AM',
    @freq_type = 4,  -- Daily
    @freq_interval = 1,
    @active_start_time = 020000  -- 02:00:00 AM
GO

EXEC dbo.sp_attach_schedule
    @job_name = N'ExMeX Archiving - Nightly',
    @schedule_name = N'Nightly at 2 AM'
GO

EXEC dbo.sp_add_jobserver
    @job_name = N'ExMeX Archiving - Nightly'
GO

Frequency Recommendations:

Data Volume	Frequency	Rationale
Low (< 10K rows/day)	Weekly	Minimal overhead, sufficient cleanup
Medium (10K-100K rows/day)	Daily	Balance between performance and timeliness
High (> 100K rows/day)	Multiple times daily	Prevent table bloat, maintain performance
Very High (> 1M rows/day)	Hourly or continuous	Critical for performance

Monitor Execution Performance

Track Archiving Metrics

Regularly review archiving logs to identify performance issues:

-- Daily archiving performance summary
SELECT
    CAST(InscriptionTimestamp AS DATE) AS ArchivingDate,
    COUNT(*) AS ExecutionCount,
    SUM(RowsRead) AS TotalRowsProcessed,
    SUM(RowsArchived) AS TotalRowsArchived,
    SUM(RowsDeletedPhysical) AS TotalRowsDeleted,
    AVG(DATEDIFF(SECOND, InscriptionTimestamp,
        LEAD(InscriptionTimestamp) OVER (ORDER BY InscriptionTimestamp))) AS AvgDurationSeconds,
    SUM(CASE WHEN ArchivingStatusNumber > 1 THEN 1 ELSE 0 END) AS ErrorCount
FROM MetadataZoneCore.ArchivingLogMessage
WHERE InscriptionTimestamp >= DATEADD(DAY, -30, GETDATE())
GROUP BY CAST(InscriptionTimestamp AS DATE)
ORDER BY ArchivingDate DESC

Set Up Alerts

Create alerts for archiving failures:

-- Alert on archiving errors
SELECT
    SourceSchemaName,
    SourceDataObject,
    InscriptionTimestamp,
    ArchivingStatusNumber,
    ErrorMessage
FROM MetadataZoneCore.ArchivingLogMessage
WHERE ArchivingStatusNumber > 1
  AND InscriptionTimestamp >= DATEADD(HOUR, -24, GETDATE())
ORDER BY InscriptionTimestamp DESC

Performance Optimization

Optimize Archive Table Storage

Implement Data Compression

Archive tables benefit significantly from compression:

-- Enable page compression on archive table
ALTER TABLE [MetadataZoneArchive].[ProcessStepVapourTrailArchive]
REBUILD WITH (DATA_COMPRESSION = PAGE)
GO

-- Monitor compression effectiveness
SELECT
    OBJECT_SCHEMA_NAME(p.object_id) AS SchemaName,
    OBJECT_NAME(p.object_id) AS TableName,
    p.data_compression_desc AS CompressionType,
    SUM(a.total_pages) * 8 / 1024 AS TotalSizeMB,
    SUM(a.used_pages) * 8 / 1024 AS UsedSizeMB
FROM sys.partitions p
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE OBJECT_SCHEMA_NAME(p.object_id) LIKE '%Archive'
GROUP BY p.object_id, p.data_compression_desc
ORDER BY TotalSizeMB DESC

Partitioning for Large Archives

For very large archive tables, consider partitioning by date:

-- Example: Create partition function for yearly partitions
CREATE PARTITION FUNCTION pf_ArchiveByYear (DATETIME2)
AS RANGE RIGHT FOR VALUES
    ('2020-01-01', '2021-01-01', '2022-01-01',
     '2023-01-01', '2024-01-01', '2025-01-01')
GO

-- Apply to archive table (requires table recreation)
-- Note: This is an advanced topic - consult SQL Server documentation

Manage Statistics and Indexes

Update Statistics Regularly

Keep statistics current for optimal query performance:

-- Update statistics on archive tables after archiving
UPDATE STATISTICS [MetadataZoneArchive].[ProcessStepVapourTrailArchive]
    WITH FULLSCAN
GO

-- Automated statistics update job
CREATE PROCEDURE MetadataZoneCore.uspUpdateArchiveStatistics
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX)
    DECLARE @table NVARCHAR(256)

    DECLARE table_cursor CURSOR FOR
        SELECT QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
        FROM sys.tables t
        INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
        WHERE s.name LIKE '%Archive'

    OPEN table_cursor
    FETCH NEXT FROM table_cursor INTO @table

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = 'UPDATE STATISTICS ' + @table + ' WITH FULLSCAN'
        EXEC sp_executesql @sql
        PRINT 'Updated statistics for ' + @table

        FETCH NEXT FROM table_cursor INTO @table
    END

    CLOSE table_cursor
    DEALLOCATE table_cursor
END
GO

Batch Size Considerations

For tables with millions of rows, consider breaking archiving into smaller batches:

-- For custom archiving logic with batch control
-- (Note: ExMeX Framework handles this internally,
-- but you can control via scheduling frequency)

-- Instead of: One daily execution archiving 1M rows
-- Better: Four executions every 6 hours archiving 250K rows each

Monitoring and Maintenance

Regular Health Checks

Weekly Archiving Health Check

Create a comprehensive health check query:

-- Archiving Framework Health Check
SELECT
    'Data Objects Registered' AS Metric,
    COUNT(*) AS Value
FROM MetadataZoneCore.ArchivingDataObject

UNION ALL

SELECT
    'Active Data Objects',
    COUNT(*)
FROM MetadataZoneCore.ArchivingDataObject
WHERE IsActive = 1

UNION ALL

SELECT
    'Executions Last 7 Days',
    COUNT(DISTINCT CAST(InscriptionTimestamp AS DATE))
FROM MetadataZoneCore.ArchivingLogMessage
WHERE InscriptionTimestamp >= DATEADD(DAY, -7, GETDATE())

UNION ALL

SELECT
    'Errors Last 7 Days',
    COUNT(*)
FROM MetadataZoneCore.ArchivingLogMessage
WHERE InscriptionTimestamp >= DATEADD(DAY, -7, GETDATE())
  AND ArchivingStatusNumber > 1

UNION ALL

SELECT
    'Total Rows Archived Last 7 Days',
    SUM(RowsArchived)
FROM MetadataZoneCore.ArchivingLogMessage
WHERE InscriptionTimestamp >= DATEADD(DAY, -7, GETDATE())

UNION ALL

SELECT
    'Total Rows Deleted Last 7 Days',
    SUM(RowsDeletedPhysical)
FROM MetadataZoneCore.ArchivingLogMessage
WHERE InscriptionTimestamp >= DATEADD(DAY, -7, GETDATE())

Monitor Archive Table Growth

Track Archive Size Over Time

-- Archive table size monitoring
SELECT
    s.name AS SchemaName,
    t.name AS TableName,
    SUM(p.rows) AS [RowCount],
    SUM(a.total_pages) * 8 / 1024 AS TotalSizeMB,
    SUM(a.used_pages) * 8 / 1024 AS UsedSizeMB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024 AS UnusedSizeMB
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
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 s.name LIKE '%Archive'
  AND i.index_id <= 1  -- Clustered index or heap only
GROUP BY s.name, t.name
ORDER BY TotalSizeMB DESC

Troubleshooting Common Issues

Issue: Archiving Takes Too Long

Solutions:

1. Check for missing indexes on archiving columns
2. Review execution order - are dependent tables blocking?
3. Consider more frequent executions with smaller batches
4. Check for database blocking/locking issues during execution

-- Identify slow archiving operations
SELECT TOP 10
    SourceSchemaName + '.' + SourceDataObject AS DataObject,
    RowsRead,
    RowsArchived,
    InscriptionTimestamp,
    DATEDIFF(SECOND, InscriptionTimestamp,
    LEAD(InscriptionTimestamp) OVER (PARTITION BY SourceDataObject ORDER BY InscriptionTimestamp)) AS DurationSeconds
FROM MetadataZoneCore.ArchivingLogMessage
WHERE InscriptionTimestamp >= DATEADD(DAY, -7, GETDATE())
  AND ArchivingStatusNumber = 0
ORDER BY DurationSeconds DESC

Issue: Archive Tables Growing Too Large

Solutions:

1. Review ArchivingRetentionPeriod - may be too long
2. Verify physical deletion is occurring (check RowsDeletedPhysical)
3. Implement partitioning for very large archives
4. Consider moving old archives to separate filegroups/drives

Issue: Data Not Being Archived

Checklist:

-- Diagnostic query
SELECT
    ado.SourceSchemaName,
    ado.SourceDataObject,
    ado.IsActive,
    ado.ArchivingColumnCode,
    ado.RetentionPeriod,
    -- Check if index exists
    CASE WHEN EXISTS (
        SELECT 1 FROM sys.indexes i
        INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
        INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
        WHERE i.object_id = OBJECT_ID(ado.SourceSchemaName + '.' + ado.SourceDataObject)
          AND c.name = ado.ArchivingColumnCode
    ) THEN 'Yes' ELSE 'No' END AS IndexExists,
    -- Check last execution
    (SELECT MAX(InscriptionTimestamp)
     FROM MetadataZoneCore.ArchivingLogMessage alm
     WHERE alm.SourceDataObject = ado.SourceDataObject) AS LastExecution
FROM MetadataZoneCore.ArchivingDataObject ado
WHERE ado.IsActive = 1

Documentation and Governance

Document Your Archiving Configuration

Maintain a Archiving Registry

Create documentation for each archived data object:

-- Generate archiving configuration report
SELECT
    SourceSchemaName + '.' + SourceDataObject AS DataObject,
    ArchivingColumnCode,
    RetentionPeriod AS [Active Days],
    ArchivingRetentionPeriod AS [Archive Days],
    RetentionPeriod + ArchivingRetentionPeriod AS [Total Days],
    CASE IsActive WHEN 1 THEN 'Yes' ELSE 'No' END AS Active,
    ArchivingExecutionOrder AS ExecutionOrder,
    Comment
FROM MetadataZoneCore.ArchivingDataObject
ORDER BY ArchivingExecutionOrder, SourceSchemaName, SourceDataObject

Export this regularly to a documentation repository.

Change Management Process

Always Follow These Steps When Modifying Archiving Configuration:

1. Document the reason for the change in @Comment parameter
2. Test in non-production environment first
3. Monitor impact for at least one full archiving cycle
4. Keep audit trail of all changes

-- Good practice: Detailed comment on changes
EXEC MetadataZoneCore.uspSetArchivingDataObject
     @SetArchivingExec          = N'U',
     @SourceSchemaName          = N'MetadataZone',
     @SourceDataObject          = N'ProcessStepVapourTrail',
     @RetentionPeriod           = 14,  -- Changed from 30
     @Comment                   = N'Reduced from 30 to 14 days per ticket #12345.
                                   Business confirmed older data rarely accessed.
                                   Changed by: J.Smith, Date: 2025-10-15'
GO

Security and Compliance

Implement Access Controls

Restrict Archive Data Access

-- Create role for archive data access
CREATE ROLE ArchiveDataReader
GO

-- Grant read-only access to archive schemas
GRANT SELECT ON SCHEMA::MetadataZoneArchive TO ArchiveDataReader
GO

-- Add users who need archive access
ALTER ROLE ArchiveDataReader ADD MEMBER [Domain\ArchiveAuditor]
GO

Audit Archiving Activities

Log Configuration Changes

First, create an audit table to track all changes to archiving configuration:

-- Create audit table for archiving configuration changes
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'ArchivingConfigurationAudit' AND schema_id = SCHEMA_ID('MetadataZoneCore'))
BEGIN
    CREATE TABLE MetadataZoneCore.ArchivingConfigurationAudit (
        AuditID BIGINT IDENTITY(1,1) NOT NULL,
        ChangeType NVARCHAR(10) NOT NULL,
        SourceDatabaseName NVARCHAR(128) NULL,
        SourceSchemaName NVARCHAR(128) NULL,
        SourceDataObject NVARCHAR(128) NULL,
        ChangedBy NVARCHAR(128) NOT NULL,
        ChangedAt DATETIME2 NOT NULL,
        ChangeDetails NVARCHAR(MAX) NULL,
        OldRetentionPeriod INT NULL,
        NewRetentionPeriod INT NULL,
        OldArchivingRetentionPeriod INT NULL,
        NewArchivingRetentionPeriod INT NULL,
        OldIsActive BIT NULL,
        NewIsActive BIT NULL,
        OldArchivingExecutionOrder INT NULL,
        NewArchivingExecutionOrder INT NULL,
        CONSTRAINT PK_ArchivingConfigurationAudit PRIMARY KEY CLUSTERED (AuditID)
    ) WITH (DATA_COMPRESSION = PAGE)

    CREATE NONCLUSTERED INDEX IX_ArchivingConfigurationAudit_ChangedAt
        ON MetadataZoneCore.ArchivingConfigurationAudit (ChangedAt DESC)
        WITH (DATA_COMPRESSION = PAGE)

    CREATE NONCLUSTERED INDEX IX_ArchivingConfigurationAudit_SourceDataObject
        ON MetadataZoneCore.ArchivingConfigurationAudit (SourceDataObject)
        INCLUDE (ChangeType, ChangedAt, ChangedBy)
        WITH (DATA_COMPRESSION = PAGE)

    PRINT 'Audit table MetadataZoneCore.ArchivingConfigurationAudit created successfully.'
END
ELSE
    PRINT 'Audit table MetadataZoneCore.ArchivingConfigurationAudit already exists.'
GO

Then create a trigger to automatically audit changes to archiving configuration:

-- Audit trigger for archiving configuration changes
CREATE TRIGGER trg_ArchivingDataObject_Audit
ON MetadataZoneCore.ArchivingDataObject
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @ChangeType NVARCHAR(10)

    -- Determine change type
    IF EXISTS (SELECT 1 FROM deleted) AND EXISTS (SELECT 1 FROM inserted)
        SET @ChangeType = 'UPDATE'
    ELSE IF EXISTS (SELECT 1 FROM inserted)
        SET @ChangeType = 'INSERT'
    ELSE
        SET @ChangeType = 'DELETE'

    -- Log the change with detailed information
    INSERT INTO MetadataZoneCore.ArchivingConfigurationAudit (
        ChangeType,
        SourceDatabaseName,
        SourceSchemaName,
        SourceDataObject,
        ChangedBy,
        ChangedAt,
        ChangeDetails,
        OldRetentionPeriod,
        NewRetentionPeriod,
        OldArchivingRetentionPeriod,
        NewArchivingRetentionPeriod,
        OldIsActive,
        NewIsActive,
        OldArchivingExecutionOrder,
        NewArchivingExecutionOrder
    )
    SELECT
        @ChangeType,
        COALESCE(i.SourceDatabaseName, d.SourceDatabaseName),
        COALESCE(i.SourceSchemaName, d.SourceSchemaName),
        COALESCE(i.SourceDataObject, d.SourceDataObject),
        SYSTEM_USER,
        SYSUTCDATETIME(),
        CASE @ChangeType
            WHEN 'INSERT' THEN 'New archiving configuration created'
            WHEN 'DELETE' THEN 'Archiving configuration removed'
            WHEN 'UPDATE' THEN
                CASE
                    WHEN d.RetentionPeriod <> i.RetentionPeriod THEN 'RetentionPeriod changed from ' + CAST(d.RetentionPeriod AS NVARCHAR(10)) + ' to ' + CAST(i.RetentionPeriod AS NVARCHAR(10))
                    WHEN d.ArchivingRetentionPeriod <> i.ArchivingRetentionPeriod THEN 'ArchivingRetentionPeriod changed'
                    WHEN d.IsActive <> i.IsActive THEN 'IsActive changed from ' + CAST(d.IsActive AS NVARCHAR(1)) + ' to ' + CAST(i.IsActive AS NVARCHAR(1))
                    WHEN d.ArchivingExecutionOrder <> i.ArchivingExecutionOrder THEN 'ArchivingExecutionOrder changed'
                    ELSE 'Configuration updated'
                END
        END,
        d.RetentionPeriod,
        i.RetentionPeriod,
        d.ArchivingRetentionPeriod,
        i.ArchivingRetentionPeriod,
        d.IsActive,
        i.IsActive,
        d.ArchivingExecutionOrder,
        i.ArchivingExecutionOrder
    FROM inserted i
    FULL OUTER JOIN deleted d ON i.SourceDataObject = d.SourceDataObject
END
GO

PRINT 'Audit trigger created successfully.'
GO

Query Audit History

View all configuration changes:

-- View audit history
SELECT
    AuditID,
    ChangeType,
    SourceSchemaName + '.' + SourceDataObject AS DataObject,
    ChangedBy,
    ChangedAt,
    ChangeDetails,
    CASE
        WHEN OldRetentionPeriod IS NOT NULL
        THEN CAST(OldRetentionPeriod AS NVARCHAR(10)) + ' → ' + CAST(NewRetentionPeriod AS NVARCHAR(10))
        ELSE NULL
    END AS RetentionPeriodChange,
    CASE
        WHEN OldArchivingRetentionPeriod IS NOT NULL
        THEN CAST(OldArchivingRetentionPeriod AS NVARCHAR(10)) + ' → ' + CAST(NewArchivingRetentionPeriod AS NVARCHAR(10))
        ELSE NULL
    END AS ArchivingRetentionPeriodChange,
    CASE
        WHEN OldIsActive IS NOT NULL
        THEN CAST(OldIsActive AS NVARCHAR(1)) + ' → ' + CAST(NewIsActive AS NVARCHAR(1))
        ELSE NULL
    END AS IsActiveChange
FROM MetadataZoneCore.ArchivingConfigurationAudit
ORDER BY ChangedAt DESC

Compliance Reporting

Generate Retention Compliance Report

-- Data retention compliance report
SELECT
    ado.SourceSchemaName + '.' + ado.SourceDataObject AS DataObject,
    ado.RetentionPeriod + ado.ArchivingRetentionPeriod AS TotalRetentionDays,
    COUNT(alm.ArchivingLogMessageID) AS ArchivingExecutions,
    SUM(alm.RowsArchived) AS TotalRowsArchived,
    SUM(alm.RowsDeletedPhysical) AS TotalRowsDeleted,
    MAX(alm.InscriptionTimestamp) AS LastArchiving,
    CASE
        WHEN MAX(alm.InscriptionTimestamp) < DATEADD(DAY, -7, GETDATE())
        THEN 'WARNING: No recent archiving'
        ELSE 'OK'
    END AS ComplianceStatus
FROM MetadataZoneCore.ArchivingDataObject ado
LEFT JOIN MetadataZoneCore.ArchivingLogMessage alm
    ON ado.SourceDataObject = alm.SourceDataObject
WHERE ado.IsActive = 1
GROUP BY ado.SourceSchemaName, ado.SourceDataObject,
         ado.RetentionPeriod, ado.ArchivingRetentionPeriod
ORDER BY ComplianceStatus DESC, DataObject

Testing and Validation

Pre-Production Testing

Test Archiving Configuration Before Production

1. Create test copy of production data

-- Create test copy
SELECT TOP 100000 *
INTO Test_ProcessStepVapourTrail
FROM MetadataZone.ProcessStepVapourTrail

2. Register test object with aggressive retention

EXEC MetadataZoneCore.uspSetArchivingDataObject
     @SetArchivingExec     = N'A',
     @SourceSchemaName     = N'dbo',
     @SourceDataObject     = N'Test_ProcessStepVapourTrail',
     @ArchivingColumnCode  = N'ProcessEndDateTime',
     @IsActive             = 1,
     @RetentionPeriod      = 1,  -- Archive after 1 day for testing
     @Comment              = N'Test configuration'
GO

3. Execute and validate

-- Execute archiving
EXEC MetadataZoneCore.uspArchivingExecute
GO

-- Validate results
SELECT * FROM MetadataZoneCore.ArchivingLogMessage
WHERE SourceDataObject = 'Test_ProcessStepVapourTrail'
ORDER BY InscriptionTimestamp DESC

4. Clean up test objects

EXEC MetadataZoneCore.uspSetArchivingDataObject
     @SetArchivingExec = N'D',
     @SourceSchemaName = N'dbo',
     @SourceDataObject = N'Test_ProcessStepVapourTrail'
GO

DROP TABLE Test_ProcessStepVapourTrail
DROP TABLE dboArchive.Test_ProcessStepVapourTrailArchive

Validation Queries

Verify Archiving Is Working Correctly

-- Compare source and archive record counts
SELECT
    'Source' AS Location,
    COUNT(*) AS RecordCount,
    MIN(ProcessEndDateTime) AS OldestRecord,
    MAX(ProcessEndDateTime) AS NewestRecord
FROM MetadataZone.ProcessStepVapourTrail

UNION ALL

SELECT
    'Archive',
    COUNT(*),
    MIN(ProcessEndDateTime),
    MAX(ProcessEndDateTime)
FROM MetadataZoneArchive.ProcessStepVapourTrailArchive

Summary Checklist

Before implementing archiving for a new data object, verify:

• Archive schema exists
• Archiving column contains valid date data
• Index exists on archiving column
• Retention periods align with business/compliance requirements
• Execution order configured for dependent tables
• Testing completed in non-production environment
• Monitoring and alerts configured
• Documentation updated
• Access controls reviewed
• Initial execution scheduled during maintenance window

See Also

• Archiving Framework Overview
• uspSetArchivingDataObject
• uspArchivingExecute
• uspConfigExMeXFramework