Comparison – SQL Database & SQL Server in Virtual Machine in Azure

Comparison – SQL Database & SQL Server in Virtual Machine in Azure

Summary:

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 –

  1. How can we setup database in Azure?
  2. What are the pre-requisite to work on Azure SQL Database?
  3. Can we install SQL Server and create databases there as we do in our premises on a server or VM?
  4. What are the different ways to create databases in Azure?
  5. What are the features available in SQL Database in Azure?
  6. What are the benefits to use SQL Database instead of SQL server in Virtual Machine? Etc. etc.

SQL Database

Explanation:

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:

  1. SQL Database in Azure as a service
  2. 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:

  1. Windows Authentication
  2. Filestream data
  3. Database Mirroring
  4. Extended Stored Procedures
  5. SQL Server Agent/Jobs
  6. SSRS & SSIS doesn’t support.

SSRS- SQL server reporting Services

SSIS – SQL Server Integration Services

T-SQL Features:

  1. Use statement is not supported. To change databases, a new connection must be established.
  2. 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

https://azure.microsoft.com/documentation/articles/sql-database-general-limitations/.

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.

Priorities:

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.

Advantages:

Using SQL Database in Azure

  1. Elastic Scale
  2. Predictable Performacne
  3. Business continuity
  4. Near-zero maintenance
  5. 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.

    Conclusion

    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.

    ——————————————————————————————————————————–Hire Me

Advertisements

About Mirza Husain

Mirza Husain is a senior consultant-database and having more than 10+ years of experience in the IT industry with different domains. He is fond of writing and speaking about SQL Server and also keen to learn new technologies. He is holding MCA degree and having Microsoft certifications as MCP & MCTS. He is also ITIL certified. In his past years, he worked with many clients as Microsoft, Bank of America etc. Currently he is associated with a France based client and offering his best services. You may reach him on his email id -- mirza_dba@outlook.com. Thanks!
This entry was posted in Azure, Database Administration, Q & A, Sql Server, windows azure and tagged , , , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s