We are building an ASP.net application using Entity Framework core with Azure DevOps and targeting IIS with MSSQL server 2016.
Our test server is a local Windows 2016 machine containing both IIS and the SQL server instance.
I have successfully written a deployment workflow enabling continuous deployment on that test server of the code itself but I can't find any way to deploy the database. The first version was targeting asp.net core 2.0 so I could use the command-line to start the application outside of IIS and trigger a database update but when we switched to 2.2 and in-process IIS deployment, we apparently lost that capability.
I would like to integrate the deployment of the database to the server in the deployment pipeline, including the DB creation, but I can't find any way to do so using Azure Devops: I can target an Azure SQL instance but, unless I'm missing something, not a local one.
EDIT: For deploying on local, I followed below two steps:
1. Create Database Script
You can create normal Create Database script which creates the database in the local database instance.
2. Apply Migrations
Create simple console application which runs the create database script first and then applies the migrations.
That's how I got it working.
Previous Contents about Publishing DB to Azure:
You need "Azure SQL Publish" task. Pre requisite is you should have Azure SQL Database already created.
Step1 : Command To generate migration script in build pipeline
Create a command line task to generate the migration script:
dotnet ef migrations script -i -o %BUILD_ARTIFACTSTAGINGDIRECTORY%\migrate.sql --project EfMigrationApp.Database\EfMigrationApp.Database.csproj --startup-project EfMigrationApp\EfMigrationApp.csproj -i -o %BUILD_ARTIFACTSTAGINGDIRECTORY%\migrate.sql
Step 2: Azure SQL Publish in release pipeline
Action: Publish Type: SQL script file Sql script should be as below: