Azure DevOps/VSTS database and production bug fixes

Question

We are currently in the process of setting up VSTS CI/CD to an azure webapp with 3 different stages. Production, Beta, Alpha. But we are facing two issues:

• 1. Database deployment, We are using Azure SQL Server but deploying the database in the pipeline is a problem. The best solution I have so far is to use a Database Project in VS housing the DDL but this means developers must work on the database through VS and doesn't work as well as SSMS. Are their any alternates?
• 2. Production bug fixes. If we require hot-fixes on the production environment then the team breakdowns the pipeline (deploying directly into the production project), as it takes too long to swap the environments and check if everything is okay. Is there a way to deploy change sets directly to the production and mark it as an hot-fix deployment? Or any suggestions?

Answer 1

1 - VSTS supports Azure SQL database deployment using DACBAC or SQL scripts, either can be generated from SQL Server Management Studio.

See:

• https://docs.microsoft.com/en-au/vsts/pipelines/apps/cd/deploy-dacpac-sqlpackage?view=vsts
• https://docs.microsoft.com/en-au/vsts/pipelines/apps/cd/deploy-database-sqlscripts?view=vsts

2 - You could consider:

• Using build tags to trigger new release process that deploys directly production, by tagging a build with something like direct-to-prod and then setting on the deployment trigger. https://docs.microsoft.com/en-au/vsts/pipelines/release/triggers?view=vsts.
• Using a separate environment in your existing release that has a manual trigger. i.e. you need to run it manually.

Also calling out that maybe it is a good opportunity to review the currently process build/release process. Ultimately the staged environments you have are there to protect against bugs making it into production. Maybe further automation can make the process faster so that there is not a timing concern going through staged environments.