How to deploy a code-first Entity Framework database using Azure Devops pipelines to SQL server

Question

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.

So:

• How can I manually create and populate the database using an ASP.NET core 2.2 in-process application on a machine with no SDK installed?
• What do I need to add to the Azure DevOps pipeline to deploy the database to a local MSSQL server database

Answer 1

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.

myDbContext.Database.Migrate();

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.

Steps:

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:

$(System.ArtifactsDirectory)/_$(Build.DefinitionName)/drop/migrate.sql 

Refer this article for setting up CI pipeline. Refer this article for setting up CD pipeline.