locked
Azyre SQL Data Sync between Row-stored table and Column-stored table? RRS feed

  • Question

  • I want to know that Can the SQL Data Sync synchronize the data between Row-stored table and Column-stored table?
    for using the Row-stored table to OLTP, and the Column-stored table to Analyze by Power BI.

    and for using Azure SQL Database standard by Row-stored table, and using SQL Server by Column-stored table with Power BI.

    Regards,

    Friday, February 1, 2019 11:31 AM

Answers

  • Hi Yoshihiro,

    Replication will not work solely on an Azure SQL Database:Supported Configurations:

    "The distribution database and the replication agents cannot be placed on an Azure SQL database."

    This scenario could work if the SQL instance was running on an Azure VM:

    The SQL Server can be an instance of SQL Server running on-premises or an instance of SQL Server running in an Azure virtual machine in the cloud. For more information, see SQL Server on Azure Virtual Machines overview.

    This information should be more clear about functionality is available. 

    Saturday, February 2, 2019 12:57 AM

All replies

  • Hi Yoshihiro,

    Data sync is a replication approach that is schema dependent. You may choose to not copy all data from the source to target but the data must comply within the defined schema that is copied from the source to the target. 

    In your case, there is a transformation that needs to take place to go from OLTP to OLAP. This blog post covers many scenarios: Azure SQL hybrid data movement with a section covering your specific scenario:

    Move data into data warehouse

    Companies periodically move data from OLTP system to OLAP system/data warehouse for data analysis and reporting. The process usually includes extracting data from data source, transformation and loading data into the target data warehouse (ETL).

    The data warehouse and OLAP system can be built on top of Azure SQL Databases, Azure SQL VMs or Azure SQL Data Warehouse.

    Most on-premises SQL Server customers use SSIS (SQL Server Integration Services) to load data into data warehouse. It extracts data from OLTP system, transforms it, and loads it into data warehouses. SSIS can also be used in the hybrid environment. For exiting SSIS customers, it will reduce the cost to build a new ETL process. If you are loading data into SQL VM or SQL Databases, we recommend running SSIS on Azure VM. See the blog of “Running SSIS on Azure VM (IaaS) – Do more with less money” for more information.

    If you are moving data into Azure Data Warehouse, you can also use ADF (Azure Data Factory) or bcp as the loading tools. See “SQL Data Warehouse Migrate Your Data” and “Use Azure Data Factory with SQL Data Warehouse” for details.

    Please let us know if you have additional questions.

    Friday, February 1, 2019 10:29 PM
  • Hi Mike, Thank you for your reply.

    I does not use Azure SQL Data Warehouse,
    cause data size is good on SQL Server Column-stored for Power BI.

    So, I ask this question.

    My scenario:

    • Main Database for OLTP using Azure SQL Database
    • Sub Database for Analyze using SQL Server and Power BI

    Can the SQL Data Sync synchronize the data between Row-stored table and Column-stored table?

    Regards,

    Friday, February 1, 2019 11:41 PM
  • Hi Yoshihiro,

    Replication will not work solely on an Azure SQL Database:Supported Configurations:

    "The distribution database and the replication agents cannot be placed on an Azure SQL database."

    This scenario could work if the SQL instance was running on an Azure VM:

    The SQL Server can be an instance of SQL Server running on-premises or an instance of SQL Server running in an Azure virtual machine in the cloud. For more information, see SQL Server on Azure Virtual Machines overview.

    This information should be more clear about functionality is available. 

    Saturday, February 2, 2019 12:57 AM
  • Hi, Thank you for your reply.

    On my scenario, Data replication from Azure SQL Database to SQL Server Express Edition with SQL Data Sync Agent on our PC.
    and Data analyze by Power BI Desktop connected DirectQuery to SQL Server Express Edition.

    I know that Power BI Desktop can analyze 100 million + data with nice performance at SQL Server Express Edition and column-stored table.

    Regards,

    Saturday, February 2, 2019 1:59 AM