Azure SQL Database

What is Azure SQL Database?

Azure SQL Database is a fully managed relational database service provided by Microsoft Azure and built on the SQL Server engine. It supports a wide array of data types, accommodating both structured and unstructured data. Structured data includes common types like integers, strings, and dates, while unstructured data, such as JSON and XML, is seamlessly handled, offering flexibility in data modeling.

This cloud-based database solution provides various deployment options such as:

• Single databases for most common use cases.
• Elastic pools in which multiple databases can share resources dynamically within the pool based on their varying usage patterns.
• Managed instances for on-premises SQL Servers, allowing for a seamless migration to the Azure cloud.

Azure SQL vs. SQL Server

SQL Azure Architecture

The architecture of Azure SQL Database is designed for maximum efficiency and reliability. It employs a multi-tiered architecture that includes multiple layers.

• The client layer represents the interface through which applications interact with the database. Applications can connect to Azure SQL Database using standard SQL Server connection libraries. This layer is responsible for sending queries and receiving results, facilitating seamless communication between applications and the underlying database.
• The service layer acts as an intermediary between the client layer and the underlying database infrastructure. It handles query processing, connection management, and other runtime operations. This stateless layer ensures efficient communication between clients and the database, optimizing performance by managing the distribution of queries to the appropriate components.
• The platform layer forms the core of Azure SQL Database architecture. It encompasses components responsible for managing and maintaining the database environment. Key functionalities include resource allocation, security, and connectivity. This layer ensures that the database operates efficiently, providing the necessary resources to meet application demands while maintaining a secure and controlled environment.
• The infrastructure layer comprises the physical and virtual resources that support the Azure SQL Database. This includes servers, storage, and networking components. Microsoft Azure takes care of the underlying infrastructure, allowing users to focus on application development rather than managing hardware. This layer ensures high availability, scalability, and reliability of the database service.

Microsoft Azure SQL Database Pricing

Azure SQL Database offers flexible pricing models catering to diverse user needs, including the vCore-based purchasing model and the DTU-based purchasing model. Azure also provides 32 GB of storage every month and 100,000 vCore seconds of serverless compute for free to try out Azure SQL database.

vCore-based Purchasing Model

The vCore-based purchasing model allows users to choose a specific number of virtual cores (vCores) and associated memory for their Azure SQL Database. This model provides more control over resource allocation and is available in both Provisioned Compute and Serverless Compute tiers.

The pricing for the vCore-based model with provisioned compute tier and general purpose service tier using a Standard-series (Gen 5) hardware type will be:

DTU-based Purchasing Model

The DTU-based purchasing model simplifies resource selection by using Database Transaction Units (DTUs), a combined measure of CPU, memory, and I/O.

For a DTU-based purchase Model with a basic service tier, the pricing will be

Extra storage is available only with standard and premium service tires, where the price for an additional 1 GB of data in the Standard tier is ₹18.42.

Azure SQL Database Features

• Easily scale resources up or down based on demand, ensuring optimal performance for varying workloads. Provides support for in-memory processing and built-in intelligence to improve performance by optimizing database performance and providing faster query execution.
• Microsoft handles routine maintenance tasks, such as updates and patching, allowing focus on application development. Features like backup and point-in-time restore capabilities for data protection and recovery are also automated.
• Security features like Transparent Data Encryption, Dynamic Data Masking, and integration with Azure Active Directory enhance data security. It also offers built-in threat detection and protection against potential security vulnerabilities.
• Provides 99.99% availability through replication to different geographical regions. The Hyperscale feature of Azure SQL Database provides a higher performance with faster backups and restores.
• Managed SQL instances including additional features such as cross-database queries, SQL Server Agent, and Linked Servers. The instance also can easily integrate with existing SQL Server tools and features for a familiar development experience.

Azure SQL Database Tiers

The vCore-based Purchasing Model provides the following Database Tiers:

General Purpose

Suited for a wide range of transactional and analytical workloads. Offers balanced performance with options for provisioning resources to meet specific requirements.

Use Case: Well-suited for applications with varying resource needs and diverse workloads.

Business Critical

Designed for high-performance and critical production workloads. Provides high availability with local SSD storage, ensuring low latency and robust performance.

Use Case: Ideal for applications demanding high transactional throughput and minimal latency.

Hyperscale

Tailored for applications with large data volumes. Utilizes a distributed architecture to scale storage dynamically, supporting both OLTP and OLAP workloads seamlessly.

Use Case: Well-suited for applications with rapidly growing data requirements, such as e-commerce and analytics platforms.

The DTU-based Purchasing Model provides the following Database Tiers:

Standard

Offers a balanced set of resources with a fixed amount of Database Transaction Units (DTUs). Suitable for applications with predictable and stable workloads.

Use Case: Well-suited for applications with consistent performance requirements and workloads.

Premium

Provides high-performance resources with a variable number of DTUs. Ideal for mission-critical applications with varying and unpredictable workloads.

Use Case: Best for applications requiring consistent high performance and responsiveness.

The vCore-based Purchasing Model also provides two different compute tiers based on different levels of processing power and resources allocated to a database.

Provisioned Compute Tier

In the Provisioned Compute Tier, users pre-allocate a fixed amount of resources (vCores and memory) for their database. This tier is suitable for applications with consistent and predictable workloads.

Serverless Compute Tier

The Serverless Compute Tier is designed for applications with unpredictable workloads. The resources scales automatically based on demand, charging users only for the resources consumed during active periods.

These compute tiers provide flexibility for users to choose the most suitable option based on the nature of their workloads. The DTU-based purchasing model provides only the provisioned compute tier.

Azure SQL Database Services

Apart from the core database service, Azure SQL Database provides additional services to enhance functionality.

Azure SQL Database Managed Backup

Automatically creates and manages backups, providing point-in-time restore capabilities. Offers retention policies for backup customization, ensuring data protection and recovery.

Azure SQL Database Threat Detection:

Provides built-in threat detection and alerts for suspicious activities or potential security vulnerabilities. Enhances database security by identifying and addressing potential threats.

Azure SQL Database Geo-Replication

Enables disaster recovery and high availability by asynchronously replicating databases to different geographical regions. Supports read-scale scenarios, enhancing application performance.

Azure SQL Database Auditing

Tracks database events and activities for compliance and security auditing purposes. Captures information on database access, changes, and queries.

Setting Up Azure SQL Database

Follow the following steps to set up an Azure SQL database in azure:

• Log in to the Azure Portal using your account credentials.

• Search for SQL database in the search bar on the home page and select the Azure SQL Databases service.

• Click on the Create SQL database button to create a new database.

• Provide necessary details such as Database name and resource Group and also select the Subscription to use for the database.

• For the server option, you can create a new server or use an existing one. To create a new server click on the Create new link and perform the following steps:

  • Enter the name of the server and select the region for the server to ensure low-latency access.
  • For authentication type, select "Use SQL authentication" and specify the username and password of the admin user.

• Select the deployment option based on your requirements (Single database, Elastic pool, or Managed instance). If you are creating your first database on your subscription, you can use the free General-purpose serverless database with 32 GB of storage. If there is no free tier, choose the appropriate pricing tier based on your performance and budget requirements.

• In the networking section, define firewall rules to control access to the database. Whitelist specific IP addresses by selecting the connectivity method to private or allowing access from all Azure services through the public endpoint connectivity method.

• In the security section, select the connection policy to Default. The connection policy defines how client applications can connect to the database In the Proxy mode, the connection is established directly to the database. In the Redirect mode, connections are redirected to a gateway that then routes them to the appropriate database server.
• Select the minimum version for Transport Layer Security (TLS) for encryption of data at transit and rest. You can also enable the Microsoft Defender for SQL to improve security.

• You can also add sample data to your database on creation to experiment with using the Data Source option on the Additional Options page. The sample data is called AdventureWorksLT and includes essential entities such as Customers, Products, Orders, and Addresses. The database also contains relationships between these entities providing a realistic representation of a typical business scenario.

• Review all configurations to ensure accuracy. Click on Review + create to validate settings and create the Azure SQL Database.