Since Synapse came to the market it has been one of my favourite products. I have had the pleasure of helping many clients architect their way to the cloud to take advantage of Azure Synapse Analytics. When first introduced, it was often referred to as the replacement for Azure Data Warehouse. Over time it has grown into an expansive service that brings together data integrations, enterprise data warehousing and big data analytics. Synapse gives the ability to query data using serverless, or with a dedicated option, and at scale when needed. In this unified experience, Synapse allows you to ingest, explore, prepare, transform, manage and serve data for immediate business intelligence and machine learning opportunities.
Synapse has become a powerhouse collection of tools and supporting features such as Machine learning, data sets to augment your work and more that simplify and speed up the time to build solutions for projects. The exciting aspect I want to share with you is that an easy way to share data from on-premises SQL Server to Azure Synapse Analytics is available in SQL Server 2022.
On-premises ETL can be expensive when it requires additional hardware to scale or if it is used for just your AI portion of the process. This is a regular issue, if you commonly use third party data sets and then have to purchase storage to save them. These processes can be cumbersome as it can be complex to scale up or out as your workloads grow. If you do not use additional or separate hardware then you will be affecting operational workloads and that can affect productivity. As you can see, problems abound!
With Azure Synapse link for SQL Server, data is moved to SQL pools through a migration and then routinely with a change feed in near real-time with limited latency between SQL Server and Synapse Analytics. Once in Synapse Analytics the data is available to be analyzed by both Spark and SQL runtimes in Synapse. You can also use the SQL pools to harness the scaleable power of the Synapse solution.