Azure SQL Database Simplified 101

on Data Analytics, Data Integration, Data Mapping, Database Management Systems, ETL • October 27th, 2021 • Write for Hevo

Azure SQL Database - Featured Image Hevo

Database Technology has seen a wide array of changes over the past years and is responsible for the storage and processing of data from multiple sources in order to gain insights from its customers. One of the most popular Cloud Databases available in the market today is the Azure SQL Database developed by Microsoft.

It is part of the Microsoft Azure franchise and is provided as an additional service to organizations from Microsoft. It comprises a group of secure, managed, and smart products that use the SQL Server Database engine integrated with the Azure Cloud. Moreover, the Azure SQL Database is built upon the familiar SQL Server Engine, which makes it very simple to migrate applications easily and continue to use different tools on it.

This article provides a comprehensive guide on the Azure SQL Database. It provides an overview of the Database along with its features so that you can use it across a wide array of platforms. Further, it also provides some steps to create and manage your resources using the Azure Portal. Read along how you can leverage the Azure SQL Database for your organization.

Table of Contents

What is Azure SQL Database?

Azure SQL Database Logo
Image Source

Azure SQL Database is a fully managed platform as a service (PaaS) database engine that handles most of the management functions offered by the database including backups, patching, upgrading, and monitoring with very minimum user involvement. 

A patched OS and stable version of SQL Server (usually latest) is needed to run Azure SQL Database successfully. Also, It helps in creating a highly available and high-performance data storage layer as well for the applications. Apart from this, Azure SQL also presents solutions including advanced query processing, intelligent query processing, and high-performance in-memory technologies. The Azure Cloud supports a wide range of products such as:

  • Azure SQL Database: It supports modern Cloud applications on a managed database service that also includes serverless compute.
  • Azure SQL Managed Instance: This service helps in modernizing the existing SQL Server applications at scale with an intelligent fully managed instance as a service.
  • SQL Server on Azure VMs: This service helps in the Lift-and-shift of your SQL Server workloads that helps in the ease and maintaining the SQL Server Compatibility and Operating System-Level Access.

To learn more about the Azure SQL Database, click this link.

Deployment Models in the Azure SQL Database

The Azure SQL Database provides 2 deployment options for a Database. They are given below.

  • Single Database: This type of database is a managed and isolated database. This type of deployment is useful if you have modern cloud applications and microservices that need a single reliable data source. More about this can be read on the following link
  • Elastic Pool: An Elastic Pool is a collection of single databases including a shared set of resources, e.g. CPU or memory. It is possible to move Single resources into and out of an elastic pool. More about this can be read on the following link

Simplify Azure MS SQL Data Analysis with Hevo’s No-code Data Pipeline

Hevo Data, a No-code Data Pipeline helps to load data from any data source such as Databases, SaaS applications, Cloud Storage, SDK,s, and Streaming Services and simplifies the ETL process. It supports Azure MS SQL, along with 100+ data sources (including 40+ free data sources), and is a 3-step process by just selecting the data source, providing valid credentials, and choosing the destination. Hevo not only loads the data onto the desired Data Warehouse but also enriches the data and transforms it into an analysis-ready form without having to write a single line of code.

Its completely automated pipeline offers data to be delivered in real-time without any loss from source to destination. Its fault-tolerant and scalable architecture ensure that the data is handled in a secure, consistent manner with zero data loss and supports different forms of data. The solutions provided are consistent and work with different BI tools as well.

GET STARTED WITH HEVO FOR FREE

Check out why Hevo is the Best:

  • Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
  • Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
  • Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
  • Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
  • Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
  • Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.

Simplify your Data Analysis with Hevo today!

SIGN UP HERE FOR A 14-DAY FREE TRIAL!

Features of the Azure SQL Database

The Azure SQL Database has a combination of innovative features that make it a good alternative to the other popular databases in the market. Some of those features are given below:

Scalability

You can define the number of resources assigned and it helps in improving the scalability of the system. Some of them are:

  • As mentioned above, in single databases, each database is isolated from others and it also provides portability. Every single database has a confirmed amount of memory and storage resources. Resources assigned with each database are dedicated to the particular database without being shared with other databases. It is also possible to scale up and scale down the database resources easily as can be read here in detail. 
  • The second option which is Elastic Pools can help you assign resources that are shared by the other databases present in the pool. It is possible to create or move the existing database as well and also save money meanwhile. It is also relatively easy to scale elastic pool resources up and down as needed. This can also be read in detail in the following link

The above-mentioned methodologies help you in making your Azure SQL Database scalable as per the resource requirements as well. However, there is a considerable difference between Dynamic Scaling and Auto-Scaling. Auto-scaling happens automatically whereas Dynamic Scaling involves manual scaling without any downtime.

Purchasing Model

There are multiple Azure SQL Database Purchasing Models bought including:

  • The first one of the Azure SQL Purchase mode is the vCore-based purchasing model in which you can choose the number of vCores, memory amount, and the storage limit as well. It also helps you to use some benefits including Azure Hybrid Benefit which helps a lot in saving costs. Further about this model of purchase can be read on the following link
  • The second one is the DTU-based purchasing model which offers the ability to compute, manage I/O resources in the three service tiers and also supports light and heavy database workloads. More can be read about this model on the following link
  • The third model is known as the Serverless Model. As mentioned above as well, this method helps in the automatic scaling of the resources including storage, memory, speed, etc. by automatically adjusting the costs as peruse and making these things inactive during the period in which these are not being used and turning those back on when they are needed hence optimizing the cost-saving process. Further can be read on the following link.

Service Tiers

Azure SQL Database Service Tiers are designed for different types of applications which are described below:

  • General Purpose or Standard Service Tier

This is designed for common workloads including budget options and balancing storage options as well.

  • Business Critical or Premium Service Tier

This service is specifically designed for OLTP applications that have not only a high transaction rate but also low-latency I/O services. It also offers high resilience to failures by isolating different replicas.  

  • HyperScale Service Tier

This service tier is available for a very large OLTP database and it also helps to autoscale the storage and helps in computing the fluidity. 

Advanced Security & Compliance

It is important to mention the Advanced Security & Compliance flexibilities offered by the Azure SQL. 

SQL Database provides a range of security features including High-Risk Users, Medium Risk Users, Unprotected Risky Sign-Ins, Legacy Authentications, and many others. Further can be read about these security features on the following link

Advanced Threat Protection

This is another important feature offered by the Azure SQL Database. Azure SQL provides a unified package for advanced SQL security capabilities. The functionalities included are managing database vulnerabilities, detecting anomalies, and malicious activities that can be a threat to the database security which is very critical. 

How to Create an Azure SQL Database

It’s now time to create a SQL Database in Azure. To do so, you’ll need an Azure user account. If you don’t have an account already, you can go and create a free Azure account on their website.

  • Once you’re ready with your account, enter your credentials and log in.
  • Click on the “Go to the Portal” link to visit the Azure Management Portal.
Azure SQL Database: Management Portal
Image Source
  • Search for “SQL databases” from the search bar, and click on it.
Azure SQL Database: SQL Databases
Image Source
  • From this page, click on the “Add” button to create a new database.
  • Now, click on the “Create SQL database” button to proceed. The “Create SQL Database” page will now be displayed.
Azure SQL Database: Create SQL Database
Image Source
  • To create a new resource group for the Azure SQL Database, click on the “Create new” button located near the “Resource group” field on the “Create SQL Database” page.
Azure SQL Database: Resource Group
Image Source
  • Enter the required details (name of the resource group) and click on “OK“.
  • In the Database Details section, give a name to this database according to the naming policies.
  • Now, you need to create a new server. Click on the “Create new” button and enter the required details on the “New server” tab located on the right side.
Azure SQL Database: Server
Image Source
  • Now, you need to decide on the purchasing model of the Azure SQL database. By default, Gen5, 2 vCores, 32 GB storage configuration is selected. Click on the “Configure database” to choose other pricing models.
Azure SQL Database: Purchasing Model
Image Source
  • After choosing the pricing model, you can click on the “Review + create” tab to review all details of the configured database.
  • After reviewing the details, click on the “Create” button to finish the setup.
Azure SQL Database: Review and create
Image Source
  • You can check the status of the deployment of the Azure SQL Database under the “Notifications” menu.
  • Once the deployment is done, the notification will be changed to “Deployment succeeded“. Now, you’re good to go.

Steps to Create & Manage Resources on the Azure Portal

The Azure portal provides a very simple and easy-to-use process for the Azure Portal which makes it possible to manage the Azure SQL resources that also include the Virtual Machines (VMs). Given below are the steps to create and manage your resources on the Azure Portal:

Step 1: Create a New Azure SQL Resource

To access a new Azure SQL resource, one simply can access the SQL page from the portal menu, select the Azure SQL resources that are needed, and can add those resources. 

Existing resources can be managed in the desired list as well. Simply create new Azure SQL resources, and just Select and Add as shown in the below image.

Azure SQL Database: Creating a Resource
Image Source

Step 2: Viewing Additional Information of the Options

After selecting and Adding, one needs to view the additional information about the various options by selecting show details on the tile as shown in the below figure.

Azure SQL Database: Additional Info in Azure SQL Database
Image Source

Azure SQL Pricing Models

The pricing model directly affects the performance of the deployed database. There are two pricing models available:

  • The Virtual core (vCore)-based Purchasing Model is available for both Azure SQL Database and Azure SQL Managed Instance. It starts from $198.95 per vCore without changing storage.
vCore-based ModelsMaximum CoresMaximum StorageMinimum Price
General Purpose804 TB$198.95/vCore
Hyperscale80100 TB$223.86/vCore
Business Critical804 TB$528.15/vCore
vCore-based Model
  • The Serverless Model is also a Virtual core (vCore)-based Purchasing Model. This helps in the automatic scaling of the resources including storage, memory, speed, etc.
vCore-based Serverless ModelMaximum CoresPricing
General Purpose40Set at a minimum of $0.5 and a maximum of $1, the Compute Cost is 0.000159/second.
Serverless Model
  • The Database transaction unit (DTU)-based Purchasing Model is available for Azure SQL Database. It starts from $1 per DTU.
DTU-based ModelsMaximum DTUsMaximum StorageMinimum Price
Basic52 GB$1/DTU
Standard30001 TB$1.5/DTU
Premium40004 TB$3.72/DTU
DTU-based Model

To better understand the related storage costs and features offered, refer to their official page.

How to Connect an Azure SQL Database

You learnt how to create an Azure SQL Database in the previous sections, you can now connect and use it. To do so, follow the below-mentioned steps.

  • Search the name of the deployed database from the search box.
  • From the search results, click on your deployed database. The management screen of the database will now be displayed.
  • Here, you can manage, monitor, and configure the database.
  • From the “Overview” tab, click on the “Set server firewall” button to connect the database.
Azure SQL Database
Image Source
  • From the “Firewall settings” page, click on the “Add client IP” and it will automatically find the public IP and will add a new rule by itself.
Azure SQL Database: Firewall Settings
Image Source
  • Click on “Save“.
  • You can now connect Azure SQL Database to the server via the SSMS.
Azure SQL Database: Connect
Image Source

Conclusion

This article provided a comprehensive guide on the Azure SQL Database. It also provided a brief overview of it and its features. It also highlighted some steps for creating and managing resources on the Azure Portal. Overall, the Azure SQL Database provides a very secure & scalable database needed to perform functionalities in a very positive way.

In case you want to integrate data from data sources like Azure MS SQL into your desired Database/destination and seamlessly visualize it in a BI tool of your choice, then Hevo Data is the right choice for you! It will help simplify the ETL and management process of both the data sources and destinations.

VISIT OUR WEBSITE TO EXPLORE HEVO

Want to take Hevo for a spin?

SIGN UP and experience the feature-rich Hevo suite first hand.

Share your experience of learning about the Azure SQL Database in the comments section below.

No-code Data Pipeline For Azure MS SQL