Automated Deployment of SQL Server Database through Azure Devops

Problem Statement :

Is it possible to have an automated deployment mechanism for 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 :

https://docs.microsoft.com/en-us/azure/devops/pipelines/agents/v2-windows?view=azure-devops

2. Once an agent is configured , we need to provide the server with below level of access to deploy database .

a) 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

So in case if the name of the server on which we installed the Self Hosted Agent is Datasharkx under domain wordpress , then Login would be :

wordpress\Datasharkx$

3.Create a Release Pipeline in Azure Pipelines and 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 SQL Server authentication type supported for SQL Server database deployment but Windows Authentication is a much secure way for deployment.