7

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
Improve this question
3
  • I think this is more on-topic on ServerFault. Commented Jan 19, 2019 at 19:49
  • I don't know: it's devops so: is it more dev or more operations? The dev team is the one responsible for the pipeline, in my case. Commented Jan 21, 2019 at 8:03
  • DevOps questions should go on Software Engineering then. But most people question here on this subject. Commented May 5, 2019 at 8:30

1 Answer 1

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you for your answer but you apparently didn't read my question properly: I'm not trying to deploy to SQL Azure, I'm trying to deploy a local SQL server instance
The only relevant difference between SQL Azure and a local instance is the server part of the connectionstring. So the answer provided above will work for you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.