Storage Considerations for Running SQL Server in Azure

Storage Considerations for Running SQL Server in Azure

<strong>Originally published by </strong>&nbsp;<a href="" target="_blank">David Bermingham</a><strong> </strong><em>at&nbsp;</em><a href="" target="_blank"></a>

Originally published by  David Bermingham at

Explore some storage considerations for running SQL Server in Azure.

If you are deploying SQL Server in Azure or any Cloud platform for that matter, instead of just provisioning storage like you did for your on-premises deployments for many years, you may consider that storage in Azure isn't exactly like the storage you may have had access to on-premises. Some traditional "best practices" may wind up costing you additional money and give you less than optimal performance, all while not providing you any of the intended benefits. Much of what I am about to discuss is also described in Performance Guidelines for Azure in SQL Server Virtual Machines.

I'm not here to tell you that you must use UltraSSD, Premium Storage, or any other disk type. You just need to be aware that you have options and know what each disk type brings to the table. Of course, like anything else in the cloud, the more money you spend, the more power, speed, throughput, etc. you will achieve. The trick is finding the optimal configuration so that you spend just enough to achieve the desired results.

Size DOES Matter

Like many things in the cloud, certain specs are tied together. For servers, if you want more RAM, you often get more CPU even if you didn't NEED it. For storage, IOPS, throughput, and size are all tied together. If you want more IOPS, you need a bigger disk. If you need more space, you also get more IOPS. Of course, you can jump between storage classes to circumvent that to some extent, but it still holds true that if you need more IOPS. You also get more space on any of the different storage types.

The size of your virtual machine instance also matters. Regardless of what storage configuration you eventually go with, the overall throughput will be capped at whatever the instance size allows. So once again, you may need to pay for more RAM and CPU than you need just to achieve your desired storage performance. Make sure you understand what your instance size can support in terms of max IOPS and MBps throughput. Many times the instance size will turn out to be the bottleneck in a perceived storage performance problem in Azure.

RAID 0 is traditionally the 3rd rail of storage configuration options. Although it provides the best combination of performance and storage utilization of any RAID option, it does so at the risk of a catastrophic failure. If just a single disk in a RAID 0 stripe set should fail, the entire stripe set fails. So traditionally RAID 0 is only used in scenarios where data loss is acceptable and high performance is desirable.

However, in Azure software, RAID 0 is desirable and even recommended in many situations. How can we get away with RAID 0 in Azure? The answer is easy. Each disk you present to an Azure virtual machine instance already has triple redundancy on the backend, meaning you would need to have multiple failures before you would lose your stripe set. By using RAID 0, you can combine multiple disks and the overall performance of the combined stripe set will increase by 100 percent for each additional disk you add to the stripe set.

So, for example, if you had a requirement of 10,000 IOPS, you might think that you need UltraSSD since Premium Storage maxes out at 7,500 IOPS with a P50. However, if you put two P50s in a RAID 0, you now have the potential to achieve up to 15,000 IOPS, assuming you are running a Standard_F16s_v2 or similarly large instance size that supports that many IOPS.

In Windows 2012 and later, RAID 0 is achieved by creating a Simple Storage Space. In Windows Server 2008 R2 you can use Dynamic Disks to create a RAID 0 Striped Volume. Just a word of caution, if you are going to use a local Storage Space and also configure Availability Groups or a SANless Failover Cluster Instance with DataKeeper, it is best to configure your storage BEFORE you create a cluster.

Just a reminder, you only have about two more months to move your SQL Server 2008 R2 instances to Azure. Check out my post on how to deploy a SQL Server 2008 R2 FCI on Azure to ensure high availability.

Don't Bother Separating Log and Data Files

Traditionally, log and data files would reside on different physical disks. Log files tend to have a lot of write activity and data files tend to have more read activity, so sometimes storage would be optimized based on those characteristics. It was also desirable to keep log and data files on different disks for recovery purposes. If you should lose one or the other, with a proper backup strategy in place you could recover your database with no data loss.

With cloud-based storage, the likelihood of losing just a single volume is EXTREMELY low. If, by chance, you lose storage, it is likely your entire storage cluster, along with the triple redundancy, went to lunch. So while it may feel right to put logs in E:\ logs and data in F:\data, you really are doing yourself a disservice. For example, if you provision a P20 for logs and a P20 for data, each volume will be 512 GiB in size and capped at 2,300 IOPS. And just think, you may not need all that size for log files, but it might not give you much room to grow for your data files, which will eventually require moving to a more expensive P30 just for the extra space.

Wouldn't it be much nicer to simply stripe those two volumes together into a nice large 1 TB volume that supports 4,600 IOPS? By doing that, both the log and data files can take advantage of the increased IOPS, and you have also just optimized your storage utilization and decreased your cloud storage cost by putting off the move to a P30 disk for your data file.

The same holds true files and filegroups. Really think hard about what you are doing and whether it still makes sense once you move to the cloud. What makes sense might be counter-intuitive to what you have done in the past. When in doubt, follow the KISS rule, Keep It Simple Stupid! The beauty of the cloud is that you can always add more storage, increase instance size, or do whatever it takes to optimize performance vs. cost.

What to do About TempDB

Use the local SSD, aka, the D: drive. The D drive is going to be the best location for your tempdb. Because it is a local drive the data is considered "temporary," meaning it can be lost if a server is moved, rebooted, etc. That's okay, tempdb is recreated each time SQL starts anyway. The local SSD is going to be fast and have low latency, but because it is local the reads and writes to it do not contribute to the overall storage IOPS limit of the instance size, so effectively, it is FREE IOPS, so why not take advantage? If you are building a SANless SQL Server FCI with SIOS DataKeeper, be sure to create a non-mirrored volume resource of the D drive so you don't needlessly replicate TempDB.

Mount Points Become Obsolete

Mount Points are commonly used in SQL Server FCI configurations when multiple instances of SQL Server are installed on the same Windows Cluster. This reduces the overall cost of SQL Server licenses and can help save costs by driving higher server utilization. As we discussed in the past, typically, there might be five or more drives associated with each SQL Server instance. If each of those drives had to consume a drive letter you would run out of letters in just about three to four instances. So instead of giving each drive a letter, mount points were used so that each instance could just be serviced by a single drive letter, the root drive. The root drive has mount points that map to separate physical disks that don't have drive letters.

However, as we discussed above, the concept of using a bunch of individual disks really doesn't make a lot of sense in the cloud, hence mount points become obsolete in the cloud. Instead, create a RAID 0 stripe, as described, and each clustered instance SQL Server will simply have its own individual volume that is optimized for space, performance, and cost. This solves the problem of running out of drive letters and gives you much better storage utilization and performance while also reducing the cost of your cloud storage.


This post is meant as a jumping off point, not a definitive guide. The main point of the post is to get you thinking differently about cloud and storage as it pertains to running SQL Server. Don't simply take what you did on-premise and recreate it in the cloud, that will almost always result in less than optimal performance and a much larger storage bill than necessary.


Learn More

☞ The Complete Python & PostgreSQL Developer Course

☞ SQL & PostgreSQL for Beginners

☞ An Introduction to Queries in PostgreSQL

☞ Spring Boot with PostgreSQL, Flyway, and JSONB

☞ Node.js - Express Persistent Session Store with PostgreSQL + Sequelize

☞ Secure Node.js, Express.js and PostgreSQL API using Passport.js

☞ Build a Basic App with Spring Boot and JPA using PostgreSQL

SQL Server Tutorial For Beginners - Learn MS SQL Server with Examples

SQL Server Tutorial For Beginners - Learn MS SQL Server with Examples

SQL Server Tutorial For Beginners, Microsoft SQL Server Tutorial, SQL Server Training. Microsoft SQL Server explains all the fundamentals of MS SQL Server with examples. What is DBMS? What is SQL? What is SQL Server? SQL Server Installation

This Edureka tutorial video on Microsoft SQL Server explains all the fundamentals of MS SQL Server with examples.
The following are the topics covered in this tutorial:

  • What is DBMS?
  • Types of DBMS
  • What is SQL?
  • What is SQL Server?
  • Features of SQL Server
  • SQL Server Installation
  • SQL Server Command Categories
  • SQL Server Data Types
  • DDL Commands
  • Keys in Database
  • Constraints in Database
  • DML Commands
  • Operators
  • Nested Queries
  • Joins
  • Stored Procedures
  • DCL Commands
  • TCL Commands
  • Exception Handling

What are the differences between Standard SQL and Transact-SQL?

What are the differences between Standard SQL and Transact-SQL?

In this article, we'll explain syntax differences between standard SQL and the Transact-SQL language dedicated to interacting with the SQL

#1 Names of Database Objects

In relational database systems, we name tables, views, and columns, but sometimes we need to use the same name as a keyword or use special characters. In standard SQL, you can place this kind of name in quotation marks (""), but in T-SQL, you can also place it in brackets ([]). Look at these examples for the name of a table in T-SQL:

CREATE TABLE dbo.test.“first name” ( Id INT, Name VARCHAR(100));
CREATE TABLE dbo.test.[first name]  ( Id INT, Name VARCHAR(100));

Only the first delimiter (the quotation marks) for the special name is also part of the SQL standard.

What Is Different in a SELECT Statement?#2 Returning Values

The SQL standard does not have a syntax for a query returning values or values coming from expressions without referring to any columns of a table, but MS SQL Server does allow for this type of expression. How? You can use a SELECT statement alone with an expression or with other values not coming from columns of the table. In T-SQL, it looks like the example below:

SELECT 12/6 ;

In this expression, we don’t need a table to evaluate 12 divided by 6, therefore, the FROM statement and the name of the table can be omitted.

#3 Limiting Records in a Result Set

In the SQL standard, you can limit the number of records in the results by using the syntax illustrated below:


T-SQL implements this syntax in a different way. The example below shows the MS SQL Server syntax:


As you notice, this uses an ORDER BY clause. Another way to select rows, but without ORDER BY, is by using the TOP clause in T-SQL:

#4 Automatically Generating Values

The SQL standard enables you to create columns with automatically generated values. The syntax to do this is shown below:


In T-SQL we can also automatically generate values, but in this way:

#5 Math Functions

Several common mathematical functions are part of the SQL standard. One of these math functions is CEIL(x), which we don’t find in T-SQL. Instead, T-SQL provides the following non-standard functions: SIGN(x), ROUND(x,[,d]) to round decimal value x to the number of decimal positions, TRUNC(x) for truncating to given number of decimal places, LOG(x) to return the natural logarithm for a value x, and RANDOM() to generate random numbers. The highest or lowest number in a list in the SQL standard is returned by MAX(list) and MIN(list) functions, but in Transact-SQL, you use the GREATEST(list) and LEAST(list) functions.

T-SQL function ROUND:


#6 Aggregate Functions

We find another syntax difference with the aggregate functions. The functions COUNT, SUM, and AVG all take an argument related to a count. T-SQL allows the use of DISTINCT before these argument values so that rows are counted only if the values are different from other rows. The SQL standard doesn't allow for the use of DISTINCT in these functions.

Standard SQL:



But in T-SQL we don’t find a population covariance function: COVAR_POP(x,y), which is defined in the SQL standard.

#7 Retrieving Parts of Dates and Times

Most relational database systems deliver many functions to operate on dates and times.

In standard SQL, the EXTRACT(YEAR FROM x) function and similar functions to select parts of dates are different from the T-SQL functions like YEAR(x) or DATEPART(year, x).

There is also a difference in getting the current date and time. Standard SQL allows you to get the current date with the CURRENT_DATE function, but in MS SQL Server, there is not a similar function, so we have to use the GETDATE function as an argument in the CAST function to convert to a DATE data type.

#8 Operating on Strings

Using functions to operate on strings is also different between the SQL standard and T-SQL. The main difference is found in removing trailing and leading spaces from a string. In standard SQL, there is the TRIM function, but in T-SQL, there are several related functions: TRIM (removing trailing and leading spaces), LTRIM (removing leading spaces), and RTRIM (removing trailing spaces).

Another very-often-used string function is SUBSTRING.

The standard SQL syntax for the SUBSTRING function looks like:

SUBSTRING(str FROM start [FOR len])

but in T-SQL, the syntax of this function looks like:

SUBSTRING(str, start, length)

There are reasons sometimes to add values coming from other columns and/or additional strings. Standard SQL enables the following syntax to do this:

As you can see, this syntax makes use of the || operator to add one string to another.

But the equivalent operator in T-SQL is the plus sign character. Look at this example:

SELECT col1 + col2  FROM tab;

In SQL Server, we also have the possibility to use the CONCAT function concatenates a list of strings:

SELECT CONCAT(col1, str1, col2, ...)  FROM tab;

We can also repeat one character several times. Standard SQL defines the function REPEAT(str, n) to do this. Transact-SQL provides the REPLICATE function. For example:


where x indicates how many times to repeat the string or character.

#9 Inequality Operator

During filtering records in a SELECT statement, sometimes we have to use an inequality operator. Standard SQL defines <> as this operator, while T-SQL allows for both the standard operator and the != operator:

SELECT col3 FROM tab WHERE col1 != col2;
#10 ISNULL Function

In T-SQL, we have the ability to replace NULL values coming from a column using the ISNULL function. This is a function that is specific to T-SQL and is not in the SQL standard.

Which Parts of DML Syntax Are Different?

In T-SQL, the basic syntax of DELETE, UPDATE, and INSERT queries is the same as the SQL standard, but differences appear in more advanced queries. Let’s look at them.

#11 OUTPUT Keyword

The OUTPUT keyword occurs in DELETE, UPDATE, and INSERT statements. It is not defined in standard SQL.

Using T-SQL, we can see extra information returned by a query. It returns both old and new values in UPDATE or the values added using INSERT or deleted using DELETE. To see this information, we have to use prefixes in INSERT, UPDATE, and DELETE.

UPDATE tab SET col='new value'
OUTPUT Deleted.col, Inserted.col;

We see the result of changing records with the previous and new values in an updated column. The SQL standard does not support this feature.

#12 Syntax for INSERT INTO ... SELECT

Another structure of an INSERT query is INSERT INTO … SELECT. T-SQL allows you to insert data from another table into a destination table. Look at this query:

INSERT INTO tab SELECT col1,col2,... FROM tab_source;

It is not a standard feature but a feature characteristic of SQL Server.

#13 FROM Clause in DELETE and UPDATE

SQL Server provides extended syntax of the UPDATE and DELETE with FROM clauses. You can use DELETE with FROM to use the rows from one table to remove corresponding rows in another table by referring to a primary key and a foreign key. Similarly, you can use UPDATE with FROM update rows from one table by referring to the rows of another table using common values (primary key in one table and foreign key in second, e.g. the same city name). Here is an example:

FROM Author
WHERE Author.Id=Book.AuthorId AND Author.Name IS NULL;

SET Book.Price=Book.Price*0.2
FROM Author
WHERE Book.AuthorId=Author.Id AND Author.Id=12;

The SQL standard doesn’t provide this syntax.


You can also use INSERT, UPDATE, and DELETE using JOIN to connect to another table. An example of this is:

DELETE ItemOrder FROM ItemOrder
JOIN Item ON ItemOrder.ItemId=Item.Id
WHERE YEAR(Item.DeliveredDate) <= 2017;

This feature is not in the SQL standard.


This article does not cover all the issues about syntax differences between the SQL standard and T-SQL using the MS SQL Server system. However, this guide helps point out some basic features characteristic only of Transact-SQL and what SQL standard syntax isn’t implemented by MS SQL Server.

Thanks for reading. If you liked this post, share it with all of your programming buddies!

Originally published on

Build a CRUD App with ASP.NET Core 2.2 and SQL Server

Build a CRUD App with ASP.NET Core 2.2 and SQL Server

​ I’ve always said that you can tell a lot about a person by the kind of music they listen to. Don’t tell me you haven’t had serious doubts about whether you can be friends with someone when you find out that they like a particular band or artist. In that spirit, I created *JudgeMyTaste*, an ASP.NET Core web application where people can enter their favorite band or artist so that people on the Internet can judge them openly. ​ The combination of ASP.NET and SQL Server is probably the most common pairing in the enterprises that use ASP.NET. With ASP.NET Core and SQL Server both being cross-platform, you don’t *have* to run this combination on Windows anymore! I’ll show you how to create a basic CRUD application using ASP.NET Core 2.2 and SQL Server 2017. I’ll be running on Linux, but with the free tools used here, it won’t matter what operating system you’re using! ​ The tools I’ll be using that are available for all platforms are: * SQL Server 2017 (I’ll be running on Ubuntu 18.04) * Visual Studio Code * Azure Data Studio * ASP.NET Core 2.2 ​ Once you’ve got all the tools installed for your platform, let’s rock and roll! ​ ​ ​ ## Scaffold Your ASP.NET Core 2.2 Application ​ No matter the platform you’re on, the ```dotnet``` CLI is available. The commands used here should be the same for everyone. To scaffold the ASP.NET Core 2.2 MVC application, create a new folder for it: I’ve always said that you can tell a lot about a person by the kind of music they listen to. Don’t tell me you haven’t had serious doubts about whether you can be friends with someone when you find out that they like a particular band or artist. In that spirit, I created JudgeMyTaste, an ASP.NET Core web application where people can enter their favorite band or artist so that people on the Internet can judge them openly.

I’ve always said that you can tell a lot about a person by the kind of music they listen to. Don’t tell me you haven’t had serious doubts about whether you can be friends with someone when you find out that they like a particular band or artist. In that spirit, I created JudgeMyTaste, an ASP.NET Core web application where people can enter their favorite band or artist so that people on the Internet can judge them openly.

The combination of ASP.NET and SQL Server is probably the most common pairing in the enterprises that use ASP.NET. With ASP.NET Core and SQL Server both being cross-platform, you don’t have to run this combination on Windows anymore! I’ll show you how to create a basic CRUD application using ASP.NET Core 2.2 and SQL Server 2017. I’ll be running on Linux, but with the free tools used here, it won’t matter what operating system you’re using!

The tools I’ll be using that are available for all platforms are:

  • SQL Server 2017 (I’ll be running on Ubuntu 18.04)
  • Visual Studio Code
  • Azure Data Studio
  • ASP.NET Core 2.2

Once you’ve got all the tools installed for your platform, let’s rock and roll!

Scaffold Your ASP.NET Core 2.2 Application

No matter the platform you’re on, the dotnet CLI is available. The commands used here should be the same for everyone. To scaffold the ASP.NET Core 2.2 MVC application, create a new folder for it:

mkdir JudgeMyTaste

Change into that new directory:

cd JudgeMyTaste

Then run the following command:

dotnet new mvc

Then open the new application in VS Code.

code .

When you open the new application in VS Code, you should get a warning in the bottom right corner asking to add some missing assets. Go ahead and add the missing assets. You’ll see the .vscode folder added with a launch.json and a tasks.json file.

These will allow you to run the application from VS Code. To verify that everything scaffolded properly, run the base application by typing F5. This will build the application, run it, and open it in a new browser window.

You may notice a strange error page come up, if you’ve never run an ASP.NET Core 2.x application before. By default ASP.NET Core wants to run on HTTPS. This is a recommended practice for web applications. You could avoid this message by removing the redirect to HTTPS in your Startup.cs or by generating a certificate for your local machine, but this error screen only comes up once in a great while, so I just side step it by clicking on Advanced and telling the browser that it’s okay to visit this site even though there is no certificate for it.

For your daily work, it will probably behoove you to create a local certificate for development so that you never have to see this message again.

Create Your SQL Server Database

Open Azure Data Studio and connect to your localhost server with the SA password you created when installing SQL Server on your machine. You’ll notice it is arranged very much like VS Code. In the Connections Explorer, you will see localhost as a connection. Right-click on the connection and choose New Query, which will open a new query window on the right side. Start typing the word CREATE and an intellisense drop down will open and one of the choices will be sqlCreateDatabase. Choose that option and a query will be scaffolded with the database name highlighted in the three places that it occurs in the query. You can just start typing the database name “JudgeMyTaste” and it will be replaced in all three places so that the final query looks like this.

-- Create a new database called 'JudgeMyTaste'
-- Connect to the 'master' database to run this snippet
USE master
-- Create the new database if it does not exist already
  SELECT [name]
    FROM sys.databases
    WHERE [name] = N'JudgeMyTaste'

Now you can just click the green Run arrow at the top of the window to create the database. Simple, no?

Now when you expand the Databases folder in the Connection Explorer, you will see the JudgeMyTaste database in the list. Right-click on the new database and choose New Query again. Start typing CREATE again and this time choose sqlCreateTable from the options presented. Again, you can start typing the table name FavoriteBands and it will be filled in all the places it occurs in the query.

You’ll also need to add some other columns to the table. Add the columns for Id, Name, EnteredBy, and EnteredOn so that the query looks like this:

-- Create a new table called '[FavoriteBands]' in schema '[dbo]'
-- Drop the table if it already exists
IF OBJECT_ID('[dbo].[FavoriteBands]', 'U') IS NOT NULL
DROP TABLE [dbo].[FavoriteBands]
-- Create the table in the specified schema
CREATE TABLE [dbo].[FavoriteBands](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [Name] [varchar](255) NULL,
  [EnteredBy] [varchar](255) NULL,
  [EnteredOn] [date] NULL

Then run the query by clicking the green Run arrow as before.

It’s good practice to create a user specifically for your application to connect with the database. One that only has the permissions that it will need to interact with your database. Here’s a script to create a login and a user for the database and assign that user dbo permissions to the database.

USE master


CREATE LOGIN webapp WITH PASSWORD=N'[email protected]!', DEFAULT_DATABASE=JudgeMyTaste




USE JudgeMyTaste


EXEC sp_addrolemember 'db_owner', 'webapp'


It might seem like a lot going on here, but it simply creates a login for SQL Server, makes that login a user for the JudgeMyTaste database, and add it to the db_owner role for the database. This will allow that login to do all the CRUD operations that the application will need. Now your database is ready to be used by your application!

Connect SQL Server to Your ASP.NET Core 2.2 MVC Application

Before anything else, you’ll need the Entity Framework Core NuGet package. To install it, run the following command in the terminal.

dotnet add package Microsoft.EntityFrameworkCore.SqlServer --version 2.2.4

Start by adding the connection string to your appsettings.json file in the root of your MVC project, so that it looks like this:

  "Logging": {
    "LogLevel": {
      "Default": "Warning"
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "JudgeMyTasteDatabase": "Server=.;Database=JudgeMyTaste;user id=webapp;[email protected]!"

In the Models folder, create a class file called FavoriteBand.cs.

using System;
using System.ComponentModel.DataAnnotations;

namespace JudgeMyTaste.Models
  public class FavoriteBand
    public int Id { get; set; }
    public string Name { get; set; }
    public string EnteredBy { get; set; }
    public DateTime EnteredOn { get; set; }

This class will be used to work with the FavoriteBand entries.

In the root of the project, create a folder called Data to house the database context for the application. Create a C# file called JudgeMyTasteContext.cs with the following contents:

using JudgeMyTaste.Models;
using Microsoft.EntityFrameworkCore;

namespace JudgeMyTaste.Data
  public class JudgeMyTasteContext : DbContext
    public JudgeMyTasteContext(DbContextOptions<JudgeMyTasteContext> options) : base(options)

    public DbSet<FavoriteBand> FavoriteBands { get; set; }

In your Startup.cs file, in the ConfigureServices() method, right before the services.AddMvc()... line, add the newly created context with the connection string.

services.AddDbContext<JudgeMyTasteContext>(options => options.UseSqlServer(Configuration.GetConnectionString("JudgeMyTasteDatabase")));

Now your database is all hooked into your application. All you need to do is create some way for the user to enter their favorite bands. To get some more scaffolding goodness for the CLI, install the Code Generation tool.

dotnet add package Microsoft.VisualStudio.Web.CodeGeneration.Design

Now you can scaffold a controller to handle all the CRUD operations for the FavoriteBand class by running the following command from the terminal.

dotnet aspnet-codegenerator controller -name FavoriteBandsController -async -m JudgeMyTaste.Models.FavoriteBand -dc JudgeMyTaste.Data.JudgeMyTasteContext -namespace Controllers -outDir Controllers -udl

This is a long one but if you break it down into its component pieces, it’s easier to understand.

The first part just calls the dotnet CLI’s new aspnet-codegenerator command for a controller. You want the controller’s name to be “FavoriteBandsController” and for the controller actions to all be -async. The model being used to generate the controller is the JudgeMyTaste.Models.FavoriteBand class, and the database context will be the JudgeMyTaste.Data.JudgeMyTasteContext class you just created. The namespace and output directory for the controller will be Controllers and the -udl switch tells the generator to use the default layout for the views it will generate (yeah, it’s going to generate views for everything too!). Pretty cool, right?

Once you run the command, you should see the controller and all it’s views show up. The only thing left is to create a link so that users can get to the favorite bands section of the site easily.

In the Views/Shared folder open the Layout.cshtml file and add a link to the menu to get to the new section of the site.

<li class="nav-item">
  <a class="nav-link text-dark" asp-area="" asp-controller="FavoriteBands" asp-action="Index">Favorite Bands</a>

Now when you run the application, you can click on the Favorite Bands menu item and see a list of all the favorite bands that have been entered. Of course there aren’t any right now, so add one using the Create New link at the top of the page and see it show up in the listing.

Now it’s a little cumbersome to add the EnteredOn value manually, and the code generator you used can’t know that you can just add that field to the entry as it’s being saved, so change the Create() method of the FavoriteBandController to add it automatically.

// POST: FavoriteBands/Create
// To protect from overposting attacks, please enable the specific properties you want to bind to, for
// more details see
public async Task<IActionResult> Create([Bind("Id,Name,EnteredBy")] FavoriteBand favoriteBand)
  if (ModelState.IsValid)
    favoriteBand.EnteredOn = DateTime.Now;
    await _context.SaveChangesAsync();
    return RedirectToAction(nameof(Index));
  return View(favoriteBand);

The only things that have changed is that I removed the EnteredOn field from the Bind statement in the method signature, and I added the value DateTime.Now as the value right before saving it to the database.

Add Authentication to Your ASP.NET Core 2.2 MVC + SQL Server Application

What you have now is okay, but there’s currently no way to keep users from editing other user’s entries. We want to make sure to judge people for their favorite band that they actually entered, right?

No reason to write this yourself. You can easily integrate Okta to handle the authentication for you and easily:

Sign up for a forever-free developer account (or log in if you already have one).

Once you have signed up and logged in, you’ll be taken to your dashboard. Make note of your Org URL in the top right corner.

Click on the Applications menu item at the top, click Add Application, and from the first page of the wizard choose Web and click Next.

On the next screen, change the application name to “Judge My Taste App” and update the Base URIs value and the Login Redirect URIs to reflect to the correct port and the fact that you’re running on the HTTPS scheme.

Then click Done and you’re taken to the application page. On the General Settings tab click Edit and add a URL to the Logout Redirect URIs with a value of <a href="https://localhost:5001/signout/callback" target="_blank">https://localhost:5001/signout/callback</a>. This is where Okta will redirect back to after the logout call. This is handled by the ASP.NET OIDC Middleware.

Configure Your ASP.NET Core 2.2 MVC Application for Authentication

Now you need to tell your application how to use Okta for authentication. The easiest way is to use the ASP.NET SDK from Okta. You can install it from NuGet using the following command:

dotnet add package Okta.AspNetCore --version 1.1.5

Add some configuration values to your appsettings.json file to that the final file looks like this:

  "Logging": {
    "LogLevel": {
      "Default": "Warning"
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "JudgeMyTasteDatabase": "Server=.;Database=JudgeMyTaste;user id=webapp;[email protected]!"
  "Okta": {
    "ClientId": "{yourClientId}",
    "ClientSecret": "{yourClientSecret}",
    "OktaDomain": "https://{yourOktaDomain}",
    "PostLogoutRedirectUri": "https://localhost:5001/"

This PostLogoutRedirectUri is the URL that the middleware will redirect to once Okta has redirected back to the signout/callback URL. You can use any valid URL in the MVC application. Here, I am just redirecting to the root of the application.

Back in the Startup.cs file, add the following using statements:

using Okta.AspNetCore;
using Microsoft.AspNetCore.Authentication.Cookies;

Then at the very beginning of the ConfigureServices() method add:

var oktaMvcOptions = new OktaMvcOptions();
oktaMvcOptions.Scope = new List<string> { "openid", "profile", "email" };
oktaMvcOptions.GetClaimsFromUserInfoEndpoint = true;

services.AddAuthentication(options =>
  options.DefaultAuthenticateScheme = CookieAuthenticationDefaults.AuthenticationScheme;
  options.DefaultSignInScheme = CookieAuthenticationDefaults.AuthenticationScheme;
  options.DefaultChallengeScheme = OktaDefaults.MvcAuthenticationScheme;

This is a pretty dense chunk of code, but most of it is boilerplate for the OIDC middleware that the Okta SDK is built on. The first part just binds all of those configuration values you just added in appsettings.json to the oktaMvcOptions. It also adds the scopes you want to receive (which are the OpenID information, the user’s profile, and the user’s email address). It also tells the middleware that it can get the claims from the user info endpoint, which all OIDC identity providers have.

When the code adds authentication, it tells the OIDC provider to use cookies for storing tokens and that you’ll be sending users to Okta from an MVC application.

To actually wire up authentication, you need to tell the Configure() method to use this service you just configured. Right before the app.UseMvc(...) line, add:


Okta is now configured in your application! You still need to set up your application to challenge the user (send them to Okta to authenticate).

Create a new controller in the Controllers folder called AccountController with the following code:

using Microsoft.AspNetCore.Authentication.Cookies;
using Microsoft.AspNetCore.Mvc;
using Okta.AspNetCore;

namespace JudgeMyTaste.Controllers
  public class AccountController : Controller
    public IActionResult Login()
      if (!HttpContext.User.Identity.IsAuthenticated)
        return Challenge(OktaDefaults.MvcAuthenticationScheme);
      return RedirectToAction("Index", "Home");

    public IActionResult Logout()
      return new SignOutResult(new[]

This will give you a Login() and Logout() method to wire up some menu items. Speaking of which, add a new view in Views/Shared called _LoginPartial.cshtml. This will house all the code for the login menu items.

@if (User.Identity.IsAuthenticated)
  <ul class="navbar-nav ml-auto">
      <span class="navbar-text">Hello, @User.Identity.Name</span> &nbsp;
      <a onclick="document.getElementById('logout_form').submit();" style="cursor: pointer;">Log out</a>
  <form asp-controller="Account" asp-action="Logout" method="post" id="logout_form"></form>
  <ul class="navbar-nav">
    <li><a asp-controller="Account" asp-action="Login">Log in</a></li>

Change the main menu in Views/Shared/_Layout.cshtml to add this in and move the main menu to the left and have the login menu on the far right. The final div that houses the menu should look like this:

<div class="navbar-collapse collapse justify-content-between">
  <ul class="navbar-nav mr-auto">
    <li class="nav-item">
      <a class="nav-link text-dark" asp-area="" asp-controller="Home" asp-action="Index">Home</a>
    <li class="nav-item">
      <a class="nav-link text-dark" asp-area="" asp-controller="Home" asp-action="Privacy">Privacy</a>
    <li class="nav-item">
      <a class="nav-link text-dark" asp-area="" asp-controller="FavoriteBands" asp-action="Index">Favorite Bands</a>
  <partial name="_LoginPartial" />

The class list for the navbar-collapse has changed to add the justify-content-between class that will keep the menus apart. The ul’s class also changed to mr-auto which will help keep it left. Lastly, the login partial is added at the end of the menu.

Don’t just sit there, fire this thing up and judge me for liking Nickleback!

Now you have a complete CRUD slice built in an ASP.NET Core 2.2 MVC application saving data to a SQL Server database! Now you can take the same path to add things like favorite movie, favorite food, and favorite beverage so that you can easily and completely judge people for their taste online!!!