How to load data from DB to adls/Blob partitioned

Saha 0 Reputation points
2023-07-24T00:59:32.6133333+00:00

Hi Experts,

I have a database in on-prem that contains data for 15 years and not used frequently. I want to store this data in ADLS or Blob Stoarge in azure to reduce cost. The requirement is that the data should be migrated from on-prem to azure cloud and should be loaded in cool tier, in a way that if user wants to extract only one day or one week data,they need to be able to extract only required data, instead of taking all data from cool tier to hot tier.

Can anyone please suggest how to achieve this?

  1. Do I need to read and load data per each day (for past 15 years) and load it in a partitioned storage?
  2. Can I load all data from on-prem DB to adls and then can distribute it as per business date?
  3. Out of ADLS and blob storage ,which one is better for this use case ? Any suggestion will be really appreciated l.
Azure Database Migration service
Azure Data Lake Storage
Azure Data Lake Storage
An Azure service that provides an enterprise-wide hyper-scale repository for big data analytic workloads and is integrated with Azure Blob Storage.
1,410 questions
Azure Blob Storage
Azure Blob Storage
An Azure service that stores unstructured data in the cloud as blobs.
2,578 questions
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,629 questions
SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
507 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Nandan Hegde 30,871 Reputation points MVP
    2023-07-24T03:46:33.8866667+00:00

    Hello Saha,

    PFB the response/Queries:

    1. Do I need to read and load data per each day (for past 15 years) and load it in a partitioned storage?
    2. Can I load all data from on-prem DB to adls and then can distribute it as per business date?

    [NH] : What is the overall size of data? This would help us determine the approach

    1. Out of ADLS and blob storage ,which one is better for this use case ? Any suggestion will be really appreciated

    Based on your above use case, I would prefer leveraging ADLS, as you can create folders pertaining to days which you can easily map /query via serverless/notebooks.

    Note: you need to ensure that the tier is hot initially while copying data from On prem to sink and post that turn it into cold