Deploy ASP.NET Core Backend to Azure App Services with SQL Database

So you have an app or part of an app, say the REST API, ready to go and it’s time to get it hosted. There are different ways by which you can get your apps published to Azure App Services. I’m going to go over one way you can do so using the built-in integration in Visual Studio and the azure portal.

*This is really geared towards students or individuals looking to host their apps for free for the first time.

Pre-reqs:

1. Visual Studio — *I’m using VS Community 2019
2. An app or backend you want to deploy.
3. An Azure account — *I have a student account that gives a year of free credit. You should be able to start and azure account with free credit even if you’re not a student.
4. A can do it attitude!

Create New App Service

From VS in the solution explorer right click on your project and select publish. You should get a window that pops up like this:

If you’ve never done this before then you won’t have anything in the select field to publish. You’re going to click on “New”. Then select “App Service”, “Create New”, then “Create Profile”.

Now we customize our service.

You can adjust the name you want to call the service, select your subscription, adjust the name of your “resource group”, and create a new hosting plan.

Select the region you’re in, and make sure to select “free” for the size of your plan! You’ll hit “ok” then “create”. It might take a few minutes to create the service, but once it’s done you will see the first screen again and have the option to publish your app to the service. Go ahead and publish it!

You might have noticed that there was an option to create a SQL database while creating the service. Though you can do it from VS, I like doing it from the Azure portal, and it’s good to know how to work around the portal, so let’s go over that.

When you’re logged into your azure portal click on the hamburger in the top left corner. That will bring out a window from the left with a lot of options. Click on the “Create a resource”.

Select “Databases” and then “SQL Database”.

Now we’ll setup the database. Select your subscription, and the resource group you created earlier (you can make a different one if you really want, but I think it’s good to keep things organized within the same group).

Then you’ll give your database a name and hit “Create new” for the server.

Go ahead and fill everything out and hit “OK”.

***Make sure you don’t forget the password for you server! We’re going to need that.

Next hit “configure the database”. You’ll want to find the tab that says Standard. This gives you a DB for $15 a month, which your credit will go towards.

You’ll hit “apply”. Then once the DB is configured, you’ll hit “review and create”, then “create”.

It might take a few minutes. But once it’s done you can click on the hamburger icon in the top left again and select “All resources” or “SQL databases”. From there click on your database and find “Connection strings”.

Copy the connection string. We’re going to use it in our app service. Now from the “All resources” blade you can select your app service and click “Configuration”.

You’re going to create a “New connection string”. You’ll give it a name and then in the value field paste the connection string you copied a moment ago. Next you need to look through your connection string. You’ll find a username={your_username}; (although Azure might autofill your username for you — it did for me), and password={your_password};. This is where you’ll need to place the admin username and password you set for the server earlier. Then select SQLserver for the type.

***Make sure to remove the curly braces for the username/password placeholders!!! Otherwise the connection will fail and you’ll spend hours trying to hunt down error codes until you finally realize you’re just a little dumb….No I don’t know that from personal experience 😅…

Make sure also that you don’t just hit “Ok” but hit save at the top. Now if you haven’t already done it, you’ll need to make a reference to your connection string name in your startup.cs file (see below). After that, along with a republish of the app (just right click on the project, select publish, make sure it has the correct app service in the select field, click publish), and your API should be working with SQL from Azure!

SQL vs SQLite for Production and Development

If you were using SQLite for your database while in development mode and would like to keep it that way, while using SQL in production mode, we’ll need to add some code to the startup.cs and AppDbContext.

In the ConfigureServices() method, we’re going to create a conditional that checks for an environment variable — “ASPNETCORE_ENVIRONMENT”. If the environment equals “Production” then we’ll pass UseSqlServer() to the options for our AppDbContext. Notice how we configure the option with the connection string. It’s important to make sure the connection string name looks EXACTLY how it does in the azure portal. Then if the environment variable is not “Production”, we’ll use our SQLite db.

But where do we set this environment variable up. From the azure portal, you’ll go to your app service, find configuration again, and instead of creating a new connection string you’ll create a new “application setting”. Input the name and set the value to “Production”.

Hit “OK” then “save” at the top.

Now we’ll need to adjust our AppDbContext a little bit like so:

We get rid of the OnConfiguring() method we might have been using beforehand and instead add an empty constructor, public AppDbContext() that accepts options.

*Note: from the startup.cs you’ll see there is also an added bit of code:

// Automatically perform database migration

services.BuildServiceProvider().GetService<AppDbContext>().Database.Migrate();

This should migrate your database for you.

***One last thing — If you’ve been using the entity framework to auto increment your model IDs with SQLite, then you’ll need to add a line of code to each model table.

> In your solution explorer find your migrations folder, then the _initial.cs migration file.

>Open it and look for any table that has this:

>Then add this line:

That should take care of the auto id generation whether you’re in development or production mode.

At this point, make sure to republish your app with all the changes you’ve made and test it out! If you run into errors, go back through the steps in case you missed anything.

Cheers!