Azure SQL Database is a fully managed relational database service that runs in the cloud without you needing to manage servers or apply patches. It offers several deployment options that fit different application needs: a single database works well for independent applications that need predictable performance, while an elastic pool lets multiple databases share the same compute resources, which saves money when some databases sit idle while others handle heavy traffic. The hyperscale tier separates compute from storage, allowing databases to grow to 100 TB or more and to add read replicas quickly for heavy read workloads. The service maintains transactional consistency through ACID properties, meaning every transaction either completes fully or rolls back completely, so your data stays accurate. For high availability, Azure SQL Database spreads replicas across multiple availability zones and can replicate data to a paired region for disaster recovery; if the primary database fails, traffic automatically moves to a replica. Automated scaling adjusts resources based on demand, and built-in backups let you restore data to any point within your retention window.
Azure SQL Managed Instance bridges the gap between running SQL Server on your own servers and using a fully managed cloud service, offering near-100% compatibility with SQL Server features. This makes it the right choice when you need to move existing SQL Server databases to the cloud without rewriting application code or changing your T-SQL commands. It supports older SQL Server features like linked servers, CLR integration, and SQL Server Agent that many legacy applications depend on. The high-availability architecture maintains multiple synchronous replicas within the same region, so a hardware failure causes no data loss and only seconds of downtime. Backup and restore features include automated backups, long-term retention policies, and point-in-time restore capabilities. The service also offers reserved capacity pricing and hybrid benefit options that reduce costs when you bring existing SQL Server licenses.
Azure Database for PostgreSQL provides a managed PostgreSQL service that handles deployment, patching, backups, and scaling automatically, so you can focus on your application rather than database administration. It delivers strong transactional consistency through PostgreSQL's MVCC implementation, which ensures that multiple transactions can read and write data without conflicting with each other. The service automatically scales compute and storage based on performance metrics, handling workload spikes without manual intervention. For high availability, it supports zone-redundant configurations that replicate data across availability zones and read replicas that offload reporting or analytics queries from the primary instance. Automated backups retain data for up to 35 days, and geo-redundant storage protects against regional disasters by storing backups in a paired Azure region.
Azure Database for MySQL provides a fully managed MySQL service designed for applications that need high availability, security, and predictable performance. It enforces transactional consistency using ACID properties and MySQL's InnoDB storage engine, which handles row-level locking and crash recovery. The service offers zone-redundant deployments that keep synchronous replicas in separate availability zones and read replicas that distribute read traffic across multiple copies. Automated scaling adjusts storage and compute resources based on workload demands, and built-in connection pooling handles large numbers of concurrent connections efficiently. The total cost of ownership is lower than running MySQL on virtual machines because the managed service eliminates administrative overhead for patching, backups, and hardware maintenance.
Choosing the right Azure relational database requires evaluating several factors together rather than in isolation. Workload patterns determine the deployment model: single databases suit independent applications, elastic pools suit multi-tenant applications with variable usage, and hyperscale suits large-scale workloads with high throughput requirements. All services provide ACID compliance for consistency, but the specific isolation levels and locking behavior differ between SQL Server, PostgreSQL, and MySQL, so applications depending on database-specific concurrency behavior may need to stay with their native platform. High-availability architectures differ in recovery time and data loss tolerance: zone-redundant deployments protect against datacenter failures within a region, while geo-replication protects against regional disasters but introduces some replication delay. Scalability options include vertical scaling (increasing compute and storage) and horizontal scaling (adding read replicas), but only Azure SQL Database's hyperscale tier separates compute from storage for near-instant scaling. Cost considerations should account for compute, storage, backup storage, and data egress charges, with reserved capacity or hybrid benefit options reducing costs for predictable workloads.
A decision tree that starts by asking whether you are migrating an existing SQL Server database or building a new cloud application. It then branches based on the database engine and workload scale to recommend Azure SQL Managed Instance, Azure SQL Database, Azure Database for PostgreSQL, or Azure Database for MySQL.
Choosing the right Azure SQL Database tier requires matching your application's needs to what each offering provides. You analyze key workload patterns like transaction volume, concurrency (how many users or processes access the database at once), and I/O throughput (how fast data can be read and written). These patterns directly influence whether you need more compute resources or a specific storage architecture to meet performance goals. You also weigh these technical needs against cost constraints, which guide the choice between the vCore and DTU purchasing models. The vCore model lets you select the number of virtual cores, memory, and storage size independently, while the DTU model provides a bundled set of these resources.
The General Purpose service tier uses a remote storage model where compute and storage layers are separated. This design makes it cost-effective for workloads that need balanced performance and high availability but do not have strict low-latency demands. It works well for general business applications with moderate transaction rates. Within this tier, you can choose between a provisioned compute tier, where you pay for a fixed amount of resources, or a serverless compute tier, which scales compute automatically based on usage and bills you only for what you consume. This flexibility helps maintain cost efficiency for applications with variable or predictable loads.
The Business Critical tier is built for performance and high availability. It delivers consistently low I/O latency by using local SSD storage instead of remote storage. It maintains multiple high availability replicas to provide fast failover and includes a free readable secondary replica for read scale-out workloads like reporting. This tier suits Online Transaction Processing systems with high transaction volumes and concurrency. When selecting this tier, you must choose appropriate vCore sizes to match your peak performance needs. While it has a higher cost, it offers a higher SLA and advanced features like automatic page repair and zone-redundant availability, making it the choice for mission-critical applications.
The Hyperscale tier provides a cloud-native architecture with independently scalable compute and storage. It supports massive databases up to 128 TB and allows you to dynamically adjust vCores without waiting for lengthy data copy operations. You can also scale out read workloads by adding multiple named replicas. This tier is designed for workloads that outgrow the limits of other tiers, including those with enormous data volumes or mixed OLTP and analytical processing. Billing is based on the actual storage used and the provisioned compute, which allows for efficient cost management as the database grows. Hyperscale ensures rapid scaling, fast backup and restore, and high availability for demanding database scenarios.
Vertical scaling, also known as scaling up or down, involves increasing or decreasing the compute size of an individual database. In Azure, you achieve this by upgrading service tiers or adjusting vCores. Service tiers determine the baseline capabilities of a database, and databases can move between tiers depending on workload needs: General Purpose balances cost and performance, Business Critical provides low-latency storage for performance-sensitive applications, and Hyperscale allows extensive storage scaling for very large databases. vCore adjustments let you fine-tune compute resources by increasing or decreasing the number of virtual cores, which directly impacts CPU, memory, and IOPS performance. Vertical scaling offers two key advantages: it provides efficiency through dynamic scaling that matches resource allocation with current demand, and it requires minimal changes to the database architecture or application logic.
Horizontal scaling, also known as scaling out or in, involves adding or removing database instances to distribute the load across multiple servers. This technique is useful for handling high throughput and scaling read or write operations independently. Elastic pools provide a group of databases that share resources, which helps manage varying workloads efficiently while controlling costs; multiple databases within a pool draw from a shared set of compute resources, allowing the system to balance load dynamically. Sharding distributes large datasets across multiple databases to improve performance, with the application layer handling data partitioning and routing queries to the correct database instance; this works well when data can be naturally divided by customer ID or geographic region. Read scale-out creates read replicas that handle read-heavy workloads without affecting write performance; write operations go to the primary database while read operations distribute across replicas, improving overall throughput for applications with high read demands. Horizontal scaling provides scalability by adding more instances to handle larger workloads and improves performance by distributing the load across multiple databases.
Azure supports dynamic scaling, which can be either manual or automatic, allowing databases to respond to changing workload demands without manual intervention. Manual scaling lets administrators adjust resources through the Azure portal, PowerShell, or CLI when they anticipate changes in demand or need to optimize costs. Automatic scaling, available in the serverless compute tier for Azure SQL Database, automatically adjusts compute resources based on workload demand; the system adds resources during peak periods and scales back during quiet times, optimizing both performance and cost. Vertical scaling is ideal for applications needing performance boosts without architecture changes, while horizontal scaling suits applications requiring extensive scalability and load distribution. Both scaling techniques can be dynamically managed to optimize performance, cost, and resource allocation.
Transparent Data Encryption (TDE) protects data at rest by encrypting database files, log files, and backups without requiring any changes to application code. When data is written to disk, TDE encrypts it using the Advanced Encryption Standard algorithm, and decrypts it when read into memory. By default, Azure enables TDE on newly created databases, securing the database encryption key with a built-in server certificate. Organizations seeking more control can replace this certificate with their own keys stored in Azure Key Vault to manage key rotation and lifecycles.
Azure Key Vault acts as a secure, centralized repository for managing and protecting cryptographic keys. When implementing a Bring Your Own Key model, ownership of the key lifecycle remains with the organization rather than the cloud provider. This architecture creates a separation of duties, ensuring that security officers who manage the keys do not have direct access to administrative database tasks. The database engine authenticates to the key vault to fetch the key wrapper, ensuring that the actual keys are protected behind defined security boundaries.
Always Encrypted provides column-level protection to ensure sensitive data is protected while in transit and in use. This mechanism performs client-side processing, where a specialized client driver encrypts sensitive columns before the data ever leaves the client application. Because the database engine never sees the plaintext data or the encryption keys, unauthorized administrators cannot read the sensitive fields. These keys are stored externally in secure locations such as Azure Key Vault or the Windows Certificate Store, which keeps the cryptographic boundaries secure.
Dynamic Data Masking limits the exposure of sensitive data to unauthorized users by obscuring it in query results on the fly. The service automatically discovers potentially sensitive data fields, such as credit card numbers or email addresses, and applies masks according to pre-defined rules. This masking occurs inline, meaning the actual data on disk remains fully intact and encrypted, but non-privileged database users only see masked results. This capability minimizes the need to modify application code while protecting sensitive information from exposure in user interfaces.
Azure Defender for SQL is a unified security package that continuously monitors database systems to defend against security risks. Within this service, vulnerability assessments scan the database for security holes and provide step-by-step instructions to fix misconfigurations. Simultaneously, the threat detection system watches live operations to spot anomalous activities, such as SQL injection attacks or unusual database access patterns. This continuous monitoring workflow ensures that potential breaches are identified and mitigated before they can compromise the database environment.
Venture into the world of Azure Infrastructure, where design meets functionality. Harness your skills and gain mastery over complex cloud structures to ace the AZ-305 Designing Microsoft Azure Infrastructure Solutions exam!
Prepare and test your skills
Prepare and test your skills
Azure SQL Managed Instance is the right choice when moving existing SQL Server databases to the cloud without rewriting application code or changing T-SQL commands. It provides near-100% compatibility with SQL Server features and supports legacy capabilities such as linked servers, CLR integration, and SQL Server Agent. In contrast, Azure SQL Database is suited for independent applications or elastic pools that do not require full SQL Server instance-level compatibility.
The General Purpose tier separates compute and storage using a remote storage model for balanced performance, whereas the Business Critical tier uses local SSD storage to deliver consistently low I/O latency. In addition, the Business Critical tier maintains multiple high-availability replicas for fast failover and includes a free readable secondary replica for read scale-out workloads. The General Purpose tier is designed for general business applications with moderate transaction rates and supports both provisioned and serverless compute options.
Transparent Data Encryption (TDE) protects data at rest by encrypting database files, log files, and backups, whereas Always Encrypted protects sensitive data both in transit and in use. TDE encrypts data using the Advanced Encryption Standard algorithm without requiring application changes and decrypts it when read into memory. Always Encrypted encrypts data at the column level on the client side before it leaves the client application, ensuring that the database engine never sees plaintext data or encryption keys.
The Hyperscale tier uses a cloud-native architecture that separates compute from storage, allowing both resources to scale independently and supporting databases up to 128 TB. Compute can be dynamically adjusted by modifying vCores without waiting for lengthy data copy operations, and read workloads can be scaled horizontally by adding multiple named replicas. Storage scales automatically based on actual usage, and billing is determined by the provisioned compute and actual storage consumed.
StudyCerts
StudyCerts