Azure SQL Improved Flashcards
What are the types of Azure SQL Database offerings?
Azure SQL Database Offerings:
- SQL Server on Azure Virtual Machines
- Azure SQL Managed Instance
- General Purpose
- Business Critical
- Azure SQL Database
- General Purpose
- Business Critical
- Hyperscale
- Elastic Pool
When should I use Azure SQL Managed Instance?
Azure SQL Managed Instance is an ideal choice when you need a fully managed SQL Server instance in the cloud that supports the following scenarios:
- Easy Lift-and-Shift for On-Premises SQL Server Workloads: It offers near-full compatibility with on-premises SQL Server, making it an excellent option for migrating existing SQL Server workloads with minimal changes to applications or databases.
- Extended SQL Server Features: It includes features not available in Azure SQL Database, such as SQL Agent, cross-database queries, and Service Broker, which are essential for applications relying on these functionalities.
- Network Isolation and Enhanced Security: If you need to isolate your database within a private network and integrate it with on-premises resources, Managed Instance can be deployed within a virtual network (VNet), enabling private IP access and enhanced security configurations.
- Compliance with Enterprise Requirements: It supports compliance needs with features like Azure Active Directory (Entra ID) authentication, Transparent Data Encryption (TDE), and Advanced Threat Protection (ATP).
- Scaling for Enterprise Workloads: It provides scaling options for both compute and storage, beneficial for enterprise applications with demanding performance and storage needs.
- Hybrid Cloud and Application Modernization: It allows for seamless integration and hybrid setups with features like Linked Servers and cross-premises connectivity, ideal for modernizing parts of an on-premises SQL Server setup while keeping certain components in the cloud.
When should you use Azure SQL Database elastic pools?
Azure SQL Database elastic pools are ideal when you have multiple databases that:
- Have Variable and Unpredictable Usage Patterns: Elastic pools are cost-effective for databases with varying or unpredictable demand, as they allow you to balance performance costs across multiple databases.
- Need Resource Sharing and Cost Savings: They enable multiple databases to share a set of resources (DTUs or vCores) at a fixed price, which can be more economical than provisioning resources for each database individually, especially for databases with low or intermittent usage.
- Use a Single-Tenant or Multi-Tenant SaaS Model: In multi-tenant SaaS scenarios, where each tenant might have its own database, elastic pools provide predictable costs while accommodating varying tenant usage.
- Require Scaling for Numerous Databases: Elastic pools simplify the management and scaling of a group of databases, eliminating the need to scale each database individually.
- Operate Under a Fixed Budget: By placing multiple databases into an elastic pool, you can control costs while ensuring sufficient resources to handle varying workload demands across the databases.
When is it most appropriate to use Azure SQL Virtual Machines?
Azure SQL Virtual Machines are most appropriate when you need full control over the operating system, require customization of the SQL Server instance, or need to use features that are not available in Azure SQL Database. They are ideal for scenarios where you need to migrate existing SQL Server workloads with minimal changes, require SQL Server Reporting Services (SSRS), or need to use SQL Server features like SQL Server Agent or Service Broker.
Who is responsible for patching the operating system when using SQL Server on Azure Virtual Machines?
You are responsible for patching.
When using SQL Server on Azure Virtual Machines, the customer is responsible for patching the operating system. Azure provides tools and services to assist with SQL Server updates and maintenance, but the management of the underlying virtual machine and its operating system, including applying patches and updates, is the customer’s responsibility.
In contrast, with Azure SQL Database, which is a fully managed Platform as a Service (PaaS) offering, Microsoft handles both operating system and database patching, reducing the customer’s maintenance responsibilities.
Who is responsible for patching the operating system and database engine in Azure SQL Database?
Microsoft is responsible for patching the operating system, database engine, and all underlying infrastructure in Azure SQL Database. This is part of the fully managed platform-as-a-service (PaaS) model, which ensures regular updates, patches, and backups for availability, security, and compliance.
When using Azure SQL on virtual machines, which type of VM should you consider for optimal performance?
For optimal performance when using Azure SQL on virtual machines, consider using a memory-optimized or storage optimized virtual machine.
How can you efficiently back up a virtual machine running SQL Server in Azure to ensure both VM-level and application-consistent backups?
Use Automated Backup, Automated Backup provides an automatic backup service for SQL Server Standard and Enterprise editions running on a Windows VM in Azure. This service is provided by the SQL Server IaaS Agent Extension, which is automatically installed on SQL Server Windows virtual machine images in the Azure portal.
All databases are backed up to an Azure storage account that you configure. Backups can be encrypted and the metadata is retained in msdb for up to 90 days, though the service doesn’t automatically delete backups past their retention date. You can use a lifecycle management policy for your storage account to balance backup retention with cost management according to your business needs.
What is the network latency for Azure SQL Database when data remains within the same region, compared to accessing a secondary database?
The network latency for Azure SQL Database when data remains within the same region is typically less than 2 milliseconds. However, accessing a secondary database, especially if it involves geo-replication or cross-region access, may introduce additional latency beyond this typical range.
How is high availability ensured in the general-purpose tier for Azure SQL Database and Azure SQL Managed Instance?
General-purpose tier for Azure SQL Database high availability is created by using a separation of compute and storage. Data is stioored in Azur Storage and replicated three time with LRS. If the compute instance fails, ther are available standby instances ready to take its place.
General-purpose tier for Azure SQL Managed Instance high availability is created by using a separation of compute and storage. Data is stioored in Azur Storage and replicated three time with LRS. If the compute instance fails, ther are available standby instances ready to take its place.
Prenium tier for Azure SQL Database high availability is created by using seperate instances of the database thet are replicated to and in the event of the primary compute node failing, one of the replicas will instatly become the primary.
How is high availability achieved in the business-critical tier for Azure SQL Database and Azure SQL Managed Instance?
High availability in the business-critical tier for Azure SQL Database and Azure SQL Managed Instance is achieved through a technology called Always On Availability Groups. In this setup, data is stored on the local disk of the primary node and is synchronously replicated to multiple secondary nodes. This ensures that any transaction is committed only after it has been successfully replicated to at least one secondary node, providing high availability and data protection.
Where is the data stored when using the Business Critical service tier for Azure SQL Database and Azure SQL Managed Instance?
In the Business Critical service tier for Azure SQL Database and Azure SQL Managed Instance, the data is stored in the local SSD storage of the nodes. There is the use of always on, where there are other nodes that are comitted to before data is marked are wrote.
Where is data stored when using the general-purpose tier for Azure SQL Database and Azure SQL Managed Instance?
Answer: Azure Blob Storage.
What does “always-on” mean in the context of Azure SQL Database and Azure SQL Managed Instance?
“Always-on” in the context of Azure SQL Database and Azure SQL Managed Instance refers to the high availability feature that ensures continuous data availability and minimal downtime. It involves replicating data between primary and secondary replicas. This replication can be configured to be either synchronous, ensuring data consistency by waiting for confirmation from secondary replicas before committing transactions, or asynchronous, allowing transactions to be committed without waiting for confirmation, which can improve performance but may risk data loss in case of a failure.
What are the service tiers in Azure SQL Database?
General Purpose
Business Critical
Hyperscale
When should you choose the General-Purpose Tier in Azure SQL Database?
- Suitable for most general-purpose workloads.
- Ideal for moderate performance needs.
- Budget-friendly: Offers cost-effective options.
- Storage: Utilizes Azure Premium SSD, supporting up to 4 TB of database size.
- Availability: Ensures high availability with automated backups and geo-replication.
- Suitable for applications with standard OLTP workloads.
- Suitable for moderate transaction rates and throughput.
- Suitable for applications requiring balanced performance at a lower cost.
How is high availability achieved for the General Purpose tier in Azure SQL Database?
High availability in the General Purpose tier of Azure SQL Database is achieved through the use of a separation of compute and storage layers. The compute layer is stateless and can be quickly replaced in case of failure, while the storage layer uses Azure Premium Storage with built-in data redundancy. Automated backups and geo-replication further enhance availability by ensuring data is protected and can be restored or accessed from another region if needed.
Which types of workloads are best suited for the General-Purpose Tier in Azure SQL Database?
- General-purpose workloads that do not require high performance or low latency.
- Applications with moderate performance and scalability requirements.
- Workloads that prioritize cost-effectiveness and balanced performance.
Which types of workloads are best suited for the General-Purpose Tier in Azure SQL Managed Instance?
- General business applications and workloads.
- Applications with moderate performance and latency requirements.
- Workloads that require balanced compute and storage resources.
- Cost-sensitive applications that benefit from a budget-friendly option.
Which workloads are suitable for the General-Purpose Tier for an Azure SQL Instance?
- Applications with standard OLTP (Online Transaction Processing) workloads.
- Workloads with moderate transaction rates and throughput requirements.
- Applications that require balanced performance at a cost-effective price.
What types of applications are best suited for the General-Purpose Tier in Azure SQL Database?
- Applications with standard Online Transaction Processing (OLTP) workloads.
- Applications with moderate transaction rates and throughput requirements.
- Applications that require balanced performance at a cost-effective price.
What purchasing models are available for Azure SQL Database and Azure SQL Managed Instance?
Azure SQL Database and Azure SQL Managed Instance offer the following purchasing models:
- vCore (Virtual Core) Model: This model allows you to choose the number of virtual cores, memory, and storage size independently. It provides flexibility to scale resources based on your workload requirements and offers options for both General Purpose and Business Critical service tiers.
- DTU (Database Transaction Unit) Model: This model is specific to Azure SQL Database and bundles compute, memory, and I/O resources into a single unit. It is available in Basic, Standard, and Premium service tiers, making it simpler to choose a performance level without managing individual resources.
Note: The DTU model is not available for Azure SQL Managed Instance.
What is the Microsoft recommended purchasing model for both Azure SQL Database and Azure SQL Managed Instance?
The recommended purchasing model for both Azure SQL Database and Azure SQL Managed Instance is the vCore-based model. This model provides flexibility by allowing you to independently choose and scale compute and storage resources according to your needs.
What type of SSD is used for storage in the General-Purpose Tier of Azure SQL Database?
Answer: Azure Premium SSD is used for storage in the General-Purpose Tier of Azure SQL Database.