Once I had a dream, and an angel came through
All I could see, appeared so real, so true
She held my hands and took me around
I was lost, I was bound

I could feel her and so could I touch
She made me feel so good, so could she comfort
Holding hands on the beach
Talking under the moon whole night
Kissing and holding each other tight

Her voice would mesmerize
her words would summarize
the world of love, the horizon of togetherness
and I thought, this world would never capsize

Then there was a jolt and I woke up
I stretched my hands, searched and tried
she was not there and I was alone
gone was the dream and so was she

I could not accept I could not realize
She was gone and so was the dream
I cried and so did I scream
I wanted her, I wanted the dream
But thats not the fate, thats not destiny

For no matter the distance
Or the obstacles for instance
I will always belong to her
For we are meant for each other
not in reality, but at least in our Dreams

Then I think this is not the last time I am sleeping
This is not the last time I am dreaming
I will still sleep, I will still dream
I will still love, I will still gleam
No matter I loose, No matter I gain
I want to sleep & want to dream again

SQL Server 2008: Change Data Capture (CDC)

 

Another one of the new features within SQL Server 2008 is the Change Data Capture (CDC).  CDC is designed to capture insert, update and delete activity on a SQL table and place the information into a separate relational table.  It uses an asynchronous capture mechanism that reads the transaction logs and populates the CDC table with the row's data which change.  The CDC table mirrors the column structure of the tracked table, together with metadata regarding the change.  In later releases, there is also going to be a audit feature allowing you to audit who accesses what information and when, based more on select statements.

 

To enable CDC, the following two statements are required. Note, SQLServerAgent must be running for this to work.

 

use testDatabase

--Activate CDC
EXEC sys.sp_cdc_enable_db_change_data_capture

--IsDatabaseEnabled?
SELECT is_cdc_enabled FROM sys.databases WHERE name = 'testDatabase'

--Enable CDC on table
EXEC sys.sp_cdc_enable_table_change_data_capture @source_schema = 'dbo', @source_name = 'Table_1', @role_name = 'cdc_test'

--IsTableEnabled?
SELECT is_tracked_by_cdc FROM sys.tables WHERE name = 'table_1'

 

The sp_cdc_enable_table... statement enables the tracking of the changes for a table, parameters are required for setting the source schema, the source name which is the table you want to track, and the role name, if the role does not exist it will create it for you.

 

After executing these statements you will have a number of new tables within your database.  The tables are:

 

·         cdc.captured_columns - Information about the captured columns being tracked.

·         cdc.change_tables - Tables being tracked.

·         cdc.ddl_history - Changes to the tracked table, details the command issued on the table.

·         cdc.index_columns - Guessing this is tracked indexed columns.

·         cdc.lsn_time_mapping - When a transaction starts and ends. Relates to rows in the tracked tables.

 

There is also a table for each table which is tracked, in my case I have just the one - cdc.dbo_Table_1_CT.  If we execute a change to the table, such as inserting a new record.

 

INSERT INTO Table_1 VALUES (1, 'First Test')

 

A new row is added to the table, with a record also being inserted into lsn_time_mapping.

__$start_lsn           __$end_lsn             __$seqval              __$operation __$update_mask id          TestMessage
0x0000001E0000006B001A NULL                   0x0000001E0000006B0018 2            0x03       1           First Test

 

If we update the table again using

 

UPDATE Table_1 SET TestMessage = 'Updated First Test' WHERE id = 1

 

Two records are inserted into the tracked table. One with the TestMessage 'First Test' (the original message) and the other with 'Updated First Test' both relating to a single transaction in the lsn_time_mapping table. 

 

If we do

 

DELETE FROM Table_1 WHERE id = 1

 

We have a single row inserted into dbo_Table_1_CT saying which row was deleted.

 

Within the dbo_Table_1_CT there is a column __$operation.  This gives you the ID relating to the operation executed on the row.  2 = Insert. 3 = Before Update. 4 = Post Update. 1 = Delete.

If we change the design of the table while it is being tracked, a row is inserted into cdc.ddl_history with the command executed on the table.

 

source_object_id object_id   required_column_update ddl_command                    
---------------- ----------- ---------------------- --------------------------------
1077578877       1093578934  0                      ALTER TABLE dbo.table_1 ADD Status nvarchar(50) NULL

 

However, if we execute another insert command on the table, the new column does not appear in the result set/dbo_table_1_ct.  It would be nice to keep the two insync and not have to worry about updating the table if it is being tracked. To get the tables back insync, I had to disable it (capture_instance parameter relates to the columns being captured I think) and re-enable the tracking. 

 

The commands to do this are:

EXEC sys.sp_cdc_disable_table_change_data_capture @source_schema = 'dbo', @source_name = 'Table_1', @capture_instance = 'all'
EXEC sys.sp_cdc_enable_table_change_data_capture @source_schema = 'dbo', @source_name = 'Table_1', @role_name = 'cdc_test'

 

In case you are worried about this using up too much space, there is a sys.sp_cdc_cleanup_change_table stored procedure which can be used to remove entries up to a point in time.  You could then link this to a Job to archive the information, or simply report and remove.