Comparison – SQL Database & SQL Server in Virtual Machine in Azure
This blog is based on the comparison of SQL database feature with SQL server installed in virtual machine in Azure. Which option is good or which one we need to opt to full fill our company\business requirements. There are multiple questions regarding this as –
- How can we setup database in Azure?
- What are the pre-requisite to work on Azure SQL Database?
- Can we install SQL Server and create databases there as we do in our premises on a server or VM?
- What are the different ways to create databases in Azure?
- What are the features available in SQL Database in Azure?
- What are the benefits to use SQL Database instead of SQL server in Virtual Machine? Etc. etc.
As Azure is progressing and capturing the IT market by leaps and bounds and companies also have started moving to Cloud and taking services from Azure.
Therefore such questions may arise in the minds of the Database Administrators regarding the databases and their creation/migration/deletion etc. etc.
To answers such questions I am writing this blog to help you out by clearing the doubts. Firstly I would like to tell you that there are two options to work on SQL databases in Azure:
- SQL Database in Azure as a service
- Create Virtual Machine and use SQL server by installing it
We can work on SQL databases by using either option however there are few differences that’s why both options are available in Azure. First option SQL Database in Azure give us almost all the features and we can use it Platform as a service (PaaS) but we can’t find all the features here.
To get all the features of SQL Server we have to create virtual machine and then install SQL Server into it as we do in our premises. If we compare SQL Database and SQL server in virtual machine, we will find a list of features not available in SQL database. Few common features which are not available in SQL Database as below:
- Windows Authentication
- Filestream data
- Database Mirroring
- Extended Stored Procedures
- SQL Server Agent/Jobs
- SSRS & SSIS doesn’t support.
SSRS- SQL server reporting Services
SSIS – SQL Server Integration Services
- Use statement is not supported. To change databases, a new connection must be established.
- Common Language Runtime (CLR)
These are few common features which are not available in SQL Database in Azure. To view the complete list please go through the Microsoft link
However the question is then why we need to use SQL Database instead of traditional SQL Server in VM.
The decision to use SQL Database or SQL Server in Virtual Machine can be difficult. There are many factors to consider when choosing between SQL Database and SQL Server in Azure Virtual Machine and these factors may be as database size, existing application versus new application, administration, business continuity etc.
SQL Database is often the right solution for cloud –designed application that are not using unsupported features and need near zero administration.
Azure virtual machine is often the right choice for new or existing application that require a high level of control and customization and which doesn’t require hardware maintenance on premise in future.
Using SQL Database in Azure
- Elastic Scale
- Predictable Performacne
- Business continuity
- Near-zero maintenance
- Low costCost Difference between SQL Database & SQL Server in VM
We can understand the cost difference as below:
Total Cost for SQL Server in VM = Windows Server Cost +SQL Server Licence Cost + Azure Storage cost
Cost for SQL Database = SQL Database is considered as a service and it is charged per hour basis. There is no cost for VM, licence etc.
Therefore I would like to state that it totally depends on the business needs (database size, application type, new vs old application, business continuity etc )whether we need to use SQL Database feature or SQL server in VM as we have seen benefits and explanation above. There are certain limitations with SQL Database feature in Azure while we can use all features of SQL Server without any limit in virtual machine.