Tutorial: Deploy an ASP.NET Core and Azure SQL Database app to Azure App Service

In this tutorial, you'll learn how to deploy an ASP.NET Core app to Azure App Service and connect to an Azure SQL Database. Azure App Service is a highly scalable, self-patching, web-hosting service that can easily deploy apps on Windows or Linux. Although this tutorial uses an ASP.NET Core 6.0 app, the process is the same for other versions of ASP.NET Core and ASP.NET Framework.

This tutorial requires:

Sample application

To explore the sample application used in this tutorial, download it from the repository https://github.com/Azure-Samples/msdocs-app-service-sqldb-dotnetcore or clone it using the following Git command:

git clone https://github.com/Azure-Samples/msdocs-app-service-sqldb-dotnetcore.git
cd msdocs-app-service-sqldb-dotnetcore

1. Create App Service and Azure SQL Database

In this step, you create the Azure resources. The steps used in this tutorial create a set of secure-by-default resources that include App Service and Azure SQL Database. For the creation process, you'll specify:

  • The Name for the web app. It's the name used as part of the DNS name for your webapp in the form of https://<app-name>.azurewebsites.net.
  • The Region to run the app physically in the world.
  • The Runtime stack for the app. It's where you select the .NET version to use for your app.
  • The Hosting plan for the app. It's the pricing tier that includes the set of features and scaling capacity for your app.
  • The Resource Group for the app. A resource group lets you group (in a logical container) all the Azure resources needed for the application.

Sign in to the Azure portal and follow these steps to create your Azure App Service resources.

Step 1. In the Azure portal:

  1. Enter "web app database" in the search bar at the top of the Azure portal.
  2. Select the item labeled Web App + Database under the Marketplace heading. You can also navigate to the creation wizard directly.

Step 2. In the Create Web App + Database page, fill out the form as follows.

  1. Resource Group → Select Create new and use a name of msdocs-core-sql-tutorial.
  2. Region → Any Azure region near you.
  3. Namemsdocs-core-sql-XYZ where XYZ is any three random characters. This name must be unique across Azure.
  4. Runtime stack.NET 6 (LTS).
  5. Hosting planBasic. When you're ready, you can scale up to a production pricing tier later.
  6. SQLAzure is selected by default as the database engine. Azure SQL Database is a fully managed platform as a service (PaaS) database engine that's always running on the latest stable version of the SQL Server.
  7. Select Review + create.
  8. After validation completes, select Create.

Step 3. The deployment takes a few minutes to complete. Once deployment completes, select the Go to resource button. You're taken directly to the App Service app, but the following resources are created:

  • Resource group → The container for all the created resources.
  • App Service plan → Defines the compute resources for App Service. A Linux plan in the Basic tier is created.
  • App Service → Represents your app and runs in the App Service plan.
  • Virtual network → Integrated with the App Service app and isolates back-end network traffic.
  • Private endpoint → Access endpoint for the database server in the virtual network.
  • Network interface → Represents a private IP address for the private endpoint.
  • Azure SQL Database server → Accessible only from behind the private endpoint.
  • Azure SQL Database → A database and a user are created for you on the server.
  • Private DNS zone → Enables DNS resolution of the database server in the virtual network.

2. Verify database connectivity

The creation wizard generated a connection string for you already. In this step, find the generated connection string for later.

Step 1. In the App Service page, in the left menu, select Configuration.

Step 2.

  1. Scroll to the bottom of the page and select the connection string defaultConnection. It was generated by the creation wizard. To set up your application, this name is all you need.
  2. If you want, you can select the Copy button copy the Value field.
  3. Select Cancel. Later, you'll change your application to use this defaultConnection connection string.

3. Deploy sample code

In this step, you'll configure GitHub deployment using GitHub Actions. It's just one of many ways to deploy to App Service, but also a great way to have continuous integration in your deployment process. By default, every git push to your GitHub repository will kick off the build and deploy action.

Step 1. In the App Service page, in the left menu, select Deployment Center.

Step 2. In the Deployment Center page:

  1. In Source, select GitHub. By default, GitHub Actions is selected as the build provider.
  2. Sign in to your GitHub account and follow the prompt to authorize Azure.
  3. In Organization, select your account.
  4. In Repository, select msdocs-app-service-sqldb-dotnetcore.
  5. In Branch, select main.
  6. In the top menu, select Save. App Service commits a workflow file into the chosen GitHub repository, in the .github/workflows directory.

Step 3. In a new browser window:

  1. Sign in to your GitHub account.
  2. Navigate to https://github.com/Azure-Samples/msdocs-app-service-sqldb-dotnetcore.
  3. Select Fork.
  4. Select Create fork.

Step 4. In the GitHub page, open Visual Studio Code in the browser by pressing the . key.

Step 5. In Visual Studio Code in the browser:

  1. Open DotNetCoreSqlDb/appsettings.json in the explorer.
  2. Change the connection string name MyDbConnection to defaultConnection, which matches the connection string created in App Service earlier.

Step 6.

  1. Open DotNetCoreSqlDb/Startup.cs in the explorer.
  2. In the options.UseSqlServer method, change the connection string name MyDbConnection to defaultConnection. This is where the connection string is used by the sample application.

Step 7.

  1. Open .github/workflows/main_msdocs-core-sql-XYZ in the explorer. This file was created by the App Service create wizard.
  2. Under the dotnet publish step, add a step to install the Entity Framework Core tool with the command dotnet tool install -g dotnet-ef.
  3. Under the new step, add another step to generate a database migration bundle in the deployment package: dotnet ef migrations bundle -p DotNetCoreSqlDb/DotNetCoreSqlDb.csproj -o ${{env.DOTNET_ROOT}}/myapp/migrate. The migration bundle is a self-contained executable that you can run in the production environment without needing the .NET SDK. The App Service linux container only has the .NET runtime and not the .NET SDK.

Step 8.

  1. Select the Source Control extension.
  2. In the textbox, type a commit message like change connection string name & add migration bundle.
  3. Select Commit and Push.

Step 9. Back in the Deployment Center page in the Azure portal:

  1. Select Logs. A new deployment run is already started from your committed changes.
  2. In the log item for the deployment run, select the Build/Deploy Logs entry with the latest timestamp.

Step 10. You're taken to your GitHub repository and see that the GitHub action is running. The workflow file defines two separate stages, build and deploy. Wait for the GitHub run to show a status of Complete. It takes a few minutes.

4. Generate database schema

With the SQL Database protected by the virtual network, the easiest way to run Run dotnet database migrations is in an SSH session with the App Service container.

Step 1. Back in the App Service page, in the left menu, select SSH.

Step 2. In the SSH terminal:

  1. Run cd /home/site/wwwroot. Here are all your deployed files.
  2. Run the migration bundle that's generated by the GitHub workflow with ./migrate. If it succeeds, App Service is connecting successfully to the SQL Database. Only changes to files in /home can persist beyond app restarts. Changes outside of /home aren't persisted.

5. Browse to the app

Step 1. In the App Service page:

  1. From the left menu, select Overview.
  2. Select the URL of your app. You can also navigate directly to https://<app-name>.azurewebsites.net.

Step 2. Add a few tasks to the list. Congratulations, you're running a secure data-driven ASP.NET Core app in Azure App Service.

6. Stream diagnostic logs

Azure App Service captures all messages logged to the console to assist you in diagnosing issues with your application. The sample app outputs console log messages in each of its endpoints to demonstrate this capability.

Step 1. In the App Service page:

  1. From the left menu, select App Service logs.
  2. Under Application logging, select File System.

Step 2. From the left menu, select Log stream. You see the logs for your app, including platform logs and logs from inside the container.

7. Clean up resources

When you're finished, you can delete all of the resources from your Azure subscription by deleting the resource group.

Step 1. In the search bar at the top of the Azure portal:

  1. Enter the resource group name.
  2. Select the resource group.

Step 2. In the resource group page, select Delete resource group.

Step 3.

  1. Enter the resource group name to confirm your deletion.
  2. Select Delete.

A screenshot of the confirmation dialog for deleting a resource group in the Azure portal. :

Frequently asked questions

How much does this setup cost?

Pricing for the create resources is as follows:

  • The App Service plan is created in Basic tier and can be scaled up or down. See App Service pricing.
  • The Azure SQL Database is created in general-purpose, serverless tier on Standard-series hardware with the minimum cores. There's a small cost and can be distributed to other regions. You can minimize cost even more by reducing its maximum size, or you can scale it up by adjusting the serving tier, compute tier, hardware configuration, number of cores, database size, and zone redundancy. See Azure SQL Database pricing.
  • The virtual network doesn't incur a charge unless you configure extra functionality, such as peering. See Azure Virtual Network pricing.
  • The private DNS zone incurs a small charge. See Azure DNS pricing.

How do I connect to the Azure SQL Database server that's secured behind the virtual network with other tools?

  • For basic access from a command-line tool, you can run sqlcmd from the app's SSH terminal. The app's container doesn't come with sqlcmd, so you must install it manually. Remember that the installed client doesn't persist across app restarts.
  • To connect from a SQL Server Management Studio client or from Visual Studio, your machine must be within the virtual network. For example, it could be an Azure VM that's connected to one of the subnets, or a machine in an on-premises network that has a site-to-site VPN connection with the Azure virtual network.

How does local app development work with GitHub Actions?

Take the autogenerated workflow file from App Service as an example, each git push kicks off a new build and deployment run. From a local clone of the GitHub repository, you make the desired updates push it to GitHub. For example:

git add .
git commit -m "<some-message>"
git push origin main

Next steps

Advance to the next tutorial to learn how to map a custom DNS name to your app.

Or, check out other resources: