0

I want to migrate on-premises SQL Server CDC to S3 files with AWS DMS. Everything works well but CDC operation time in target. I have:

    [__$start_lsn]
    ,[__$end_lsn]
    ,[__$seqval]
    ,[__$operation]

columns data in target but nothing regarding timing. How can I have the operation time in the target?

I know that if there is a way to return the result of a SQL function in target I can call sys.fn_cdc_map_lsn_to_time(x.__$start_lsn) function, but no idea how to do this.

On-premises SQL Server version is:

Microsoft SQL Server 2014 (SP1-CU4)- 12.0.4436.0 (X64) Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

Amir
  • 1
  • Are you looking for something like `select sys.fn_cdc_map_lsn_to_time(x.__$start_lsn) from yourTable`? – Ben Thul Jun 13 '19 at 02:04
  • Yes, call this function for each CDC record and have the result (change timing) in target beside other columns. – Amir Jun 13 '19 at 02:22
  • How do you enable those columns in the target? I have enable CDC but those columns are missing – Stay Foolish Jul 23 '19 at 10:56

1 Answers1

0

Based on your answer to my clarifying question in the comment, I think this will work:

select sys.fn_cdc_map_lsn_to_time(__$start_lsn)
from «some change instance»;

You can get the value for «some change instance» from the sys.sp_cdc_help_change_data_capture system stored procedure.

But! I'm guessing though that you want to use this to track the progress of your DMS operation. I that's the case, I'd suggest changing the query slightly. Like so:

select sys.fn_cdc_map_lsn_to_time(max(__$start_lsn))
from «some change instance»;

That is, map the time of the last_lsn rather than all of them. Good hunting!

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
  • I think there is a misunderstanding here, I want to use AWS DMS to store all the on-premises SQL server changes in S3 automatically. I did that but in the generated files I do not have the time of that operations (let's say UPDATE, DELETE, INSERT). I know how to get the time in SQL server but I do not know how to automate this process to populate time in the generated files in S3. – Amir Jun 13 '19 at 04:12
  • Ah. Understood. You want to somehow tag the files (presumably currently tagged with an LSN) with a human readable timestamp. I don't have a good solution for you here other than to say that you might do this with a lambda (or step) function on your bucket that parses out the LSN and does that mapping on your behalf. – Ben Thul Jun 13 '19 at 16:34
  • Ya, That's the thing could help. I will try Lambda. Thanks – Amir Jun 13 '19 at 23:15