Share via

Moving multiple SQL server data warehouse databases from On premise to Azure

Sreenivasa Chary Tatikonda 1 Reputation point
2022-06-24T21:21:02.96+00:00

Dear Experts,

I am new to Azure. I have few questions please help me. we have one SQL server dw database per each customer on premise for reporting (using SSRS). Each dw database consists few dimension tables, one fact and one staging table.

  1. if I want to move dw data for all customers into Azure then which storage is good?
  2. if I choose Azure Analysis service or Azure Synapse Analytics for data processing then Do I need to create one database per each customer like in on premise or is there way to put all
    customers data on a single database with multiple tables.

Thanks for your support!!!

Regards,
Sreenivasa

Azure Analysis Services
Azure Analysis Services
An Azure service that provides an enterprise-grade analytics engine.
444 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,694 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. HimanshuSinha-msft 19,386 Reputation points Microsoft Employee
    2022-06-27T21:19:53.323+00:00

    Hello @Sreenivasa Chary Tatikonda ,
    Thanks for the question and using MS Q&A platform.
    As we understand the ask here is where to move the data from the in permise DW when migrating to Azure , please do let us know if its not accurate.
    Let me assume that once you have migrated the data you still want to use the data for reporting . Are you planning to use SSRS or move to PowerBI ?
    This question will make a lot of difference , since SSRS does not support too many data source (read more here ), you will have to move the data to Azure SQL, so that SSRS can consume that .

    Lets assume that you want to move to PowerBI also , PowerBI is very reach and support many Datasource ( Read more here ) , then you have many options . You can write tthe data to ADLS Gen2 using Azure synapse analytics . Once the data is on the cloud storage you can use PowerBI to read . In this case there is NO database involved and so the solution will be comparatively cheap .
    I am not aware of the minute details of the project , but you can always write the data in Azure Analysis services , Synapse etc using Azure synapse analytics / Azure data factory .

    if I choose Azure Analysis service or Azure Synapse Analytics for data processing then Do I need to create one database per each customer like in on premise or is there way to put all
    customers data on a single database with multiple tables

    You never mentioned as to how many customers are there , but having one DB for each customer is not a great design . Anyways as I said I am not having all the details of the project , may be thats the way to go . I think you can always have one database and different schema for each customer . Since you can provide access on the schema level , this should work out for you .

    Please do let me if you have any queries.
    Thanks
    Himanshu

    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
      • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators