SQL Server Partitioned Tables - how to sync table definitions?

Question

Our SQL Server data warehouse makes extensive use of partition switching, and many tables have peer tables used to switch in and out various partitions.

These "staging" tables have to be perfect replicas of the main table - i.e. exactly the same columns, the same constraints and the same indices.

Is there a smart way to keep the staging tables in sync with the primary table? e.g. do you know of any DDL triggers that would replicate DDL statements against the primary table to the staging tables, or some other simple way of keeping all the tables in sync?

Answer 1

I've ended up writing the following DDL trigger, which pretty much implements the solution I was looking for:

CREATE TABLE [Helpers].[LoadingTables](
    [SchemaName] [sysname] NOT NULL,
    [TableName] [sysname] NOT NULL,
    [LoadingSchemaName] [sysname] NOT NULL,
    [LoadingTableName] [sysname] NOT NULL,
    [SkipPrimaryKey] [bit] NOT NULL,
    [SkipIndexes] [bit] NOT NULL,
    [SkipConstraints] [bit] NOT NULL,
    [MaintainMergeOnInsertTrigger] [bit] NOT NULL,
    [MaintainArchiveOnDeleteTrigger] [bit] NOT NULL,
 CONSTRAINT [PK_LoadingTables] PRIMARY KEY CLUSTERED 
(
    [SchemaName] ASC,
    [TableName] ASC,
    [LoadingSchemaName] ASC,
    [LoadingTableName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) 



SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'SwitchTableSynchronizer')
BEGIN
    DISABLE TRIGGER [SwitchTableSynchronizer] ON DATABASE;
    DROP TRIGGER [SwitchTableSynchronizer] ON DATABASE;
END 

GO

CREATE TRIGGER [SwitchTableSynchronizer]
ON DATABASE 
FOR DDL_TABLE_EVENTS, DDL_INDEX_EVENTS
AS 
    -- Common properties
    DECLARE @EventType  varchar(100) = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)');
    DECLARE @ObjectType varchar(100) = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)');
    DECLARE @SchemaName varchar(100) = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)');
    DECLARE @CommandText varchar(max) = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)');
    DECLARE @TableName varchar(100), 
            @LoadingSchemaName sysname, 
            @LoadingTableName sysname,
            @LoadingFullTableName varchar(max),
            @SkipPrimaryKey bit,
            @SkipIndexes bit,
            @SkipConstraints bit,   
            @MaintainMergeOnInsertTrigger bit,
            @MaintainArchiveOnDeleteTrigger bit,                    
            @SQL varchar(max);

    IF @ObjectType = 'INDEX' 
    BEGIN
        DECLARE @IndexName varchar(100) = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)');
        SET @TableName = EVENTDATA().value('(/EVENT_INSTANCE/TargetObjectName)[1]','nvarchar(max)');
    END ELSE BEGIN
        SET @TableName  = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)');
    END

    IF OBJECT_ID('Helpers.LoadingTables') IS NULL RETURN;
    IF NOT EXISTS (SELECT * FROM Helpers.LoadingTables WHERE SchemaName = @SchemaName   AND TableName = @TableName) RETURN;

    DECLARE @FullTableName varchar(max) = '[' + @SchemaName + '].[' + @TableName + ']';

    IF NULLIF(CHARINDEX(@FullTableName, @CommandText), 0) IS NULL
        SET @FullTableName = @SchemaName + '.' + @TableName;

    IF NULLIF(CHARINDEX(@FullTableName, @CommandText), 0) IS NULL
    BEGIN
        DECLARE @msg varchar(max) = @TableName + ' participates in partition switching. Use a schema qualified name when altering this table or int''s indexes.';
        RAISERROR (@msg,18,0);
        IF @@TRANCOUNT > 0 ROLLBACK;
        RETURN;               
    END

    DECLARE LoadersCursor CURSOR FOR 
            SELECT LoadingSchemaName, LoadingTableName, SkipPrimaryKey, SkipIndexes, SkipConstraints, MaintainMergeOnInsertTrigger, MaintainArchiveOnDeleteTrigger
            FROM Helpers.LoadingTables                                                                
            WHERE SchemaName = @SchemaName             
            AND TableName = @TableName;

    OPEN LoadersCursor;
    FETCH NEXT FROM LoadersCursor INTO @LoadingSchemaName, @LoadingTableName, @SkipPrimaryKey, @SkipIndexes, @SkipConstraints,
                                        @MaintainMergeOnInsertTrigger, @MaintainArchiveOnDeleteTrigger;


    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @LoadingFullTableName = @LoadingSchemaName + '.' + @LoadingTableName
        SET @SQL = REPLACE(@CommandText, @FullTableName, @LoadingFullTableName);

        IF @EventType LIKE 'CREATE_TABLE'
        BEGIN
            SET @SQL = REPLACE(@SQL, @TableName + ' PRIMARY KEY', @LoadingTableName + ' PRIMARY KEY');

            SET @SQL = @SQL + CHAR(13) 
            + 'EXEC sp_addextendedproperty @name = N''microsoft_database_tools_support'', @value = 1, @level0type = ''schema'', @level0name = N''' 
            + @LoadingSchemaName + ''', @level1type = ''table'', @level1name = N''' + @LoadingTableName + ''''; 
        END

        IF @EventType LIKE 'ALTER_TABLE'
        BEGIN
            DECLARE @ConstraintName varchar(max) = EVENTDATA().value('(/EVENT_INSTANCE/AlterTableActionList/*/Constraints/Name)[1]','nvarchar(max)');

            IF @ConstraintName IS NOT NULL
            BEGIN

                DECLARE @NewName varchar(max) = REPLACE(@ConstraintName, @TableName, @LoadingTableName);
                SET @SQL = REPLACE(@SQL, @ConstraintName, @NewName);

                IF @SQL LIKE '%PRIMARY KEY%' AND @SkipPrimaryKey = 1 SET @SQL = NULL;           
                IF @SQL NOT LIKE '%PRIMARY KEY%' AND @SkipConstraints = 1 SET @SQL = NULL;

                IF @SQL LIKE '%REFERENCES ' + @FullTableName + '%'
                BEGIN
                    DECLARE @msg2 varchar(max) = @TableName + ' participates in partition switching and may not participate in self-referencing constraints.';
                    RAISERROR (@msg2,18,0);
                    IF @@TRANCOUNT > 0 ROLLBACK;
                    RETURN;     
                END
            END;
        END

        IF @EventType LIKE '%INDEX' AND @SkipIndexes = 1 SET @SQL = NULL;       

        PRINT @CommandText;
        PRINT @SQL;

        IF @SQL IS NOT NULL AND @SQL <> @CommandText 
        BEGIN
            EXEC (@SQL);
        END 

        FETCH NEXT FROM LoadersCursor INTO @LoadingSchemaName, @LoadingTableName, @SkipPrimaryKey, @SkipIndexes, @SkipConstraints,
                                            @MaintainMergeOnInsertTrigger, @MaintainArchiveOnDeleteTrigger;
    END         

    CLOSE LoadersCursor;
    DEALLOCATE LoadersCursor;
GO