Steps to be done for CDC enabled tables for DDL changes

  • Hi !

    Can some one shade light on what steps to be taken when there are structural changes in the CDC enabled tables . Do we need to disable the CDC on table and implement the change in structure(Add,delete,alter,data type change in columns) and then enable CDC?

    Thanks in advance

  • You have to use a trigger to monitor DDL changes on any table.

    I used the below trigger to monitor any changes to any tables, including the user who made the change, and the actual Change statement.

    CREATE TABLE [dbo].[tblMonitorChange](

    [EventType] [nvarchar](max) NULL,

    [SchemaName] [nvarchar](max) NULL,

    [ObjectName] [nvarchar](max) NULL,

    [ObjectType] [nvarchar](max) NULL,

    [EventDate] [datetime] NULL,

    [SystemUser] [varchar](100) NULL,

    [CurrentUser] [varchar](100) NULL,

    [OriginalUser] [varchar](100) NULL,

    [DatabaseName] [varchar](100) NULL,

    [tsqlcode] [nvarchar](max) NULL

    ) ON [PRIMARY]

    GO

    CREATE TRIGGER [trgMonitorChange] ON DATABASE

    --WITH ENCRYPTION

    FOR DDL_DATABASE_LEVEL_EVENTS

    AS

    set nocount on

    declare @EventType nvarchar(MAX)

    declare @SchemaName nvarchar(MAX)

    declare @ObjectName nvarchar(MAX)

    declare @ObjectType nvarchar(MAX)

    DECLARE @DBName VARCHAR(100)

    DECLARE @Message VARCHAR(1000)

    DECLARE @TSQL NVARCHAR(MAX)

    SELECT

    @EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)')

    ,@SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)')

    ,@ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')

    ,@ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)')

    ,@DBName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)')

    ,@TSQL = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

    -- Is the default schema used

    if @SchemaName = ' '

    SELECT @SchemaName = default_schema_name

    FROM sys.sysusers u

    INNER JOIN sys.database_principals p

    ON u.uid = p.principal_id

    WHERE u.[name] = CURRENT_USER

    insert into dbo.tblMonitorChange -- Change database name to whatever you are using to record changes

    select @EventType, @SchemaName, @ObjectName, @ObjectType, getdate(), SUSER_SNAME(), CURRENT_USER, ORIGINAL_LOGIN(), @DBName, @TSQL

    GO

    ENABLE TRIGGER [trgMonitorChange] ON DATABASE

    GO

    QUIGROUP- Need a Certified experienced DBA for a project or troubleshooting? Need help with programming, database recovery, performance tuning, ETL, SSRS or developing new databases? Contact us.. 1-786-273-9809

  • I dont think the trigger solution is what was asked for. ie (monitoring structual changes)

    It seemed to be more about the realease process for making structural changes to the DB when cdc is enabled on a table so that any data in the change tables is not lost.

    Im also interested in how to modify the db correctly by implementing a change script when the script may attempt to alter a table that is tracked.

    it is a perfectly normal practice to have a development database that contains structural changes that are tested through a uat process then need to be released to production with a change script.

    the question is what do you need to do in this change script to maintain the cdc.schema and not lose change history during the release ?

    Regards

    ColinR

  • The last couple of posts in this thread http://social.technet.microsoft.com/Forums/da-DK/sqldatawarehousing/thread/3fb03851-2de8-4bfe-9df8-2e7dfbbc60cc suggest how to retain historical changes after a schema change.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply