Creating An Azure SQL Database

This article will explain how to create Azure SQL Database. We will see it step-by-step.

Please refer to my previous article,

Before we start, let's understand the objective of this demonstration which tells what exactly will be covered in this article.

Create an Azure SQL Database.
Create a server-level IP firewall rule.
Connect to the database.
Create tables in the database.

Prerequisites

Conceptual Understanding of Azure SQL Database.
You must have Azure Portal Access.

Azure SQL Database

SQL Database is a high-performance reliable cloud database. It is a managed cloud databaseas-a-Service (SaaS) that uses the SQL Server database engine in the Azure Cloud Platform.

It becomes easier for the developers/programmers to develop their products as there is less involvement in the maintenance part of the product. It supports relational, JSON, XML, and spatial data structures. Azure SQL provides various advanced features to its users such as Long-term backup retention, Geo-replication, Automatic tuning, Scaling database resources, business continuity, etc.

Step 1

Open the Azure management portal and log in to https://portal.azure.com

Step 2

Click on "Create a Resource".

Create Resource

Step 3

Search and Select "SQL Database". Then, Click on "Create".

SQL Database

SQL Database Search

Step 4

Click on the "+ Create" button.

Create SQL Database

Step 5

After clicking create option, fill in the required details in the Basics tab

Subscription
Choose a suitable subscription.
Resource group
Resource group is a container that holds related resources for an Azure solution. You can keep the resource group as it is or you can also create one by clicking "Create New".

Before details are filled.

Basic Details

After the details are filled.

Basic Details after details filled

Step 6

Fill the details under the "Database Details"

Database Name
Name your database.

Server
After naming the database, you will have to create a server. Click on "Create new", and you will be prompted to set up some details.
- Server name: Enters a unique server name.
- Location: Select the location for your server.
- Authentication method: Use the SQL authentication method
- Server Admin Login: Create a username as per your choice.
- Password: Enter a strong password containing uppercase alphabets, special characters, and numbers. Then, confirm your password by re-entering it.

Afterward, Click on OK button at the bottom.

SQL Database server

Elastic Pool
If you had a large number of databases and want to manage the performance and the cost together, you could put them in an elastic pool. But here, you need to create a new one. Hence, Select "No"

Compute + Storage
Once you are done creating a server, you will be able to click on the "Configure Database", where you will have to select pricing options. Here, you need to configure the database. This defines the storage and CPU usage of the databases. There are mainly two types of configurations – DTU and vcore-based
- DTU stands for the Database Transaction Unit that will define how many resources your database has. DTU offers a blend of computing, memory and I/O resources. There are three configurations
  - Basic: DTU:5, Size:2GB
  - Standard: DTU:10-3000, size: 1TB
  - Premium: DTU:125-4000, size: 4TB
- vCore Model allows you to dig into the underlying resources and scale them independently for optional performance. It allows you to take Azure Hybrid Benefit for SQL Server.
  - General-purpose/Standard: Scalable compute and storage options
  - Business Critical/Premium: On-demand scalable storage
  - Hyperscale: High transaction rate and high resiliency

For the initial use, the In-Service tier, Select "Basic" under the DTU-based purchasing model. Then, Click on "Apply".

Service Tier

Service Tier Basic

Step 7

"Backup Storage Redundancy" section, Select Geo-redundant backup storage.

Locally-redundant backup storage
Copies your backups synchronously three times within a single physical location in the primary region. LRS is the least expensive replication option but isn't recommended for applications requiring high availability.
Zone-redundant backup storage
Copies your backups synchronously across three Azure availability zones in the primary region.
Geo-redundant backup storage
Copies your backups synchronously three times within a single physical location in the primary region using LRS, then copies your data asynchronously to a single physical location in the paired secondary region.

Step 8

After configuring the server, Now, Click on "Next: Networking >" to jump onto the networking section

Now, In the Networking tab, Choose the below options

Select the Connectivity method as a Public endpoint
Choose the No option for Allow Azure Services and resources to access this server.
Select the Yes option for Add current client IP address.

Networking

Step 9

Click on "Next: Security >" and Keep these options as it is.

Security

Step 10

Click on "Next: Additional Settings >" and go to the Additional Settings tab. Select the "None" option for "Use existing data".

Step 11

Click on "Next: Tags >" and Keep it as it is. Then, Click on "Review + Create".

Step 12

"Review + Create" option will provide you with an overview of your Azure SQL Database. Afterwards, click on "Create" to create your database.

Review + Create

Step 13

Once click on "Create" it will Initialize validation for deployment

Validation initialize

Step 14

Initializing deployment After Validation Passed

Step 15

A message will notify you once the deployment is done.

Deployment complete

Step 16

Once done with the deployment, when you go to the Resource group, you can see the database that you have just created. Select your database.

Azure Resource

Step 17

When you click on the database, you will the 'server name'. Copy the server name and then open SQL Server Management Studio (SSMS).

Step 18

Open SSMS, Enter the server name which you have copied in the previous step, and change the Authentication to "SQL Server Authentication". Click on "Connect".

SSMS

Step 19

After clicking on "Connect", the below pop-up window appears. Now, you have to set the firewall rules for your Azure SQL server.

Step 20

Now, Click on Set Server Firewall from the Overview tab of the SQL Database.

Set Firewall

Step 21

Now, In the firewall rules section, Add your "Client IP address". Then, you’ll notice one IP address gets automatically added to the below list. "Save" the Changes.

IP Address

Step 22

Now again, In the SSMS again, Click on "Connect" after entering the credentials.

SSMS

Step 23

Your Azure SQL Database is now successfully connected. You can access the database from Azure Database in SSMS.

SSMS connect to Azure SQL Database