Forum Discussion

fabricpribeiro's avatar
fabricpribeiro
Icon for Post Patron rankPost Patron
6 months ago
Solved

SQL Server 2008 on-prem - CDC Fabric

Dears,   My architecture in fabric is :   Raw->Bronze (with history)->Silver->Gold   The topic of this post is related with Raw and Bronze   In Raw, we are consuming different data sources. ...
workspaces
tayloramy's avatar
tayloramy
Icon for Super User rankSuper User
6 months ago

Hi fabricpribeiro, 

 

Fabric Mirroring doesn't support SQL Server 2008, so you will need to do some things yourself. 

 

CDC is the way to go. You will need to have somewhat complicated logic however. First, you will need to pull up the change tables which will tell you what rows were inserted, updated, or deleted. 

From there, you will need logic in Fabric to delete any deleted records from your table, and then for inserted or updated records, you will need to pull those from the main table, but using the change table to filter what records to pull. 

 

It's a little more complicated than if you could use Mirroring, but it is workable  

fabricpribeiro's avatar
fabricpribeiro
Icon for Post Patron rankPost Patron
6 months ago

Thanks for your reply, yap we know we cannot use mirroring

 

We want to use CDC

 

Question was more :

 

Given the behavior we would like to have, shall we use Copy Job from Fabric or Shall we instead use Fabric data factory copy task to achieve what we wish?

 

Thanks a lot,

 

Pedro

  • v-dineshya's avatar
    v-dineshya
    Icon for Community Support rankCommunity Support
    6 months ago

    Hi fabricpribeiro ,

    Thank you for reaching out to the Microsoft Community Forum.

     

    Hi tayloramy , Thank you for your prompt response.

     

    Hi fabricpribeiro ,  Use Fabric Data pipelines  -->Copy Activity  in CDC mode instead of Copy job, CDC in Copy Job (Preview) have some limitations. Fabric Data Factory Copy Activity supports CDC deletes, it reads from the CDC tables (cdc._CT) including delete, insert, update (before) and update (after).

     

    Note: It reads from CDC change tables (cdc.__CT), Captures __$operation and writes operation metadata into Lakehouse.

     

    Please refer below link.

    Change data capture (CDC) in Copy Job - Microsoft Fabric | Microsoft Learn

    What is change data capture (CDC)? - SQL Server | Microsoft Learn

    Change data capture - Azure Data Factory & Azure Synapse | Microsoft Learn

     

    I hope this information helps. Please do let us know if you have any further queries.

     

    Regards,

    Dinesh

    • v-dineshya's avatar
      v-dineshya
      Icon for Community Support rankCommunity Support
      6 months ago

      Hi fabricpribeiro ,

      We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

       

      Regards,

      Dinesh

      • v-dineshya's avatar
        v-dineshya
        Icon for Community Support rankCommunity Support
        6 months ago

        Hi @fabricpribeiro ,

        We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

         

        Regards,

        Dinesh