This blog post will go over some of the Azure database administration interview questions and provide answers to them. If you are an aspiring Azure DBA, this blog can help prepare you for your next interview. We hope that these helpful tips and tricks help you land that first job as a DBA or even just get good practice for interviews!
Who is Azure DBA?
A Microsoft Azure DBA is an expert in Data Warehouse Administration. They are responsible for planning, design, and implementation of new data warehouses across the organization which will be used to store all sorts of different types of information from sources like Sales Intelligence Systems that provide real-time insights into customer behavior patterns so marketers can efficiently serve them more effectively at scale with personalized content aimed just where consumers want it most!
Q1.What are the components of Azure Virtual Network?
Answer: The following are the components of Azure virtual network Subnets VPN gateway(Virtual appliance) – Address space – DNS server.
Q2.What is an affinity group in Azure?
Answer: An affinity group ensures that there is a dedicated capacity for all services running on a particular virtual machine or availability set. It also brings two or more VMs under a common load balancer to ensure high availability for those VMs as well as any other VMs that might be running on the same node.
Q3.What are Azure Site Recovery (ASR) components?
Answer: The following are Azure site recovery (ASR) components: – Hyper-V host – Storage Replica server – ASR configuration server (primary and secondary sites) – Protection group with application VM, replica vhd/vhds, and storage account for replication.
Q4.What is a static route in Azure?
Answer: A static route is a route entry added to a routing table of Windows Azure virtual machine which specifies an interface and next-hop IP address for destinations outside the network of the Virtual machine, but it doesn’t advertise them on Azure Internal or public networks. Static routes can be added for Azure internal and Azure public networks only.
Q5.What are Azure storage services?
Answer: Azure storage services are as follows: – Blobs – Files – Tables – queues – Drives.
Q6.In which Azure environment can I use Azure Site Recovery (ASR)?
Answer: ASR can be used in the following Azure environments: – Azure Government Cloud-US Government Cloud-Azure Germany Cloud-Azure US DoD Cloud-Azure UK Public Sector Cloud.
Q7.What is an Azure storage account?
Answer: Azure storage account is a container for blob, file, and table data in Azure. Data that needs to be stored in Azure storage can be transferred into an Azure storage account and then it can be used as and when desired by Azure applications running on any platform such as Windows Azure, Linux Azure, etc.
Q8.What are the different types of SQL databases available in Azure?
Answer: The following are the different types of SQL databases available in Azure: – Basic (SQL Database)-Standard (SQL Database)-Premium (SQL Database) -Web edition-Business edition-Enterprise edition.
Q9.What is a network access protection list (NAPL)?
Answer: A network access protection list (NAPL) is a list of IP address ranges that are to be allowed or denied. It’s used to control network activity and the flow of traffic into and out of Azure virtual machines.
Q10.What Azure service can I use for data warehousing?
Answer: Azure SQL DW (SQL Data Warehouse) as azure service provides Azure DBAinterview questions and answers as well as azure virtual machine admin etc. Azure SQL DB helps to design, deploy and scale large relational databases in Azure with enterprise-level security. This service supports various versions like 2014, 2012, and 2008 R2 which enables users to choose the right version depending upon their needs and azure services, azure service fabric azure batch, and Azure cloud.
Q11.What is an Azure virtual machine?
Answer: Azure Virtual Machine allows customers to maximize their server utilization by letting them run up to 160 VMs per host on the Standard tier and up to 320 VMs per host on the Premium tier. Azure Virtual Machines provides three types of virtual machines so customers have a choice of creating applications using Windows or Linux operating systems. These different types of Virtual Machines for running Windows or Linux operating systems are Standard tier, Web or Business tiers, and finally, the premium tier with a scale set size of a maximum of 160 VM’s per node/host/server up to a maximum number of 32 GB’s of RAM per VM up to 32 vCPUs.
Q12.What Azure services are available for web development?
Answer: The Azure services for web development are as follows- Azure App Service-Azure Functions -Azure CDN Azure Active Directory B2C -Azure Search.
Q13.What Azure service is recommended for Java developers?
Answer: Azure service recommended for java developers is azure functions which allow using serverless computing to develop and run small pieces of code without having to manage the underlying infrastructure. Azure Blob Storage is another Azure service for Java developers, allowing them to store unstructured data like file systems, images, videos, etc. Azure App Service provides scalable application back ends for Azure websites, Azure service fabric, Azure batch, Azure cloud, and Azure web jobs.
Q14.What Azure services are available for big data analysis?
Answer: Azure services for big data analysis include- Microsoft R Server -HDInsight -PowerBI Premium -Azure Data Lake Analytics -Cognitive Services – Azure Stream Analytics -Azure Log Analytics azure service fabric azure batch azure cloud azure web jobs and azure mobile app.
Q15.What are the types of load balancing available in Azure?
Answer: The following are the different types of load balancing available in Azure: – Basic (Application Load Balancer)-Standard (Application Load Balancer)-affinity group based-Availability Set Based-Internal (Network Load Balancer) – Front End IP/Back End service model – Performance Level Application(HTTP) / Network(TCP) level.
Q16. What is the difference between a Web and Business edition of Azure SQL Database?
Answer: A Web Edition has a 10 GB max database size while a business edition has a 200 GB max size. Web editions can be easily migrated to business editions. Web editions do not support elastic pools, memory-optimized tables and stored procedures, table variables and temp tables, etc. You need to upgrade to either Standard or Premium tiers for these features in the business edition.
Q17. How does Microsoft handle patching on Azure SQL DB? What options are available?
Answer: Microsoft handles all billing-related changes/updates (like changes in storage pricing) automatically without any downtime. For non-billing-related changes like critical patches/updates, Microsoft will usually have a maintenance window of 2 hours. Customers can opt for extended maintenance windows as well.
Azure SQL DB supports patching using the following methods:
On-Demand – You can manually initiate a patching operation on your databases where the downtime is limited to only the time required for the patches to complete i.e. less than 20 minutes in most cases. There is no additional charge for this option – it is free of cost and you only pay for the compute hours consumed by this operation.
Scheduled – You can schedule hourly, daily or weekly recurring automatic patching operations that do not impact database availability during peak hours (except these scheduled downtime windows). Azure automatically manages to compute resource provisioning and scaling to ensure that target database performance SLA is not impacted during patching. Scheduled options are available in Basic, Standard, and Premium plans.
Automated – There is also a fully automated option to apply critical patches/updates within minutes without any downtime or additional cost. This requires appropriate monitoring of Azure SQL DB using integrated alerts and automation tools like Azure Automation runbooks, Azure Functions, etc., which should be configured as per customer’s requirements.
Q18. How do I configure replication from an on-premise SQL Server to Azure?
Answer: One can use Availability Group Listeners to establish a bidirectional replication channel between on-premise SQL server hosting the primary replica endpoint and subscriber databases hosted on higher editions of Azure SQL DB (BizTalk and Business Critical tiers) where the primary replica endpoint can be created as a read-only secondary. This is available only in Enterprise editions of Azure SQL DB.
Q19. Is it possible to automate the creation of replication endpoints without using scripts?
Answer: You can use Azure portal or Microsoft Visual Studio IDE to configure Availability Group Listeners and specify the Database User credentials (SQL Authentication) for creating endpoints on remote subscriber databases hosted in Azure. No scripting is required.
Q20.How does Security work in sync with Availability Groups?
Answer: Using AlwaysOn Availability Groups, you always have at least 2 replicas out of which one will be Active and others Passive – this guarantees the highest degree of data protection even when there are network disruptions between on-premise servers hosting endpoint replicas, Azure SQL servers, etc. – all Availability replicas are always up to date with the latest changes made on the primary replica.
Q21. How do I monitor resource utilization? Can I add custom metrics?
Answer: There are several built-in system metrics that can be monitored using Azure Portal, CLI, and SDKs, e.g CPU/Memory usage by Virtual Machine Scale Sets, etc. Also, one can use Azure Monitor to set alerts as well as monitor key performance indicators (KPI) such as the percentage of CPU utilization on a given VM instance or an entire virtual machine scale set.
Q22. How do I monitor my application’s throughput?
Answer: Azure Monitor provides comprehensive logging and diagnostic capabilities for all parts of Azure infrastructure from networking through storage – it captures statistical data about requests per second for a given web endpoint, TCP connections established by an app service, etc. Users can configure alerts for important KPIs using the diagnostics section in the portal. An alternative solution is to integrate Azure Application Insights with the application. Azure App insights provide real-time as well as historical analytics and also has a powerful alerting system that can be used to configure alerts on important metrics such as failed requests, average response time, etc.
Q23. How do I monitor SQL Server databases hosted in Azure?
Answer: Azure Monitor can be used to monitor the performance of SQL Databases – it’s possible to determine the number of reads/writes per second, most recent latency experienced by end-users, etc. For advanced monitoring capabilities, one can use System Center Operations Manager (SCOM) which integrates with Azure Monitor APIs. SCOM monitors services using Management Packs which are essentially sets of rules written using XML code or PowerShell for gathering relevant information from each supported service. In addition, SCOM provides alerting as well as centralized reporting capabilities.
Q24. How do I optimize Azure for my application?
Answer: One can use the Azure portal to configure VM instance count, storage account size, etc. for a given virtual machine scale set or a Virtual Machine through the role blade within each service – also, one can use the ‘Scale Sets’ feature of Azure Automation to dynamically manage to compute resources needed by a given web app based upon load – the same is achievable using ARM templates and PowerShell scripts. Currently, there are several built-in optimizations available with our most popular VM sizes. For example, A5 Virtual Machines have an optimization that balances vCores assigned among co-located cores on the physical host. This setting is managed by the hypervisor and cannot be changed.
Q25. How do I monitor the performance of SQL Databases?
Answer: One can use Azure Monitor to determine key system metrics such as the number of reads/writes per second, most recent latency experienced by end-users, etc. For advanced monitoring capabilities, one can use System Center Operations Manager (SCOM) which integrates with Azure Monitor APIs. SCOM monitors services using Management Packs which are essentially sets of rules written using XML code or PowerShell for gathering relevant information from each supported service. In addition, SCOM provides alerting as well as centralized reporting capabilities.
Q26. How do I back up my Azure SQL Database?
Answer: Backups of Azure SQL Databases are essentially full database snapshots taken by Microsoft via Azure Backup service. These backups are available for download from the Azure portal for 7 days, after which they are automatically deleted. To create your own backups one can use 3rd party tools such as DPM, Veeam, or similar that leverage SQL Server’s native backup APIs.
Q27. How do I connect to an on-premises SQL Server from Azure?
Answer: If the on-premises server is reachable via VPN or ExpressRoute connection it’s possible to establish a trusted connection between them which eliminates the need to open up ports in virtual networks. This is achieved by specifying ‘valid’ certificate thumbprints during the initial connection string creation. It would be possible to use dynamic ports as well but that would mean configuring firewall rules at both ends of the VPN connection – something that may not be feasible depending upon network setup and security policies. To create a trusted connection between VMs running Azure DbContext template should also be used to define a ‘master’ SQL Server connection string.
Q28. Can I connect from Azure SQL Database to an on-premises Oracle or MySQL database?
Answer: No, it is not possible to directly establish connectivity from Azure SQL Database instance to other databases – this includes Oracle, MySQL, etc. However if required one can always leverage a 3rd party tool e.g. CloudGateway which essentially provides a secure gateway for any DBMS via ODBC or JDBC drivers and have them running in your data center/private cloud while being managed by the same set of tools you use for all your other Azure resources. If access is restricted, one can just set up a VPN connection between the network and the CloudGateway VM image and all other servers/resources in the private cloud would be reachable.
Q29. How do I access Azure Storage from my VM?
Answer: Azure Storage can be accessed by mounting it as a drive, just like one would mount an SMB share or NFS export to the VM. Alternatively, it is possible to set up Azure Files shares which provide read-write access to Azure Blob storage via Server Message Block (SMB) protocol. This feature requires the latest version of Windows Server running on your VMs.
Q30. How do I know the capacity remaining for a storage account?
Answer: For a general overview, one can use a free service called Azure Storage explorer which allows you to not only see the total capacity but also how much each individual Blob or Table occupies currently. Alternatively, PowerShell commands such as Get-AzureStorageUsage will do the trick too.