Database Project for use with Azure Synapse Link for SQL Server 2022

Reading Time: 5 minutes

You can consider this post to be a follow up to my Data Toboggan session on how you can create a database project for use with Azure Synapse Link for SQL Server 2022.

This post covers the below:

• Quick introduction to my Azure Synapse Link for SQL Server 2022 setup.
• How to create a database project for a database running in SQL Server 2022 which is the source for Azure Synapse Link.
• Make the folder containing the database project a Git repository.

As promised during Data Toboggan a diagram is included in this post.

In the next post I will take this one step further and cover how to use this database project to update both the SQL Server database and the Azure Synapse Analytics dedicated SQL Pool in the same deployment pipeline using Azure DevOps. That post will also include a diagram.

One key point to remember here is that Azure Synapse Link for SQL Server 2022 is now generally available.

Quick introduction to Azure Synapse Link for SQL Server 2022 setup

I am not going to go into too much detail here Azure Synapse Link for SQL Server 2022. Just a general overview about my setup for this post. For further details please read my other post. Where I covered my initial tests for Azure Synapse Link for SQL Server 2022.

For this demo I use the same SQL Server database that I used in the previous post.

However, because this setup was for a live demo, I created a new Azure Synapse Link connection with less tables. It contains the below two tables from the WideWorldImporters database that I restored onto a SQL Server 2022 instance.

• Sales.OrderLines
• Sales.Orders

This is how the setup for the two tables looks in the Integrate section of Synapse Studio when the link is running.

Here is a diagram that shows basic overview of what happens with whilst the link is running.

In reality, what happens is more complicated than that. You can find out more by reading my initial tests for Azure Synapse Link for SQL Server 2022 post.

For this post we create the database project based on the database running in SQL Server 2022. Represented on the left-hand side of the above diagram.

If you intend to create the same link and you want to follow along you do not need to stop the link just to create the database project. However, you will need to stop it for the next post to deploy updates.

Creating a database project for use with Azure Synapse Link

For my demo I chose to create the database project in Azure Data Studio. Just like I have done for other demos in the past. So that I can create dacpac files and perform state-based migrations.

If you have the SQL Database Projects extension installed in Azure Data Studio you can create a project with ease. You can create a database project by right clicking the database and selecting Create Project From Database.

From there you enter the name of my choice and the location. One key point to remember here is that when you add a location it automatically creates a subfolder with the name of the project in it. So, you do not need to create one yourself.

Once that is done, you can change the target platform to the same compatibility level as the database in SQL Server 2022. Which is SQL Server 2016. In reality, it is currently easier to create dacpacs in Azure DevOps for databases with a compatibility level lower than SQL Server 2022.

This also helps me highlight a key point here. When you use Azure Synapse Link for SQL Server 2022 the database does not have to be set to the SQL Server 2022 compatibility level.

To change the target platform, go into the Database Projects extension in Azure Data Studio. From there right click and select Manage to change the target platform.

As far as creating the database project is concerned that is it. However, I want to cover how to make this database a local Git repository in this post as well.

Make the Database Project a Git repository

To make the database project a Git repository we must initialize the folder. You can do this with ease if the database project is already open in Azure Data Studio.

First, you select the Source Control extension in Azure Data Studio. Then click on the blue Initialize Repository button.

From there, you can select the current location of the database project. Once done you can then do your first commit to the Git repository. Which is also known as an initial commit. By entering a comment and then clicking on the tick button.

It is worth noting that even though the Synapse Link has only two tables setup we still want all the databases added to the database project for a good reason. Which is that you still want to keep all of the tables in your SQL Server databases updated.

Once that is done the folder containing your database project becomes a Git repository.

Final words

I hope this post about how you can create a Database Project for use with Azure Synapse Link for SQL Server 2022 has been useful for some of you.

In the next post I take this one step further and cover how to use this database project to update both the SQL Server database and the dedicated SQL Pool using Azure DevOps.

Of course, if you have any comments or queries about this post feel free to reach out to me.