Tutorial: Build an ASP.NET Core and Azure SQL Database app in Azure App Service

Azure App Service provides a highly scalable, self-patching web hosting service in Azure. This tutorial shows how to create a .NET Core app and connect it to SQL Database. When you're done, you'll have a .NET Core MVC app running in App Service on Windows.

Azure App Service provides a highly scalable, self-patching web hosting service using the Linux operating system. This tutorial shows how to create a .NET Core app and connect it to a SQL Database. When you're done, you'll have a .NET Core MVC app running in App Service on Linux.

app running in App Service

In this tutorial, you learn how to:

  • Create a SQL Database in Azure
  • Connect a .NET Core app to SQL Database
  • Deploy the app to Azure
  • Update the data model and redeploy the app
  • Stream diagnostic logs from Azure
  • Manage the app in the Azure portal

If you don't have an Azure subscription, create a free account before you begin.

Prerequisites

To complete this tutorial:

Create local .NET Core app

In this step, you set up the local .NET Core project.

Clone the sample application

In the terminal window, cd to a working directory.

Run the following commands to clone the sample repository and change to its root.

git clone https://github.com/azure-samples/dotnetcore-sqldb-tutorial
cd dotnetcore-sqldb-tutorial

The sample project contains a basic CRUD (create-read-update-delete) app using Entity Framework Core.

Run the application

Run the following commands to install the required packages, run database migrations, and start the application.

dotnet tool install -g dotnet-ef
dotnet ef database update
dotnet run

Navigate to http://localhost:5000 in a browser. Select the Create New link and create a couple to-do items.

connects successfully to SQL Database

To stop .NET Core at any time, press Ctrl+C in the terminal.

Use Azure Cloud Shell

Azure hosts Azure Cloud Shell, an interactive shell environment that you can use through your browser. You can use either Bash or PowerShell with Cloud Shell to work with Azure services. You can use the Cloud Shell preinstalled commands to run the code in this article without having to install anything on your local environment.

To start Azure Cloud Shell:

Option Example/Link
Select Try It in the upper-right corner of a code block. Selecting Try It doesn't automatically copy the code to Cloud Shell. Example of Try It for Azure Cloud Shell
Go to https://shell.azure.com, or select the Launch Cloud Shell button to open Cloud Shell in your browser. Launch Cloud Shell in a new window
Select the Cloud Shell button on the menu bar at the upper right in the Azure portal. Cloud Shell button in the Azure portal

To run the code in this article in Azure Cloud Shell:

  1. Start Cloud Shell.

  2. Select the Copy button on a code block to copy the code.

  3. Paste the code into the Cloud Shell session by selecting Ctrl+Shift+V on Windows and Linux or by selecting Cmd+Shift+V on macOS.

  4. Select Enter to run the code.

Create production SQL Database

In this step, you create a SQL Database in Azure. When your app is deployed to Azure, it uses this cloud database.

For SQL Database, this tutorial uses Azure SQL Database.

Create a resource group

A resource group is a logical container into which Azure resources, such as web apps, databases, and storage accounts, are deployed and managed. For example, you can choose to delete the entire resource group in one simple step later.

In the Cloud Shell, create a resource group with the az group create command. The following example creates a resource group named myResourceGroup in the West Europe location. To see all supported locations for App Service in Free tier, run the az appservice list-locations --sku FREE command.

az group create --name myResourceGroup --location "West Europe"

You generally create your resource group and the resources in a region near you.

When the command finishes, a JSON output shows you the resource group properties.

Create a SQL Database logical server

In the Cloud Shell, create a SQL Database logical server with the az sql server create command.

Replace the <server-name> placeholder with a unique SQL Database name. This name is used as the part of the globally unique SQL Database endpoint, <server-name>.database.windows.net. Valid characters are a-z, 0-9, -. Also, replace <db-username> and <db-password> with a username and password of your choice.

az sql server create --name <server-name> --resource-group myResourceGroup --location "West Europe" --admin-user <db-username> --admin-password <db-password>

When the SQL Database logical server is created, the Azure CLI shows information similar to the following example:

{
  "administratorLogin": "<db-username>",
  "administratorLoginPassword": null,
  "fullyQualifiedDomainName": "<server-name>.database.windows.net",
  "id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/myResourceGroup/providers/Microsoft.Sql/servers/<server-name>",
  "identity": null,
  "kind": "v12.0",
  "location": "westeurope",
  "name": "<server-name>",
  "resourceGroup": "myResourceGroup",
  "state": "Ready",
  "tags": null,
  "type": "Microsoft.Sql/servers",
  "version": "12.0"
}

Configure a server firewall rule

Create an Azure SQL Database server-level firewall rule using the az sql server firewall create command. When both starting IP and end IP are set to 0.0.0.0, the firewall is only opened for other Azure resources.

az sql server firewall-rule create --resource-group myResourceGroup --server <server-name> --name AllowAzureIps --start-ip-address 0.0.0.0 --end-ip-address 0.0.0.0

Tip

You can be even more restrictive in your firewall rule by using only the outbound IP addresses your app uses.

In the Cloud Shell, run the command again to allow access from your local computer by replacing <your-ip-address> with your local IPv4 IP address.

az sql server firewall-rule create --name AllowLocalClient --server <server-name> --resource-group myResourceGroup --start-ip-address=<your-ip-address> --end-ip-address=<your-ip-address>

Create a database

Create a database with an S0 performance level in the server using the az sql db create command.

az sql db create --resource-group myResourceGroup --server <server-name> --name coreDB --service-objective S0

Create connection string

Get the connection string using the az sql db show-connection-string command.

az sql db show-connection-string --client ado.net --server <server-name> --name coreDB

In the command output, replace <username>, and <password> with the database administrator credentials you used earlier.

This is the connection string for your .NET Core app. Copy it for use later.

Configure app to connect to production database

In your local repository, open Startup.cs and find the following code:

services.AddDbContext<MyDatabaseContext>(options =>
        options.UseSqlite("Data Source=localdatabase.db"));

Replace it with the following code.

services.AddDbContext<MyDatabaseContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("MyDbConnection")));

Important

For production apps that need to scale out, follow the best practices in Applying migrations in production.

Run database migrations to the production database

Your app currently connects to a local Sqlite database. Now that you configured an Azure SQL Database, recreate the initial migration to target it.

From the repository root, run the following commands. Replace <connection-string> with the connection string you created earlier.

# Delete old migrations
rm Migrations -r
# Recreate migrations
dotnet ef migrations add InitialCreate

# Set connection string to production database
# PowerShell
$env:ConnectionStrings:MyDbConnection="<connection-string>"
# CMD (no quotes)
set ConnectionStrings:MyDbConnection=<connection-string>
# Bash (no quotes)
export ConnectionStrings__MyDbConnection=<connection-string>

# Run migrations
dotnet ef database update

Run app with new configuration

Now that database migrations is run on the production database, test your app by running:

dotnet run

Navigate to http://localhost:5000 in a browser. Select the Create New link and create a couple to-do items. Your app is now reading and writing data to the production database.

Commit your local changes, then commit it into your Git repository.

git add .
git commit -m "connect to SQLDB in Azure"

You're now ready to deploy your code.

Deploy app to Azure

In this step, you deploy your SQL Database-connected .NET Core application to App Service.

Configure local git deployment

FTP and local Git can deploy to an Azure web app by using a deployment user. Once you configure your deployment user, you can use it for all your Azure deployments. Your account-level deployment username and password are different from your Azure subscription credentials.

To configure the deployment user, run the az webapp deployment user set command in Azure Cloud Shell. Replace <username> and <password> with a deployment user username and password.

  • The username must be unique within Azure, and for local Git pushes, must not contain the ‘@’ symbol.
  • The password must be at least eight characters long, with two of the following three elements: letters, numbers, and symbols.
az webapp deployment user set --user-name <username> --password <password>

The JSON output shows the password as null. If you get a 'Conflict'. Details: 409 error, change the username. If you get a 'Bad Request'. Details: 400 error, use a stronger password.

Record your username and password to use to deploy your web apps.

Create an App Service plan

In the Cloud Shell, create an App Service plan with the az appservice plan create command.

The following example creates an App Service plan named myAppServicePlan in the Free pricing tier:

az appservice plan create --name myAppServicePlan --resource-group myResourceGroup --sku FREE

When the App Service plan has been created, the Azure CLI shows information similar to the following example:

{ 
  "adminSiteName": null,
  "appServicePlanName": "myAppServicePlan",
  "geoRegion": "West Europe",
  "hostingEnvironmentProfile": null,
  "id": "/subscriptions/0000-0000/resourceGroups/myResourceGroup/providers/Microsoft.Web/serverfarms/myAppServicePlan",
  "kind": "app",
  "location": "West Europe",
  "maximumNumberOfWorkers": 1,
  "name": "myAppServicePlan",
  < JSON data removed for brevity. >
  "targetWorkerSizeId": 0,
  "type": "Microsoft.Web/serverfarms",
  "workerTierName": null
} 

In the Cloud Shell, create an App Service plan in the resource group with the az appservice plan create command.

The following example creates an App Service plan named myAppServicePlan in the Free pricing tier (--sku F1) and in a Linux container (--is-linux).

az appservice plan create --name myAppServicePlan --resource-group myResourceGroup --sku F1 --is-linux

When the App Service plan has been created, the Azure CLI shows information similar to the following example:

{ 
  "adminSiteName": null,
  "appServicePlanName": "myAppServicePlan",
  "geoRegion": "West Europe",
  "hostingEnvironmentProfile": null,
  "id": "/subscriptions/0000-0000/resourceGroups/myResourceGroup/providers/Microsoft.Web/serverfarms/myAppServicePlan",
  "kind": "linux",
  "location": "West Europe",
  "maximumNumberOfWorkers": 1,
  "name": "myAppServicePlan",
  <JSON data removed for brevity.>
  "targetWorkerSizeId": 0,
  "type": "Microsoft.Web/serverfarms",
  "workerTierName": null
} 

Create a web app

Create a web app in the myAppServicePlan App Service plan.

In the Cloud Shell, you can use the az webapp create command. In the following example, replace <app-name> with a globally unique app name (valid characters are a-z, 0-9, and -).

az webapp create --resource-group myResourceGroup --plan myAppServicePlan --name <app-name> --deployment-local-git

When the web app has been created, the Azure CLI shows output similar to the following example:

Local git is configured with url of 'https://<username>@<app-name>.scm.azurewebsites.net/<app-name>.git'
{
  "availabilityState": "Normal",
  "clientAffinityEnabled": true,
  "clientCertEnabled": false,
  "clientCertExclusionPaths": null,
  "cloningInfo": null,
  "containerSize": 0,
  "dailyMemoryTimeQuota": 0,
  "defaultHostName": "<app-name>.azurewebsites.net",
  "deploymentLocalGitUrl": "https://<username>@<app-name>.scm.azurewebsites.net/<app-name>.git",
  "enabled": true,
  < JSON data removed for brevity. >
}

Note

The URL of the Git remote is shown in the deploymentLocalGitUrl property, with the format https://<username>@<app-name>.scm.azurewebsites.net/<app-name>.git. Save this URL as you need it later.

Create a web app in the myAppServicePlan App Service plan.

In the Cloud Shell, you can use the az webapp create command. In the following example, replace <app-name> with a globally unique app name (valid characters are a-z, 0-9, and -). The runtime is set to DOTNETCORE|3.1. To see all supported runtimes, run az webapp list-runtimes --linux.

# Bash
az webapp create --resource-group myResourceGroup --plan myAppServicePlan --name <app-name> --runtime "DOTNETCORE|3.1" --deployment-local-git
# PowerShell
az --% webapp create --resource-group myResourceGroup --plan myAppServicePlan --name <app-name> --runtime "DOTNETCORE|3.1" --deployment-local-git

When the web app has been created, the Azure CLI shows output similar to the following example:

Local git is configured with url of 'https://@.scm.azurewebsites.net/.git'
{
  "availabilityState": "Normal",
  "clientAffinityEnabled": true,
  "clientCertEnabled": false,
  "cloningInfo": null,
  "containerSize": 0,
  "dailyMemoryTimeQuota": 0,
  "defaultHostName": ".azurewebsites.net",
  "deploymentLocalGitUrl": "https://@.scm.azurewebsites.net/.git",
  "enabled": true,
  < JSON data removed for brevity. >
}

You’ve created an empty web app in a Linux container, with git deployment enabled.

Note

The URL of the Git remote is shown in the deploymentLocalGitUrl property, with the format https://<username>@<app-name>.scm.azurewebsites.net/<app-name>.git. Save this URL as you need it later.

Configure connection string

To set connection strings for your Azure app, use the az webapp config appsettings set command in the Cloud Shell. In the following command, replace <app-name>, as well as the <connection-string> parameter with the connection string you created earlier.

az webapp config connection-string set --resource-group myResourceGroup --name <app-name> --settings MyDbConnection="<connection-string>" --connection-string-type SQLAzure

In ASP.NET Core, you can use this named connection string (MyDbConnection) using the standard pattern, like any connection string specified in appsettings.json. In this case, MyDbConnection is also defined in your appsettings.json. When running in App Service, the connection string defined in App Service takes precedence over the connection string defined in your appsettings.json. The code uses the appsettings.json value during local development, and the same code uses the App Service value when deployed.

To see how the connection string is referenced in your code, see Configure app to connect to production database.

Push to Azure from Git

Back in the local terminal window, add an Azure remote to your local Git repository. Replace <deploymentLocalGitUrl-from-create-step> with the URL of the Git remote that you saved from Create a web app.

git remote add azure <deploymentLocalGitUrl-from-create-step>

Push to the Azure remote to deploy your app with the following command. When Git Credential Manager prompts you for credentials, make sure you enter the credentials you created in Configure a deployment user, not the credentials you use to sign in to the Azure portal.

git push azure master

This command may take a few minutes to run. While running, it displays information similar to the following example:

Enumerating objects: 268, done.
Counting objects: 100% (268/268), done.
Compressing objects: 100% (171/171), done.
Writing objects: 100% (268/268), 1.18 MiB | 1.55 MiB/s, done.
Total 268 (delta 95), reused 251 (delta 87), pack-reused 0
remote: Resolving deltas: 100% (95/95), done.
remote: Updating branch 'master'.
remote: Updating submodules.
remote: Preparing deployment for commit id '64821c3558'.
remote: Generating deployment script.
remote: Project file path: .\DotNetCoreSqlDb.csproj
remote: Generating deployment script for ASP.NET MSBuild16 App
remote: Generated deployment script files
remote: Running deployment command...
remote: Handling ASP.NET Core Web Application deployment with MSBuild16.
remote: .
remote: .
remote: .
remote: Finished successfully.
remote: Running post deployment command(s)...
remote: Triggering recycle (preview mode disabled).
remote: App container will begin restart within 10 seconds.
To https://<app-name>.scm.azurewebsites.net/<app-name>.git
 * [new branch]      master -> master

Back in the local terminal window, add an Azure remote to your local Git repository. Replace <deploymentLocalGitUrl-from-create-step> with the URL of the Git remote that you saved from Create a web app.

git remote add azure <deploymentLocalGitUrl-from-create-step>

Push to the Azure remote to deploy your app with the following command. When Git Credential Manager prompts you for credentials, make sure you enter the credentials you created in Configure a deployment user, not the credentials you use to sign in to the Azure portal.

git push azure master

This command may take a few minutes to run. While running, it displays information similar to the following example:

Enumerating objects: 273, done.
Counting objects: 100% (273/273), done.
Delta compression using up to 4 threads
Compressing objects: 100% (175/175), done.
Writing objects: 100% (273/273), 1.19 MiB | 1.85 MiB/s, done.
Total 273 (delta 96), reused 259 (delta 88)
remote: Resolving deltas: 100% (96/96), done.
remote: Deploy Async
remote: Updating branch 'master'.
remote: Updating submodules.
remote: Preparing deployment for commit id 'cccecf86c5'.
remote: Repository path is /home/site/repository
remote: Running oryx build...
remote: Build orchestrated by Microsoft Oryx, https://github.com/Microsoft/Oryx
remote: You can report issues at https://github.com/Microsoft/Oryx/issues
remote: .
remote: .
remote: .
remote: Done.
remote: Running post deployment command(s)...
remote: Triggering recycle (preview mode disabled).
remote: Deployment successful.
remote: Deployment Logs : 'https://<app-name>.scm.azurewebsites.net/newui/jsonviewer?view_url=/api/deployments/cccecf86c56493ffa594e76ea1deb3abb3702d89/log'
To https://<app-name>.scm.azurewebsites.net/<app-name>.git
 * [new branch]      master -> master

Browse to the Azure app

Browse to the deployed app using your web browser.

http://<app-name>.azurewebsites.net

Add a few to-do items.

app running in App Service

Congratulations! You're running a data-driven .NET Core app in App Service.

Update locally and redeploy

In this step, you make a change to your database schema and publish it to Azure.

Update your data model

Open Models/Todo.cs in the code editor. Add the following property to the ToDo class:

public bool Done { get; set; }

Rerun database migrations

Run a few commands to make updates to the production database.

dotnet ef migrations add AddProperty
dotnet ef database update

Note

If you open a new terminal window, you need to set the connection string to the production database in the terminal, like you did in Run database migrations to the production database.

Use the new property

Make some changes in your code to use the Done property. For simplicity in this tutorial, you're only going to change the Index and Create views to see the property in action.

Open Controllers/TodosController.cs.

Find the Create([Bind("ID,Description,CreatedDate")] Todo todo) method and add Done to the list of properties in the Bind attribute. When you're done, your Create() method signature looks like the following code:

public async Task<IActionResult> Create([Bind("ID,Description,CreatedDate,Done")] Todo todo)

Open Views/Todos/Create.cshtml.

In the Razor code, you should see a <div class="form-group"> element for Description, and then another <div class="form-group"> element for CreatedDate. Immediately following these two elements, add another <div class="form-group"> element for Done:

<div class="form-group">
    <label asp-for="Done" class="col-md-2 control-label"></label>
    <div class="col-md-10">
        <input asp-for="Done" class="form-control" />
        <span asp-validation-for="Done" class="text-danger"></span>
    </div>
</div>

Open Views/Todos/Index.cshtml.

Search for the empty <th></th> element. Just above this element, add the following Razor code:

<th>
    @Html.DisplayNameFor(model => model.Done)
</th>

Find the <td> element that contains the asp-action tag helpers. Just above this element, add the following Razor code:

<td>
    @Html.DisplayFor(modelItem => item.Done)
</td>

That's all you need to see the changes in the Index and Create views.

Test your changes locally

Run the app locally.

dotnet run

Note

If you open a new terminal window, you need to set the connection string to the production database in the terminal, like you did in Run database migrations to the production database.

In your browser, navigate to http://localhost:5000/. You can now add a to-do item and check Done. Then it should show up in your homepage as a completed item. Remember that the Edit view doesn't show the Done field, because you didn't change the Edit view.

Publish changes to Azure

git add .
git commit -m "added done field"
git push azure master

Once the git push is complete, navigate to your App Service app and try adding a to-do item and check Done.

Azure app after Code First Migration

All your existing to-do items are still displayed. When you republish your ASP.NET Core app, existing data in your SQL Database isn't lost. Also, Entity Framework Core Migrations only changes the data schema and leaves your existing data intact.

Stream diagnostic logs

While the ASP.NET Core app runs in Azure App Service, you can get the console logs piped to the Cloud Shell. That way, you can get the same diagnostic messages to help you debug application errors.

The sample project already follows the guidance at ASP.NET Core Logging in Azure with two configuration changes:

  • Includes a reference to Microsoft.Extensions.Logging.AzureAppServices in DotNetCoreSqlDb.csproj.
  • Calls loggerFactory.AddAzureWebAppDiagnostics() in Program.cs.

To set the ASP.NET Core log level in App Service to Information from the default level Error, use the az webapp log config command in the Cloud Shell.

az webapp log config --name <app-name> --resource-group myResourceGroup --application-logging true --level information

Note

The project's log level is already set to Information in appsettings.json.

To start log streaming, use the az webapp log tail command in the Cloud Shell.

az webapp log tail --name <app-name> --resource-group myResourceGroup

Once log streaming has started, refresh the Azure app in the browser to get some web traffic. You can now see console logs piped to the terminal. If you don't see console logs immediately, check again in 30 seconds.

To stop log streaming at any time, type Ctrl+C.

For more information on customizing the ASP.NET Core logs, see Logging in ASP.NET Core.

Manage your Azure app

To see the app you created, in the Azure portal, search for and select App Services.

Select App Services in Azure portal

On the App Services page, select the name of your Azure app.

Portal navigation to Azure app

By default, the portal shows your app's Overview page. This page gives you a view of how your app is doing. Here, you can also perform basic management tasks like browse, stop, start, restart, and delete. The tabs on the left side of the page show the different configuration pages you can open.

App Service page in Azure portal

Clean up resources

In the preceding steps, you created Azure resources in a resource group. If you don't expect to need these resources in the future, delete the resource group by running the following command in the Cloud Shell:

az group delete --name myResourceGroup

This command may take a minute to run.

Next steps

What you learned:

  • Create a SQL Database in Azure
  • Connect a .NET Core app to SQL Database
  • Deploy the app to Azure
  • Update the data model and redeploy the app
  • Stream logs from Azure to your terminal
  • Manage the app in the Azure portal

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

Or, check out other resources: