Microsoft Azure – Azure SQL Database Deployment Options

Azure SQL Database is a database built for modern Cloud applications. Now coming back to the eccentric concepts for which consider If you have ever worked with Azure SQL Database, you must have come across terms such as “single database” or “Elastic scaling” and so on. There is an increasing trend of building applications in the cloud today. For any application hosted in Azure, a database is a necessary component. 

In this article, we take a beginner-friendly approach to understanding Azure SQL Databases and the various types of deployment the service offers.

Overview

• Defining and understanding Azure SQL Database
• Single Databases
• Elastic pools
• How to deploy Azure SQL Database

Tip: Let us discuss few terminologies that are as follows:

1. PaaS: Platform as a Service
2. DB: Database
3. SLA: Service Level Agreements
4. DTU: Data Transmission Units
5. vCores: Virtual Cores   

Azure SQL DB (database) is a PaaS (Platform as a Service) feature of Microsoft Azure. It’s based on the latest version of the SQL Server DB engine.  

PaaS means that Azure takes care of maintaining, upgrading, and patching the database, leaving you free to build storage around Azure services and applications without worrying about infrastructure. Azure SQL database also ensures 99.99% SLA of availability. Azure SQL Database also offers 3 service tiers, namely standard, Premium, and hyper-scale tiers based on your requirements.  

Available deployment options

A. Single Database: As the name suggests, is a database with a dedicated set of resources running on an Azure server. Each database has individually associated resources and database engines. Each database can be independently assigned to pricing tiers and scaled up/down according to requirements. These databases are best suited if you have an application with predictive load patterns. 

For example: The application load increases between 4-6 pm every day.

Note: One can also add a single database to an Elastic pool, as we will see further.

B. Elastic pools: As the name suggests Elastic pools are basically a group of databases that share some resources. These databases share a common server and common resources. These resources (compute, memory and storage) can flexibly be scaled up or down based on application requirements. You can also add/remove databases from an Elastic pool with negligible downtime. Here we only have to pay per hour/per pool, depending upon the DTU or vCore usage. The more databases that share a single pool, the higher the cost savings. 

Note: Elastic pools are best suited for applications with unpredictable load patterns.

Implementation: 

Suppose an application developer under a conventional database-providing model, you will have to figure out your database capability. Let’s suppose you initially provision 4 vCores for your database. When the application load is low, your 4 vCores sit unutilized, while you pay for every hour those resources are provisioned. On the other hand, at peak hours, you run the risk of 4 vCores being insufficient for your application needs.  Elastic pool deployment solves all these problems. You can add a specific number of databases (let’s say 5). These 5 databases reside on a single server and share resources. You can set a minimum and maximum threshold for resources. When database load is high, Azure automatically provisions resources until the maximum threshold is reached. When the load is low, Azure Databases consume minimal to zero resources.  

By now we are familiar with grasp essentials for Azure DB and its deployment options, now let us see a demo from the Azure portal.

Note: Visit this portal and adhering forward if user does not have created an account, here is how we can create an account and get started with Azure’s free trial. If you do already have an account, log in and let’s adhere forward. 

How to Deploy Azure SQL Database?

Below is the home page of Azure that appears that is shown below in visual aid as follows: 

Step 1: Type “SQL database” in the search bar and hit enter.

Step 2: On the page that opens, click “Create SQL Database”.

SQL Database home

Step 3: On the following page, under the “Basics” tab, enter information like your subscription and resource group. Note that we can also create a new resource group/SQL server at this stage by clicking “Create new” button.

Create SQL Server

Step 4: This is followed by your server details and account details. If you want to deploy an elastic pool, you can toggle to the “Yes” option and provision resources for your pool. You can also select backup options under the Basics tab or under the additional settings tab.

Basics tab

Step 5: Move on to the “Networking” tab, and define your IP connectivity and firewall rules. 

Note: User need at least one port to work with a database. Define the firewall rules. Also do remember that private endpoints are often designed to provide a network interface where the SQL storage service can communicate privately with the application.

Networking

Step 6: Under the “Security” tab, you have a list of Azure services that provide database security.

Security tab

Step 7: The “tags” pane allows us to provide optional tagging. In commercial environments, tagging is crucial to define owners, pricing, environments, and more.

Tags

Step 8: Click on “Review+Create”. Here, you can see a summary of all selected options and the final estimated cost. 

Step 9: Lastly click on “Create” to deploy your first SQL Database for Azure.