Since you’re here, I assume you’ve either seen the benefits of deploying software to the cloud, or you’re at least curious about how to do so. Or both. I’m going to help you with that in the most direct way possible. In the following text, I describe how to get an ASP.NET Core Web API deployed to Azure, as well as how to deploy a local database to the cloud and get your Web API communicating with it.

Here are the requirements you’ll need to complete this tutorial:

1. Visual Studio 2019 (or greater)
2. SQL Server Management Studio 2019 (or greater)
3. A registered account on Azure (portal.azure.com)

That said, let’s begin.

Create the project.

Start by opening Visual Studio 2019 and creating a new project. Select ASP.NET Core Web API. Click Next.

Next, configure your project (solution) name. I’m calling my project “Things” in order to make this as generic as possible and to use it for further demonstrations and how-to’s, but you can call it whatever you’d like. Click Next.

I like to keep my projects as close to the bleeding edge of the technology curve as possible, so as of this writing, that leaves me with .NET 5.0 being the most current version of .NET (although if I wait another month or so, .NET 6 should be generally available). But I’m operating today, not a month from now, so set your Target Framework to .NET 5.0. Set the Authentication Type to “None” since this tutorial isn’t covering authentication. But do check the Configure for HTTPS and Enable OpenAPI support checkboxes. All communication these days should make use of HTTPS, and we need OpenAPI support in order to use Swagger, which is an extremely useful tool for building and testing Web API’s.

After making your selections, click the Create button.

You should now see your solution in the Solution Explorer pane with some default code enabled. Delete both the WeatherForecastController.cs and WeatherForecast.cs files, because we don’t have an API about weather forecasts. We have a very specific API about very specific “things”.  (I actually want you to delete those files because they don’t connect to a SQL database; they return hard-coded data to the user.)

Right-click your Things (or whatever) project, and select Add > New Scaffoled Item… Then select “API Controller – Empty”. Lastly, click the Add button.

We’ve got one more step before we can really add the file. You need to select “API Controller – Empty” as well as name your Controller. Always keep the word “Controller” at the end of the name, before the “.cs”. This is needed to make ASP.NET Core routing work correctly by default. I’ve decided to name my controller, “ColorsController” because I plan on returning a list of colors from the SQL database we’re going to create. After you’ve selected your controller name, click Add, and Visual Studio will actually add your file (for real) this time. 

Now that your controller has been created, let’s add a model class for returning data from the controller to the user. In the Things project, add a new folder called “Models”, and then add a new class called “ColorModel”. The name is a bit redundant due to being in the Models folder already and thus having “Models” in its namespace, but in the future, my API may well need a ColorEntity class for encapsulating data from a database, as well as possibly a ColorDTO to transfer Color data throughout my codebase before it gets translated to/from an entity class for saving to a database, or to/from a model class that gets serialized into JSON before being returned to the user in one of our endpoints.

I’m giving my ColorModel two properties that coincide with exactly how the data is structured in the database. In most cases, you probably wouldn’t want to pull data from your database and return it directly to the user via a data entity, but the point of this article isn’t to go full architecture-astronaut, but to instead show how to get your app and database into Azure as quickly as possible.

My two columns are “Id”, and “Name”.

Let’s go back to the ColorsController.cs tab. Replace the code you see with the following code.

This code replaces the default scaffolded code with a constructor accepting an IConfiguration instance and a single GetAll() method for getting all colors from our database. Normally, a full-fledged app will contain much more code and it will have a need to encapsulate data access in a class that is separate from the controller. However, for this demo, keeping the data access code in the controller is fine.

At this point, you’ll probably notice a lot of red squigglies underlining your code. This is because we need to install and reference Dapper (a micro-ORM for querying a SQL database) as well as the latest version of System.Data.SqlClient from NuGet. Don’t forget to also reference your ColorModel class.

USE [master]
GO
/****** Object:  Database [Things]    Script Date: 10/7/2021 12:43:12 PM ******/
CREATE DATABASE [Things]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Things', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Things.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'Things_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Things_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
 WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO
ALTER DATABASE [Things] SET COMPATIBILITY_LEVEL = 150
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Things].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [Things] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [Things] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [Things] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [Things] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [Things] SET ARITHABORT OFF 
GO
ALTER DATABASE [Things] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [Things] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [Things] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [Things] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [Things] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [Things] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [Things] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [Things] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [Things] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [Things] SET  DISABLE_BROKER 
GO
ALTER DATABASE [Things] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [Things] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [Things] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [Things] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [Things] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [Things] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [Things] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [Things] SET RECOVERY FULL 
GO
ALTER DATABASE [Things] SET  MULTI_USER 
GO
ALTER DATABASE [Things] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [Things] SET DB_CHAINING OFF 
GO
ALTER DATABASE [Things] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [Things] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
ALTER DATABASE [Things] SET DELAYED_DURABILITY = DISABLED 
GO
ALTER DATABASE [Things] SET ACCELERATED_DATABASE_RECOVERY = OFF  
GO
EXEC sys.sp_db_vardecimal_storage_format N'Things', N'ON'
GO
ALTER DATABASE [Things] SET QUERY_STORE = OFF
GO
USE [Things]
GO
/****** Object:  Table [dbo].[Color]    Script Date: 10/7/2021 12:43:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Color](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO
USE [master]
GO
ALTER DATABASE [Things] SET  READ_WRITE 
GO

USE [Things]
GO
INSERT INTO dbo.Color(Name) VALUES('Red'),('Blue'),('Green'),('Yellow'),('Black'),('White')
GO

Set up Azure SQL.

Alright. You’ve got an app, and you’ve got a local database. Time to set up Azure. Log into your account on the Azure portal (portal.azure.com). Once there, click the Create a resource button on the left (denoted by a big “+” sign). Once you’ve done that, enter “azure sql server” in the search bar. You should see “Azure SQL” like below as the first result. Click the Create button.

Change the resource type to “Database server”. The reason for this is that we don’t want to start off with an actual database resource in Azure; we just want the SQL Server in Azure. This tutorial focuses on deploying a local database into Azure. If you want to create a blank SQL Database in the same step as creating the SQL Server, then you can select “Single database”.

On the following screen, select a resource group to put your SQL Database server under, if you don’t have one already. Really, any new, isolated project should have its own resource group so that you don’t muddy the concerns of one project with another, but I digress. If you’re curious how to name your Azure resources, consult this link by Microsoft for best practices in naming your resources: Recommended abbreviations for Azure resource types

After you’ve selected your resource group, enter a name for your server as well as selecting the location for where your server will be hosted. A location that corresponds best to where your users are located works best, but since this is just a demo where you’re the only user, you can set the location somewhere close to where you live or work out of so as to get maximum communication speed between your machine and your server.

Select “Use SQL Authentication” and enter an admin login and password. We’ll use this information in our connection string in our Web API app. Beware though that the admin user has full SQL privileges, and that in a production app, you’d only want your app to query the database with a user that has only read/write privileges as opposed to full DBO (Database Owner) privileges as a security precaution.

After filling out the required screens, click “Review + create”, and then the “Create” button on the final screen.

Once your resource is created, navigate to it by clicking the “Go to resource” button if you’ve waited on the creation screen, or click the name of your resource group and then click the name of your SQL Database Server on the next screen. Once you’re viewing your SQL Database Server screen, click the “Firewalls and virtual networks” button. It’s in the bottom-left of the next image.

This next step is extremely important. A database that can’t be accessed by you or your app is the equivalent of a pricey paperweight. On the Firewalls and virtual networks screen, click the “Add client IP” button, and give the rule an appropriate name. In my case, I named it “Kyle’s Work Laptop”. Adding rules here for IP addresses will allow the machines associated with those IP addresses to directly connect to the SQL Server. You’ll want to be able to do this if you want to query your database via SQL Server Management Studio or Azure Data Studio.

After adding your IP address to the rules section, click the “Yes” option (pink in the screenshot) under “Allow Azure services and resources to access this server”. This will make your SQL Server accessible by the app that we’re going to deploy to Azure next.

And lastly, don’t forget to click the “Save” button.

Onto deployment of our database to Azure! Open your local database instance in SQL Server Management Studio, right-click it and navigate to Tasks > Deploy Database to Microsoft Azure SQL Database…

In the Server connection section, click the “Connect” button, and enter your Azure SQL Server. If you can’t remember or are unsure of its name, pull up the resource in the Azure portal. It’ll be towards the top in the “Overview” section.

Then, create a new database name. I like following Microsoft’s conventions in most cases, thus I prefixed my database name with “sqldb-” for a full name of “sqldb-things-dev” (“dev” for development). In the “Edition of Microsoft Azure SQL Database”, select “Basic”. This is the cheapest option that as of this writing costs about ~$5 USD/month. If you’ve just recently created your Azure account, you’ll likely have more than enough dollar credits to cover this. If you don’t wish to pay $5/mo, you can delete your Azure SQL Database after this tutorial.

Lastly, click the “Next” button, and then the “Finish” button afterward.

Once your database has been deployed, navigate to your resource group in the Azure portal, and if you don’t see your database there from the previous step, click the “Refresh” button a couple of times. Once it shows up, click it to navigate to the following screen so that we can grab the connection string for the database by clicking on the “Connection strings” link circled in the screenshot below.

Copy and paste the connection string either manually by selecting the whole thing, or clicking the tiny copy button in the bottom right-hand corner.

Pull up the Things solution in Visual Studio 2019. Open the appsettings.Development.json file, and paste your Azure SQL Server’s database string into a “ConnectionStrings” entry like below.

NOTE: Storing your connection strings in an appsettings file is considered bad practice due to the risk of someone nefarious finding a way to navigate to the file once your solution is deployed to the server, as well as the inherent risk of storing sensitive passwords/secrets in plain-text format in source control. Since this is just a demo with low-security risk, we’re going to ignore these issues for the sake of expediency. If you want to learn more about appropriate app password and secret storage, learn about Azure Key Vault.

Also, notice the “Password” entry in the connection string? How instead of actually displaying your password it has the text “{your_password}”? You’ll need to replace that text with your SQL Server admin login in the prior steps. As stated in the “NOTE”, this is bad practice for a variety of reasons, but considering this is a DEV environment with low-security implications, we’re going to ignore these risks for the sake of expediency.

Now if you haven’t sanity-checked your app code yet, do that now by performing a build and clicking the Debug button so that we can make sure the code compiles. Assuming you’ve done everything correctly up to this point, you should get a browser window with SwaggerUI pages on it displaying your single API endpoint. Click the Try it out button, and then the big blue Execute button. Assuming you’ve been following this tutorial in its entirety, your screen and results should look like the below screenshot.

Create the Azure App Service.

Back to to the Azure portal. In your resource group, click the Create icon and enter “App Service” in the search bar. Click the Create button in the first result which should look like the image below.

Assign your web app to the resource group you used in the preceding steps. Pick a name for your app too. You may have to play around with it because if you’ve named your app something as generic as “Things”, then it’s quite likely that name is already taken. Hence why in the screenshot I refer to my app as “things1”.

Set your app’s runtime stack as .NET 5 since that’s what we did when creating the project in Visual Studio 2019, and select the region you want your app to be hosted in. I kept mine the same as I did when creating the database in the preceding steps. Having your app and database in the same data center will reduce latency when they communicate with each other.

Lastly, create an App Service Plan if you don’t have one already. According to Microsoft’s recommended Azure resource naming conventions, I prefixed my name with “plan-“. Using the Free F1 instance is good enough for our testing purposes. For higher workloads (e.g., production), you’ll want to select a better “Sku and size” App Service Plan.

Once you’ve filled out the screen like below, click Review + create and then Create on the final screen.

Publish your Web API.

Okay go back to Visual Studio 2019 now. Right-click the Things project and select Publish…. Then select Azure App Service (Windows). Then click Next.

On the App Service screen, select your subscription name and find the instance of your Azure App Service that you created in the prior steps. Select it in the “App Service instances” section. Lastly, click Next to enter the API Management screen, but select the checkbox that says Skip this step. Then click Finish.

Once the app is created, you can go and view it by entering the URL generated for it. However, you’ll likely notice a blank screen mentioning a 404 error. This is because there’s no default page set for your screen. When debugging locally, you had a Swagger page, so what’s going on here?

Well, turns out that if you don’t manually set the ASPNETCORE_ENVIRONMENT variable, then by default, its value is set to “Production”.

So in order to fix this, we need to make a simple change. First, enter the “Configuration” section of your Azure App, click New application setting, and for the name, enter “ASPNETCORE_ENVIRONMENT”, and set its value to “Development”. Don’t forget to click the Save button at the top of the screen.

if (env.IsDevelopment())

block, then republished the app to Azure.

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }

            app.UseSwagger();
            app.UseSwaggerUI(c => c.SwaggerEndpoint("/swagger/v1/swagger.json", "Things v1"));

            app.UseHttpsRedirection();

            app.UseRouting();

            app.UseAuthorization();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllers();
            });
        }

One last publish to Azure and BAM. I navigate to my app’s URL, click the Try it out button, and then click the big blue Execute button just like we did when we were trying things out locally. And it works like a charm. Notice the URL in the following screenshot.

And that’s it! In this tutorial you learned how to create a Web API and a SQL Server Database, and deploy both to Azure and how to get them to communicate with each other while in Azure. It seems like a lot of steps on your first try with Azure but once you’ve done it once, doing it again is a piece of cake! Thanks for taking the time to read this tutorial. If you have any feedback regarding this tutorial, please feel free to leave a message below!