In this post I want to do a crash course on how to connect a local SQL Server database to Azure Synapse Analytics. So that Azure Synapse Analytics can get data from it.
I thought I would do this post since I got asked last week in a meeting if this was possible. In reality, within ten minutes of the meeting ending I had it up and running.
With this in mind, I wanted to share how to do it quickly it here. Before I start, bear in mind that this is a crash course on how to set it up quickly on your own laptop. Of course, in a work environment there are other things you will have to take into consideration.
To set some realistic expectations about this post, it does not cover Azure Synapse Link for SQL Server 2022. You can find out more about that in another post I wrote called ‘Initial tests for Azure Synapse Link for SQL Server 2022‘.
Below you can see that on my laptop I have a copy of the WideWorldImporters sample database. It is in a local Docker container that has SQL Server 2019 installed.
To connect this database with Azure Synapse I must install setup a self-hosted integration runtime first. To start this process, I first go into Synapse Studio. You can find out more about Synapse Studio in my ‘Five-minute crash course about Synapse Studio‘ post.
In reality, once you are in Synapse Studio you can follow the Microsoft guide on how to create a self-hosted IR via Azure Data Factory UI. Because the only differences are how the screens below look.
Warning
Before I go any further, just a quick warning here for everybody. If you’re Azure Synapse workspace is connected to a Git repository and you decide to switch to Synapse live mode you will have issues.
Because adding a self-hosted Integration runtime will not work if you do this. So, make sure you are connected to Git if Git has been enabled.
For example, using Synapse live as highlighted above will cause the below error to appear.
Connecting to local database
Once the Integration runtime was installed I was able to setup a connection to a local database. I did this quickly by adding it as a Linked service.
Once I had done the above to select a new Linked service using SQL Server I filled out the relevant details. Selecting the self-hosted Integration runtime I had installed, as you can see below.
One key point here is that I added the server name the same as I would do to connect to the service running in the Docker container locally. For example, when connecting using Azure Data Studio.
If you happen to see the same view as above, remember to scroll down to enter in the SQL Server credentials.
After I had entered in all the credentials, I tested the connection which worked.
Connecting to the local SQL Server database using the Linked service
Once I had setup a linked service to connect to the local SQL Server database, I was able to use the Linked service in Azure Synapse a number of ways.
For example, I can import data from tables using the Ingest option on the home screen.
After I had clicked on the Ingest option the ‘Copy Data tool’ wizard appears. Allowing me to select the Linked service for use. From there I am able to select which tables and views to use, as you can see below.
I suspect you will need to know how to connect a local SQL Server database to Azure Synapse so that you can configure the new Azure Synapse Link for SQL Server. Which is a new feature that was announced at Microsoft Ignite that is supported in SQL Server 2022. However, I have not tried this.
Final words about connecting a local SQL Server database to Azure Synapse
Anyway, I hope this crash course on how to connect a local SQL Server database to Azure Synapse Analytics is helpful.
Because I know a lot of things in Azure Synapse Analytics is new for a lot of people. However, as you can see it does not take long to setup things like this yourself.
Of course, if you have any comments or queries about this post feel free to reach out to me.
[…] For example, I used a database inside container to test that I can connect a local SQL Server database to Azure Synapse Analytics. […]