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
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings