Azure SQL Database – Interview Q&A

Part 1 – Azure SQL Fundamentals

1. What is Azure SQL Database?

Priority: High

Azure SQL Database is a fully managed relational database service (PaaS) based on the SQL Server engine. Microsoft manages infrastructure, patching, backups, and built-in availability.

Key Points: PaaS, managed SQL, automatic backups.

2. What is the difference between Azure SQL Database and SQL Server on Azure VM?

Priority: High

3. What are the main Azure SQL deployment options?

Priority: High

• Single Database: Independent database with dedicated resource allocation.

• Elastic Pool: Multiple databases sharing compute resources.

• Managed Instance: Managed SQL Server instance with high compatibility.

• SQL Server on VM: Full SQL Server and OS control.

4. What is Azure SQL Single Database?

Priority: High

A Single Database is independently managed, scaled, and billed based on its configuration.

Key Point: Suitable for ASP.NET Core applications, APIs, and individual business databases.

5. What is Azure SQL Elastic Pool?

Priority: High

An Elastic Pool allows multiple databases to share compute resources, improving cost efficiency when workloads have different peak periods.

Example: 50 customer databases in a multi-tenant SaaS application.

6. What is Azure SQL Managed Instance?

Priority: High

Azure SQL Managed Instance is a managed SQL Server offering with support for many instance-level features such as SQL Server Agent and cross-database queries.

Key Point: Suitable for migrating existing enterprise SQL Server applications.

7. What is an Azure SQL Logical Server?

Priority: High

A Logical Server is an administrative container for Azure SQL databases. It manages login settings, firewall rules, and the database endpoint.

Key Point: It is not a physical server or VM.

Part 2 – Pricing and Service Tiers

8. What is the difference between DTU and vCore?

Priority: High

9. What are the Azure SQL vCore service tiers?

Priority: High

• General Purpose: Cost-effective for common business workloads.

• Business Critical: Low-latency, high-I/O applications.

• Hyperscale: Very large databases requiring scalable storage.

10. What is Azure SQL Serverless?

Priority: High

Serverless automatically scales compute based on workload demand and can auto-pause in supported configurations.

Key Point: Useful for development, testing, and intermittently used databases.

11. What is the difference between Provisioned and Serverless compute?

Priority: High

• Provisioned: Compute resources remain allocated continuously.

• Serverless: Compute scales automatically and may pause during inactivity.

Key Point: Provisioned is often suitable for continuously active production workloads.

12. What is Azure SQL Hyperscale?

Priority: Medium

Hyperscale uses distributed storage and separate compute components to support very large databases and rapid storage growth.

Key Point: Supports databases up to approximately 100 TB, subject to service limits.

13. Which Azure SQL tier is suitable for banking applications?

Priority: High

Business Critical is often appropriate for latency-sensitive financial transaction systems requiring high I/O performance and availability.

Key Point: Select the tier based on measured workload and business requirements.

Part 3 – Networking and Connectivity

14. How does Azure SQL Database connect to an application?

Priority: High

Applications connect through the SQL server endpoint using SQL authentication or Microsoft Entra authentication.

Key Point: SQL Server commonly uses TCP port 1433.

15. What is an Azure SQL Firewall?

Priority: High

Azure SQL Firewall controls which public IP addresses can access the database's public endpoint.

Key Point: Firewall access does not replace authentication.

16. What is Azure SQL Private Endpoint?

Priority: High

Private Endpoint provides private IP connectivity to Azure SQL through an Azure Virtual Network.

Key Point: Disable public network access separately for private-only connectivity.

17. What is the difference between Public Endpoint and Private Endpoint?

Priority: High

Part 4 – Azure SQL Security

18. What is Transparent Data Encryption (TDE)?

Priority: High

TDE encrypts database files, transaction logs, and backups at rest.

Key Point: Enabled by default for newly created Azure SQL databases.

19. What is the difference between TDE and Always Encrypted?

Priority: High

• TDE: Encrypts database files at rest.

• Always Encrypted: Encrypts selected sensitive columns using supported client drivers.

Key Point: Always Encrypted provides additional protection for sensitive application data.

20. What is Microsoft Entra Authentication?

Priority: High

Microsoft Entra authentication allows users and applications to authenticate to Azure SQL using Microsoft Entra identities.

Key Point: Supports centralized identity management.

21. What is Managed Identity in Azure SQL?

Priority: High

Managed Identity allows Azure applications to authenticate to Azure SQL without storing database passwords.

Key Point: Useful for Azure App Service and ASP.NET Core applications.

22. What is Dynamic Data Masking?

Priority: Medium

Dynamic Data Masking hides sensitive values in query results for users without unmasking permissions.

Example: Display XXXX1234 instead of a complete account number.

23. What is Azure SQL Auditing?

Priority: Medium

Auditing records database activities to help investigate access, changes, and potential security incidents.

Key Point: Audit logs can be sent to supported Azure monitoring and storage destinations.

Part 5 – Backup and Recovery

24. Does Azure SQL Database support automatic backups?

Priority: High

Yes. Azure automatically manages full, differential, and transaction log backups to support database recovery.

Key Point: No manual backup job is required for standard automated backups.

25. What is Point-in-Time Restore (PITR)?

Priority: High

PITR restores a database to a selected point within its backup retention period.

Example: Restore a database to 10:00 AM before an accidental table deletion at 10:30 AM.

26. What is the default backup retention period?

Priority: High

The common default short-term retention is 7 days, configurable up to 35 days for eligible Azure SQL Database configurations.

Key Point: Retention limits depend on the database configuration and offer.

27. What is Long-Term Retention (LTR)?

Priority: High

LTR retains selected database backups for long-term compliance, potentially up to 10 years.

Key Point: Supports weekly, monthly, and yearly retention policies.

28. What is Geo-Restore?

Priority: High

Geo-Restore recovers an Azure SQL database in another region using geo-redundant backups.

Key Point: Useful for disaster recovery when a primary region is unavailable.

29. What is the difference between RPO and RTO?

Priority: High

• RPO (Recovery Point Objective): Maximum acceptable data loss measured in time.

• RTO (Recovery Time Objective): Maximum acceptable time to restore service.

Example: RPO = 5 minutes, RTO = 30 minutes.

Part 6 – High Availability and Disaster Recovery

30. Does Azure SQL Database support High Availability?

Priority: High

Yes. Azure SQL Database includes built-in high availability through service-tier-specific redundancy and failover mechanisms.

Key Point: Availability architecture varies by service tier.

31. What is Zone Redundancy?

Priority: High

Zone Redundancy distributes supported database components across multiple availability zones within an Azure region.

Key Point: Helps protect against availability-zone failures.

32. What is Active Geo-Replication?

Priority: High

Active Geo-Replication asynchronously replicates a primary Azure SQL database to readable secondary databases in other regions.

Key Point: Supports regional disaster recovery and read workloads.

33. What is an Azure SQL Failover Group?

Priority: High

A Failover Group coordinates database failover between servers, typically across regions, and provides stable listener endpoints.

Key Point: Supports automatic failover policies and simplifies application reconnection.

34. What is the difference between Geo-Replication and Failover Group?

Priority: High

Part 7 – Performance and Monitoring

35. How do you monitor Azure SQL Database performance?

Priority: High

Use Azure Monitor metrics, Query Store, Query Performance Insight, and database execution plans.

Key Points: CPU, memory pressure, data I/O, blocking, query duration.

36. What is Query Store?

Priority: High

Query Store captures query execution plans and runtime statistics to help identify performance regressions.

Key Point: Useful for investigating queries that suddenly become slower.

37. What is Automatic Tuning?

Priority: High

Automatic Tuning recommends or applies supported improvements such as index management and forcing a previously better execution plan.

Key Point: Helps address query performance regressions.

38. What is Connection Pooling?

Priority: High

Connection Pooling reuses existing database connections instead of repeatedly creating new physical connections.

Key Point: Improves efficiency in high-traffic ASP.NET Core applications.

39. Why is retry logic important in Azure SQL?

Priority: High

Temporary connection failures can occur during scaling, maintenance, failover, and Serverless resume.

Key Point: EF Core supports transient-failure retries using EnableRetryOnFailure().

Part 8 – Real-Time Scenario Questions

40. Your ASP.NET Core application requires a managed SQL database. Which service would you choose?

Priority: High

Choose Azure SQL Single Database for managed infrastructure, automatic backups, and independent scaling.

41. You have 100 customer databases with different peak workloads. What is your solution?

Priority: High

Use Azure SQL Elastic Pool so multiple databases share compute capacity efficiently.

42. Your application requires SQL Server Agent and cross-database queries. Which option would you choose?

Priority: High

Choose Azure SQL Managed Instance after validating application compatibility.

43. Your database is idle for most of the day. How would you reduce costs?

Priority: High

Evaluate Azure SQL Serverless with auto-pause, considering connection-resume latency and storage charges.

44. An administrator accidentally deletes a production table. How do you recover it?

Priority: High

Use Point-in-Time Restore to create a database from a point before deletion, then recover the required data after validation.

45. How do you connect Azure App Service to Azure SQL without storing passwords?

Priority: High

Enable Managed Identity for App Service, create the appropriate database user, and grant minimum required permissions.

46. How do you prevent public internet access to Azure SQL?

Priority: High

Configure Private Endpoint, private DNS, and application VNet connectivity. Then disable public network access.

47. Your Azure SQL database must automatically fail over to another region. What would you use?

Priority: High

Use Azure SQL Failover Groups with an appropriate automatic failover policy and a stable read-write listener endpoint.

48. Your database performance suddenly becomes slow. How would you troubleshoot?

Priority: High

Check Azure Monitor metrics, Query Store, execution plans, blocking, missing indexes, and resource utilization before scaling.

49. Your organization must retain backups for seven years. Which feature would you choose?

Priority: High

Configure Azure SQL Long-Term Retention with an appropriate backup retention policy.

50. Your Azure SQL database CPU reaches 100%. Should you immediately increase vCores?

Priority: High

No. First investigate expensive queries, execution plans, indexes, and workload patterns. Scale compute when justified by sustained resource demand.