April 14, 2011 at 8:11 am
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
April 14, 2011 at 2:24 pm
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
July 19, 2012 at 5:34 am
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
July 19, 2012 at 7:33 am
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