Help me to start CDC for Standard edition

Nov 24, 2012 at 9:07 AM

I am new to this Standard CDC. 

could you provide a user guide or steps to configure and start CDC in standard edition.

Thanks in advance.

Coordinator
Nov 24, 2012 at 12:24 PM

The steps to implement are the same as the actual CDC feature in SQL Server.  For this particular project you will need to create the split array function first, or update the code to leverage an existing split stored procedure or function.

High-level steps:

  1. Execute ImplementSplitArray.sql
  2. Execute sp_StandardCDC_cleanup_table.sql
  3. Execute sp_StandardCDC_enable_db.sql
  4. Execute sp_StandardCDC_enable_table.sql

The stored procedures are well commented, so you review first to get a full understanding of what you are enabling.

Nov 27, 2012 at 3:57 AM

Thank you so much. now i am able to audit for a table.

I have one more doubt on this.

Ex:
I have a employee table and designation as follows 
A,B,C,D.

i want to track only designation of A,B alone. is there any way to do audit.

whether i need to change " sp_StandardCDC_enable_table " or any other conditions i have to include while executing " EXEC sp_StandardCDC_enable_table ".

could you plz suggest me.

Thanks in Advance.

Nov 27, 2012 at 5:17 PM

HI there,

If I understand you correctly, I have done this as I have common auditing columns on most users tables to track other data. As these columns all have the same name between tables eg createdBy, dateCreated, I can hard-code the SCDC enable table proc to ignore them when creating the SCDC trigger. This means no additional run-time overhead 

If this isn't a good fit, if your column names you wish to exclude vary between one table to another for instance, another way would be to add a bit column to the SCDC table that tracks the audited table schemas and amend the trigger code to filter on that, which means another lookup overhead.

I could drop you an example when I get back to work but my project has deviated from SCDC here and won't drop in and work. Whitney has done a great job writing and sharing very easy to understand procs and structures and it's worth the personal effort to understand them. One day, there will come a time when your audited table schemas will change and your SCDC trigger may break but you'll want to preserve the audit history to date; given that the database is probably live that's not a time to start learning, that's when you'll need to know what you're doing. 

Regards

Derek

Dec 5, 2012 at 7:56 AM
Edited Dec 5, 2012 at 8:03 AM

Dear Derek,

sorry for late reply. i have enabled the CDC for standard Edition. I can enable only for few tables.

when i enable CDC for some tables i am getting following error msg.

I have enabled as follows:

EXEC sp_StandardCDC_enable_table
@SourceSchema = N'dbo',
@SourceName   = N'employee', 
@RoleName     = NULL ,
@CapturedColumnList ='empcode,empname,createduser,crdate,updateduser,update,dept', 
@FileGroupName='primary', 
@CaptureUser=''

Error msg:

Msg 50000, Level 16, State 3, Procedure sp_StandardCDC_enable_table, Line 364
Error on line 1: Column names in each table must be unique. Column name 'empcode' in table 'employee' is specified more than once.

After analyzing sp_standardCDC_enable_table

i am getting duplicate columns ("empcode) when i run following query



 SELECT distinct TQ.columName  
     ,CASE 
      WHEN TQ.dataTypeName = 'datetimeoffset' THEN TQ.dataTypeName + '(' + CONVERT(varchar(10), TQ.scale) + ')' 
      WHEN TQ.dataTypeName IN ('decimal', 'numeric') THEN TQ.dataTypeName + ' (' + CONVERT(varchar(10), TQ.[precision]) + ', ' + CONVERT(varchar(10), TQ.scale) + ')' 
      WHEN TQ.dataTypeName IN('nchar', 'nvarchar') AND TQ.max_length >= 2 THEN TQ.dataTypeName + '(' + CONVERT(varchar(10), TQ.max_length/2) + ')' 
      WHEN TQ.dataTypeName = 'varchar' AND TQ.max_length > 0 THEN TQ.dataTypeName + '(' + CONVERT(varchar(10), TQ.max_length) + ')' 
      WHEN TQ.dataTypeName IN('nvarchar', 'varchar', 'varbinary') AND TQ.max_length = -1 THEN TQ.dataTypeName + '(max)' 
      ELSE TQ.dataTypeName 
     END AS dataType 
     ,TQ.nullSpecification 
     ,TQ.column_id 
     ,ROW_NUMBER() OVER (ORDER BY TQ.column_id ASC) 
     ,TQ.isPrimaryKey 
    FROM( 
    SELECT c.name AS columName 
     ,t.name AS dataTypeName 
     ,c.max_length 
     ,c.[precision] 
     ,c.scale 
     ,CASE  
      WHEN c.is_nullable = 0 THEN 'NOT NULL' 
      ELSE 'NULL' 
     END AS nullSpecification 
     ,c.column_id 
     ,CASE 
      WHEN c.column_id = ic.column_id THEN 1 
      ELSE 0 
     END AS isPrimaryKey      
    FROM sys.columns AS c 
     INNER JOIN sys.objects AS o ON o.[object_id] = c.[object_id] 
     INNER JOIN sys.types AS t ON t.system_type_id = c.system_type_id 
     LEFT JOIN sys.indexes AS i ON i.[object_id] = c.[object_id]  
     left JOIN sys.index_columns AS ic ON ic.[object_id] = c.[object_id]  
      AND ic.column_id = c.column_id      
      AND ic.index_id = i.index_id 
      AND i.is_primary_key = 1 
    WHERE o.name = 'employee' 
     AND SCHEMA_NAME(o.[schema_id]) = 'dbo' 
     AND t.name NOT IN('sysname', 'timestamp') 
     AND (c.name IN(SELECT cc.Element FROM utility.SplitArray('empcode,empname,createduser,crdate,updateduser,update,dept', ',') AS cc) OR 'empcode,empname,createduser,crdate,updateduser,update,dept ' IS NULL) 

     )AS TQ 
    ORDER BY TQ.column_id;

output as follows:

columName dataType nullSpecification column_id (No column name) isPrimaryKey
empcode varchar(17) NOT NULL 1 1 1
empcode varchar(17) NOT NULL 1 2 0
empcode varchar(17) NOT NULL 1 3 0
empcode varchar(17) NOT NULL 1 4 0
empcode varchar(17) NOT NULL 1 5 0
empcode varchar(17) NOT NULL 1 6 0
empcode varchar(17) NOT NULL 1 7 0
empcode varchar(17) NOT NULL 1 8 0
empcode varchar(17) NOT NULL 1 9 0


kindly suggest me to enable CDC for employee table.

thanks in advance.

Regards
Mahendiran

Dec 5, 2012 at 6:42 PM

Hi Mahendiran

I've not seen this particular error before but then I don't use the splitArray function. It seemed very clunky to me so I posted a simple alternative quite a while ago but it doesn't seem to have been taken up.

Please see http://standardeditioncdc.codeplex.com/discussions/270380

I advise implementing the code change mentioned in 270380 above and trying again (backup the proc first).  

for instance;

 

declare @CapturedColumnList varchar(max) ='empcode,empname,createduser,crdate,updateduser,update,dept'


 SELECT distinct TQ.columName   
     ,CASE  
      WHEN TQ.dataTypeName = 'datetimeoffset' THEN TQ.dataTypeName + '(' + CONVERT(varchar(10), TQ.scale) + ')'  
      WHEN TQ.dataTypeName IN ('decimal', 'numeric') THEN TQ.dataTypeName + ' (' + CONVERT(varchar(10), TQ.[precision]) + ', ' + CONVERT(varchar(10), TQ.scale) + ')'  
      WHEN TQ.dataTypeName IN('nchar', 'nvarchar') AND TQ.max_length >= 2 THEN TQ.dataTypeName + '(' + CONVERT(varchar(10), TQ.max_length/2) + ')'  
      WHEN TQ.dataTypeName = 'varchar' AND TQ.max_length > 0 THEN TQ.dataTypeName + '(' + CONVERT(varchar(10), TQ.max_length) + ')'  
      WHEN TQ.dataTypeName IN('nvarchar', 'varchar', 'varbinary') AND TQ.max_length = -1 THEN TQ.dataTypeName + '(max)'  
      ELSE TQ.dataTypeName  
     END AS dataType  
     ,TQ.nullSpecification  
     ,TQ.column_id  
     ,ROW_NUMBER() OVER (ORDER BY TQ.column_id ASC)  
     ,TQ.isPrimaryKey  
    FROM(  
    SELECT c.name AS columName  
     ,t.name AS dataTypeName  
     ,c.max_length  
     ,c.[precision]  
     ,c.scale  
     ,CASE   
      WHEN c.is_nullable = 0 THEN 'NOT NULL'  
      ELSE 'NULL'  
     END AS nullSpecification  
     ,c.column_id  
     ,CASE  
      WHEN c.column_id = ic.column_id THEN 1  
      ELSE 0  
     END AS isPrimaryKey       
    FROM sys.columns AS c  
     INNER JOIN sys.objects AS o ON o.[object_id] = c.[object_id]  
     INNER JOIN sys.types AS t ON t.system_type_id = c.system_type_id  
     LEFT JOIN sys.indexes AS i ON i.[object_id] = c.[object_id]   
     left JOIN sys.index_columns AS ic ON ic.[object_id] = c.[object_id]   
      AND ic.column_id = c.column_id       
      AND ic.index_id = i.index_id  
      AND i.is_primary_key = 1  
    WHERE o.name = 'employee'  
     AND SCHEMA_NAME(o.[schema_id]) = 'dbo'  
     AND t.name NOT IN('sysname', 'timestamp')  
     AND (CHARINDEX(','+c.name, ','+@CapturedColumnList) > 0  OR @CapturedColumnList IS NULL)  

     )AS TQ  
    ORDER BY TQ.column_id;

will give the output you're after.

I hope this helps.

Regards

Derek

Dec 6, 2012 at 2:22 PM
Edited Dec 6, 2012 at 2:23 PM

Hi Derek
Thank you very much for this CDC approach.

I had the same problem as Mahendiran's.
I tried the solution you mentioned above but didn't worked.
I analysed the query referenced by Mahendiran at sp_standardCDC_enable_table and I found that there was a problem when the table that the sp was running for, had more than one indexes (including pk's clustered index). It was multiplying the columns with the indexes. 
The problem was at the last two left outer joins.  
The condition AND i.is_primary_key = 1  had to be at the sys.indexes join instead of sys.index_columns.
I changed this and worked as expected.

   LEFT JOIN sys.indexes AS i ON i.[object_id] = c.[object_id] AND i.is_primary_key = 1  
     left JOIN sys.index_columns AS ic ON ic.[object_id] = c.[object_id]   
      AND ic.column_id = c.column_id       
      AND ic.index_id = i.index_id  
      AND i.is_primary_key = 1  
    WHERE o.name = 'employee' 


Regards

Pantelis

Dec 6, 2012 at 6:16 PM

Hi Pantelis

When a implemented the employee table I made the empcode col the primary key, after all it's only polite and good design to have one and SCDC will complain if it's not there. Seemed to work fine. Given that the first join should probably be an inner join. I've never had problem with other indexes or FKs on tables but I diverged from the project some time ago.

Regards

Derek

ps it will be some time before I can respond again. My project has just been canned and, as a contractor, that means I'm instantly disposable.

Dec 7, 2012 at 6:38 AM

Dear All,

I have done changes in sp_standardCDC_enable_table as per Pantelis advise and it is working fine.

again i have a problem while enabling Employee table.

if datatype is char then mirrortable doesnt have same char length.

Ex: if empname is char(20) then in mirrortable its creating as char(1).

for time being i have altered size of empname as char(20) of mirror table.

whether i need alter mirror table if it has char datatype whenever i am enabling a table.

kindly suggest me to move further.

 

Regards,

Mahendiran

Dec 7, 2012 at 9:15 AM

Hi Mahendiran

char(1) sounds like it's missing the length specification somehow as char = char(1)

eg

declare @c char ='123'

select @c

would return '1' but it's hard to help you directly without knowing what you've changed. You should try and post as much useful data as you can and whatever table create statement that would save time for those that wish to help but are pressed for time, aren't we all?

Regards

Derek

Dec 10, 2012 at 9:35 PM

Hi,

Sorry about my late reply, but I was too busy at weekend.

I'm glad to hear that Mahendiran's 1st problem is OK.

As for the last one (CHAR datatype), the problem is that  sp_standardCDC_enable_table doesn't handle CHAR datatypes.
To do so, change the line for VARCHAR datatype from

WHEN TQ.dataTypeName = 'varchar' AND TQ.max_length > 0 THEN TQ.dataTypeName + '(' + CONVERT(varchar(10), TQ.max_length) + ')'  

to

WHEN TQ.dataTypeName IN ('char', 'varchar') AND TQ.max_length > 0 THEN TQ.dataTypeName + '(' + CONVERT(varchar(10), TQ.max_length) + ')'  

Important: Do not forget to change the same line at the GROUP part of the same Select statement, or an exception will be thrown.

After that, CHAR datatypes will be created with the same size as at parent table.

Hope this helps

Pantelis