Differences Between SQL Azure Database and SQL Server in Azure VM

SQLServerF1

Microsoft is making huge investments in cloud technology and is betting big for future growth in this area. Microsoft has started providing cloud services for various products, applications, Operating System or hardware support. Based on the type of service, Microsoft has made available different offerings which include Infrastructure as a Service (IaaS), Platform as a Service (PaaS), and Software as a Service (SaaS). Microsoft cloud support many applications or products out of which SQL Server is one of the Microsoft product which is supported on cloud by Microsoft. There are two different kind of offerings provided by Microsoft for SQL Server, which are Infrastructure as a Service (IaaS), Platform as a Service (PaaS).

Among these two services Infrastructure as a Service (IaaS), Platform as a Service (PaaS) offered by Microsoft, we a DBA or management, we need to understand what best suits our requirement and based on that we can choose the offering. It is important to understand the differences between these two offerings available for SQL Server, so that we can make an informed decision. Each offering for SQL Server from Microsoft has its own advantages and disadvantages interms if features, cost, High Availability, Disaster recovery, which operations tasks are taken care by Microsoft, etc. Below are some of the differences between SQL Azure SQL Database (PaaS) and SQL Server in Azure VM (IaaS).

SQL Azure database as service is best suited for new applications designed or optimized for cloud solutions or which depend on other cloud technologies, so that they can be used together. Developers building software-as-a-service (SaaS) applications can use Azure SQL Database to provide flexibility to support both explosive growth and profitable business models. On the other hand with Infrastructure as a Service (IaaS) is for applications which are already existing ones and cannot be changed easily and need to be migrated on to cloud without much changes to be made. Applications which are dependent on other on-premise resources are best suited for Infrastructure as a Service (IaaS) compared to SQL Azure database as service.

SQL Azure database as service does not support all features available in SQL Server and has many limitations for each feature, many of the features cannot be controlled by DBAs or developers and there is not much control for DBAs for operational tasks like patching, backups, HA or DR. Infrastructure as a Service (IaaS) is just like on-premise SQL Server instance on which DBAss or developers can make configuration changes, performs backups/restores, use required technologies for HA and DR purposes, etc. There are some limitations with SQL Azure database as service like max database size supported as of 2015 is only 500 GB, but this limitation does not apply for Infrastructure as a Service (IaaS). Another limitation include, SQL Azure database as service does not allow resources to be accessed from Azure to on-premise. Infrastructure as a Service (IaaS) does not have any such limitations.

In Infrastructure as a Service (IaaS), hardware, its maintenance costs are eliminated as it is taken care by Microsoft. SQL Azure database as service also eliminates the costs mentioned before, in addition to that, in SQL Azure database as service also eliminates maintenance and administration efforts and costs of patching, backups, HA, DR, etc. in SQL Azure database as service, there are different services available to choose for High Availability and disaster recovery where Point in Time Restore, Geo-Restore, and Geo-Replication to increase can be chosen to increase business continuity. In Infrastructure as a Service (IaaS), the High availability and disaster recovery is to be taken care by DBAs and we can choose any technology of our choice and we are responsible for administering, monitoring and fixing any problems.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Different offers from Microsoft Azure for SQL Server

SQLServerF1

Cloud solutions has been gaining increased support and many customers moving their data on to cloud technologies or planning to move in future. The reason for increase in popularity of the cloud technologies include that it reduces the operational and maintenance cost of hosting the own hardware and its day to day maintenance. Many operation tasks such as backups, patching, etc can also be taken care by the cloud solutions depending on the kind of offering selected. Microsoft and other companies like Amazon are investing a lot on the cloud technologies and trying to provide features that match the on-premise servers and applications or more than that.

Microsoft is offering its cloud services in different types depending on the services offered. These include Infrastructure as a Service (IaaS), Platform as a Service (PaaS), and Software as a Service (SaaS). These offerings are generic and is applicable for different applications which include SQL Server too. Coming specifically to SQL Server, Microsoft is offering two services SQL Server on Azure VMs (IaaS) and SQL Azure Database (PaaS).
SQL Server on Azure VMs (IaaS) – This is similar to on-premise SQL Server running on a virtual machine which is running on a host system whose hardware is maintained in Microsoft’s data center. In on-premise servers, DBAs or management discuss and decide on the hardware required like CPU, Memory and storage and the Operating System required, on top of which DBAs install SQL Server instances and create or restore databases. in SQL Server on Azure VMs (IaaS), Microsoft provides required hardware(CPU, Memory, Storage) and provides a VM with required Operating System too. DBAs can install SQL Server instance on this VM, configure and manage and administer it just like an on-premise SQL Server instance. If any support is required related to hardware, then DBAs are required to contact the Microsoft support. This is best suited for applications which does not need much changes after moving to cloud technology and where management want more control on the SQL Server.

SQL Azure Database (PaaS) – SQL Azure on the other hand is a Database as a Service offering of SQL Server. This SQL Server runs on a VM which is maintained by Microsoft and this server will be hosted on Microsoft Data Center. In this offering DBAs does not need to install, or manage things like patches, backups, upgrades, etc, as these things are taken care by Microsoft team either through automated scheduled tools to by manual maintenance depending on the type of the task. There are many things which are offloaded from DBA like high availability, disaster recovery, patch maintenance, etc. If high availability is required, we need to choose the right offering and license which provides the high availability. Here we can choose which server needs which features and based on the features used and the usage the price will be decided. DBAs or managers get access to Azure portal where we can setup new SQL Azure instances, configure them, choose required features, and find the connection strings to be used in the application or to use in SSMS to connect to this instance locally from out laptop or other devices. From SSMS or other applications, we can create databases, tables, users, etc and use the databases to store and retrieve the data. This is best suited for new applications which just need access to data and does not want to spend time and efforts on maintaining SQL Server like backups, patches, high availability, etc. One of the major limitation in this is that the existing applications are required to be rewritten to able to work on SQL Azure.

During initial stages when the SQL Azure service was introduced, there were lot of limitations, so was not used much, but as in 2015, the offerings have increased a lot and can support many applications to move their data to SQL Azure and use it without much issues. However there are still many limitations, when compared to on-premise SQL Server instances, which almost does not have any limitations.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings