Evaluate requirements for the database deployment
In this we will learn about the requirements for database deployment. However, you can automatically deploy your database updates to the Azure SQL database after every successful build.
DACPAC
The simplest way for database deployment is to create a data-tier package or DACPAC. DACPACs may be used to package and deliver data as well as schema updates. The SQL database project in Visual Studio may be used to construct a DACPAC.
SQL scripts
Instead of using a DACPAC, you can also use SQL scripts to deploy your database. For example, check the SQL script that creates an empty database.
SQL
USE [main]GO
IF NOT EXISTS (SELECT name FROM main.sys.databases WHERE name = N’DatabaseExample’)
CREATE DATABASE [DatabaseExample]GO
Further, to run SQL scripts as part of a pipeline, you will need Azure Powershell scripts to create and remove firewall rules in Azure. The Azure Pipelines agent cannot communicate with Azure SQL Database without firewall rules.
Azure service connection
The primary tool for deploying a database to Azure is the Azure SQL Database Deployment job. An Azure service connection is required as an input for this activity, as it is for all other built-in Azure activities. Further, the Azure service connection stores the credentials to connect from Azure Pipelines or TFS to Azure.
However, being logged in as a user who owns both the Azure DevOps organization and the Azure subscription is the simplest approach to get started with this activity. You won’t have to manually build the service connection in this situation.
Deploying conditionally
You may choose to deploy only certain builds to your Azure database.
Additional SQL actions
All SQL server tasks that you wish to conduct may not be supported by SQL Azure Dacpac Deployment. In these circumstances, you may simply perform the instructions using Powershell or command line scripts. However, you must utilise a self-hosted agent and have the tool loaded on your agent in order to use this tool.
Extract
Creates a database snapshot (.dacpac) file from a live SQL server or Microsoft Azure SQL Database.
Command Syntax:
SqlPackage.exe /TargetFile:”” /Action:Extract
/SourceServerName:”.database.windows.net”
/SourceDatabaseName:”” /SourceUser:”” /SourcePassword:””
Example:
command
SqlPackage.exe /TargetFile:”C:\temp\test.dacpac” /Action:Extract /SourceServerName:”DemoSqlServer.database.windows.net”
/SourceDatabaseName:”Testdb” /SourceUser:”ajay” /SourcePassword:”SQLPassword”
Help:
command
sqlpackage.exe /Action:Extract /?
Publish
Incrementally updates a database schema to match the schema of a source .dacpac file. Moreover, if the database does not exist on the server, the publish operation will create it. Otherwise, an existing database will be updated.
Command Syntax:
command
SqlPackage.exe /SourceFile:”” /Action:Publish /TargetServerName:”.database.windows.net”
/TargetDatabaseName:”” /TargetUser:”” /TargetPassword:” “
Example:
command
SqlPackage.exe /SourceFile:”E:\dacpac\ajyadb.dacpac” /Action:Publish /TargetServerName:”DemoSqlServer.database.windows.net”
/TargetDatabaseName:”Testdb4″ /TargetUser:”ajay” /TargetPassword:”SQLPassword”
Help:
command
sqlpackage.exe /Action:Publish /?
Export
Exports a live database, including database schema and user data, from SQL Server or Microsoft Azure SQL Database to a BACPAC package (.bacpac file).
Command Syntax:
command
SqlPackage.exe /TargetFile:”” /Action:Export /SourceServerName:”.database.windows.net”
/SourceDatabaseName:”” /SourceUser:”” /SourcePassword:””
Example:
command
SqlPackage.exe /TargetFile:”C:\temp\test.bacpac” /Action:Export /SourceServerName:”DemoSqlServer.database.windows.net”
/SourceDatabaseName:”Testdb” /SourceUser:”ajay” /SourcePassword:”SQLPassword”
Help:
command
sqlpackage.exe /Action:Export /?
Import
Imports the schema and table data from a BACPAC package into a new user database in an instance of SQL Server or Microsoft Azure SQL Database.
Command Syntax:
command
SqlPackage.exe /SourceFile:”” /Action:Import /TargetServerName:”.database.windows.net”
/TargetDatabaseName:”” /TargetUser:”” /TargetPassword:””
Example:
command
SqlPackage.exe /SourceFile:”C:\temp\test.bacpac” /Action:Import /TargetServerName:”DemoSqlServer.database.windows.net”
/TargetDatabaseName:”Testdb” /TargetUser:”ajay” /TargetPassword:”SQLPassword”
Help:
sqlpackage.exe /Action:Import /?
DriftReport
Creates an XML report of the changes that have been made to a registered database since it was last registered.
Command Syntax:
command
SqlPackage.exe /Action:DriftReport /TargetServerName:”.database.windows.net” /TargetDatabaseName:””
/TargetUser:”” /TargetPassword:”” /OutputPath:””
Example:
command
SqlPackage.exe /Action:DriftReport /TargetServerName:”DemoSqlServer.database.windows.net” /TargetDatabaseName:”Testdb”
/TargetUser:”ajay” /TargetPassword:”SQLPassword” /OutputPath:”C:\temp\driftReport.xml”
Help:
command
sqlpackage.exe /Action:DriftReport /?
Script
Creates a Transact-SQL incremental update script that updates the schema of a target to match the schema of a source.
Command Syntax:
command
SqlPackage.exe /SourceFile:”” /Action:Script /TargetServerName:”.database.windows.net”
/TargetDatabaseName:”” /TargetUser:”” /TargetPassword:”” /OutputPath:””
Example:
command
SqlPackage.exe /Action:Script /SourceFile:”E:\dacpac\ajyadb.dacpac” /TargetServerName:”DemoSqlServer.database.windows.net”
/TargetDatabaseName:”Testdb” /TargetUser:”ajay” /TargetPassword:”SQLPassword” /OutputPath:”C:\temp\test.sql”
/Variables:StagingDatabase=”Staging DB Variable value”
Help:
command
sqlpackage.exe /Action:Script /?
Reference: Microsoft Documentation