Microsoft Azure SQL Database Tutorial For Beginners

This tutorial explains concepts related to Microsoft Azure SQL Database like how to connect to and create a database, architecture, price, etc.:

As per today’s modern generation technology, a cloud solution is in high demand, and in the corporate world, they have already adopted modern data center technologies. Microsoft Azure SQL server provides the tremendous, fast, and fully managed cloud database relational solution technique with scalability and data security mechanisms.

In this article, we will understand creating the database using the Azure SQL database, set the standard, and move the data utilizing the Platform as a Service (PaaS) technique.

=> Detailed Series of Simple Azure Tutorials

What is Microsoft Azure SQL Database

Azure SQL Database is a reliable, scalable Database as a Service (DaaS) that also has the capability of durability with fully automated AI-powered techniques.

Using DaaS we can host the database and develop the relational SQL database in the Azure cloud without the use of hardware or software installation. Azure SQL database enables the modern cloud application technique which has plenty of resources to support both the types of relational and non-relational data, for example, XML, graphs, and JSON.

In Azure cloud, we can create the database and run it within 5 minutes. We can also scale up and scale down the database as per the business requirements.

Azure SQL Database Features

The features are:

1. Upgrading and patching.
2. Best long-term backup continuity
3. Geographically distributed data networks
4. Monitoring
5. Develop high-performance data storage layer
6. Support the non-relational data
7. Fast performance using the AI-based Automatic tunic feature
8. High-availability
9. High-scalability using the database resources
10. Automated techniques of the data backups
11. Tremendous business continuity

SQL Azure Database Architecture

Microsoft Azure SQL Database has the ability of scalability and also it is an extremely multi-tenant relational database service that is available on the cloud and performs as Platform-as-a-Service (PaaS) which facilitates the installation of the software, handles the patching, and maintains the servers at cloud platform level without the use of the software license key and versioning.

We can easily go to the Azure portal and monitor it and move the data using PaaS.

Architecture is divided into below 3 categories:

#1) Client Layer: It performs as the User Interface (UI) for applications to get the SQL Database, for transferring the data among the SQL Database and applications. Here we have the mechanism of Tabular Data Stream (TDS), which can easily communicate to the software applications with the. NET, ADO.NET technologies.

#2) Service Layer: It enables the secure gateway connection among the platform and client layers. It has the capability of user access authentication, including SQL database validation rules.

#3) Platform Layer: Using the platform layer we can host the SQL database in the physical server and can use it in multiple physical servers. As per the architecture process, we can see that there are two elements. It also establishes the connection between the SQL database and can be used in the application.

Suggested Reading=>> Review Microsoft Azure tutorial

Azure Service Fabric

Azure Service Fabric can deploy, package, and run the build using microservices and containers and can be used in Azure Infrastructure within the Azure SQL DB, Azure Cosmos DB services.

Below is the feature of Azure Service Fabric:

1. Open source and enable the distributed systems platforms.
2. Develop scalable and stateless reliable solutions with the microservice.
3. Faster code deployment capability.
4. Automate the application independently.
5. Monitor the insights of the health and performance of the applications.
6. Secure point-to-point connection.
7. Best traceability in case of any error.

Single Database In Azure SQL Database

It is a well-managed standalone single database and has its associated resources. We can consider it as a moderate database in an SQL server.

• It handles and runs independently and is suitable for the software application which requires the assigned resources, independent database developments.
• It is a reduced database-centric programming model.
• It can predict performance with the best load balancer techniques

[image source]

Steps to maintain a single database using Microsoft Azure Cloud:

For this, we will require Azure Portal using these below scripts:

1. PowerShell script
2. Azure CLI script

A single database is the most manageable and most responsive option for Azure SQL Database. We can also raise the query to the database using the Query editor in the Azure portal. A single database is an advanced cloud application and maintained by microservices and reliable data sources.

Follow the steps below for creating the Single Database using the Azure portal:

Step 1: Go to Azure portal https://portal.azure.com/ and Sign in to your account

Step 2: Go to the search bar and enter the Azure SQL:

Step 3: On the Azure SQL screen, select ‘+ Add’.

Step 4: Go to Create Azure SQL resource. It will take you to the Select SQL deployment option.

Step 5: Go to Create SQL Database ->Basics. Select the Azure Subscription.

Under ‘Resource group’, click on ‘Create new’, type a resource group name (Your project name -sql), and then select OK.

Step 6: Enter the Database details. Enter Database name (yourdatabase).

For Server, click on ‘Create new’.

Step 7: For providing the server name-> Click on Create new. It will take you to the pop-screen. Here you need to provide the name of the server (SqlServer44). Provide the credential.

Choose the location: For example (US) Central US. Select OK.

Step 8: Under ‘Compute + storage’, for configuring the database you need to, choose ‘Configure database’.

Step 9: On the Configure page, you can select the database ‘Standard’, . Then select Apply.

Azure create SQL database standard:

Step 10: Select ‘Next: Networking’.

Step 11: Choose the ‘connectivity method’ under the ‘Network connectivity’ from the Networking Tab.

Step 12: Go to the Additional settings tab, in the Data source section. For ‘Use existing data’, select Sample. Select ‘Review + create’.

Step 13: Select Create

Step 14: Query the Database: For querying the database, as per the screenshot, we need to use a built-in Query Editor. Here we can easily connect to the database and access the data as in the preview result view.

Step 15: Type the query as per the screen-shot: select ‘Run’ and then it will show you the result.

How to manage and create servers with single databases in Azure SQL Database?

In the Azure Cloud SQL database, we can easily manage and create the servers with the help of these below mechanisms:

• Powershell
• Rest API
• Transact-SQL
• Azure CLI (Common Language Interface)

[image source]

Azure SQL Services

This section explains the Deployment Models for Running SQL Server on the Cloud. Azure SQL is developed by three deployment models.

They are:

• Single Database: It is deployed to an Azure VM and operated with a SQL Database server. This is the primary deployment model.
• Elastic Pool: It is a collection of connected databases that distribute pooled resources.
• Managed Instance: It is a fully operated database instance. It is intended to facilitate the smooth migration of on-premises SQL databases.

With these deployments, we can use these three distinct service tiers:

• General Purpose: It is composed of standard workloads. we can consider it as the default tier.
• Business Critical: It is used for Online Transaction Processing (OLTP) based applications, and provides high-throughput performance.
• Hyperscale: It is an extension of the Business Critical tier. It is invented for large OLTP-based implementations and permits auto-scaling of computing and storage.

In the deployment, we can select two compute models:

• Provisioned: Using this you can use your database for the dedicated Azure SQL service.
• Serverless: Using this your database is performed as a serverless segment. It has the technique of auto-scaling the compute volumes. These volumes are billed by second.

Performance Tuning In Azure SQL Database

Azure SQL Database provides several Database Advisors to implement deep performance tuning suggestions and enables the automatic tuning approaches to enhance performance.

Using the Azure cloud portal we can monitor the CPU and IO usage and pooled databases which can be used in unique sources of applications, data warehouses. It provides insights into the Azure portal dashboard in the Metrics view.

These metrics allow you to check if a database is seizing 100% of IO resources, processors, and memory.

Scaling Using Azure SQL Database

Microsoft Azure provides the dynamic scalability feature, and it enables your database to work as transparently. It provides manual scaling with the least downtime with the automotive Elastic pool technique, which allows the distribution of resources in a pool based on specific database needs.

It has inbuilt scripts which help the automated scalability for a single database in Azure SQL Database.

Types of Scaling:

1. Horizontal scaling: It can add and remove the database to improve retention and performance, also described as “scaling out”.
2. Vertical scaling describes the increasing or decreasing the compute size of an original database, also is recognized as “scaling up.”

[image source]

Cross Queries In Azure SQL database

Microsoft Azure provides the cross-querying feature in Azure SQL Database by elastic queries. It provides the elastic feature which enables the cross-querying technique.

It provides the Transact-SQL process which implements on multiple Azure SQL databases and can easily connect to Microsoft tools. For example, Excel, PowerBI, and various third-party visualization tools like Tableau which works with the query result from across data tiers with different databases.

Benefits of using Elastic Queries:

1. Elastic queries enable the read-only querying of remote databases. SQL Server users can transfer applications by connecting servers between an Azure SQL environment and on-premises.
2. Elastic queries can be easily available on the standard tier and premium tiers.
3. Using Elastic queries we can execute the stored-procedure or remote functions with the sp_execute _remote and can easily push the SQL parameters and execute on a remote database.
4. Using the elastic query, we can refer the external tables to remote tables with a different table name or schema.
5. It has the capabilities of data partitioning such as Vertical partitioning–Cross-database queries and Horizontal Partitioning–Sharding.

Azure SQL Database Price

Microsoft Azure SQL database provides the below purchasing deployment model:

1. The vCore-based purchasing model: It is convenient for Azure SQL Database and Azure SQL managed Instance.
2. The DTU-based purchasing model is convenient for Azure SQL Database.

Azure SQL Service Tiers

Below are the two Azure SQL service tiers we can use for the Azure SQL Database and Azure SQL Managed Instance. A General Purpose tier for collective workloads:

1. A Business Critical tier for the OLTP applications requires low latency and high flexibility.
2. A Hyperscale tier for massive OLTP systems with more agile auto-scaling, backup and restore support.

In Azure SQL pricing we have the Pay as you Go model, which provides the billed on a per-second basis service. Using this, you can start and stop it at any time, and pay only as per your usage.

As per the below image analysis of the usage of the pay-as-you-go method, we can find the Azure pricing model plan per year from the East US region:

[image source]

The below analysis is based on 200 hours a month:

Frequently Asked Questions

Q #1) Is Azure SQL DB PaaS, IaaS, or SaaS?

Answer: Azure SQL Database is completely based on the managed Platform as a Service (PaaS). It maintains the database management features, for example, patching, upgrading, backups, and monitoring using the Azure portal.

Q #2) What is SQL Azure?

Answer: It is the database management system that we can host on to the Microsoft Azure cloud.

Q #3) How can you compare Azure SQL DB versus Managed Instances?

Answer: Azure SQL database provides the features such as Query store, Row-level security, Dynamic data masking, on the other side Azure SQL database is managed instance and is completely based on the cross-database queries, linked server, and support CLR.

Q #4) Can we use Azure SQL DB as a free tier?

Answer: No, Azure SQL DB’s basic tier is the most reasonable plan for $5 per month. Using it can get 5 Database Throughput Units (DTU).

Q #5) How can I do load balance using SQL Azure?

Answer: Follow the steps below to set up the load balancer using SQL Azure:

1. Sign in to Azure.
2. Go to load balancer resources->Create backend servers.
3. Create the virtual network->Start outbound rule configuration.
4. Install IIS-> test the load balancer.

Q #6) How good is SQL Azure’s performance?

Answer: Azure SQL Database has the capabilities of several Database Advisors to produce intelligent performance with the help of tuning features to improve performance.

Further Reading =>> Comparison of Azure Monitoring Tools

Conclusion

We hope this article helped you with understanding the concept of Microsoft Azure SQL Database, Azure SQL database features, and Azure SQL database architecture flow with the Azure pricing deployment model processes.

This article will be helpful to professional developers, Azure cloud developers, and DBA.

PREV Tutorial | NEXT Tutorial