Here is a modern interview question on SQL Server. In addition to general questions on administration and maintenance, Azure based questions have become common now because many maintain databases either in a VM hosted in cloud or as a Cloud-Managed-Database. Therefore, let's talk about an interview question related to Azure.
Here is the question. What are the factors you can use for determining whether the database has to be set up with a Virtual Machine hosted on Azure or set up as an Azure SQL Database?.
There is a very common answer for this question. If we want to get the entire database managed by Azure, such as Disaster Recovery, Backup, or Performance Tuning, then Azure SQL Database is the best. If we need to manage the instance by ourselves, then we need to go ahead with SQL Server on Azure Virtual Machine.
There is nothing wrong with the answer but it always better to know few more factors that can be used for deciding the way. Let's talk about some important factors.
Azure SQL Database is a relational Database-as-a-Service (DBaaS) (that falls under industry categories of Software-as-a-Service - SaaS and Platform-as-a-Service - PaaS) and SQL Server on Azure Virtual Machine is a Infrastructure-as-a-Services - IaaS. Azure SQL Database is completely managed by Microsoft, you do not need to worry about the Operating System, configuring hardware, service packs, patches. But if it is SQL Server on Azure Virtual Machine, then we need to manage everything by us. It is good if you have a team or dedicated DBA for managing the database and you need to be the administrator of it, not Microsoft.
Azure SQL Database Server is logical server though it can hold multiple databases. It is not considered as an Instance that you configure in SQL Server on Azure Virtual Machine. You know that, you can have multiple unrestricted administrators in an instance which is possible with SQL Server on Azure Virtual Machine but not possible with Azure SQL Database.
Another key thing you need to remember is, Microsoft makes most of new features available initially with Azure SQL Database before making them available with other versions. That is why you see features such as Treat Detection, Temporal Tables with Retention Policies only available with Azure SQL Database. In addition to that, this has an Intelligence Database Service that recommends possible optimizations for performance tuning.
Do not forget, Azure SQL Database is limited to 1 TB whereas instance of SQL Server on Azure Virtual Machine can consume 64 TB with multiple databases.
We do not need to worry on High Availability with DBaaS as it is provided with the service. With IaaS needs a manual set up with multiple VMs using clustering + Availability Group as High Availability is only for the VM, not for the SQL Server instance.
Azure SQL Database has no hardware and administrative cost where as SQL Server on Azure Virtual Machine has administrative cost. When it comes to License cost, Azure SQL Database is sold as a service based on multiple tiers and SQL Server on Azure Virtual Machine comes with a license but you can use your own license if you need.
If you need to use other components such as Integration Services, Analysis Services or Reporting Services, then SQL Server on Azure Virtual Machine is the only option though some services such as Analysis Services available as a managed service.
There are few more tiny reasons for picking one over other. But as an Interviewee, the mentioned ones should be known.
No comments:
Post a Comment