I’m looking for the proper workflow to deploy an SQL Server without any manual action.

I have a folder where I describe how an SQL Server’s Database should look like, by that I mean .sql files which create tables, roles, etc.

At this point, we have a .sln file generated by Visual Studio, which is not used for any other reason than building a solution file .sln which build a .dacpac, so ideally, I ‘d like to get rid of it.

I want to use the Azure Data Studio to generate an .sqlproj file which contains info about what folders contain the .sql files that need to be used in order to build the .dacpac file and then deploy it to a sql database.

The problem is that if you decide to add a folder with new tables you need to re-build the .sln (or, is that possible: run the .sql scripts (as queries) against a dev database in order to be registered by the database and these changes to be reflected in the .sqlproj file when it is built by the corresponding pipeline step.)

Pipeline looks like that (Build and Deployment of DACPAC via Azure DevOps Pipeline without .sln (or any artifact)):

variables:
  ARMSvcCnnName: YourAzureResourceManagerServiceConnectionName
  AzureSQLServerName: YourAzureSQLSeverName
  AzureSQLDBName: YourAzureSQLDBName
  buildPlatform: 'Any CPU'
  buildConfiguration: 'Release'

pool:
  vmImage: 'windows-latest'

steps:
- task: VSBuild@1
  displayName: 'Build DACPAC from SQL Database Project'
  inputs:
    solution: '***.sqlproj'
    platform: '$(buildPlatform)'
    configuration: '$(buildConfiguration)'
- task: SqlAzureDacpacDeployment@1
  displayName: 'Deploy Azure SQL DB'
  inputs:
    azureSubscription: '$(ARMSvcCnnName)'
    AuthenticationType: 'servicePrincipal'
    ServerName: '$(AzureSQLServerName).database.windows.net'
    DatabaseName: '$(AzureSQLDBName)'
    deployType: 'DacpacTask'
    DeploymentAction: 'Publish'
    DacpacFile: '***.dacpac'
    AdditionalArguments: '/v:ETLUserPassword="$(sql-password)" /v:AppUserPassword="$(sql-password)"'
    IpDetectionMethod: 'AutoDetect'
    DeleteFirewallRule: false

At this point we need to build the solution to get the updates which I like to get rid of.

So, as I see it up to now:

Let’s say I have an empty db and I have created an .sqlproj using:

1. Empty Azure Database
2. Generate .sqlproj file using Azure Data Studio
3. Place it in my repo
4. Trigger the pipeline (finds .sqlproj and deploys an empty database to the empty database`)

Ideal way of working:

1. Edit folder in the repo
2. Pipeline finds out about the changes
3. Generates a new .sqlproj
4. Builds .sqlproj
5. Deploy to the Azure Sql Database