Configuring Git in Azure Data Studio for SQL & Synapse Pools

Overview

In this blog post, we'll delve into the process of configuring Git to manage database objects within a Synapse Dedicated Pool environment (or Azure SQL Database) using Azure Data Studio.

We'll walk through the necessary steps to seamlessly integrate Git version control into your development workflow, allowing for efficient collaboration and tracking of changes across database objects like Tables, Stored Procedures, Views, and any other SQL objects.

By leveraging the powerful capabilities of Azure Data Studio and Git, you can streamline development processes, ensure version consistency, and enhance team productivity within your Synapse Dedicated Pool (and Azure SQL Database) environment. It’s also very important that the Database objects are integrated with Git in order to implement Data Ops.

Pre-requisites

1. Create Azure SQL Database or Synapse Dedicated Pool.
2. Create a few Database objects in Azure SQL (ex: Tables Stored Procedures and Views)
3. Install Azure SQL Data Studio directly from the Microsoft official page. You get Azure Data Studio automatically, along with the installation of SQL Server Management Studio (SSMS).
4. Install git software on your machine.
5. Create a git repository in Azure DevOps (or any other similar tools) and clone it in your local machine.

Install Azure Data Studio Extension

Launch the Azure Data Studio, navigate to the extensions window, and install the extension called SQL Database Projects, as shown below.

Open the Git Repo folder

Let’s now open our project folder where you have cloned the Database repository using the git window, as shown below.

Once you open the folder, make sure that you are able to view the default branch name as shown below.

Create SQL Connection from Azure Data Studio

The next step is to create a connection to Azure SQL Database or Synapse Dedicated Pool by navigating to the Connections tab and clicking on New Connection, as shown below.

It opens up a new popup where you need to provide all the connection details, as shown below.

Note. Please make sure you have your Public IP Address whitelisted in the Firewall rules of Azure SQL Server.

Once you provide all the details, click on Connect to create the Connection. If you face any challenges, please provide your error details in the comments section below.

If everything went well, you should see something as shown below.

Create Database Project

When you want to work with Database Projects, the first step is to create the Database Project itself. Let’s create the new Database Project using the Create new button available in the Database Projects window as shown below.

In the Create new database project window, choose the Azure SQL Database, provide a name, choose a location (where you cloned the git repo), and make sure the SDK-Style Project checkbox is checked.

Once you provide all the details, click on the Create button to create the new project. Once you click on the Create button the SQL Database Project file is created as shown below.

In most cases, in the DEV Environment, the SQL developers directly create the SQL objects using a SQL Client tool like SSMS (or something similar). It’s very important to integrate git with the SQL Database objects.

In order to retrieve all the SQL Objects and integrate them with git, we now need to leverage the SQL Data Projects extension. Open the Database Projects window and connect to the existing database as shown below.

It opens up a popup where all the below details are filled.

1. Server/Database: Select the SQL Server and the Database that we need to connect.
2. Location: Location of the folder where you cloned the git repo.
3. Folder Structure: Select Schema/Object Type (feel free to choose other types)
4. Update Action: Apply all changes or View Changes in Schema Compare if you want to review the changes.
   

Once you choose all the details, click on Update Button. A small window that shows the progress shows up in the right-hand corner as shown below.

After a minute or two, all the Database objects would be added to the Database Project as shown below screenshot.

At the same time, a build also gets triggered automatically and a .dacpac file is also created. Another article, to understand more about the .dacpac file.

As shown below, add all the temporary/intermediate files to .gitignore so that they are not added to the git repo as shown below

Finally, the below files must be committed to your git branch. Once you review all the files, stage the changes and commit them to the git branch as shown below.

That’s it. We have now integrated SQL objects with git. Henceforth, you need to make sure that all the new SQL Objects are integrated with git without fail. The next step is to implement Data DevOps for automated deployments to higher environments.

Summary

The article provides a comprehensive guide on configuring Git for managing database objects within a Synapse Dedicated Pool or Azure SQL Database environment using Azure Data Studio. It outlines the necessary steps to integrate Git version control seamlessly into the development workflow, enabling efficient collaboration and change tracking across SQL objects such as Tables, Stored Procedures, and Views.

Prerequisites include creating a database, installing Azure Data Studio, installing Git software, and setting up a Git repository. The article then details the process of installing the Azure Data Studio extension, opening the Git repository folder, creating an SQL connection, and establishing a database project.

It emphasizes the importance of integrating database objects with Git to implement Data Ops effectively. The process involves leveraging Azure Data Studio's SQL Database Projects extension to retrieve SQL objects and integrate them with Git. Additionally, it covers the steps for staging and committing changes to the Git repository.

The integration of SQL objects with Git facilitates version consistency, enhances team productivity, and sets the stage for implementing Data DevOps for automated deployments to higher environments.