Coding Azure 2: Configuring an Azure SQL Database

The critical part of a business application is the database. Here’s how to configure a serverless database in Azure SQL (and how to do it for free, if you just want to experiment).

This post is going to walk through setting up an Azure SQL database for a typical cloud-native application. That actually requires setting up three things:

• An instance of a SQL Server database management system: For this post, I’ll call that “the database engine”
• A virtual machine that the database engine will be running on: I’ll call that “the database server”
• A collection of tables managed by my database: I’ll call that “the database”

When I’m referring to all three of these three separate meanings, I’ll refer to “the database resource.”

Defining Your Database Resource

The first step is to surf to the Azure portal, type “Azure SQL” into the search box in the top center of the page, select Azure SQL from the dropdown list to get to the Azure SQL overview page.

Once on the Azure SQL overview page, from the menu across the top of the page, at the left-hand end, click the + Create menu choice to take you to the Select SQL deployment option page.

Currently (January 2025), this page gives you three choices. For the sample app I’m creating for this series, I’m going to set up the simplest version: In the SQL databases choice, I selected the Single database option in the Resource Type dropdown and clicked the Create button. That’s actually not a bad choice for most applications that don’t need the scalability of, for example, a managed instance. For an in-depth discussion of the right options, I liked this overview.

Defining the Database Engine

Clicking the + Create button for your database option will bring you to the Create SQL database wizard. For the case study in this series, I’m going to pick the free Azure SQL database option that’s currently offered. That makes sense when you only want a database resource to use for demo purposes (e.g., this series of blog posts). If you’re doing this in real life, you’ll pick something that doesn’t have the free offer’s limitations: 32 GB of data and 100,000 free processing seconds every month (there’s been at least one month where I used up all my processing seconds just in writing this series).

This offer also commits me to creating a serverless solution (the database server may be auto-scaled if it sees enough activity and, if that happens, I’ll be charged for extra cores as I use them). I’m OK with that because my sample app won’t ever be busy enough to trigger auto-scaling with the resulting charges. In real life, Serverless is a pretty good choice for a production system if, for no other reason, auto-scaling reduces the administration required to size your database resource correctly.

The other choice (Provisioned) only makes sense if you’re confident that you can both predict the demand for your database resource so that you can size your database resource correctly and you don’t expect that demand to fluctuate much around that prediction (if you have a lot of fluctuation, then you’ll be paying for a lot of resources that you’re not using much of the time).

I need to assign my database resource to a resource group and give it a name (upper/lower case and spaces are allowed here). I used WarehouseMgmt. I’m, eventually, going to put all the resources for my application in this resource. Other organizations might put all their Azure SQL databases in a single resource group even though the databases are used by several different applications (a strategy that can make sense if you have a single group that’s responsible for all your databases).

After that, you need to give your database server a name (I used WarehouseDB).

Next, you’ll need to pick a database server to host your database engine (not a real server, of course—you’re creating a virtual server, which means that you’re just being allocated some amount of processing power, disk space and memory on some computer in an Azure datacenter). If you don’t already have a virtual server set up that you can use, click the Create New button to be taken to the Create SQL Database Server page.

Defining the Database Server: Part I

On this page, you’ll need to:

• Give your server a name (this name gets rolled into a URL and will have to be all lowercase and without spaces). Copy the resulting URL—you’ll need it later (if you don’t write it down, don’t panic—you can always return to the Overview page for your database resource to retrieve the URL). I used warehousedbserver.
• Pick an Azure region from the Location dropdown list. I used Canada Central.
• Pick an Authentication method. For the purposes of this case study, I want to show you how to write the code required so that this server can only be accessed by other out-of-the-box Azure resources, so I left that choice at the default of Use Microsoft Entra-only authentication. Other choices would, for example, let you authenticate against your on-premises identity provider (i.e., Active Directory Domain Services).
• Specify which user will act as your administrator: Click on the Set admin link and, from the panel that appears, pick a user from the provided list of identities set up in your tenant’s Entra ID (you’ll probably pick you).

After clicking the OK button, you’ll return to your Create SQL Database page.

Defining the Database Server: Part II

Your next choices are to decide:

• If you want your database engine to share processing resources with other engines by selecting Elastic Pools (this makes sense if you have multiple database engines that never hit capacity at the same time and can share the pool). For my sample app, I did not.
• Whether you want a Production or Development workload environment. These two choices represent bundles of capacity choices that you can modify a little further down on this page. Picking Development, for example, limits you to a single vCore with 32 GB of space; picking Production turns on Hyperscale and removes the limit on size. For my sample application, I picked Development because it’s the cheapest choice.

Configuring Your Database Server Capacity

Now you can override some/all of the Production/Development capacity settings by clicking on the Configure database link. Because I picked the free offer, most of the choices I could make here can’t be changed. The only choice I can make is to auto-pause the database engine after I’ve used up my 100k of free seconds for the month and not to restart the database engine until next month (which I did to avoid running up any charges).

In real life, you can pick:

• The maximum and minimum number of vCores you’ll be allocated (that will give you more asynchronous/parallel processing for handling multiple requests)
• The maximum space that your database resource can grow to
• Whether you’d like your database resource to be copied to another zone

That last option probably requires some explanation. By default, you have failover protection within the Microsoft datacenter hosting your database resource. You can pick zone redundancy, which gives you failover coverage for your database resource in in another datacenter in the same region (e.g., East US, UK South). All the Microsoft datacenters in the same region have roughly the same network response speed so, if your database resource does fail over to another datacenter, you shouldn’t notice any degradation in performance (though there will probably be a short disruption during the failover). Zone redundancy will, of course, cost you more.

After you click the Apply button, you’ll be back to your Create SQL Database page.

Defining the Database Server: Part III

Your last choice is where your backups are stored. Because I picked the free offer, I don’t get a choice (I still get backups, though—I just don’t get to pick where they’ll be kept). The good news here is that I’m getting the cheapest option (locally redundant with all the copies of my backups are stored in the same datacenter that’s hosting my database resource) and, for this case study, that’s what I want.

The other options will store your backups in another datacenter in the same region or in a datacenter that’s (usually) over 300 miles away, or both (at higher cost, of course).

Configuring Access

The next tab in the wizard lets you set your networking options. Selecting No access effectively defers setting access until after your database resource is created when you can configure its firewall settings. But you will, in the end, have to let something access your database engine, and you might as well select your initial setup here.

Initially, I want to enable the Public option, which allows devices to access outside of the Azure cloud so that I can use my standard database management tools (SQL Server Management Studio and Azure Data Studio) from my desktop to configure my database.

However, I also wanted to limit that public access to just the computer that I’m going to use to manage my database (which is the computer I’m currently using when creating my Azure SQL database). I used the “add your client” option on the network tab that created an exception in the database server’s firewall to let my computer in.

In real life you don’t, in the long run, want the public option enabled—it’s an unnecessary exposure to a cruel and dangerous world. Eventually, you’ll return to database engine and, from the networking tab in the left-hand menu, select the Networking option to remove your client computer from the list of allowed devices.

You’ll also want to set the Allow Azure services … option that enables applications in your tenant to access the database engine. I turned this on because my case study will include a Web Service running in an Azure App Service that will need to access this database engine.

Loading Data

I then skipped ahead to the Additional settings tab and, under Data source, selected Sample. This loads a copy of one of Microsoft’s standard sample databases, AdventureWorksLT. I can do this because I really don’t care what data I use for this case study, so Adventure Works is fine with me.

You, on the other hand, will want to load some data from your organization (you have a lot of choices for loading existing data. Those options include just loading from an on-premises SQL Server backup (provided you can get the backup file up to the cloud).

After selecting the Sample data, I clicked the Review + create button to go to the wizard’s last page where I clicked the Create button to create my database server and its virtual machine.

Accessing the Database

To confirm that everything is set up correctly, you can use Azure Data Studio (ADS) or SQL Server Management Studio (SSMS) to connect to your database server and review its contents.

Azure Data Studio

After starting ADS, select the Create a Connection choice to connect to your Azure SQL database server. To make that connection, when the Connection panel appears:

• In the Server textbox, enter the URL for database server (note: not the database engine) that you copied earlier. In my case, that’s warehousedbserver.database.windows.net.
• From the Authentication type dropdown, select the authentication method you set when you created the database server (I picked Entra ID as my authentication provider, so I picked that option).
• From the Account dropdown list, select Add an account and go through the process of logging into Azure.
• In the Name textbox, give this connection a name that you’ll recognize when you want to reconnect with this database engine from ADS.
• Finally, click the Connect button.

You should connect to your database server and get a panel listing the databases managed by your engine (e.g., master and whatever database you just created—in my case, that’s the database I called WarehouseDB that holds the Northwind sample database).

To view any tables in your database, just double-click on your new database (I did and was able to drill down to the sample AdventureWorksLT tables and their data that I loaded).

SQL Server Management Studio

Personally, I prefer using SSMS, but that’s probably just habit on my part. After starting SSMS, you’ll be presented with the Connect to Server dialog that will let you connect to your database server (notice: not the database engine—it’s an important distinction). In that dialog:

• In the Server name textbox, enter the URL for the database server that you copied earlier (in my case, that’s warehousedbserver.database.windows.net).
• In the Authentication dropdown, select the choice that matches the authentication method you picked when creating your database server. When I created my database engine, I picked Entra ID as my authentication choice, so I selected Microsoft Entra MFA from this list.
• In the User name textbox that appears, enter your Azure username.
• Click the Connect button—you’ll almost certainly be asked to authenticate to Azure. Once you’re logged in you can drill down to the tables in your database in the panel on the left side of the SSMS window.

Next Steps

Now, with your database created, you can start thinking about creating an application that uses it. That’s my next post, where I’ll create an Azure App Service/Web app, create a Web Service in Visual Studio that access the database and deploy that Web Service to my App Service.