Tutorial: Deploy an ASP.NET app to Azure with Azure SQL Database

Azure App Service provides a highly scalable, self-patching web hosting service. This tutorial shows you how to deploy a data-driven ASP.NET app in App Service and connect it to Azure SQL Database. When you're finished, you have an ASP.NET app running in Azure and connected to SQL Database.

Published ASP.NET application in Azure App Service

In this tutorial, you learn how to:

  • Create a database in Azure SQL Database
  • Connect an ASP.NET 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

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

Prerequisites

To complete this tutorial:

Install Visual Studio 2019 with the ASP.NET and web development workload.

If you've installed Visual Studio already, add the workloads in Visual Studio by clicking Tools > Get Tools and Features.

Download the sample

The sample project contains a basic ASP.NET MVC create-read-update-delete (CRUD) app using Entity Framework Code First.

Run the app

Open the dotnet-sqldb-tutorial-master/DotNetAppSqlDb.sln file in Visual Studio.

Type Ctrl+F5 to run the app without debugging. The app is displayed in your default browser. Select the Create New link and create a couple to-do items.

New ASP.NET Project dialog box

Test the Edit, Details, and Delete links.

The app uses a database context to connect with the database. In this sample, the database context uses a connection string named MyDbConnection. The connection string is set in the Web.config file and referenced in the Models/MyDatabaseContext.cs file. The connection string name is used later in the tutorial to connect the Azure app to an Azure SQL Database.

Publish ASP.NET application to Azure

In the Solution Explorer, right-click your DotNetAppSqlDb project and select Publish.

Publish from Solution Explorer

Select Azure as your target, click next, and make sure that Azure App Service (Windows) is selected and click next again.

Publish from project overview page

Sign in to Azure

In the Publish dialog, click Add an account from the account manager drop down, and then sign in to your Azure subscription. If you're already signed into a Microsoft account, make sure that account holds your Azure subscription. If the signed-in Microsoft account doesn't have your Azure subscription, click it to add the correct account.

Sign in to Azure

Note

If you're already signed in, don't select Create yet.

Configure the web app name

You can keep the generated web app name, or change it to another unique name (valid characters are a-z, 0-9, and -). The web app name is used as part of the default URL for your app (<app_name>.azurewebsites.net, where <app_name> is your web app name). The web app name needs to be unique across all apps in Azure.

Create app service dialog

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.

  1. Next to Resource Group, click New.

    Next to Resource Group, click New.

  2. Name the resource group myResourceGroup.

Create an App Service plan

An App Service plan specifies the location, size, and features of the web server farm that hosts your app. You can save money when you host multiple apps by configuring the web apps to share a single App Service plan.

App Service plans define:

  • Region (for example: North Europe, East US, or Southeast Asia)
  • Instance size (small, medium, or large)
  • Scale count (1 to 20 instances)
  • SKU (Free, Shared, Basic, Standard, or Premium)
  1. Next to App Service Plan, click New.

  2. In the Configure App Service Plan dialog, configure the new App Service plan with the following settings:

    Create App Service plan

    Setting Suggested value For more information
    App Service Plan myAppServicePlan App Service plans
    Location West Europe Azure regions
    Size Free Pricing tiers
  3. The Publish dialog shows the resources you've configured. Click Finish.

    the resources you've created

Create a server

Before creating a database, you need a logical SQL server. A logical SQL server is a logical construct that contains a group of databases managed as a group.

  1. Click Configure next to SQL Server Database under Connected Services.

    Create a SQL Database

  2. In the Azure SQL Database dialog, click New next to Database Server.

    A unique server name is generated. This name is used as part of the default URL for your server, <server_name>.database.windows.net. It must be unique across all servers in Azure SQL. You can change the server name, but for this tutorial, keep the generated value.

  3. Add an administrator username and password. For password complexity requirements, see Password Policy.

    Remember this username and password. You need them to manage the server later.

    Create server

    Important

    Even though your password in the connection strings is masked (in Visual Studio and also in App Service), the fact that it's maintained somewhere adds to the attack surface of your app. App Service can use managed service identities to eliminate this risk by removing the need to maintain secrets in your code or app configuration at all. For more information, see Next steps.

  4. Click OK. Don't close the Configure SQL Database dialog yet.

Create a database in Azure SQL Database

  1. In the Azure SQL Database dialog:

    • Keep the default generated Database Name.
    • Select Create.

    Configure database

  2. In the Database connection String Name, type MyDbConnection. This name must match the connection string that is referenced in Models/MyDatabaseContext.cs.

  3. Enter the Admin username and password you used in Create a server step 3 into the Database username and password respectively.

    Configure database connection string

  4. Select Finish.

Once the wizard finishes creating the Azure resources, click Publish to deploy your ASP.NET app to Azure. Your default browser is launched with the URL to the deployed app.

Add a few to-do items.

Published ASP.NET application in Azure app

Congratulations! Your data-driven ASP.NET application is running live in Azure App Service.

Access the database locally

Visual Studio lets you explore and manage your new database easily in the SQL Server Object Explorer.

Create a database connection

From the View menu, select SQL Server Object Explorer.

At the top of SQL Server Object Explorer, click the Add SQL Server button.

Configure the database connection

In the Connect dialog, expand the Azure node. All your SQL Database instances in Azure are listed here.

Select the database that you created earlier. The connection you created earlier is automatically filled at the bottom.

Type the database administrator password you created earlier and click Connect.

Configure database connection from Visual Studio

Allow client connection from your computer

The Create a new firewall rule dialog is opened. By default, a server only allows connections to its databases from Azure services, such as your Azure app. To connect to your database from outside of Azure, create a firewall rule at the server level. The firewall rule allows the public IP address of your local computer.

The dialog is already filled with your computer's public IP address.

Make sure that Add my client IP is selected and click OK.

Create firewall rule

Once Visual Studio finishes creating the firewall setting for your SQL Database instance, your connection shows up in SQL Server Object Explorer.

Here, you can perform the most common database operations, such as run queries, create views and stored procedures, and more.

Expand your connection > Databases > <your database> > Tables. Right-click on the Todoes table and select View Data.

Explore SQL Database objects

Update app with Code First Migrations

You can use the familiar tools in Visual Studio to update your database and app in Azure. In this step, you use Code First Migrations in Entity Framework to make a change to your database schema and publish it to Azure.

For more information about using Entity Framework Code First Migrations, see Getting Started with Entity Framework 6 Code First using MVC 5.

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; }

Run Code First Migrations locally

Run a few commands to make updates to your local database.

From the Tools menu, click NuGet Package Manager > Package Manager Console.

In the Package Manager Console window, enable Code First Migrations:

Enable-Migrations

Add a migration:

Add-Migration AddProperty

Update the local database:

Update-Database

Type Ctrl+F5 to run the app. Test the edit, details, and create links.

If the application loads without errors, then Code First Migrations has succeeded. However, your page still looks the same because your application logic is not using this new property yet.

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() method on line 52 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 ActionResult Create([Bind(Include = "Description,CreatedDate,Done")] Todo todo)

Open Views\Todos\Create.cshtml.

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

<div class="form-group">
    @Html.LabelFor(model => model.Done, htmlAttributes: new { @class = "control-label col-md-2" })
    <div class="col-md-10">
        <div class="checkbox">
            @Html.EditorFor(model => model.Done)
            @Html.ValidationMessageFor(model => model.Done, "", new { @class = "text-danger" })
        </div>
    </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 Html.ActionLink() helper methods. Above this <td>, add another <td> element with 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.

Type Ctrl+F5 to run the app.

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.

Enable Code First Migrations in Azure

Now that your code change works, including database migration, you publish it to your Azure app and update your SQL Database with Code First Migrations too.

Just like before, right-click your project and select Publish.

Click Configure to open the publish settings.

Open publish settings

In the wizard, click Next.

Make sure that the connection string for your SQL Database is populated in MyDatabaseContext (MyDbConnection). You may need to select the myToDoAppDb database from the dropdown.

Select Execute Code First Migrations (runs on application start), then click Save.

Enable Code First Migrations in Azure app

Publish your changes

Now that you enabled Code First Migrations in your Azure app, publish your code changes.

In the publish page, click Publish.

Try adding to-do items again and select Done, and they should show up in your homepage as a completed item.

Azure app after Code First Migration

All your existing to-do items are still displayed. When you republish your ASP.NET application, existing data in your SQL Database is not lost. Also, Code First Migrations only changes the data schema and leaves your existing data intact.

Stream application logs

You can stream tracing messages directly from your Azure app to Visual Studio.

Open Controllers\TodosController.cs.

Each action starts with a Trace.WriteLine() method. This code is added to show you how to add trace messages to your Azure app.

Open Server Explorer

From the View menu, select Server Explorer. You can configure logging for your Azure app in Server Explorer.

Enable log streaming

In Server Explorer, expand Azure > App Service.

Expand the myResourceGroup resource group, you created when you first created the Azure app.

Right-click your Azure app and select View Streaming Logs.

Enable log streaming

The logs are now streamed into the Output window.

Log streaming in Output window

However, you don't see any of the trace messages yet. That's because when you first select View Streaming Logs, your Azure app sets the trace level to Error, which only logs error events (with the Trace.TraceError() method).

Change trace levels

To change the trace levels to output other trace messages, go back to Server Explorer.

Right-click your Azure app again and select View Settings.

In the Application Logging (File System) dropdown, select Verbose. Click Save.

Change trace level to Verbose

Tip

You can experiment with different trace levels to see what types of messages are displayed for each level. For example, the Information level includes all logs created by Trace.TraceInformation(), Trace.TraceWarning(), and Trace.TraceError(), but not logs created by Trace.WriteLine().

In your browser navigate to your app again at http://<your app name>.azurewebsites.net, then try clicking around the to-do list application in Azure. The trace messages are now streamed to the Output window in Visual Studio.

Application: 2017-04-06T23:30:41  PID[8132] Verbose     GET /Todos/Index
Application: 2017-04-06T23:30:43  PID[8132] Verbose     GET /Todos/Create
Application: 2017-04-06T23:30:53  PID[8132] Verbose     POST /Todos/Create
Application: 2017-04-06T23:30:54  PID[8132] Verbose     GET /Todos/Index

Stop log streaming

To stop the log-streaming service, click the Stop monitoring button in the Output window.

Stop log streaming

Manage your Azure app

Go to the Azure portal to manage the web app. Search for and select App Services.

Search for Azure App Services

Select the name of your Azure app.

Portal navigation to Azure app

You have landed in your app's page.

By default, the portal shows the 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, you can delete them by deleting the resource group.

  1. From your web app's Overview page in the Azure portal, select the myResourceGroup link under Resource group.
  2. On the resource group page, make sure that the listed resources are the ones you want to delete.
  3. Select Delete, type myResourceGroup in the text box, and then select Delete.

Next steps

In this tutorial, you learned how to:

  • Create a database in Azure SQL Database
  • Connect an ASP.NET 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 easily improve the security of your connection Azure SQL Database.

More resources:

Want to optimize and save on your cloud spending?