Azure SQL Database Vs Synapse analytics

Question

Hi - I need some good inputs in implementing analytical solution.

We are planning to migrate on-premise analytical solution to cloud.

Existing Process: SSIS>>SQL Server>>SSAS(Tabular)>>Power BI

01 We are planning to reuse SSAS Model and power BI dashboards without any changes other than deploying on premise AS model to Azure AS

02 SSIS will replace with Data Pipelines

03 Data Storage: Initially we thought of using Synapse analytics for computing and storage but since it has some limitations with using cross database queries, constraints, triggers, foreign keys, etc. we are contemplating to use Azure SQL DB as a second options.

We are aware that there would be some limitations with storage and performance with using Azure SQL DB, are there any major drawbacks of using Azure SQL DB instead of Synapse analytics. Anyway our semantic layer resides in Azure Analysis services and Power BI dashboards will hit Azure AS model.

Any suggestions, thoughts would be appreciable

thanks

Answer 1

Azure SQL Database can definitely be used if your workload not huge. If you need cross-database queries, you'll want Managed Instance, and its resource limits are documented here.

Pay particular attention to the Log Write Throughput Limits, as all databases are always in Full recovery. The current limits are

• 3 MB/s per vCore
• Max 120 MB/s per instance
• 22 - 65 MB/s per DB

If you can live without cross database queries for now the Azure SQL Database Hyperscale tier supports larger database sizes (up to 100TB) and faster log throughput with fewer VCores.

By contrast Synapse SQL Pools support massive data loading jobs, as it's an MPP engine and supports scale-out data loading and querying.