Azure SQL

Core Functionality

1. Azure SQL Database: A fully managed relational database service that offers built-in high availability, automated backups, and AI-powered performance optimization. It supports a wide range of SQL Server features and provides elastic scalability for varying workloads.
2. Azure SQL Managed Instance: A fully managed instance of SQL Server that provides near 100% compatibility with the latest SQL Server on-premises (Enterprise Edition) features. It is ideal for modernizing and migrating existing SQL Server applications to Azure with minimal changes.
3. SQL Server on Azure Virtual Machines: Provides full control over SQL Server installations on Azure VMs, allowing you to manage and maintain your SQL Server databases with familiar tools and configurations. It supports high availability and disaster recovery through Always On availability groups and backups to Azure Storage.

Architecture Design Considerations

• Performance: Optimize performance by choosing the appropriate service tier and hardware configuration based on your workload requirements. Use features like indexed views, query optimization, and partitioning to enhance query performance.
• Security: Implement security best practices such as encryption at rest and in transit, firewall rules, and Azure Active Directory authentication to protect your data. Use features like Advanced Threat Protection and vulnerability assessment to enhance security.
• High Availability: Design for high availability by leveraging built-in features like geo-replication, auto-failover groups, and Always On availability groups. Implement a disaster recovery plan to ensure business continuity.
• Cost Management: Manage costs by selecting the appropriate pricing tier and scaling resources based on demand. Use tools like Azure Cost Management and Budget to monitor and optimize spending.

Pricing

Azure SQL offers a flexible pricing model based on the chosen deployment option, service tier, and resources used. It provides both provisioned and serverless compute options, allowing you to optimize costs based on your workload requirements.

Automatic Tuning

Azure Automatic Tuning is a feature that helps optimize the performance of your SQL databases by automatically applying recommendations to improve query performance. Here’s a brief summary:

Key Features of Automatic Tuning

1. Automatic Index Management: Creates, drops, and re-creates indexes based on usage patterns to ensure optimal performance.
2. Plan Forcing: Identifies and corrects query plan regressions by forcing the optimal execution plan, ensuring consistent query performance.
3. Automatic Verification: Monitors and validates the impact of any applied tuning actions to ensure they positively affect performance.
4. Safety and Reliability: Ensures that any changes made are safe and beneficial by thoroughly testing and validating them before application.
5. Customizable: Allows you to enable or disable specific tuning options based on your needs, providing flexibility in how tuning is applied.

By leveraging Azure Automatic Tuning, you can significantly improve the performance and efficiency of your SQL databases with minimal manual intervention.

SQL Statement Types

SQL statements are grouped into three main logical groups:

Data Definition Language (DDL) Data Control Language (DCL) Data Manipulation Language (DML)

DDL

Use DDL statements to create, modify, and remove tables and other objects in a database.

• CREATE
• ALTER
• DROP
• RENAME

DCL

Use DCL statements to manage access to objects in a database by granting, denying, or revoking permissions to specific users or groups.

• GRANT
• DENY
• REVOKE

DML

use DML statements to manipulate the rows in tables. These statements enable you to retrieve (query) data, insert new rows, or modify existing rows. You can also delete rows if you don’t need them anymore.

• SELECT
• INSERT
• UPDATE
• DELETE

References

• Azure SQL Overview
• Azure SQL Database
• Azure SQL Managed Instance
• SQL Server on Azure Virtual Machines