none
Sync SQL Server On Premise Database to a Azure SQL

    Întrebare

  • Hello,

    I have a Azure SQL Retail Datawarehouse Database database that needs to collect data from different SQL Server Express On Premises Retail Stores.

    All the Data need to go from The on premises Databases to the SQL Azure Database, to create a Reporting Tool with Power BI.

    So I have Retail 1, Retail 2 and Retail 3

    I want Data from Some of the Tables to go from Reatil 1 to the Main Hub Azure SQL, same for Retail 2 and Retail 3.

    Is this possible with Azure SQL Data Sync?

    Or there is a better solution?

    Thank you so much!

    sâmbătă, 14 iulie 2018 11:06

Toate mesajele

  • Hello,

    I have a Azure SQL Retail Datawarehouse Database database that needs to collect data from different SQL Server Express On Premises Retail Stores.

    All the Data need to go from The on premises Databases to the SQL Azure Database, to create a Reporting Tool with Power BI.

    So I have Retail 1, Retail 2 and Retail 3

    I want Data from Some of the Tables to go from Reatil 1 to the Main Hub Azure SQL, same for Retail 2 and Retail 3.

    Is this possible with Azure SQL Data Sync?

    Or there is a better solution?

    Thank you so much!

    Good day silva,

    My personal preferred in these type of cases is NOT to use built-in tools, but simply sync the data using a small app service that we develop. This way I have full flexibility on design the best solution and the result are usually best.

    Anyhow, if you want to count on built-in services then you can use this procedure:

    1. Sync the data first to from on-prem to Azure SQL Database

    This can be done using simple merge replication, or with Azure SQL Data Sync (choosing the right solution depend on your specific system and without fully familiar with the system I cannot recommend what is the best solution for you)

    * another option is to copy the data from the on-premises SQL Servers to Azure Blob storage. If there is no need in ETL on the data, this option might fit better.

    2. Use Azure Data Factory to continuous load the data from Azure SQL Database or from the Azure Blob storage into Azure Data Warehouse.

        


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    sâmbătă, 14 iulie 2018 14:44
    Moderator
  • Hi Ron,

    thank you so much for helping me and give your opinion.

    You are right regarding the sync tool, if we develop it we can be more free to create what we want, but i have some constraint:

    1) Cannot Change much the local Application that is feeding the local SQL Servers, that's why it's difficult to trigger the changes to the local database, and notify a service that there are changes (CRUD), that need to be applied to the HUB Database. I was thinking for every change to feed a Azure Service BUS Queue, so that some Azure Function can apply the changes to the HIB database.

    2) Cannot use Azure Data Factory because it's too expensive.

    So I'm thinking of using Azure SQL Data Sync.

    My scenario it's pretty common i think?

    1) 3 Retail Stores with SQL Sever Express. 

    2) Need to centralize the Sales (Client, Products, Sales) in the Central Hub Database to create Dashboards and Report, with Data coming from the 3 Stores SQL Express Database.

    3) I never used Azure SQL Data Sync, so i was wondering if the above scenario it's possible!

    Thanks Again


    sâmbătă, 14 iulie 2018 16:12
  • Hi Ron,

    thank you so much for helping me and give your opinion.

    You are right regarding the sync tool, if we develop it we can be more free to create what we want, but i have some constraint:

    1) Cannot Change much the local Application that is feeding the local SQL Servers, that's why it's difficult to trigger the changes to the local database, and notify a service that there are changes (CRUD), that need to be applied to the HUB Database. I was thinking for every change to feed a Azure Service BUS Queue, so that some Azure Function can apply the changes to the HIB database.

    2) Cannot use Azure Data Factory because it's too expensive.

    So I'm thinking of using Azure SQL Data Sync.

    My scenario it's pretty common i think?

    1) 3 Retail Stores with SQL Sever Express. 

    2) Need to centralize the Sales (Client, Products, Sales) in the Central Hub Database to create Dashboards and Report, with Data coming from the 3 Stores SQL Express Database.

    3) I never used Azure SQL Data Sync, so i was wondering if the above scenario it's possible!

    Thanks Again

    Hi,

    >> My scenario it's pretty common i think?

    the most common scenario is when someone want to buy the entire world but he have no resources for it.

    >> Cannot use Azure Data Factory because it's too expensive

    Therefore, yes if your case is that you cannot use something because it's too expensive then this is common scenario, but this mean that you might not fit to give the service that want to give, same as the person that want to buy the entire world.

    >> Cannot Change much the local Application that is feeding the local SQL Servers

    Why do you think about using the existing application for a new task?!?

    You can have a special application which load the data from the local SQL Server to the Azure DW directly (I am not saying that this solution is best for your case but that this limitation has no meaning).

    >> it's too expensive + 3... SQL Sever Express     

    Why did you choose to use Azure DW ?!?

    I have no idea about your system but my guess is that you do not understand the power of SQL Server and you could solve all in a one center local SQL Server or one Azure SQL Database. This will allow you to use direct replication or direct Azure SQL Data Sync to migrate the data from the SQL Express to the center dataabse

    In fact, three express DB means that you have less then 30 GB data, which is a tiny database size for SQL Server (theoretically can deal with databases in the size of 524272000 GB and not 30 GB).

    >> Need to centralize the Sales (Client, Products, Sales) in the Central Hub Database to create Dashboards and Report...I never used Azure SQL Data Sync

    As I explained above, you can use Azure SQL Data Sync to sync local database to Azure SQL Database, which is probably the solution that can fit you according to the information we have. I do not see any reason (at this time, with the info we have) why you need to use Azure DW instead of Azure Database.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    sâmbătă, 14 iulie 2018 18:01
    Moderator
  • Hello,

    A retailer on Mexico with 72 stores is currently using SQL Data Sync to centralize all sales data to Azure SQL Data Sync. All stores have SQL Server Express to collect daily sales data.


    There is a limit of 30 member databases synching with a hub database, but then you can have a hub database synching to another hub database to where you can connect Power BI to show dashboards with sales data.


    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com


    sâmbătă, 14 iulie 2018 18:35