Connecting SQL Server Database to Azure Synapse

This post will take you through the process of connecting a database on SQL Server to Azure Synapse so that Azure Synapse Analytics can access data from the SQL Server.

But first, a word on SQL Server and Azure Synapse.

Microsoft SQL Server

Microsoft SQL Server is an RDMS (Relational Database Management System) that complements the whole suite of Microsoft .NET framework and supports applications either on a single machine or local area network across the web. The database is based on the popular programming language SQL and together with Oracle database and IBM’s DB2, has for long been among the leading database technologies in the world.

Azure Synapse

Azure Synapse is an analytics service that combines enterprise data warehousing and Big Data analytics. You can query data through either server-less or provisioned resources at scale. With Azure Synapse, these two can be used to ingest, prepare, manage, and serve data for machine learning and BI requirements.

SQL Server to Azure Synapse

To connect SQL Server to Azure Synapse, first, have the SQL Server installed in the system. To connect this database with Azure Synapse, you must first install and set up a self-hosted integration routine which can be done through the Synapse Studio. Once you have accessed Synapse Studio, follow the Microsoft guide on creating a self-hosted IR via Azure Data Factory UI.Private network support is achieved by installing runtime to machines in the same on-premises network as the resource which the integration runtime is connecting to.

A note of caution here. If your Azure Synapse workspace is connected to a GIT repository and you decide to switch to Synapse live mode the Integration runtime will not function. It is necessary to be connected to GIT if it is enabled.

Once the Integration runtime is installed, set up a connection to the database by adding a Linked service. Select the new Linked service using the SQL Server.

To connect SQL Server to Azure Synapse setup a linked service in the SQL Server database. This service can be used in Azure Synapse in several ways.

For example, you can import data from tables through the Ingest option. To do this step, click on the Ingest option and the “Copy Data tool” wizard appears. This will help you to select the Linked service for use from where you can decide which tables to select and the views to use.

How to connect SQL Server to Azure Synapse to configure the new Azure Synapse Link for SQL Server? For this, go to the new feature that was announced at Microsoft Ignite that is supported in SQL Server 2022.

Try this process to connect SQL Server to Azure Synapse. Azure Synapse is new but as you can see it does not take long to set up things yourself.