Perform automated deployment methods for resources
In this tutorial we will learn how to perform automated deployment methods for resources.
DACPAC
The simplest way for database deployment is to create data-tier package or DACPAC. DACPACs can be used to package and deploy schema changes as well as data. However, you can create a DACPAC using the SQL database project in Visual Studio.
SQL scripts
Instead of using a DACPAC, you can also use SQL scripts to deploy your database. For example, a 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
Azure service connection
The Azure SQL Database Deployment task is the primary mechanism to deploy a database to Azure. This task, as with other built-in Azure tasks, requires an Azure service connection as an input. Moreover, the Azure service connection stores the credentials to connect from Azure Pipelines or TFS to Azure.
Deploying conditionally
You may choose to deploy only certain builds to your Azure database.
Additional SQL actions
SQL Azure Dacpac Deployment may not support all SQL server actions that you want to perform. In these cases, you can simply use Powershell or command line scripts to run the commands you need. However, this section shows some of the common use cases for invoking the SqlPackage.exe tool. Further, as a prerequisite to running this tool, you must use a self-hosted agent and have the tool installed on your agent.
Basic Syntax
<Path of SQLPackage.exe> <Arguments to SQLPackage.exe>
You can use any of the following SQL scripts depending on the action that you want to perform
Extract
Creates a database snapshot (.dacpac) file from a live SQL server or Microsoft Azure SQL Database.
Command Syntax:
command
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. However, 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:
command
sqlpackage.exe /Action:Import /?
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