AzDO Pipelines

Create a Visual Studio Database project, host in DevOps Git-repo and deploy with pipelines

Learn to create a Visual Studio Database project, host it in a DevOps Git repo, and deploy it using Azure DevOps pipelines.

Hector Sven

Hector Sven

— 6 min read
Create a Visual Studio Database project, host in DevOps Git-repo and deploy with pipelines

Introduction

I wrote some time ago a similar article...

Provisioning Database Security thru Visual Studio Database Projects and Deploying via DevOps YAML Pipelines
The purpose of this article is to explain how access to an SQL Database can be configured from a Visual Studio (VS) Project in order to be deployed along with a DACPAC thru DevOps pipelines.
TechTacoFridayHector Sven

But I missed to include precisely what I'm about to cover with this, properly create a Visual Studio Database Project within a MSFT DevOps Git-repo, I will also include how to import the schema of an existing Azure Database.

Setting the stage

Here's the scenario: your are part of a developer team working on an application (e.g. power bi report, webapp, pipeline, or any other) that uses a backend database to support your it (e.g. it's data source, configuration); the database is hosted on cloud in the Azure platform, and it is an Azure Database and its schema it has been developed via SQL Server Manager Studio (SSMS).

Currently there are tables, keys, foreign keys (relations), stored procedures, but they are not versioned-controlled, so, it's quite common for the team to have integration issues during day-to-day work and deployment to upper environments is expensive in time & effort as we need to detect schema differences and manually fix those using scripts... sounds familiar 🤔?

If so, I have two great news for you ... first is that a much better way to this and two, I'm about to show you how 😁👍

Tools and services used...

  1. Source SQL Database (Azure or On-prem)
  2. Target Azure SQL Database
  3. Azure Key Vault
  4. Visual Studio with SQL Server Data Tools
  5. Azure DevOps services
🤔
If you wonder, is it Visual Studio necessary? the answer is, not anymore 😁👍, I just check and apparently there's an extension for VS Code SQL Database Projects for Azure Data Studio and VS Code ... please note however that I use Visual Studio 2022 for all supporting images and steps, ok? 😉

Solution

Assuming that you have a MSFT DevOps Git-repo and you have it locally cloned via Azure Visual Studio 2022 (or higher), then your VS would look something like this...

Add .gitignore file for Visual Studio

As described, this is to "Ignore Visual Studio temporary files, build results, and files generated by popular Visual Studio add-ons" you can either download a copy of the file from VisualStudio.gitignore or just copy its content as I did.

Create the database project

From Visual studio, click File > New > Project... and search the templates for SQL Server Database Project, select and click Next

Next we will have to Configure the new database project, but I think a quick notes with regards to the difference between the Project and the Solution.

  • Projects are represents a single software component or module, such as a library, application, or service; they contain source code files, configuration settings, and build instructions
  • Solution is a container that holds one or more related projects, they facilitate managing multiple projects together as a cohesive whole

With this in mind, hope it would make sense the following configuration...

😉
Important! Make sure the Location is the same where you locally cloned your Git Repository

Outcome should base somehow similar to this...

🤓
In summary, projects represent individual components, while solutions group these components together for efficient development and management

Import schema from an existing database

Simple, let's just connect to an existing database an import it's schema, click

Select the database you would like to use as a source and, if otherwise required, configure Import Setting as shown below.

Outcome shouldn't be a surprise, right? The database schema was imported to the project!

Configure the project's to be Azure-deployment compatible

Go to the database project Properties (right click > Properties) and select as Target Platform: Microsoft Azure SQL Database

🤩
Notice all other options available, including Azure Synapse Analytics and Data Warehouse 😉

Deploy using a pipelines

On the next part of the article, I'll explain just the most relevant parts of the code

👨‍💻
REMEMBER TO DOWNLOAD THE CODE!!!! from this AzDO Git repository create-visual-studio-database-project 😁🤞 and follow along.

In the example shared in the code, I'm using IaC to create the report's back-end database along it's with the SQL Server Logic server, notice that I'm assigning an Entra's ID security group (1) to be the server's Administrator (2)(3).  

I created group SQLServerAdministrators manually and added as member the security principal representing my AzDO Service Connection (1)

The result is that SQL Logic server admin is the security group SQLServerAdministrators and by proxy, pipeline via its service connection has admin privileges to deploy the DACPAC

😉
In a nutshell, the pipeline via its service connection needs to have Admin privileges over Azure's SQL Logic Server. There're many ways to achieve this, I'm just sharing how I usually do it... feel free to use the method that best fits your environment and security policies.

We need to Build the database project (1), this will create a DACPAC (Data-tier Application Package) and create an Artifact (2)

🤓
DACPAC (Data-tier Application Package) is a binary file that represents a snapshot of a database schema. It encapsulates database objects such as tables, views, stored procedures, and functions. Developers use DACPACs to deploy, upgrade, or version database schemas consistently across different environments.

We then use the SqlAzureDacpacDeployment yaml activity (1) to deploy the DACPAC towards any target database

DevOps pipeline (2) will do the rest 😁🤞 that is, applying a Data-tier Application Package (DACPAC) to a target database

🤓
Applying a DACPAC involves deploying the schema changes encapsulated in the DACPAC to an existing database. During this process, the DACPAC is compared with the target database, and any differences (such as table modifications, additions, or deletions) are identified. The deployment generates a script that synchronizes the target database schema with the DACPAC, ensuring consistent and automated schema management across environments.

Provisioning Database Security

Visit this article where I explain in detail how to do it 😁

Provisioning Database Security thru Visual Studio Database Projects and Deploying via DevOps YAML Pipelines
The purpose of this article is to explain how access to an SQL Database can be configured from a Visual Studio (VS) Project in order to be deployed along with a DACPAC thru DevOps pipelines.
TechTacoFridayHector Sven

Conclusion

DACPAC deployment ensures consistent schema changes across databases, simplifying database management and promoting automation via...

    • Consistency: Ensures consistent schema across environments (development, testing, production).
    • Version Control: DACPACs can be versioned and tracked in source control.
    • Automated Deployment: Easily deploy schema changes without manual scripts.
    • Safety: Deployment scripts are generated, allowing review before execution.

Call to action

I explained the code, now is your turn to ...

🧑‍💻
DOWNLOAD THE CODE!!!! from this AzDO Git repository create-visual-studio-database-project and give a try! 😁👍

Read more