Analytics for transactional workloads in near-real time
Historically, extract, transform, and load (ETL) procedures that were specifically designed have been used to extract data from operational data stores for analytical systems. These operations frequently take a long time to complete, place stress on the source systems, and only ever execute in batch mode. While this level of delay and overhead may be acceptable for some workloads, more and more businesses are discovering that they need to perform operational data analyses that are more closely tied to real-time, which traditional ETL systems are unable to handle.
Enter Azure Synapse Link for SQL.
Without needing to create unique ETL procedures, Azure Synapse Link for SQL offers an automated method of extracting data from source operational systems. The following are some advantages of Azure Synapse Link for SQL:
🟣Low-code/no-code solution: Using Azure Synapse Link for SQL, you can load data into an analytical system without having to design special processes to extract it. Azure Synapse Link for SQL handles the rest when you select the tables you want to replicate and indicate how you want them saved in the destination Azure Synapse Analytics dedicated SQL pool.
🟣 We made an effort to extract data from the source systems with the least possible impact on them. Azure Synapse Link for SQL leverages the new change feed capabilities provided by SQL Server 2022 and Azure SQL Database to obtain the data without needing to execute custom queries, which can be expensive in a traditional ETL procedure.
🟣 Data is continuously transferred from the source systems into the Azure Synapse Analytics environment in close to real-time. If you don't require near-real-time data flow, you can choose to switch to "scheduled mode."
How does it function?
The new change feed functionality that has been added to SQL Server 2022 and Azure SQL Database powers Azure Synapse Link for SQL. With the use of this functionality, we can keep an eye on tables for modifications as they take place without incurring the additional costs associated with change data capture (CDC)-based data movement solutions.
A Gen2 Azure Data Lake storage (ADLS) account is used as the "landing zone" when a transaction is committed on a table that is being replicated by Azure Synapse Link for SQL. The data is then loaded into a specific SQL pool for Azure Synapse Analytics by an ingestion service. You can query the data once it has arrived there just like any other dedicated SQL pool.
Who will benefit?
Here are a few situations when Azure Synapse Link for SQL would be advantageous:
🟡 Database consolidation: With Azure Synapse Link for SQL, you can combine data from various source databases into a single, analytics-specific SQL pool. Azure Synapse Link for SQL can combine all of your data into a single analytical platform, whether you have various tenant databases that you want to use for market-based analytics or you have expanded through acquisition and need to bring together multiple source systems.
🟡 Hybrid on-premises/cloud: You may transfer data into a shared analytical solution from wherever it resides because Azure Synapse Link for SQL supports both Azure SQL Database and SQL Server 2022.
🟡 Using Azure Synapse Link for SQL, you could transfer the tables from the source systems into the Azure Synapse Analytics dedicated SQL pool alongside the data that is processed in your nightly ETL system. Then, you could perform reporting and analytics tasks over the entire set of data. This is useful if you have an ETL system that meets the majority of your needs but has a few tables where you want data to arrive closer to real-time.
