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

In this tutorial, you'll learn how to deploy a data-driven ASP.NET Core app to Azure App Service and connect to an Azure SQL Database. You'll also deploy an Azure Cache for Redis to enable the caching code in your application. 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 7.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, database, and cache

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, Azure SQL Database, and Azure Cache. 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>.chinacloudsites.cn.
  • 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 7 (STS).
  5. Add Azure Cache for Redis?Yes.
  6. Hosting planBasic. When you're ready, you can scale up to a production pricing tier later.
  7. 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.
  8. Select Review + create.
  9. 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 endpoints → Access endpoints for the database server and the Redis cache in the virtual network.
  • Network interfaces → Represents private IP addresses, one for each of the private endpoints.
  • Azure SQL Database server → Accessible only from behind its private endpoint.
  • Azure SQL Database → A database and a user are created for you on the server.
  • Azure Cache for Redis → Accessible only from behind its private endpoint.
  • Private DNS zones → Enable DNS resolution of the database server and the Redis cache in the virtual network.

2. Verify connection strings

The creation wizard generated connection strings for the SQL database and the Redis cache already. In this step, find the generated connection strings 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 find AZURE_SQL_CONNECTIONSTRING in the Connection strings section. This string was generated from the new SQL database by the creation wizard. To set up your application, this name is all you need.
  2. Also, find AZURE_REDIS_CONNECTIONSTRING in the Application settings section. This string was generated from the new Redis cache by the creation wizard. To set up your application, this name is all you need.
  3. If you want, you can select the Edit button to the right of each setting and see or copy its value. Later, you'll change your application to use AZURE_SQL_CONNECTIONSTRING and AZURE_REDIS_CONNECTIONSTRING.

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 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 2: In the App Service page, in the left menu, select Deployment Center.

Step 3: 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 4: Back the GitHub page of the forked sample, 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 AZURE_SQL_CONNECTIONSTRING, which matches the connection string created in App Service earlier.

Step 6:

  1. Open DotNetCoreSqlDb/Program.cs in the explorer.
  2. In the options.UseSqlServer method, change the connection string name MyDbConnection to AZURE_SQL_CONNECTIONSTRING. This is where the connection string is used by the sample application.
  3. Remove the builder.Services.AddDistributedMemoryCache(); method and replace it with the following code. It changes your code from using an in-memory cache to the Redis cache in Azure, and it does so by using AZURE_REDIS_CONNECTIONSTRING from earlier.
builder.Services.AddStackExchangeRedisCache(options =>
{
options.Configuration = builder.Configuration["AZURE_REDIS_CONNECTIONSTRING"];
options.InstanceName = "SampleInstance";
});

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 --version 7.0.14.
  3. Under the new step, add another step to generate a database migration bundle in the deployment package: dotnet ef migrations bundle --runtime linux-x64 -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 Configure DB & Redis & 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 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>.chinacloudsites.cn.

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.

Tip

The sample application implements the cache-aside pattern. When you visit a data view for the second time, or reload the same page after making data changes, Processing time in the webpage shows a much faster time because it's loading the data from the cache instead of the database.

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 Azure Cache for Redis is created in Basic tier with the minimum cache size. There's a small cost associated with this tier. You can scale it up to higher performance tiers for higher availability, clustering, and other features. See Azure Cache for Redis 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

How do I debug errors during the GitHub Actions deployment?

If a step fails in the autogenerated GitHub workflow file, try modifying the failed command to generate more verbose output. For example, you can get more output from any of the dotnet commands by adding the -v option. Commit and push your changes to trigger another deployment to App Service.

Next steps

Advance to the next tutorial to learn how to secure your app with a custom domain and certificate.

Or, check out other resources: