Describe the Azure SQL family of products including Azure SQL Database, Azure SQL

The Azure SQL family of products is a suite of fully-managed, secure, and intelligent SQL Server-based offerings, which include Azure SQL Database, Azure SQL Managed Instance, and the latest addition, Azure Synapse Analytics. We’ll delve into each of these in finer detail with the aim of providing a comprehensive understanding of these integral components in the Azure ecosystem, crucial for anyone preparing for the DP-900 Microsoft Azure Data Fundamentals exam.

Azure SQL Database

Azure SQL Database is a general-purpose relational database, provided as a fully managed, Platform as a Service (PaaS) offering. With near-zero administration, Azure SQL Database allows you to focus more on the application rather than managing infrastructure or databases.

Azure SQL Database supports intelligent features such as automated backups, automated tuning, threat detection, vulnerability assessments, and data discovery & classification. It also supports scalability features like serverless and Hyperscale. Serverless automatically pauses databases during inactivity, which helps to save costs, while Hyperscale supports very large databases up to 100 TB.

For instance, you can create an Azure SQL Database using the Azure portal, PowerShell, or Azure CLI. You can then manage and query your database using tools like SQL Server Management Studio (SSMS) or Azure Data Studio.

Azure SQL Managed Instance

Azure SQL Managed Instance is an intelligent, scalable, cloud database service that bridges the gap between Azure SQL Database and the full version of SQL Server installed in your own environment. It provides nearly 100% compatibility with the latest SQL Server (Enterprise Edition) Database Engine, providing a native virtual network (VNet) implementation that addresses most common security concerns. It also comes with built-in management operations, such as patching and backups, which reduces much of the overhead associated with manual configuration and constant monitoring.

In terms of use cases, Managed Instance is aimed at businesses that want to migrate their SQL Server workloads to Azure with minimal changes. For example, an app hosted on-premise using SQL Server can be moved to Azure SQL Managed Instance without any changes to the application.

Azure Synapse Analytics

Azure Synapse Analytics, formerly SQL Data Warehouse, brings together big data and data warehousing into an integrated, serverless environment. It provides a unified experience to ingest, prepare, manage, and serve data for immediate business intelligence and machine learning needs.

In terms of capabilities, Synapse can query data warehousing and big data systems concurrently, remove data silos, and bridge the gap between big data and data warehousing tasks. For instance, you could use Synapse Analytics to explore real-time analytics over a data lake, refreshing dashboards with a petabyte-scale SQL pool.

In conclusion, the Azure SQL family of products offers flexible, scalable, and intelligent data management solutions, each with their unique strengths and use cases. Recognizing where each product fits in within your organization’s data strategy is integral when preparing for the DP-900 Microsoft Azure Data Fundamentals exam.

Practice Test

True/False: Azure SQL Database is a fully managed relational database service provided by Microsoft.

• True
• False

Answer: True.

Explanation: Azure SQL Database is indeed a fully managed database service provided by Microsoft that provides relational databases as a service.

Azure SQL Database supports elastic pools.

• A. True
• B. False

Answer: A. True.

Explanation: Elastic pools in Azure SQL Database provide a cost-effective solution for managing multiple databases with fluctuating usage patterns.

Which of the following can you do with Azure SQL?

• A. Automatically scale resources
• B. Automated updates
• C. Intelligent performance tuning
• D. All of the above

Answer: D. All of the above.

Explanation: Azure SQL Database offers built-in intelligence that learns your unique database patterns and auto-tunes your database for better performance and protection.

True/False: Azure SQL Database and Azure SQL Managed Instance are the same.

• True
• False

Answer: False.

Explanation: While both are part of the Azure SQL family, Azure SQL Database is a fully managed platform as a service (PaaS), and Azure SQL Managed Instance is a fully managed instance of the SQL Server Database Engine.

Azure SQL Database is built on the _____.

• A. MySQL
• B. Sequelize
• C. SQL Server
• D. SQLite

Answer: C. SQL Server.

Explanation: Azure SQL Database is built on the latest stable version of the Microsoft SQL Server Database Engine.

Azure SQL Database is primarily for building ______.

• A. On-premises applications
• B. Cloud CMS
• C. Infrastructure as Code
• D. Cloud-native applications

Answer: D. Cloud-native applications.

Explanation: Azure SQL Database provides a relational database service for building cloud-native applications.

What accessing feature does Azure SQL Database provide?

• A. Hybrid connectivity
• B. Virtual private network
• C. ExpressRoute connectivity
• D. None of the above

Answer: A. Hybrid connectivity.

Explanation: Azure SQL Database can access your on-premise data using Azure Hybrid Connections.

True/False: Azure SQL Data Warehouse is now known as Azure Synapse Analytics.

• True
• False

Answer: True.

Explanation: Microsoft re-branded Azure SQL Data Warehouse as Azure Synapse Analytics.

Which of the following is NOT a security feature in Azure SQL?

• A. Advanced threat protection
• B. Data encryption
• C. Role-based access control
• D. Virtual Firewall

Answer: D. Virtual Firewall.

Explanation: Azure SQL Database provides Advanced Threat Protection, data encryption, and RBAC, but there’s no feature called “Virtual Firewall”.

Can Azure SQL Managed Instances use Private Endpoints for a secure network?

• A. True
• B. False

Answer: A. True.

Explanation: Azure SQL Managed Instances can use Azure Private Endpoints to create a secure network.

The Azure SQL Database’s serverless compute option automatically pauses activities during_______.

• A. Inactivity
• B. High Traffic
• C. Both A and B
• D. None of the above

Answer: A. Inactivity

Explanation: The Serverless compute tier of Azure SQL Database automatically pauses during periods of inactivity.

Azure SQL Database provides which types of backup options?

• A. Point-in-time backups
• B. Geo-redundant backups
• C. Both A and B
• D. None of the above

Answer: C. Both A and B.

Explanation: Azure SQL Database provides point-in-time backups to restore your databases and geo-redundant backups for disaster recovery.

True/False: Azure SQL supports REST API.

• True
• False

Answer: False.

Explanation: Azure SQL doesn’t provide native support for REST API. However, you can create REST API interfaces to Azure SQL Database using Azure Functions or Azure App Service.

In Azure SQL, do you need to manage or patch the underlying SQL Server?

• A. Yes
• B. No

Answer: B. No

Explanation: Azure SQL is a fully managed service, and therefore all patches and updates are managed and applied by Microsoft.

True/False: Azure SQL Managed Instance has SQL Server Agent.

• True
• False

Answer: True.

Explanation: Azure SQL Managed Instance includes most of the SQL Server features including the SQL Agent for automated job scheduling.

Interview Questions

1. What is Azure SQL Database?

Azure SQL Database is a fully managed relational database service in the cloud, that is built on the SQL Server engine and provides built-in intelligence, security, and performance.

2. How does Azure SQL Database differ from SQL Server?

Azure SQL Database is a platform as a service (PaaS) offering, while SQL Server is an on-premises or infrastructure as a service (IaaS) offering.

3. What are the deployment options for Azure SQL Database?

Azure SQL Database offers a single database, elastic pool, and managed instance deployment options.

4. What is an elastic pool in Azure SQL Database?

An elastic pool is a collection of databases with a shared set of resources managed through a common resource pool.

5. What is Azure SQL Managed Instance?

Azure SQL Managed Instance is a fully managed platform as a service that provides a SQL Server instance with version-free programming, built-in high availability, and scalability.

6. How does Azure SQL Managed Instance differ from Azure SQL Database?

Azure SQL Managed Instance provides instance-level control and greater compatibility with SQL Server features compared to Azure SQL Database.

7. What is the difference between Azure SQL Database and Azure SQL Data Warehouse?

Azure SQL Database is a transactional database service, while Azure SQL Data Warehouse is an analytical database service for querying and analyzing large datasets.

8. What is the pricing model for Azure SQL Database?

Azure SQL Database offers a consumption-based pricing model where customers pay only for the resources they use.

9. How does Azure SQL Database ensure data security?

Azure SQL Database provides built-in security features such as encryption at rest and in transit, access control, threat detection, and auditing.

10. What is the purpose of Azure SQL Database Hyperscale?

Azure SQL Database Hyperscale is designed for large-scale applications that require high performance and scalability by separating compute and storage resources.

11. What are the supported programming languages for Azure SQL Database?

Azure SQL Database supports T-SQL, .NET, Java, PHP, Node.js, and Python programming languages.

12. How does Azure SQL Database provide high availability?

Azure SQL Database automatically replicates data to ensure availability and provides automatic failover in case of a service interruption.

13. How can users monitor performance in Azure SQL Database?

Azure SQL Database offers built-in monitoring features such as Query Performance Insights and Intelligent Insights to analyze and optimize performance.

14. How does Azure SQL Database handle backups and disaster recovery?

Azure SQL Database provides automated backups with point-in-time restore and geo-replication for disaster recovery to ensure data availability and protection.

15. How can users scale resources in Azure SQL Database?

Azure SQL Database allows users to scale resources vertically by adjusting compute and storage sizes, or horizontally using elastic pools for multiple databases.