question

mjharper avatar image
mjharper asked

CDC - Initial Data Load

I am currently investigating setting up CDC. There are lots of useful resources and templates for getting data changes once CDC is setup. However I’m just thinking through the initial setup and not sure the standard way of doing the initial data load from source to target database. Let’s say I have one table called **DataTableToMonitor** enabled for CDC. I have created the Datetime wrapper functions using sys.sp_cdc_generate_wrapper_function so have a function called **fn_all_changes_dbo_DataTableToMonitor** which accepts **@start_time, @end_time, @row_filter_option**. I will have a table called **CDC_TableParameters** with columns TableName, start_date, end_time and use this to track the datetime data was last retrieved from the source database. I will set start_date = end_date after data is pulled across to the target database so that the next time data is retrieved there are no overlap or gaps. However I’m just trying to work out how structure the initial data load and populate the **CDC_TableParameters** table. Initially I was going to use the script below, but I’m concerned there could be changes to the **DataTableToMonitor** table between setting the @LoadTime variable and loading the data into the Target Database. That would mean when I come to load the incremental changes (using @LoadTime as the start_date) I would duplicate data. Is the correct thing to do use this script, but set the transaction isolation level to SNAPSHOT? I figure there must be a standard way of doing this initial data load, but couldn’t find any information. DECLARE @LoadTime DATETIME = GETDATE() INSERT INTO TargetDatabase.dbo.DataTableToMonitor SELECT * FROM SourceDatabase.dbo.DataTableToMonitor INSERT INTO dbo.CDC_TableParameters VALUES ('DataTableToMonitor', @LoadTime, NULL)
sql-server-2014cdc
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.