15 Azure SQL Interview Questions and Answers

Azure SQL is a cloud-based relational database service provided by Microsoft, designed to handle a wide range of data workloads. It offers scalability, high availability, and robust security features, making it a popular choice for businesses looking to leverage cloud technology for their database needs. With its seamless integration with other Azure services, Azure SQL enables efficient data management and analytics, supporting both transactional and analytical processing.

This article aims to prepare you for interviews by presenting a curated selection of questions and answers focused on Azure SQL. By familiarizing yourself with these topics, you will gain a deeper understanding of the platform’s capabilities and be better equipped to demonstrate your expertise to potential employers.

Azure SQL Interview Questions and Answers

1. Describe the architecture of Azure SQL Database.

Azure SQL Database is a managed relational database service by Microsoft Azure, designed for a range of workloads. Its architecture includes:

• Logical Server: An administrative container for databases, centralizing management for security and other tasks.
• Databases: Isolated databases within a logical server, ensuring performance and security.
• Elastic Pools: Collections of databases sharing resources, useful for scaling with varying usage patterns.
• Compute and Storage: Separate, independently scalable resources for flexible scaling.
• High Availability and Disaster Recovery: Built-in high availability with automatic failover and geo-replication.
• Security: Multiple layers including network security, authentication, and encryption.

2. Explain the concept of DTUs (Database Transaction Units) and how they are used.

DTUs, or Database Transaction Units, measure Azure SQL Database performance, combining CPU, memory, reads, and writes. They simplify selecting the right service tier by abstracting hardware and resource management, allowing users to focus on performance needs.

3. What are Elastic Pools and when would you use them?

Elastic Pools manage and scale multiple databases with varying usage patterns by sharing resources. This optimizes performance and cost, especially for SaaS applications with different tenant usage. They are useful when managing multiple databases with unpredictable usage, optimizing resource utilization, and ensuring performance consistency.

4. How do you implement Row-Level Security?

Row-Level Security (RLS) controls access to database rows based on user characteristics, useful for multi-tenant applications. Implement RLS by creating a security policy and predicate function to define access logic.

Example:

-- Create a sample table
CREATE TABLE Sales (
    SaleID int,
    SalesPerson nvarchar(50),
    Amount decimal(10, 2)
);

-- Insert sample data
INSERT INTO Sales VALUES (1, 'Alice', 100.00);
INSERT INTO Sales VALUES (2, 'Bob', 200.00);

-- Create a predicate function
CREATE FUNCTION dbo.SalesPredicate(@SalesPerson AS nvarchar(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS result
WHERE @SalesPerson = USER_NAME();

-- Create a security policy
CREATE SECURITY POLICY SalesSecurityPolicy
ADD FILTER PREDICATE dbo.SalesPredicate(SalesPerson) ON dbo.Sales
WITH (STATE = ON);

In this example, the SalesPredicate function filters rows based on the SalesPerson column, allowing users to see only the rows where the SalesPerson matches their username.

5. Explain the difference between Azure SQL Database and SQL Managed Instance.

Azure SQL Database and SQL Managed Instance are both managed services but cater to different needs. Azure SQL Database is designed for modern cloud applications, offering scalability and minimal administrative overhead. SQL Managed Instance provides near 100% compatibility with SQL Server, ideal for migrating on-premises workloads with minimal changes.

Key differences include:

• Deployment Model: Azure SQL Database is a single database or elastic pool service, while SQL Managed Instance provides a fully managed instance of SQL Server.
• Compatibility: SQL Managed Instance offers near 100% compatibility with SQL Server, whereas Azure SQL Database may require some modifications to existing applications.
• Scalability: Azure SQL Database offers built-in scalability options such as elastic pools, while SQL Managed Instance provides instance-level scalability.
• Features: SQL Managed Instance includes features like SQL Server Agent and cross-database queries, which are not available in Azure SQL Database.
• Use Cases: Azure SQL Database is ideal for new cloud-native applications, while SQL Managed Instance is better suited for migrating existing on-premises SQL Server workloads.

6. What is Geo-Replication and how does it work?

Geo-Replication in Azure SQL creates readable secondary databases in different regions for disaster recovery and reducing latency. It works by asynchronously replicating transactions from the primary to secondary databases, which are read-only. In case of primary failure, you can manually failover to a secondary database.

Key points:

• Asynchronous Replication: Transactions are replicated asynchronously, meaning there might be a slight delay between the primary and secondary databases.
• Read-Only Secondaries: Secondary databases are read-only, which can be used for offloading read workloads and reporting.
• Manual Failover: In case of a primary database failure, you can manually failover to a secondary database.
• Multiple Secondaries: You can create multiple secondary databases in different regions to ensure global availability and low latency.

7. Write a SQL query to retrieve all records from your table where DateCreated is within the last 30 days.

To retrieve records from a table where DateCreated is within the last 30 days, use:

SELECT * 
FROM YourTableName
WHERE DateCreated >= DATEADD(DAY, -30, GETDATE());

This query uses DATEADD to subtract 30 days from the current date, filtering records accordingly.

8. Explain the concept of Sharding.

Sharding distributes data across multiple databases to improve performance and scalability. It divides large datasets into smaller pieces called shards, each operating as an independent database. This allows for parallel processing and enhances read and write operations.

Sharding methods include:

• Range-based Sharding: Data is divided based on a specific range of values.
• Hash-based Sharding: A hash function determines which shard data resides in.
• Directory-based Sharding: A lookup table maps data to specific shards.

In Azure SQL, Elastic Database tools help manage sharding, automating data distribution and shard management.

9. How do you implement Transparent Data Encryption (TDE)?

Transparent Data Encryption (TDE) protects data at rest by encrypting data files. To implement TDE in Azure SQL:

1. Create a master key in the master database:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'your_password';

2. Create a certificate in the master database:

CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate';

3. Create a database encryption key and protect it with the certificate:

USE your_database;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert;

4. Enable encryption on the database:

ALTER DATABASE your_database
SET ENCRYPTION ON;

10. What are the different types of replication supported?

Azure SQL supports several replication types:

• Transactional Replication: Replicates changes from publisher to subscriber in near real-time.
• Merge Replication: Allows changes at both publisher and subscriber, merging them together.
• Snapshot Replication: Takes a snapshot of the entire database and applies it to the subscriber.
• Peer-to-Peer Replication: Allows multiple databases to act as both publishers and subscribers.

11. Explain how high availability is achieved in Azure SQL Database.

High availability in Azure SQL Database is achieved through features like automated backups, geo-replication, failover groups, and zone redundant configuration. Different service tiers offer varying levels of performance and availability.

12. What are the key security features available in Azure SQL Database?

Azure SQL Database offers security features such as:

• Authentication and Authorization: Supports Azure Active Directory (AAD) authentication and role-based access control (RBAC).
• Encryption: Provides data encryption at rest and in transit, including Transparent Data Encryption (TDE) and Always Encrypted.
• Advanced Threat Protection: Monitors and alerts for potential security threats.
• Network Security: Configurable firewall rules and private endpoints.
• Auditing and Compliance: Tracks database events for compliance and forensic purposes.

13. How can you optimize cost when using Azure SQL Database?

To optimize cost in Azure SQL Database:

• Choose the Right Service Tier: Select based on workload requirements to avoid over-provisioning.
• Use Elastic Pools: Share resources among multiple databases with varying usage patterns.
• Auto-Scale: Automatically adjust resources based on workload.
• Pause and Resume: Save costs by pausing databases when not in use.
• Monitor and Optimize Queries: Regularly optimize inefficient queries.
• Backup and Retention Policies: Balance cost and data recovery needs.
• Reserved Capacity: Purchase reserved capacity for predictable workloads.

14. Describe the different service tiers available in Azure SQL Database and their use cases.

Azure SQL Database service tiers include:

• Basic: For small-scale applications with light workloads, suitable for development and testing.
• Standard: For medium-scale applications with moderate workloads, ideal for business and web applications.
• Premium: For large-scale applications with high-performance needs, suitable for mission-critical systems.
• Business Critical: For applications requiring high performance, availability, and security.
• Hyperscale: For applications with large databases and high throughput requirements.

15. How does Azure SQL Database integrate with other Azure services like Azure Data Factory or Power BI?

Azure SQL Database integrates with Azure services like Azure Data Factory and Power BI for data management and analytics. Azure Data Factory enables data workflows, connecting to Azure SQL for ETL processes. Power BI connects directly to Azure SQL for real-time dashboards and reports, facilitating data-driven decision-making.