Automated Deployment of SQL Server Database through Azure Devops

Problem Statement

Is it possible to have an automated deployment mechanism for the SQL server database through Azure DevOps?

Prerequisites

1. Server/Virtual machine able to access the SQL database
2. DACPAC file

Solution

1. Create a Self Hosted Agent in Azure DevOps mapping to the Server (from which one can access the SQL database).
   Steps for Setting up a Self-hosted Agent.
2. Once an agent is configured, we need to provide the server with the below level of access to deploy the database.
3. Server admin access on the SQL database server

   USE [master]
   GO
   CREATE LOGIN [domain\<<VMName>>] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
   GO
   ALTER SERVER ROLE [serveradmin] ADD MEMBER [domain\ <<VMName>> ]
   GO

4. Create a Release Pipeline in Azure Pipelines  set the Build Agent to the Self Hosted Agent and finally add the SQL Server Database deploy task with the below configurations:

YAML

Steps

• Task: SqlDacpacDeploymentOnMachineGroup@0
• displayName: ‘SQL Server Database Deployment’

inputs

• DacpacFile: ‘$(DACPAC Path)’
• ServerName: ‘$(Server Name)’
• DatabaseName: ‘$(Database Name)’

There is also an SQL Server authentication type supported for SQL Server database deployment but Windows Authentication is a much more secure way for deployment.