sys.fn_cdc_map_time_to_lsn function will perform works in the opposite manner. That is it will return the LSN for the relevant datetime value. This function has two parameters; The first parameter is a relational operator which can be either one of following operators.
- largest less than
- largest less than or equal
- smallest greater than
- smallest greater than or equal
The above operators will allow a user to return the exact LSN number as most of the time there won’t be a LSN for the exact date time. For example, If you want to get data from 2008-08-25 09:45, there won’t be an LSN record which matches that time.
DECLARE @begin_time datetime, @end_time datetime, @begin_lsn binary(10), @end_lsn binary(10);
SET @begin_time = ‘2008-08-15 09:45:00.000’
Set @end_time = ‘2008-08-15 10:15:00.000’
SELECT @begin_lsn = sys.fn_cdc_map_time_to_lsn(‘smallest greater than’, @begin_time);
print @begin_lsn
SELECT sys.fn_cdc_map_lsn_to_time(@begin_lsn)
Returns 2008-08-15 09:46:53.090
SELECT @end_lsn = sys.fn_cdc_map_time_to_lsn(‘largest less than or equal’, @end_time);
print @end_lsn
SELECT sys.fn_cdc_map_lsn_to_time(@end_lsn)
Returns 2008-08-15 10:12:13.457
There are another two functions to query data. cdc.fn_cdc_get_all_changes_capture_instance and cdc.fn_cdc_get_net_changes_capture_instance are those function. cdc.fn_cdc_get_all_changes_capture_instance will be created after enabling cdc but to create second one you need to set 1 for @supports_net_changes parameter when using sys.sp_cdc_enable_table stored procedure.
EXECUTE sys.sp_cdc_enable_table
@source_schema = N’dbo’
, @source_name = N’Products’
, @role_name = N’cdc_Admn’
,@captured_column_list =’ID,ProductName,Date’
,@supports_net_changes=1
GO
cdc.fn_cdc_get_all_changes_capture_instance returns one row for each change applied to the source table within the specified log sequence number (LSN) range. In this function, there are three parameters. First two parameters are from and to LSNs. Last parameter is filter option. For the filter option you have two operations. all filter return all records but it returns only the updated records not before update record. This means that you will see records with operation 1,2 and 4. The other parameter, all update old will return all the records including before update records.
cdc.fn_cdc_get_net_changes_capture_instance returns one net change row for each source row changed within the specified LSN range. That is, when a source row has multiple changes during the LSN range, a single row that reflects the final content of the row is returned by the function. For example, if a transaction inserts a row in the source table and a subsequent transaction within the LSN range updates one or more columns in that row, the function returns only one row, which includes the updated column values.
DECLARE @begin_time datetime, @end_time datetime, @from_lsn binary(10), @to_lsn binary(10);
SET @end_time = GETDATE();
SELECT @from_lsn = sys.fn_cdc_get_min_lsn (‘dbo_Products’)
SELECT @to_lsn = sys.fn_cdc_map_time_to_lsn(‘largest less than or equal’, @end_time);
declare @row_filter nvarchar(30)
Set @row_filter =’all’
SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_Products(@from_lsn, @to_lsn,@row_filter);
Returns 3 records one each for the records
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Products(@from_lsn, @to_lsn,@row_filter);
Retruns all records for this time range.
The main drawback of this method of auditing is that it does not have user information.
DDL Changes
Monitoring your schema changes is very important as it will impact your future releases. In SQL Server 2005, you could use DDL triggers to capture those changes. However, it is not an easy task as you need to create a DDL trigger for each and every table. However, with cdc things have become easy. After enabling CDC in the database, there will be a table called [cdc].[ddl_history]. Rather than simply executing a SELECT statement, there is a stored procedure to execute to get the DDL changes.
exec sys.sp_cdc_get_ddl_history
@capture_instance =’dbo_Products’
The above T-SQL script will return the DDL history for the products table.
Like the previous case, you will not be able to find the user information from this query.
Conclusion
Although there are limitations (Enterprise edition is needed for CDC) in CDC, it is a perfect solution to capture data changes in your database.
CDC can also be used in data warehouses. As it needs discussion, I will leave this discussion for another article. Like other articles, I wish to open this for discussion.
]]>