The goal of this post is to develop a CICD process for Azure SQL or SQL Server database projects using Visual Studio and to discuss the management of VSDB Projects.
- Visual Studio
- SQL Server Express Edition
- Docker
- SQL Authentication must be enabled on DB Servers.
- Basic understanding of version control using
git
- Basic understanding of shell scripting
To use sqlpackage
with git bash on windows, you'll need to run git bash as an administrator.
Figure 1: Basic architecture of a database
A visual studio database project is a special type of visual studio .NET
project. Its purpose is to create and manage SQL database scripts. In this case, it will be used to manage the metadata of a database. In the following sections, we'll learn how to manage VSDB projects and understand how *.dacpac
files work.
Figure 2: Proposed VSDB Project Structure
In the proposed VSDB project structure, the project is managed with a single repository. The repository will mainly consist of the following branches
Prod
Test
Feature
Figure 3: Code Repository
The Prod
branch keeps track of the PROD DB metadata, Test
keeps track of the TEST DB metadata, and Feature
keeps track of the feature database metadata. Typically, there will be one PROD DB, one TEST DB, and several FEATURE DBs. The feature DBs will be hosted locally on the developers machine using tools like Docker or SQL Server Express Edition. They will be used for experimentation and prototyping prior to the addition of new objects to the PROD DB.
In the following sections, we'll take a deep dive into how we can manage VSDB projects. We'll start by setting up our environment.
We'll be using docker to start SQL Server Express Edition. Alternatively, you can download the SQL Server Express Edition from here.
This will be used to host your local/feature databases. For the sake of this tutorial, the production database will also be hosted on the server.
docker pull mcr.microsoft.com/mssql/server:2022-latest
If you're running SQL Server with docker then start the server with the following command.
# Server 1
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=yourStrong(!)Password" -p 1434:1433 -d mcr.microsoft.com/mssql/server:2022-latest
SqlPackage is a command-line utility that automates database development tasks by exposing some of the public Data-Tier Application Framework (DacFx) APIs. We'll be installing SQLPackage with dotnet
.
docker pull ubuntu:latest
Start the container
docker run -d ubuntu bash -c "tail -f /dev/null"
Exec into ubuntu container
Exec
into the ubuntu container and install sqlpackage
with dotnet
.
# Install dotnet
sudo apt-get update
sudo apt-get install -y dotnet-sdk-7.0
# Install sqlpackage
dotnet tool install -g microsoft.sqlpackage
Go here to learn how to install dotnet
on windows.
dotnet tool install -g microsoft.sqlpackage
Run the Scripts/restoreDB/restore_db.sql
script to create a sample production database - productionDB
.
Let's add some features to our production database.
-
Open Visual Studio.
-
Under the Get started section, click on Create a new project.
-
Use the SQL Server Database Project template. In the search bar, type query to find the template and click on Next.
Figure 4: Create a new Visual Studio Database Project
-
Provide a project name in the project configuration and click on Create. In this tutorial, AzureSQLCICD is the project name.
Figure 5: Configure the project
-
In the Solution Explorer, right-click on the project, select import, and click on Database.
Figure 6: Import the productionDB schema
-
Set up the connection to the database and click on Connect.
Figure 7: Connection to the production database
-
Click on Start to commence the database import.
Figure 8: Commence database import
-
Upon successful completion, all the
productionDB
objects are displayed in the solution explorer.Figure 9: Production DB Schema objects
The productionDB
schema is now available in the Visual Studio Project. Before we add some new features to the productionDB
schema, let's push the schema to a remote.
-
In the solution explorer, click on Git Changes at the bottom of the screen.
-
Create a
git
repo -
Push the local changes to the remote. The repo name is AzureSQLCICD.
Figure 10: Create git repository
Create new ITVF in the production
schema.
-
Right-click on the
production
folder, click on Add, and then click on New Folder. Name the folder as ITVF (you can also use any name).Figure 11: Create a new folder in Production schema
-
In the
ITVF
folder, create a file calledtf_getProducts.sql
. Paste the following code in the file.Figure 12: New folder in production schema
create function production.tf_getProducts() returns @returnTable table ( product_id varchar(10), product_name varchar(500), brand_name varchar(500), category_name varchar(500), model_year varchar(200), list_price varchar(200) ) as begin insert @returnTable select products.product_id, products.product_name, brands.brand_name, categories.category_name, products.model_year, products.list_price from production.products products inner join production.brands brands on products.brand_id = brands.brand_id inner join production.categories categories on products.category_id = categories.category_id; return end;
Figure 13: New feature branch
-
Clone the repo from the remote.
git clone [email protected]:RashidCodes/Azure-SQL-CICD.git
-
Open Visual Studio.
-
Click on
File
, click onOpen
, and click onFolder
.Figure 14: New developer folder
-
Select the project folder;
AzureSQLCICD
. -
Right-click on the
*.sln
file and click on Open.Figure 15: Open solution file
The new developer can now add features to the project.
The first step in creating a new feature is the creation of a local feature database. This database will be used for prototyping/experimentation. See the setup/create_local_env
directory for more details.
Depending on the feature, the developer may or may not need data. For e.g. the creation of a sequence object does not require data whereas the creation of a view does. Where data is required, a *.dacpac
file containing the production database (productionDB
) metadata and data from selected tables has to be created and deployed to the feature database. If the creation of the feature does not require data, then visual studio can be used to publish the production database metadata to the feature database.
In the following sections, we'll learn how to create *.dacpac
files and publish them to a feature database.
A DACPAC - Data Tier Application Package is a zip file that contains a data model (database objects). A *.dacpac
file may contain database metadata only or database metadata and database data.
Figure 16: Composition of a dacpac file
In the instance where a developer needs to make changes to a stored procedure, view, etc. that uses a particular table, this command can be used.
sqlpackage /Action:Extract /TargetFile:sample_dpk.dacpac /DiagnosticsFile:sample_dpk_log.log /p:TableData='Person.Person' /p:VerifyExtraction=true /SourceServerName:host.docker.internal,1434 /SourceDatabaseName:${DATABASE} /SourceUser:sa /SourcePassword:sqlserver_password@3212 /sec:False
# Windows CMD
sqlpackage /Action:Extract /TargetFile:"C:\Users\user\source\repos\Azure-SQL-CICD\sample_dpk.dacpac" /DiagnosticsFile:"C:\Users\user\source\repos\Azure-SQL-CICD\sample_dpk_log.log" /p:TableData='Person.Person' /p:VerifyExtraction=true /SourceServerName:localhost,1434 /SourceDatabaseName:${DATABASE} /SourceUser:sa /SourcePassword:sqlserver_password@3212 /sec:False
#!/bin/bash
DATABASE="sample-database"
SERVER_NAME="rashid-is-awesome.database.windows.net"
SOURCE_USER="the-best-sql-adminstrator"
PASSWORD=""
# Extract the schema from the whole db
sqlpackage /Action:Extract /TargetFile:sample_dpk.dacpac /DiagnosticsFile:sample_dpk_log.log /p:VerifyExtraction=true /SourceServerName:${SERVER_NAME} /SourceDatabaseName:${DATABASE} /SourceUser:${SOURCE_USER} /SourcePassword:${PASSWORD} /sec:False;
# Windows CMD
sqlpackage /Action:Extract /TargetFile:"C:\Users\user\source\repos\Azure-SQL-CICD\sample_dpk.dacpac" /DiagnosticsFile:"C:\Users\user\source\repos\Azure-SQL-CICD\sample_dpk_log.log" /SourceConnectionString:"Server=localhost,1434;Initial Catalog=productionDB;User ID=sa;Password=yourStrong(!)Password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;
#/bin/bash
DATABASE="sample_database"
# Extract source db schema and data
sqlpackage /Action:Extract /TargetFile:sample_dpk.dacpac /DiagnosticsFile:sample_dpk_log.log /p:ExtractAllTableData=true /p:VerifyExtraction=true /SourceServerName:localhost,1434 /SourceDatabaseName:${DATABASE} /SourceUser:sa /SourcePassword:sqlserver_password@3212 /sec:False
# Windows CMD
sqlpackage /Action:Extract /TargetFile:"C:\Users\user\source\repos\Azure-SQL-CICD\sample_dpk.dacpac" /DiagnosticsFile:"C:\Users\user\source\repos\Azure-SQL-CICD\sample_dpk_log.log" /p:ExtractAllTableData=true /p:VerifyExtraction=true /SourceServerName:localhost,1434 /SourceDatabaseName:${DATABASE} /SourceUser:sa /SourcePassword:sqlserver_password@3212 /sec:False
# Windows CMD
sqlpackage /Action:Publish /SourceFile:"C:\Users\user\source\repos\Azure-SQL-CICD\sample_dpk.dacpac" /TargetConnectionString:'Server=tcp:localhost,1434;Initial Catalog=${DATABASE};User ID=sa Password="sqlserver_password@3212";Encrypt=False;TrustServerCertificate=False;Connection Timeout=30;';
Inspect destination server to make sure the Database and it's objects were created. The *.dacpac
file can be deployed to a feature database for development.
Now that we've learned how to create a *.dacpac
file, let's learn how to create a new feature. Make sure you have the productionDB
database project open in Visual Studio.
Inspect the Scripts/setup/create_local_env
directory to learn how to publish prod metadata and data to a local feature database.
After some experimentation in the feature_20221004_create_some_view
database, here's the code for the view
create view [production].[vw_productView]
as
select
products.product_id,
products.product_name,
brands.brand_name,
categories.category_name,
products.model_year,
products.list_price
from production.products products
inner join production.brands brands
on products.brand_id = brands.brand_id
inner join production.categories categories
on products.category_id = categories.category_id;
The view has been created in the feature_20221004_create_some_view
database. We can now use the schema comparison tool in VS to compare the schema of the feature database and the production database.
-
In Visual Studio, click on Tools, select SQL Server, and select New Schema Comparison.
Figure 17: Schema comparison
-
Select a source.
Figure 18: Select source
-
Click on the Database radio button and click on Select Connection....
Figure 19: Select source db connection
-
Click on the feature database -
feature_20221004_create_some_view
and click on Connect. -
Select a target.
Figure 20: Select target
-
Click on the Project radio button. In the drop menu below, select your project and click on OK.
Figure 21: Final schema comparison
-
Click on Compare
-
Inspect the new changes and click on the Update button to update the AzureSQLCICD VS Project to include the new view created in the feature database.
Figure 22: Schema comparison window
-
We can now see the view definition in the productionDB schema (in the solution explorer)
Figure 23: New product view
-
We are now ready to push our local changes to the metadata to a remote feature branch.
Figure 24: Deployment to UAT DB by merging a feature branch to the UAT branch
Figure 25: Deployment to Prod DB by merging UAT branch to PROD branch
Figure 26: Direct deployment to Test DB from Feature DB
Figure 27: Passwordless Cross DB Replication
The build fails unless the BlockOnPossibleDataLoss
parameter is set to false
sqlpackage /Action:Publish /SourceFile:sample_dpk.dacpac /TargetConnectionString:'Server=tcp:host.docker.internal,1435;Initial Catalog=sample_database;User ID=sa;Password="sqlserver_password@3212";Encrypt=False;TrustServerCertificate=False;Connection Timeout=30;' /p:BlockOnPossibleDataLoss=false;
The build fails; the object definition MUST be corrected.
Object types can be excluded by setting the ExcludeObjectTypes
parameter.
sqlpackage /Action:Publish /SourceFile:sample_dpk.dacpac /TargetConnectionString:'Server=tcp:host.docker.internal,1435;Initial Catalog=sample_database;User ID=sa;Password="sqlserver_password@3212";Encrypt=False;TrustServerCertificate=False;Connection Timeout=30;' /p:BlockOnPossibleDataLoss=false /p:ExcludeObjectType='ScalarValuedFunctions,SearchPropertyLists';
Yes, you can deploy a dacpac built from azure sql to sql server 2022.
https://github.com/kevchant/AzureDevOps-AzureSQLDatabase
https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage?view=sql-server-ver16
https://azureops.org/articles/dacpac-sql-server-database/
https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver16
https://dev.to/omiossec/using-azure-container-instance-with-multiple-containers-43fd
https://learn.microsoft.com/en-us/powershell/module/az.app/new-azcontainerapp?view=azps-10.4.1
https://github.com/microsoft/DacFx/blob/main/src/Microsoft.Build.Sql/docs/Tutorial.md