The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded
I like where Microsoft is going with Change Data Capture (CDC). In a data warehousing scenario where you only need what’s changed since the last load it’s a magical solution. There is a learning curve however. Here are four lessons we learned while implementing CDC.
Lesson One: There isn’t a way in CDC to track WHO made a change nor can you identify WHEN easily or quickly.
When I first started researching CDC I immediately thought of change auditing so I was surprised to find it missing. I figured with some application of clever sql judo I could still get what I wanted. Hence lesson one.
Answering the “who” half of the question proved impossible outside of triggers – ewwww, shutter – or programmatic approaches outside of CDC. Aaron Bertrand has submitted a bug about this on connect but it was closed “by design”.
You CAN find out WHEN a change occurred through use of the sys.fn_cdc_map_lsn_to_time() system function. This returns the DateTime associated with the change via it’s log sequence number . The __$start_lsn field is included in the change tracking table that CDC creates when you enable it. You use the system function like this:
SELECT sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS time_changed, additional_change_capture_fields FROM cdc.myCaptureInstance_CT WHERE primarykey = @PK;
If you know anything about scalar functions you know they don’t perform well, especially in large numbers. I played around with this hack that I documented on dba.stackexchange to find a faster way. Tread lightly with this approach, not only is the sequence or time not guaranteed accurate due to the way records are read from the log by CDC, but you’re messing with system tables – not usually a good idea.
Lesson Two: CDC can be restored from backup with KEEP_CDC but NOT with NORECOVERY.
Like the issue above, this one was closed as “won’t fix”. That little fact should raise some questions. What about point in time recovery? What about setting up a mirror? Robert Davis has expertly demonstrated that CDC and mirroring are compatible. But the issue we ran into was not setting up a mirrored server with CDC, it was moving the failover partner to a different server after CDC had already been setup. Attempting to apply a restore using both NORECOVERY and KEEP_CDC produces the following:
Ag 3031, Level 16, State 1, Line 1
Option ‘norecovery’ conflicts with option(s) ‘keep_cdc’.
Remove the conflicting option and reissue the statement.
In a normal log restore scenario the Robert Davis article points out that you wait to specify KEEP_CDC until the final restore, but when setting up a mirror failover partner that final step never occurs – the database remains in recovery. The workaround? According to MS documentation you don’t need to specify KEEP_CDC when restoring to a database that already has it.
The solution for us then was to first restore a full backup with KEEP_CDC. Then, restore the full backup again with NORECOVERY. We could then continue to apply logs and re-establish the mirror partner as normal. CDC is present from the first full recovery restore. You’ll still need to ensure that the agent jobs etc. are ready for failover but it is possible.
Lesson Three: Inevitably, when you start tracking changes for the data warehouse, they are going to want to know what columns were changed in each change record. Like LSN time mapping, Microsoft provides a column in the system change capture table called __$update_mask that’ll identify the changed columns. As long as you don’t mind reading varbinary that is.
We were able to get the column list in a much more user friendly format with the following query leveraging FOR XML – I find myself using the for xml path trick a lot.
SELECT __$update_mask, (SELECT CC.column_name + ',' FROM cdc.captured_columns AS CC INNER JOIN cdc.change_tables AS CT ON CC.[object_id] = CT.[object_id] WHERE capture_instance = 'dbo_OurTableName' AND sys.fn_cdc_is_bit_set(CC.column_ordinal, PD.__$update_mask) = 1 FOR XML PATH ('')) AS changedcolumns FROM cdc.dbo_MyTableName AS PD;
Also documented on dba.stackexchange is an approach we took using CLR but it wasn’t quite as fast as what I’m giving you above.
Lesson Four: After implementing CDC on several servers; one started suffering from marked memory pressure. After some poking around we discovered that the memory pressure was coming from the CLR being used by CDC. The major difference between it and the servers that were working fine? It was an older 32 bit server. We needed to adjust our memtoleave setting.
If you want an excellent overview of VAS or Virtual Address Space – Jonathan Kehayias wrote a thorough article on the topic. VAS – controlled in SQL Server by the memtoleave setting – is the memory SQL reserves on startup for operations outside of the SQL memory pool such as OLEDB, Extended Stored Procedures, CLR and others. On 32 bit servers the default memtoleave setting is 256mb. In our case we adjusted that number up via the -g startup parameter in Sql Server Configuration Manager. I didn’t just pick a number out of the air, I relied heavily on the memtoleave analysis queries provided by John Sansom on his blog. Know what you’re doing before you start messing with start up parameters.
In our initial instance we did not see any errors. Just memory pressure slowing everything else down. But on the failover mirror which we implemented later we did see the occasional error pointing directly at memtoleave.
Failed to initialize the Common Language Runtime (CLR) v2.0.50727 due to memory pressure. This is probably due to memory pressure in the MemToLeave region of memory.
Sansom points out a variety of other error messages in his blog that would also lead you to memtoleave.