Interested in becoming an Azure SQL DBA but don’t know how to start? This article prepares you for the Azure SQL DBA interview by providing reference material links.
Introduction
Just like any other job, you need to prepare for an interview. The usual preparation includes practicing answers to questions, getting your resume up to date, and practicing mock interviews with a friend. One thing I like to emphasize to people is that they should use their time to prepare effectively.
If you are one of the professionals that have worked in an on-premises SQL environment, then you know that the concept of working in the cloud is way different. For those looking to take the next step as an SQL DBA and begin working as an Azure SQL DBA, you should know that there is a significant difference between the two platforms.
What Is Azure SQL?
Azure SQL defines Azure cloud-based managed, secure, and intelligent SQL Server database engine. We have the following Azure SQL solutions for SQL Server.
- Azure SQL Database
- Azure SQL Managed Instance (MI)
- SQL Server on Azure VMs
Reading Material:
- https://docs.microsoft.com/en-us/azure/azure-sql/azure-sql-iaas-vs-paas-what-is-overview
- Azure SQL Database vs SQL Server on Azure VMs
What is Azure SQL Database?
Azure SQL Database is an intelligent, scalable, managed relational database service for SQL Server. It optimizes query performance, durability using AI-powered features. It supports serverless compute architecture along with hyper scale storage for automatically scaling resources on remand.
What is the primary difference between Azure SQL Database and Azure SQL Managed Instance (MI)?
Azure SQL Managed Instance (MI) offers fully managed SQL instances as a service. It provides almost 100% SQL Server features and is suitable for most database migrations in the cloud. While Azure SQL Database is a database as a service and does not includes all SQL Server features such as SQL Server Agent, Database mail, PolyBase.
Reading Material:
- https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/sql-managed-instance-paas-overview
- Check out the Azure SQL Managed Instance video from the Azure SQL video series
Explain SQL Server on Azure VM and its use cases?
The SQL Server on Azure Virtual Machines (VM) enables users to deploy a full version of SQL Server without managing the underlying infrastructure. There is no difference from the DBA perspective in managing on-prem SQL Server and SQL Server on Azure VM, and the only difference is that your virtual machine is now in Azure infrastructure cloud.
You should use the SQL Server on Azure VM in cases:
- If you need complete control of SQL Server similar to on-prem SQL instance.
- If you use features not supported by Azure SQL Database, Azure Managed instances.
- Legacy applications migrations to Azure
- Optimized for lift-and-shift cloud migrations
- Extending On-premises Environment to the Cloud
- Development and Test Environment
Reading Material:
- Azure SQL Database vs SQL Server on Azure VMs
- https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/
- Video reference: https://channel9.msdn.com/Series/Azure-SQL-for-Beginners/SQL-Server-on-Azure-VM-Overview-4-of-61
Explain different purchasing models for Azure SQL Database?
Azure SQL has two purchasing models:
- Virtual core (vCore)-based purchasing model: The vCore purchasing model allows users to select compute independently and storage resources in the provisioned compute tier. It also provides a serverless tier that offers auto-scale compute resources based on a defined compute range. It is suitable for users who require flexibility, control, and transparency.
- Database transaction unit (DTU)-based purchasing model: In this model, Azure provides bundles of compute and storage packages. The DTU model is suitable for users who want simple, preconfigured resource options
Reading Material:
- DTU and vCore based models for Azure SQL Databases
- https://docs.microsoft.com/en-us/azure/azure-sql/database/resource-limits-vcore-single-databases
Is it possible to stop Azure SQL Database while it is not in use?
No, you cannot stop the Azure SQL Database whether it is actively used or not. However, in the serverless computer tier, you can configure automatically pause and resume based on workload activity. During the paused state, Azure does not charge for the compute resources.
Reading Material:
What are different DTU-based service tiers?
The DTU offers Basic, Standard and Premium service tiers.
What is the default backup retention in the DTU service tiers?
The maximum backup retention depends on the service tiers, and the basic has 7 days while the standard, premium service tier offers 35 days backup retention period.
What is the maximum database size in the DTU service tier?
The basic service tier supports a maximum of 2 GB database size, and the standard and premium support 1 TB and 4 TB databases.
Reading Material:
What are different service tiers in vCore based purchasing models?
The vCore purchasing model has General Purpose, Business Critical and Hyperscale service tiers.
What is different storage available in General Purpose, Business Critical and Hyperscale service tiers?
- General-purpose: It uses remote storage and supports up to 4 TB space.
- Business-critical: It supports local SSD storage between 5 GB to 4 TB space.
- Hyperscale: It uses SSD storage for buffer pool cache and local data usage. It also uses Azure remote storage for the long-term data store.
Reading Material:
Explain different connection policies for Azure SQL Database?
Azure supports the following server’s connection policy setting:
- Redirect: The client establishes a connection directly to the node hosting the database in the redirect policy. It reduces latency and improves throughput.
- Proxy: The proxy connection policy redirects connect through the Azure SQL Database gateways. Therefore, it increases the latency and reduces throughput.
- Default: The default policy uses Redirect for all client connections originating inside the Azure network and Proxy for all internet or outside Azure network traffic.
Reading Material:
What will happen if the database space used reaches the maximum data size limit?
Select, delete, drop, and truncate statements work fine if the database space reaches the maximum limit. The client receives an error message for the insert or update statements.
What is a server or database firewall for connecting to Azure SQL database?
To connect with Azure SQL Database, the client IP should be part of the Server or Database firewall.
- Initially, it checks the database firewall and grants database level access if the firewall rule permits.
- It checks for server firewall, and if firewall permits, the user gets Azure server access (all databases).
Reading Material:
What is automatic backup frequency for Azure SQL Database?
Azure takes full database every week, differential backup every 12 to 24 hours and transaction log backup every 5-10 minutes.
What is the Backup storage redundancy for Azure SQL Database?
We can choose storage redundancy between locally redundant, zone-redundant, or geo-redundant storage blobs. The default mode is geo-redundant storage.
Reading Material: Configure long-term backup retention for Azure SQL database
Can we have point-in-time recovery (PITR) for Azure database backups?
Yes, by default, it supports up to 7 days of PITR recovery.
What is accelerated database recovery for Azure SQL Database?
The Accelerated Database Recovery (ADR) feature redesigns the SQL Server database engine recovery process for improving database availability, especially for long-running transactions. It provides Instantaneous transaction rollback, Fast and consistent database recovery. By default, it is enabled for the Azure database, and users cannot disable it.
Reading Material:
- Accelerated Database Recovery and Long Running Transactions with Transaction Log Growth
- Accelerated Database Recovery; Instant Rollback and Database Recovery
How many replicas are maintained by Azure SQL Database?
Three, Azure SQL maintains three replicas as primary and secondary replicas. If the primary replica goes down, the secondary replica is promoted as primary.
Can we create SQL Server agent jobs in Azure SQL?
No, it does not support SQL Server agent jobs directly. However, you can automate things using the Azure functions or elastic jobs (preview).
What are different migration tools for migrating to Azure SQL Database?
Azure recommends the use of Azure Migrate, Data Migration assistant or Azure database migration service. You can use alternative tools such as transaction replication, Import Export Service\BACPAC, Bulk copy, Azure Data Factory, SQL Data Sync.
You can refer to https://docs.microsoft.com/en-us/azure/azure-sql/migration-guides/database/sql-server-to-sql-database-overview for migration tools comparison.
What happens when resource limits of Compute CPU are reached for Azure Databases?
If the Azure database compute CPU utilization reaches the threshold, you might notice latency in query results or query timeouts. You might need to wait for resources for executing a query.
What action can you perform if the situation depicted in question 21 occurs?
You can optimize queries to reduce CPU resource utilization of each query or upgrade the compute size of the database.
What are the capabilities offered by Azure SQL Database for recovering from an outage?
Azure offers the following capabilities:
- Active geo-replication
- Auto failover group
- Geo-restore
- Zone-redundant databases
Reading Material:
- Azure SQL database Geo-Replication
- Quick start guide to Geo-restore in Azure SQL Database
- Azure SQL Server auto-failover groups
- https://docs.microsoft.com/en-us/azure/azure-sql/database/high-availability-sla
- https://docs.microsoft.com/en-us/azure/azure-sql/database/recovery-using-backups#point-in-time-restore
Conclusion
In this article, we learned about basic interview questions for the Azure SQL Database. We’ll continue to explore more questions in our next article, so stay tuned! If you’re interested in reading more about Azure SQL, see our other blog posts about the topic.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023