Azure Synapse with SSIS package

0
votes

I have used several Azure services to upload data from on-premises to Azure SQL DW for Power BI.

SQL Server (On Perm) -> Azure Data Factory (SSIS IR, SSIS in Azure SQL Database) -> Azure SQL Database<br/>
2 Azure services used

However, we find that the data size is growing much bigger than design the platform.
We are planning to change to Azure Synapse.

But based on Microsoft Documentation, it seems the Data Factory (Preview) did not come with SSIS IR.
Here is what come up on my mind:

SQL Server (On Perm) -> Azure Data Factory (SSIS IR, SSIS in Azure SQL Database) -> Azure Synapse<br/>
3 Azure services used

I wonder does it have a better way for Synapse with SSIS.

Many thanks.

1
azuressisazure-sql-databaseazure-synapse
I think you are using Azure SQL database not Azure SQL DW. Azure Synapse Analytics is formerly Azure SQL Data Warehouse. – Leon Yue
Based on the current estimation, the data size will more than 5TB, which over SQL Database data limit. I did think about splitting the Database, but it will be more complicated for the end-user to consume in Power BI – Prisoner
In the Hyperscale tier, storage costs are calculated based on actual allocation. Allocated space increases automatically as needed, up to 100 TB. – Leon Yue
Hi @Prisoner, do you think about scale up the price tier? – Leon Yue
Have you looked at moving all staging data out of the database and into the data lake? This will save space in the db. Also look at columnstore indexes which offer massive compression, between 5-10x, depending on your data. – wBob

1 Answers

2
votes

Azure SQL database now has a Hyperscale service tier.

The Hyperscale service tier in Azure SQL Database provides the following additional capabilities:

  • Support for up to 100 TB of database size
  • Nearly instantaneous database backups (based on file snapshots stored in Azure Blob storage) regardless of size with no IO impact on compute resources
  • Fast database restores (based on file snapshots) in minutes rather than hours or days (not a size of data operation)
  • Higher overall performance due to higher log throughput and faster transaction commit times regardless of data volumes
  • Rapid scale out - you can provision one or more read-only nodes for offloading your read workload and for use as hot-standbys
  • Rapid Scale up - you can, in constant time, scale up your compute resources to accommodate heavy workloads when needed, and then scale the compute resources back down when not needed.

Since the Azure Synapse Analytics is not supported with SSIS IR, I think scale up the Azure SQL Database service tier is good choice for you.